Unanswered

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

(0) Share

Report

Posted on by 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

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

LaurensM 12,516 Moderator on at

Like (0)

Report
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!

Was this reply helpful? Yes No
nicktalsma 38 on at

Like (0)

Report

@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

Was this reply helpful? Yes No
LaurensM 12,516 Moderator on at

Like (0)

Report
@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!

Was this reply helpful? Yes No
nicktalsma 38 on at

Like (0)

Report
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' )

Was this reply helpful? Yes No
LaurensM 12,516 Moderator on at

Like (0)

Report

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!

Was this reply helpful? Yes No