Unanswered

Show latest record for each unique value in a different column

(0) Share

Report

Posted on by Oboecil66

I have TWO SharePoint lists. One is Projects and one is ProjectUpdates. Projects contains one row per Project (with fields for ProjectName, ProjectManager, Annual Budget) and ProjectUpdates (with fields like BudgetStatus, ScheduleStatus, BudgetSpent, etc.) contains one row for each monthly update of each project. So there are multiple rows in ProjectUpdates for each row in Projects. The two tables are linked through a Lookup field in ProjectUpdates that references the ProjectName field in the Projects table.

This configuration has caused me much heartache, and I'm open to chucking it all and putting all the data in one list if it will simplify my life. But my soul cringes at the thought of duplicating all the "one" data in the "many" table over and over again!

My particular problem is that I want to get the last (most recent) update from the ProjectUpdates table for each project in the Projects table and use the data from all the fields in that record. (If it helps, remember that the ProjectName field is also present as a Lookup in ProjectUpdates). I don't know what combination, in what order, of Filter, Sort, Lookup, GroupBy, etc. will product the desired result.

What complicates the situation (I think) is that I'm doing this in Galleries where the Gallery is based on the Projects table (to get one Item per project) but the gallery also contains fields from ProjectUpdates.

I'm VERY new at Power Apps (although I spent many years working in IT before moving to my current non-IT job 11 years ago), so please have mercy when you share your ideas! Thanks so much.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Sort by

elseb 774 Moderator on at

Like (1)

Report
Copy link

Link copied!

Hi,

I wouldn't abandon the multiple lists idea, it's a very nice way to keep the data separate and tidy.
I've created a project management app myself and i use few lists for it couple of which are very similar setup as yours.

if your want to display last record from the list in a gallery based on selection from another it will be pretty easy, I would recommend using some form of ID field in the future as opposed to the name of the project to track it, that way it will be possible to adjust the name of the project if you ever need it.

what you want to do is:
In the first gallery display your Projects and in second second:
Last(filter(ProjectUpdates, unique_id=MasterGallery.selected.unique_id))
Seb

Was this reply helpful? Yes No
Faisal__Wasim 56 on at

Like (0)

Report
Copy link

Link copied!
Hi @Oboecil66 ,

You can get your desired results by using two Galleries, One for Projects and the Other for ProjectUpdates,
In the first Gallery Items Property you will only pass the Projects SharePoint List and show Projects,

In the second Gallery's Items Property you will use the following code to get your Data:

Last(Filter(ProjectUpdates,ProjectName.Value = ProjectsGallery.Selected.ProjectName))

I have Included example images as well.

Do let me know if you see any challenges, I am always happy to help.

Regards,
Faisal

if this reply/solution works Mark it as the solution and give a thumbs-up.

Was this reply helpful? Yes No
Oboecil66 28 on at

Like (3)

Report
Copy link

Link copied!

@Faisal__Wasim , @elseb , I am an idiot! LOL

I was way overcomplicating this. It didn't dawn on me that the formula I applied was being evaluated for EACH row in my gallery. So I didn't need to do any grouping, etc.

Here is my working formula!
LookUp(
SortByColumns(ProjectUpdates,"ID",SortOrder.Descending),
ProjectName.Value=ThisItem.ProjectName,
Text(Spent,"$#,##0", "en-US"))

Thank you both very much for your amazingly fast responses. Now I'm going to post another one about summing the values displayed in the gallery - not the underlying data source.

Was this reply helpful? Yes No
Oboecil66 28 on at

Like (1)

Report
Copy link

Link copied!

BTW, here is the current state of my screen with dummy records. I learned how to create this kind of "table" gallery in an amazing video from @RezaDorrani . The link to the video: https://www.youtube.com/watch?v=ubnPqwWdBL0

Was this reply helpful? Yes No