Filtering Gallery to show Records with latest version ID.

[RBIowa1]

I have a SP data source that is driving a gallery with dynamic filtering. It is working correctly.  The records include an SPR_ID number (not SP ID) as well as a Modification Number (Mod No.). The latest version of a record will have the SAME SPR_ID number but the highest Mod No. As additional versions of a record are made, the Mod No. will increment but the SPR_ID number will remain the same. In the example below, I want to only include the record SPR_ID: 1007 AND Mod No. 1 in the gallery as this is the latest version of the record. Other records with no modifications will have a Mod No. of 0 and only have one record entry as a result.

(Data below is fictitious test data). 

Below is my filter query in the gallery items property where I SortByColumns by SPR_ID or by record Modified Date. A number of dynamic values are used in the filter from text boxes and drop down selections.

Thank you.

[Scott_Parker · Accepted solution]

Use a nested gallery. I'm acting on the assumption this should be delegable. The outer gallery will primarily be used to get the SPR_ID. The SPR_ID will be used in the inner gallery to display the highest Mod No. for that SPR_ID. The goal of the Outer gallery is to get down to one record for each SPR_ID. If you have a separate List with SPR_ID, you could use that as the source here.

Add this criteria addition to your above Filter(SPRData) expressions:

'Mod No.' = 0

Use this at the data source for your outer gallery. Put another gallery inside of the first. This inner gallery will contain all of your controls. Set the Items property to:

First(Sort(Filter(SPRData, SPR_ID = ThisItem.SPR_ID), 'Mod No.', Descending))

This will return the highest Mod No. record for each SPR_ID. Please note, this is going to result in a lot of traffic because there is a Filter for every row on the table. Additionally, filters on fields that might change when the 'Mod No.' does won't be reliable because the outer gallery is filtered to 'Mod No.' = 0. Based on what you have on your current filters, that doesn't look like it will be an issue.

I phrased my answer as starting from scratch, but since you have a bunch of controls in a gallery already you would probably want to:

1. Add a gallery
2. Change the new gallery's Items property to your existing gallery's Items property (including 'Mod No.' = 0 in the filter)
3. Copy the existing gallery into the new gallery and change its Items property to be the inner gallery.

[RBIowa1 · Accepted solution]

Thank you for your reply. This worked exactly as you portrayed.

[RBIowa1]

Thank you for your reply. This worked exactly as portrayed. Still.....would think some sort of formula would be helpful here from Microsoft as this is a very common scenario. I now have 3 nested galleries in my main gallery pointing to other data sources and now, this one to "filter" a filtered gallery items property. Thank you again.

[Scott_Parker]

The problem is really dependent on what your data source is. This is more of a limitation of SharePoint in some ways.  In database terminology, this is basically a self-join. If you were using SQL server or another database as the data source, you could set-up a view that did the join for you and just refer to that view from Power Apps.

[RBIowa1]

Since implementing this solution, it has now become necessary to start the Mod_No (actually SPR_Mod_No) other than a default of "0". For example, it may start at 4, and then increment by one for each successive 'modification'. I therefore need to look for the MINIMUM SPR_Mod_No value in the outer gallery instead of setting the filter to just look for SPR_Mod_No = 0. 

I have tried multiple options of using Min(SPR_Mod_No) however this does not resolve to the lowest SPR_Mod_No. It appears using First(Sort(.........),Mod_No, Ascending) will not work with my large Filter query. I have tried something like FirstN(Sort(.....),Mod_No, Ascending),1000) which is accepted (FirstN works but First throughs and error) but does not resolve the filtered table to the minimum SPR_Mod_No. ). I have also looked at nested Filters to no avail.

I am clearly missing a fundamental here and expect it to be a rather simple solution and will probably kick myself for not being able to figure it out!!

[Scott_Parker · Accepted solution]

There isn't a good option here. The point of using Mod_No = 0 was to have distinct Mod_No. There isn't a delegable way to get distinct values with Power Apps. Neither GroupBy() nor Distinct() are delegable.

---

@RBIowa1 wrote:

Since implementing this solution, it has now become necessary to start the Mod_No (actually SPR_Mod_No) other than a default of "0". For example, it may start at 4, and then increment by one for each successive 'modification'.

The root issue here is the back end. Frankly, your list already should be split into two lists (one for the SPR entity, the other for the Mod_No entities related to the SPR entity), so piling too many workarounds on top of each other isn't going to end well. You need to have some consistency in your backend data source. I don't know the situation here, but without some data discipline there is going to be continued headaches.

I can think of one janky solution. I'll tell you roughly how to do it, but I can't recommend using it. It will degrade the performance substantially depending on how many Mod_No you see normally. You can use a Flexible Height Gallery as the outer gallery instead of a normal gallery. Use the same data source you were originally (remove the Mod_No = 1). The filter will now return all Mod_No and the strategy changes to hiding the ones we don't care about. Change the Visible property of all controls within the outer gallery to a formula that evaluates to true for only a single one of the SPR numbers (doesn't have to be the minimum Mod_No):

LookUp(SPRData, ThisItem.SPR_ID = SPR_ID).ID = ThisItem.ID

This formula relies on the assumption that the record returned from the LookUp for given SPR_ID is consistent as we scroll further down the gallery.

To make the row spacing consistent in the flexible gallery, set property TemplatePadding = 0.

This is going to show some funny looking behavior while scrolling because items are going to be added and then immediately hidden again. The scrollbar will behave strangely sometimes.

[RBIowa1]

Thank you for the reply and clarification. This explains the underlying complexity of what I am attempting to do and why I was unable to find a solution for this. Given this, and not wanting to degrade performance of the app, I have resorted to a functional design change to accomplish the desired outcome by allowing an initial SPR entry (SPR_Mod_No default to "0") and then allow a onetime edit when the SPR_Mod_No is 0 to change this to the desired SPR_Mod_No and creating a new SPR record - ensuring there is always an SPR record with an SPR_Mod_No of 0 to satisfy the nested gallery sorting requirements.