Distinct and Sort

[Davy1]

Hi Experts,

I have a SharePoint list called history, which looks similar to below. I have a gallery with a search box to filter the values in gallery. I would be entering the name of a person in the search box and the gallery should filter and give what device(s) is currently being used by the respective person

Device	Name	Date
MS-1	John	10/8/2022
MS-1	David	9/7/2022
MS-1	Scott	8/24/2022
MS-1	John	8/16/2022
MS-2	David	11/5/2022
MS-2	Mark	4/18/2022
MS-2	Matt	6/8/2022
MS-2	John	7/6/2022
KB-1	Scott	5/8/2022
KB-1	Mark	4/5/2022
KB-1	David	9/8/2022

• If I type John, it should return (MS-1;John;10/8/2022), because he is the last user of the MS-1 device, though he has used the same device before on 8/16/2022, it should not show up that record. M-2 on John's name should also not show up since he is not the last user.
• If I type David (MS-2;David:11/5/2022) and (KB-1;David;9/8/2022) should be displayed and no other line, since he is last user of these devices
• If I type Scott, nothing should show up at all since he is currently not the last user of any device, so should be with Matt and Mark.
• By default, the gallery should just be showing last user of each device based on the date - I think this is what I have to address first and all the above will fall in place. right now, the gallery is showing everything.

Please help, thanks in advance for your help.

[WarrenBelz]

Hi @Davy1 ,

I am not sure this will do exactly what you need, but it should show each device and the last user and date

AddColumns(
   GroupBy(
      Sort(
         YourListName,
         Date,
         Descending
      ),
      "Device",
      "Data"
   ),
   "Name",
   First(Data).Name,
   "Date",
   First(Data).Date
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Davy1]

Hi @WarrenBelz ,

Thanks for your response, please confirm if I should be putting this on the Items property of the Gallery. Also, the formula has "data" and "device" should i just add them as is, there is a comma after "data" and then a parenthesis, please let me know if its intentional.  Apologies I am not aware of it, thanks in advance for your response.

[WarrenBelz]

@Davy1 ,
Commas fixed - and yes, it is the Items of the gallery.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Davy1]

@WarrenBelz thanks for the help again. The formula works really well but the Device is a lookup column and i am not able to use it in the formula. I am sincerely sorry, i should have told you earlier. Also, i think its time to say the Name is a person column if it helps. Apologies, thanks for your help in advance.

[WarrenBelz · Accepted solution]

@Davy1 .

Yes, that is very important (actually essential) information if you want to solve this. I do not (for very good reasons) use either column type and I also have to assume that Date (a very bad name for a field) is a Date/Time column. Try this

AddColumns(
   GroupBy(
      AddColumns(
         Sort(
            YourListName,
            Date,
            Descending
         ),
         "DeviceName",
         Device.Value
      ),
      "DeviceName",
      "Data"
   ),
   "Name",
   First(Data).Name.DisplayName,
   "Date",
   First(Data).Date
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Davy1]

@WarrenBelz Thank you very much, it worked. God bless you. 

Just curious, wanted to understand what approach you would use recommend using instead of lookup, below is the base design model I have for the app, how this could be redesigned. Thanks in advance for your help

I have two SharePoint List Asset Manager and Asset History.

Asset Manager

• Asset Tag
• Manufacturer
• Model
• Purchase date
• Status

Asset History

• Asset Tag (Lookup Column in SharePoint on the Asset Manager list to Asset Tag)
• Assigned To
• Assigned on

The Asset Tag in the Asset History is a lookup column to Asset Manager and all the below could be for one asset

[WarrenBelz]

Hi @Davy1 ,

You simply do the query in Power Apps. SharePoint is not a relational database, so any queries that are relational are not Delegable. If you are looking to use the field in a Combo Box, simply use the other (looked up) list and field in the Items

ListName2.LookedUpField

and then write back to a Text field in the main list - Update

CBName.Selected.LookedUpField

DefaultSelectedItems (Combo Box)

{LookedUpField: ThisItem.TextFieldName}

You can also Sort on the Text field in a Delegable manner and do not need the "code gymnastics" from your example in GroupBy.

If you are looking to get another field from the second list "belonging" to the one chosen, do a LookUp (in Power Apps)

LookUp(
   SecondList,
   Field1 = DDName.Selected.Field1
).Field2

which is fully Delegable. I also have a blog on getting Distinct values from another list in a Combo Box in a Delegable manner.

Unless you want to have drop-downs in an "edit in grid view" in SharePoint (and you can replace this with an editable gallery in an integrated app), I have never found any need across hundreds of at times large and complex apps to use these columns.

[Davy1]

@WarrenBelz thanks for your response.

Please correct me if i am wrong, what you are recommending is that the lookup column that i have in the Asset history should just be a plain text field and the person field as well.

[WarrenBelz]

@Davy1 ,

Yes for the lookup definitely. Person columns have their uses, but have some of the same limitations  (Sorting in particular). I tend to store the user's email and/or name in text fields and get anything else I may need back in Power Apps with the Office365Users connector.