Update row in Excel after listed rows have been filtered.

[JamieF4T]

Hi,

I'm trying to update a row in a test spreadsheet after I have applied a couple of filters to the data.

The spreadsheet is a simple table;

1)  I have a manual trigger that just collects some text for comment.

2)  I have a List Rows that is pulling in the data from the spreadsheet.

3)  I then apply 2 filters, one to pull out all the items for Duck and the second to check Have Got = No.

The filters are working correctly and I end up with Output that contains just the one row of data for Item - Duck, Have Got - No

4) This is where I hit the problem, I now try to update the comment on this filtered data with Update Row.

This update is ignoring the filters and just updating the first item with Duck in it.

How do I get it to just update the filtered information?  I thought that the Apply to each step would have made it just write to the data it retrieved from the filters.

Thanks

Jamie.

[efialttes]

Hi!

The update is ignoring the filters since the filters are not the input for the update. 'Update rows' take as the input the excel table you reference inside, there is no way to reference a filtered table.

In my opinion, you need a key column with unique values. Let's assume you add a new column to your excel table, let's call it 'Index', and provide a unique value per row (1, 2, 3...)

Now, you should modify slightly your flow, assign as Key Column 'Index' and assign as 'Key value' the following WDL expression:

item()?['Index']

Please note you will probably need to delete and add 'Update row' first in order to read the new Column structure

Hope this helps

[JamieF4T]

Hi,

Thanks for your reply but it doesn't really do what I was hoping.  This is just a test sheet to prove the process for other things but what I want to be able to do is just amend/add to the data that is returned from a filter array.   

The filters are pulling the data down to a single rows worth of information but it seems nuts that once you have that 'Output' you can't then action changes on just the 'Output' data.

I basically just want to add to the "comment": "" listed above.

[efialttes · Accepted solution]

Hi!

If you follow the approach I suggested you would update just the rows from your filtered data. But, if adding an index column cannot be done according to your requirements, I cannot think of any other approach to overcome your challenge.

I mean, the original problem is, you have more than one entry in your Excel table with Key Value 'Duck', so the first one is the one updated. Your current design approach is IMHO wrong.

Happy flowing!

[JamieF4T]

I got there in the end using your idea for a record ID.

Rather than go into lots of detail, I basically added a RecordID to each record using a generated unique string, (based on timestamp).

When I came to updating the rows I could then run filters and from the filters extract the RecordID and then update the row that corresponded to that RecordID.

Thanks for you help, pointed me in a different way of thinking which worked a treat 🙂