cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rhiheu
Advocate III
Advocate III

Gallery to show latest value within groups of items

Hi,

I have a SharePoint list full of customer appointments. When a new booking is made it hits that list, but when a change to the booking is made (to change the time, date, to add a note etc) it also creates another row on that list. It will have the same appointment ID ('AptID'), but other details could be different. There also could be other appointments on that list for the same customer.

I would like to filter my gallery to show only the latest item for each appointment (not each customer) - so therefore the user can only select the most recent version of details from the other system, i.e. the most recently created Item for each AptID.

Any suggestions for the formula for this? And if it can be delegable too that would be amazing 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @rhiheu :

Do you want to get the latest record among the records with the same AptID?

My suggestion is to get the list of deduplicated AptIDs first, and then use Forall function to construct the collection of target records.

Pleas try this code:

 

ForAll(
    Distinct(
        'your SP list'.'AptID',
        'AptID'),
    LookUp(
        Sort(
            'your SP list',
            ID,/*In addition, because the ID is a standard field in the SP list, it is self-increasing and unique. Therefore, the record with the larger ID has a shorter creation time.*/
            Descending
        ),
        'AptID' = Result
    )
)

 

I've made a test for your reference:

My data source:'List A'

My 'AptID':Title

1.JPG

Best Regards,

Bof

View solution in original post

7 REPLIES 7
KrishnaV
Super User III
Super User III

Hi @rhiheu ,

 

You need to group the list data by APPID and then sort with the appointment date:

ClearCollect(collAppointments, GroupBy(SpListAppointments, "APPID","AppointmentDate"));
Sort(collAppointments,AppointmentDate,Descending);

Apply the above formula screen onVisible or onSelect of a button.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP, Microsoft Certified Trainer
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.

Hi @KrishnaV, thanks for your reply. I don't think I can use collections unfortunately, as my SharePoint list has around 10 000 items on it at any given time, with around 5 new items being created a minute (and a retention policy clearing out old ones each day).

Is there a way to do this without a collection?

Hi @rhiheu ,

 

If the database growth is that hight SharePoint list is not a good option you could have stored the data in CDS. There is no good workaround for your solution other than collection unless you move the data to CDS.

 

I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP, Microsoft Certified Trainer
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.

Hi @rhiheu ,

 

To best of my experience, I am suggesting you change the data source to CDS, if not down the lane performance will pulldown your app badly. we should not use the SharePoint list for such rapid data transactions.

 

I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP, Microsoft Certified Trainer
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.
v-bofeng-msft
Community Support
Community Support

Hi @rhiheu :

Do you want to get the latest record among the records with the same AptID?

My suggestion is to get the list of deduplicated AptIDs first, and then use Forall function to construct the collection of target records.

Pleas try this code:

 

ForAll(
    Distinct(
        'your SP list'.'AptID',
        'AptID'),
    LookUp(
        Sort(
            'your SP list',
            ID,/*In addition, because the ID is a standard field in the SP list, it is self-increasing and unique. Therefore, the record with the larger ID has a shorter creation time.*/
            Descending
        ),
        'AptID' = Result
    )
)

 

I've made a test for your reference:

My data source:'List A'

My 'AptID':Title

1.JPG

Best Regards,

Bof

View solution in original post

Hi @v-bofeng-msft, thanks so much for this! It looks like exactly what I need. I haven't had a chance yet to actually try it out in my app but I'll do so on Monday and let you know how it goes. Have a great weekend!

Hi @v-bofeng-msft, that worked thanks! I'll mark your response as the solution, but I have a follow-up question if that's ok? I then need to filter the gallery by CustomerID (inputted into a text box) and this doesn't seem to working. After typing in a customer ID to search, it has the little loading dots for a few minutes then doesn't show any results. This is the function I added to yours:

Filter(
ForAll(
    Distinct(
        'your SP list'.'AptID',
        'AptID'),
    LookUp(
        Sort(
            'your SP list',
            ID,/*In addition, because the ID is a standard field in the SP list, it is self-increasing and unique. Therefore, the record with the larger ID has a shorter creation time.*/
            Descending
        ),
        'AptID' = Result
    )
),
CustSearchTextInput.Text=CustomerID)

Do you know what I might be doing wrong? Considering how long it attempts the query for before not returning results, I wonder if I've just reached the capacity of how far I can push Power Apps with such a large SharePoint list.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza Winner Announcement

Please join us on Wednesday, July 21st at 8a PDT. We will be announcing the Winners of the Demo Extravaganza!

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,409)