cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cindycharrett
Regular Visitor

Display max value from list when an item is selected from a gallery

I'm building an app for a silent auction. I have a SP list for the items and another list for bids (Bids_DOTW). I'm using a patch to connect and update the lists. I need help to display the current bid and person that placed it, based on the item that is selected from the gallery (Screen3).  I have used Collect(MaxBidAmount,Bids_DOTW) in the OnStart and Sort(Filter(MaxBidAmount, ItemName), "BidAmount",Descending) in the Items of the Gallery.  I'm wondering if this should actually just be a single label though.  Any help would be greatly appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @cindycharrett :

Do you want to find the item with the largest BidAmount among all types of items.

I assume BidAmount is a Number column.The point is Using GroupBy() function. I've made a test for your reference:

Set the gallery's items property to:

Filter(
    Bids_DOTW,
    ID in AddColumns(
        GroupBy(
            Bids_DOTW,
            "ItemName",
            "NewGroup"
        ),
        "ID",
        LookUp(
            NewGroup,
            BidAmount = Max(
                NewGroup,
                BidAmount
            )
        ).ID
    ).ID
)

1.JPG

Best Regards,

Bof

View solution in original post

7 REPLIES 7
v-bofeng-msft
Community Support
Community Support

Hi @cindycharrett :

Do you want to find the item with the largest BidAmount among all types of items.

I assume BidAmount is a Number column.The point is Using GroupBy() function. I've made a test for your reference:

Set the gallery's items property to:

Filter(
    Bids_DOTW,
    ID in AddColumns(
        GroupBy(
            Bids_DOTW,
            "ItemName",
            "NewGroup"
        ),
        "ID",
        LookUp(
            NewGroup,
            BidAmount = Max(
                NewGroup,
                BidAmount
            )
        ).ID
    ).ID
)

1.JPG

Best Regards,

Bof

View solution in original post

Thank you so much @v-bofeng-msft!  This is exactly what I was looking for!  I'm also wondering if there is a way to filter these items based on the previous Gallery_DOTW item selected on the previous screen.  
I really appreciate your help. 

Cindy

Hi @cindycharrett :

Yes.If you want to further filter the results, please refer to the following formula:

Filter(
    Bids_DOTW,
    ID in AddColumns(
        GroupBy(
            Bids_DOTW,
            "ItemName",
            "NewGroup"
        ),
        "ID",
        LookUp(
            NewGroup,
            BidAmount = Max(
                NewGroup,
                BidAmount
            )
        ).ID
    ).ID && Your Filter Condition
)

Best Regards,

Bof

@v-bofeng-msft , thank you for responding so quickly!  I think the issue I'm having is that I'm trying to filter on a gallery that is selecting items from another SharePoint list (not the bid list).  When I try using && Gallery_DOTW.Selected.Item it doesn't recognize it because the Gallery points to the bid items.  Is there a way around this?

Thank you for your help.

Cindy

Hi @cindycharrett :

&&Gallery_DOTW.Selected.Item is wrong syntax.

Please try:

 

ID=Gallery_DOTW.Selected.ID

 

Best Regards,

Bof

@v-bofeng-msft , This has been super helpful!  Is there any way to get around the "Delegation warning:  The highlighted part of this formula might not work correctly on large data sets".  I have already increased the Data Row limit to 2000.  Would using variables help with this and what would that look like?  Sorry for asking so many questions.  I'm still new to PowerApps.

 

Filter(Bids_DOTW, ID in AddColumns(GroupBy(Bids_DOTW,"ItemName", "NewGroup"), "ID", LookUp(NewGroup,BidAmount = Max(NewGroup,BidAmount)).ID).ID && ID = Gallery_DOTW.Selected.ID)

 

Thank you!

Cindy

Hi @cindycharrett :

Because this formla uses a lot of functions and operators that cannot be delegated.

If the number of items in your data source is less than 2000, you can ignore the "Delegation Warning".

Else you must consider saving the records in the SharePoint list into a collection in batches and then use the collection as your data source .

For example:

Step1:Saving the records in the SharePoint list into a collection

ClearCollect(TheCollection,Field1="Type1");
Collect(TheCollection,Field1="Type2");
Collect(TheCollection,Field1="Type3");
Collect(TheCollection,Field1="Type4");
……

Step2:Modify the code

Filter(
 TheCollection, 
 ID in AddColumns(GroupBy(TheCollection,"ItemName", "NewGroup"), "ID", LookUp(NewGroup,BidAmount = Max(NewGroup,BidAmount)).ID).ID && ID = Gallery_DOTW.Selected.ID)

Best Regards,

Bof

 

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 (71,015)