Update all records in a gallery on one click

(0) Share

Report

Posted on by Kambro

Hi,

I have a gallery that functions as an editable table: Gallery3.

It is populated by collection: colTxnReviewFiltered

The user can make edits some some of the fields, and on clicking a button I can successfully patch the updates to the underlying SP list.

I have a field for each record in the gallery, GL_lookup. This doesn't exist in the collection but it serves to flag if there is a mismatch between a value in the collection and a 'suggested' value that is derived from another list. This field gets filled red or green if there is a match or not.

If(
 IsBlank(GL_lookup.Text), RGBA(300,0,0,1),
 GL_lookup.Text = GL_CLASS_dd.Selected.Value, RGBA(0, 200, 0, 1),
 RGBA(300,0,0,1)
)

I would like to be able to either filter or sort the gallery by the colour of the GL_lookup field. The column at the end is the field

I can't seem to be able to filter directly on that field (which makes sense as you it doesn't exist in the collection). I was thinking I could patch a 1 or 0 to a field in the collection and then sort on that but I am having a nightmare of a time working out how to patch values to the collection to begin with.

I thought I could use a variation of the patch/forall function I having working for other updates in this gallery:

But I get an error that the datasource is invalid if I use the collection (colTxnFilterREview) in lieu of 'CC Txns - template' which is a SP list.

Patch(
 'CC Txns - template',
 ForAll(Gallery3.AllItems,
 {
 ID: ID,
 //APPROVALSTATUS: Figure out issue with approval status
 //Figure out how to update approver
 DESC: GL_DESC_dd.Selected.Value,
 CLASS: GL_CLASS_dd.Selected.Value,
 BRANCH: Dropdown18.Selected.Title,
 DEPT: Dropdown18_1.Selected.Title,
 HAS_GST: If(GST.Text <> Blank(), true, false),
 GST: If(GST.Text <> Blank(), Value(GST.Text), Blank())
 }
 )
)

Any help would be greatly appreciated.

Cheers

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (0)

Sort by

Scott_Parker 1,090 on at

Like (0)

Report
Copy link

Link copied!

You shouldn't need to do any patching. I don't have a complete picture: what is your Gallery.Items currently? That would help with the answer.

If I'm understanding you correctly, I would use AddColumns() to add GL_lookup value inside of the Gallery.Items property and then sort on the GL_lookup. Whatever the ordering of the records is inside Gallery.Items is what you'll see on screen.

Was this reply helpful? Yes No
Kambro 80 on at

Like (0)

Report
Copy link

Link copied!

Hi Scott,

The Gallery.Items property = colTxnReviewFiltered.

By use AddColumns() to add GL_lookup value inside of the Gallery.Items property, do you mean use AddColumns() to the collection colTxnReviewFiltered or is there some other way?

Was this reply helpful? Yes No
Kambro 80 on at

Like (0)

Report
Copy link

Link copied!

Actually, could you explain how to use Addcolumns() to add the values? I'm not sure how to do it.

Cheers
K

Was this reply helpful? Yes No
Scott_Parker 1,090 on at

Like (0)

Report
Copy link

Link copied!
Sure, I mean something like:
Gallery.Items = Sort( AddColumns( colTxnReviewFiltered, "GL_lookup", <whatever you're doing to get GL_lookup> ), If(GL_lookup = CLASS, 1, 0), SortOrder.Descending )
This adds the GL_lookup field onto the table you retrieve from the collection. Then it sorts based on a formula that is evaluated for each record of the table. This formula can make use of any column in the table, including the GL_lookup column that we just added on.

Was this reply helpful? Yes No
Kambro 80 on at

Like (0)

Report
Copy link

Link copied!
Hi @Scott_Parker

I guess the 'whatever you're doing to get the GL_lookup' part is what I'm struggling with.

I'm not sure if this helps paint a clearer picture but here is a bit more info:

GL_lookup is the name of the control;
Its Text property is:
First( Filter( 'GL category lookup reference', Description in ThisItem.MERCHANT ) ).Classification

The fill property is:
If( IsBlank(GL_lookup.Text), RGBA(300,0,0,0.5), GL_lookup.Text = GL_CLASS_dd.Selected.Value, RGBA(0, 200, 0, 1), RGBA(300,0,0,0.5) )

I've tried so many different things to get this working - surely there is a solution! It's driving me nuts!

Cheers

Was this reply helpful? Yes No
Scott_Parker 1,090 on at

Like (0)

Report
Copy link

Link copied!
I meant to substitute what you have in the control.text property into that part of the code. So:
Gallery.Items = Sort( AddColumns( colTxnReviewFiltered As xItem, "GL_lookup", LookUp('GL category lookup referece', Description in xItem.MERCHANT).Classification ), If(GL_lookup = CLASS, 1, 0), SortOrder.Descending )
Also, the LookUp function is essentially a First(Filter()) in one function, so I used that instead.

Was this reply helpful? Yes No
Kambro 80 on at

Like (0)

Report
Copy link

Link copied!
Ah cool - that works! Thank you.

How would I get the First(Filter()) version to work? I ask because I am likely to encounter delegation issues with the 'in' operator. If the 'GL category lookup reference' list is below 500 records, would I still encounter the delegation issue, or in this instance would that only be a limiting factor if the number of records in the gallery exceeded 500 (or 2000 if thats the upper limit from memory?).

Wrt to the First(Filter()) option I tried:
Sort( AddColumns( colTxnReviewFiltered As xItem, "GL_lookup", First(Filter('GL category lookup reference', Description = xItem.MERCHANT)).Classification ), If(GL_lookup = CLASS, 1, 0), SortOrder.Descending )

And that didn't populate the GL_lookup column.

If I did this slightly different way below, the If(GL_lookup = CLASS, 1, 0) part throws an error that I cannot compare a record and text?
Sort( AddColumns( colTxnReviewFiltered As xItem, "GL_lookup", First(Filter('GL category lookup reference', Description = xItem.MERCHANT).Classification) ), If(GL_lookup = CLASS, 1, 0), SortOrder.Descending )

Thanks again for your help 🙂

Was this reply helpful? Yes No
Scott_Parker 1,090 on at

Like (0)

Report
Copy link

Link copied!
LookUp and First(Filter()) are going to get you the same result in the end. They are functionally identical, except that Filter() is returning a whole bunch of extra records that aren't kept. Both delegate, no difference there.

In your second code example, you 1) get the .Classification column of returned table; then 2) get the First() row of that single column table. That row is a record. You would need to do .Classification again to get the text value out of the record. You'll end up with the same value as before, just with more verbose code.

Regarding the "in" keyword and delegation, it comes back to why xItem.Merchant is an array in the first place? And why are you getting all of the matching values (Description in xItem.Merchant) to then only keep a single one of them? Assuming xItem.Merchant is an array, another option is a to execute the LookUp() multiple times. Note that will significantly increase your API calls:

ForAll(xItem.Merchant As xMerchant, LookUp('GL category lookup reference', Description = xMerchant))

Was this reply helpful? Yes No