2 tables in one gallery but view different titled columns

Question

Morning all,

I am creating an app from 2 SPL. The fist list is an action item tracker, and the second list contains all the updates for those items. (this is to keep all historic updates and not loose data). On the front page of the app, using a gallery, I want to view 3 columns from data source A, and 3 columns from source B. None of the columns have the same name, but all need to be joined on the AI ID to so correct data. Ultimately if the collection could function as a place to pull data from both tables together, or joined, on the AI ID that would be ideal and allow other sections of the app to function better as well.

Question 1: Collection? or Filter? or other? Question 2: correct syntax to join data and show DIFFERNT columns.

TIA

ClearCollect(MergCol,ShowColumns('Logistics Action Tracker FY24', "Action Items ID", "Summary of Action", "Point of Contact)ShowColumns('Action Items Update',"Update Comments", "Update Date", "Updated by")

formula was 'broken' and would not access the correct records? I think. I was hoping this would create a collection that would become a single source to access both SPL. If I could just join both lists on the ID and have all columns that would be acceptable too.

Answer 1

To achieve this, you can use the ClearCollect function along with the LookUp function to join the data from both lists based on the ID. Here's how you can do it:

ClearCollect(
    MergedCol,
    AddColumns(
        'Logistics Action Tracker FY24',
        "Update Comments",
        LookUp(
            'Action Items Update',
            'Action Items ID' = 'Logistics Action Tracker FY24'.'Action Items ID'
        ).'Update Comments',
        "Update Date",
        LookUp(
            'Action Items Update',
            'Action Items ID' = 'Logistics Action Tracker FY24'.'Action Items ID'
        ).'Update Date',
        "Updated by",
        LookUp(
            'Action Items Update',
            'Action Items ID' = 'Logistics Action Tracker FY24'.'Action Items ID'
        ).'Updated by'
    )
)