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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (45,603)