How to Append the Latest Creation Date From a SharePoint List to an Existing Collection

[nicktalsma]

I have a collection of data 'FacilityCollection' that contains a list of unique facilities (unique identifier is 'facility_id'). I would like to show the entire 'FacilityCollection', but add a column that shows the most recent/max 'last_updated' date from the SharePoint list 'sp_form_facility' (matching 'facility_id' from 'FacilityCollection' to 'current_fac_id' from 'sp_form_facility'.

Basically, I am trying to provide a list of all facilities and see when is the last time they sent us an update or show a blank in the 'last_updated' field if no update has ever been sent.  See the 'Desired Output' table below for an example of what I am looking for. 

Any help would be greatly appreciated! Thank you!!

FacilityCollection

facility_id	location
1	Chicago
2	New York
3	Memphis
4	Tampa
5	Miami
6	Tempe
7	Billings
8	Atlanta
9	Dallas

sp_form_facility

current_fac_id	created_date	update_notes
1	1/1/2024	Testing
1	1/2/2024	Hello
4	1/3/2024	Hi
2	1/4/2024	Test
4	1/5/2024	Bye

Desired Output 

facility_id	location	last_updated
1	Chicago	1/2/2024
2	New York	1/4/2024
3	Memphis
4	Tampa	1/5/2024
5	Miami
6	Tempe
7	Billings
8	Atlanta
9	Dallas

[LaurensM · Accepted solution]

Hi @nicktalsma,

The following code should do the trick:

AddColumns(
    //Add column called last_updated
    FacilityCollection,
    "last_updated",
    //Fetch the last created record's date for this facility id
    First(Sort(Filter(sp_form_facility, facility_id = current_fac_id), 'created_date', SortOrder.Descending)).'created_date'
)

If this solves your question, would you be so kind as to accept it as a solution.
Thanks!

[nicktalsma]

@LaurensM that worked perfect!  A little slow, but I figure I can solve for that by filtering 'FacilityCollection', as there are about 1,000 records in that collection. 

One more question, which I assume is probably simple... How do I also add the 'update_notes' from the most recent record from sp_form_facility? Should like like below output. 

Thank you so much!!!

Desired Output 

facility_id	location	last_updated	notes
1	Chicago	1/2/2024	Hello
2	New York	1/4/2024	Test
3	Memphis
4	Tampa	1/5/2024	Bye
5	Miami
6	Tempe
7	Billings
8	Atlanta
9	Dallas

[LaurensM]

@nicktalsma,

To avoid multiple API calls within the same record I will swap the AddColumns to ForAll, allowing us to use the With function:

ForAll(
    FacilityCollection,
    With(
        {
            wFacilityForms: Sort(Filter(sp_form_facility, facility_id = current_fac_id), created_date, SortOrder.Descending)
        },
        {
            facility_id: facility_id,
            location: location,
            //Fetch both the notes & date of the most recent record
            'last_updated': First(wFacilityForms).'created_date',
            notes: First(wFacilityForms).notes
        }
    )
)

Should you have a lot of facilities (even after filtering), it may be beneficial to store the most recent note & update date within your Master Data list (each loop is an API call to your sp_form_facility list).

In other words, when a new sp_form_facility is added, it updates the master data facility list for those 2 fields. This allows you to simply display the Master Data list instead of merging this with the most recent data.

I hope this helps!

[nicktalsma]

Hmm, isn't ForAll non-delegable? 

If using your original solution, would this be the correct syntax for adding the most recent notes column? Thank you!

AddColumns(
    //Add column called last_updated
    FacilityCollection,
    "last_updated",
    //Fetch the last created record's date for this facility id
    First(Sort(Filter(sp_form_facility, facility_id = current_fac_id), 'created_date', SortOrder.Descending)).'created_date',
    "notes",
    First(Sort(Filter(sp_form_facility, facility_id = current_fac_id), 'created_date', SortOrder.Descending)).'update_notes'
)

[LaurensM]

Hi @nicktalsma,

Delegation-wise there is no difference between AddColumns & ForAll. Both will return a table of records with the size dependent on your Data Row Limit. The Filter within the ForAll is delegable.

Your code indeed is correct. 😊 Keep in mind that AddColumns requires 2 seperate Filter functions whereas the ForAll would only fetch the data once.

I hope this helps!