For all Patch, only updating one record (collection part lookup)

[william_nr3]

I'm building an estimating app where the user can select accessory colors before converting the estimate to a contract. I have it set up where parts display in a gallery from a collection that gets created when the page loads. I am doing it this way so that qty, part numbers, cost, customer cost are persevered while material name, part number, and color can be updated by selecting a specific color.

Right now, it is functioning when the user goes through the gallery and clicks save on each item. I would like one button to update all the records however I am striking out. My patch only updates one record. Any guidance would be greatly appreciated!

DropDown to reference color options:

Items: Distinct(Filter(ColMaterials, Category = ThisItem.Category).Color,Color)

Default: ThisItem.Color

Save button: 

ForAll(Gallery8.AllItems,Patch(OrderLines,First(Filter(OrderLines,OrderIDRef=varOrderID&&Material=ThisItem.Material)),{Material:LookUp(ColMaterials,Category=ThisItem.Category&&Color=colordropdown.Selected.Result,'Material Name'),Color:colordropdown.Selected.Result,'Part Number':"test"}))

[RandyHayes]

@william_nr3 

What is your datasource you are using?

You should be working with the primary key of the data and also your ForAll is backward and being used as a For/Loop...which it's not.

Also you are referencing ThisItem...are you doing this in some control beside a button?

[william_nr3]

Hey Randy, thanks for the reply. I am using Dataverse as my data source for the gallery. I am using 'ThisItem' to reference the value that I want in the record.

[RandyHayes]

@william_nr3 

Sorry, didn't notice that the save button was in the gallery!!

But here is the thing - if you have a save button in the row, then the implication is that you are saving that row.  So then why would you be saving the whole gallery?

[william_nr3]

You are totally right. I started small with saving each record so that's why I have a save in each row. I would like to save the whole gallery. I took your guidance and have a save button outside of the gallery now + referencing the primary key. It runs but updates all the records in the gallery with the Material Name, Color, and Part Number from the first row. I'm not sure what I am missing.

ForAll(RenameColumns(OrderLines,"cr1a5_id","Custom_ID"),Patch(OrderLines,LookUp(Gallery8.AllItems,cr1a5_id=Custom_ID),{Material:LookUp(ColMaterials,Category=TextInput9.Text&&Color=colordropdown.Selected.Result,'Material Name'),Color:colordropdown.Selected.Result,'Part Number':"test"}))

[RandyHayes · Accepted solution]

@william_nr3 

Yeah, first, your ForAll is backward - you're trying to use it as a For/Loop.  It's a table producing function.  When you use it like a for/loop, your performance will suffer.

Your Datasource in this case is the Gallery.  In your formula you are not making that the source and thus it is just referencing the first row.

Your formula should be this:

Patch(OrderLines,                
    ForAll(Gallery8.AllItems As _item,
        {cr1a5_id: _item.cr1a5_id,
         Material: _item.colordropdown.Selected.'Material Name',
         Color: _item.colordropdown.Selected.Result,
         'Part Number':"test"
        }
    )
)

Your colordropdown Items property should contain the data necessary for the Material or it should be coming from another source to avoid the Lookup you had - but I'm not clear on how you have that part of it.

The important part here is to include the ID primary key in your resultant table that you are submitting to Patch.  Patch will know what to do with it then.

[william_nr3]

Got it- thanks a ton. I got it working with the code below. I removed the lookup and put the lookup in the gallery itself (made it invisible). I may not be optimal, but the goal is for the user to change the color of the part and when they save it, it references the materials collection and updates the part in OrderLines with the appropriate name, color, and part number. The base quantity, customer price, and cost remain untouched. 

Patch(OrderLines, Gallery8.AllItems,
    ForAll(Gallery8.AllItems As _item,
        {cr1a5_id: _item.cr1a5_id,
       Material: _item.timat.Text,
         Color: _item.colordropdown.Selected.Result,
         'Part Number':"test"
        }
    )
)

[RandyHayes · Accepted solution]

@william_nr3 

Very good!!

Except you have an issue in that formula.  

It should be:

Patch(OrderLines,
    ForAll(Gallery8.AllItems As _item,
        {cr1a5_id: _item.cr1a5_id,
       Material: _item.timat.Text,
         Color: _item.colordropdown.Selected.Result,
         'Part Number':"test"
        }
    )
)

Having the extra gallery table in the Patch like that will cause issues.