Apply to Each Not Using my Filter Array to Update a Row in Excel.

[MadScientist]

Hello,

I am attempting to update a row after I have filtered the initial table results. The filter array works, but when I try to update a row based on the output of the filter array results, it selects from the overall results and not the filter outputs. This is what I have so far:

When I test it, the filter array gives out three results based on the table you see below (the outputs are the three yellow rows in table).

Of those three, I want to only update the one row that has the lot number 0115103. This should give me one result. I placed the ‘update a row’ in an ‘apply to each’ with the output of the previous step being the filter array results. My key value I am looking for is the row with the lot number (0115103). When I run the test, the flow says it is a success, but the table does not update based on the filter array results. As you can see it updates a row with the lot number 0115103, but it is not tested on the date a specified in my filter array. Does anyone know how to fix this? Its driving me crazy!

[Matthy79]

Hello @MadScientist 

LotNumber is not unique. Use "ItemInternalId" instead.

[MadScientist]

Hello @Matthy79 ,

Thank you for reaching out to me.  I changed LotNumber to "ItemInternalId" and I am getting the following error: 

What am I doing wrong? 

Thank you for your help!

[Matthy79]

Hello @MadScientist 

You changed the key column but not the key value. Since Lot Number is not unique you can’t use this value to update a single record.

[MadScientist]

Hello @Matthy79 ,

The "Lot Number" in the key value section is the form response. When someone fills out the form, only a single lot number will be listed. The form that I am currently using to test this workflow (see below) has the answer to lot number as 0115103. It should be a specific single value. When I ran the program (see screenshot below) you can see that the key value is that number and not a string of numbers.

It is still saying the error is with the key column input. Does the Key Value still need to be changed?

I also fiddled around with the formatting of the ItemInternalID and put it in as this:

The error that I got in return was this:

The output indicates that the ColumnID is null. I don't know if I made it better or worse. Let me know if I did. 

If you have another answer for my problem, please specify which format version I should work with. Thank you so much for your help! 

[Matthy79]

Filter array gets you 3 rows. Do you want to update all 3 rows with the same values from the form? I don’t understand that because the form takes Lot Number as an input. Why is that not relevant for the filter?

But if you really want to update 3 rows, the Lot Number is not important from the form and you can use the „ItemInternalId“ from „filter array“ to do the updates.

[MadScientist]

@Matthy79 Thank you for the reply!

No, I do not want to update all three rows. Of those three rows, I want it to update the one that contains the lot number that matches the lot number in the form (0115103).

Once it has identified that one row out of the three, I want to update the Retest & Failure columns with the form submissions on that single row. 

I'm looking to filter my table by date (which the filter array does) and then filter by lot number (which I am attempting to do in the "update a row" action).

Is this the wrong way to go about doing this?

[Matthy79 · Accepted solution]

Ok, understood.

So the way to go is just to filter out that one row you want to update and use the unique id to update the row. Please try it and if you don’t have any success post how far you came and I will provide a solution with screenshots tomorrow.

[MadScientist · Accepted solution]

Thank you for the Unique ID idea! I added an UniqueID column to my excel sheet. Added a "Get a Row" function and it worked! @Matthy79 thank you so much for your help!

I copied my workflow below in case someone else has a similar issue. 

[Matthy79]

Great that it works now but isn't ItemInternalId already a unique column in your Excel table? Why don't you use this column?

[MadScientist]

How exactly would I go about doing that? I tried to do that, but I keep getting errors. As you can see from my screenshot, the output for the filter array does have "ItemInternalId" present. 

However, when I enter ItemInternalId as the key column I get the following error: 

Could you please advise on how to do the ItemInternalId correctly? 

Thanks again @Matthy79 !

[Matthy79]

Isn't the column available to select for "Key Column"? That would be strange because during list rows and filter rows it is visible.

The "Key Value" has to be provided as an expression otherwise it would try to update the string you provided and of course "item()....." can't be found. Since you are in a loop the correct expression would be "items('Apply_to_each_2')['ItemInternalId']" but this also could be available as Dynamic Content since your new unique column seems to available too.

[MadScientist]

ItemInteralId isn't present in the dropdown menu for the "Key Column."

Do you know why that would be? 

[Matthy79]

Sorry. Forget what I said about ItemInternalId. This seams to be an ID created during reading the table. This ID changes every time you read the table so you can't use it. Creating a unique column like you did was the correct solution.