Updating Excel Rows with data from an Email subject Line

[archiejg]

Hi All,

I have a spreadsheet which contains booking data. On the first of each month, a row is created in the spreadsheet. Example below:

The purpose of this row is to almost 'cut off' any bookings for the previous month and also create a row where we can enter the amount once it's been paid.

When I receive an email with the invoice for that month (which can be after the 1st) I need to update this row with the Invoice ID populating into the ID column. Where it says 'Invoice X Payment' I would like the X replacing with the same ID.

The invoice ID is in the subject line (which always remains the same - I am currently testing using the following expression which has been capturing the ID successfully.

slice(triggerOutputs()?['body/subject'],36)

The Invoice ID should also be populated into each row above where the invoice column is either empty or has a placeholder (something similar to TBC, or Awaiting Invoice)

Here's an example of a completed set of bookings:

Is there a way I can possibly automate this?

Thanks so much in advance!! 🙂

Archie

[Koen5]

Dear Archie,

1. I would read the entire table (make sure that your excel is defined as table) using the Excel action 'List rows in table'.
2. Filter the output array based on your criteria (select only those rows which need to have the invoice ID).
3. Use an excel action 'Update row' to replace any existing value for the invoice ID.

Happy flowing,

Koen

[archiejg]

Hi @Koen5 - Thanks for that.

I have two filter arrays setup to filter the excel table and it's pulling through the right rows which need updating.

I can't see how I can update the row using 'Update a row' action based on the filtered outputs?

[Koen5]

Dear Archie,

Add an action 'Apply to each' with as value the output of the latest filter array (assuming that the last filter array output is an array which has both filters applied).

Inside the 'Apply to each' loop, add the Excel action 'Update row' and fill in as needed.

If this has helped, please indicate as like/solution in the thread for other users.

Happy flowing,

Koen

[archiejg]

Hi @Koen5 - Thanks for the reply.

Does this look right?

When testing it updated the first row correctly but then failed to update subsequent rows from the array filter:

Excel table already has a row with id '54321'.

Thanks,

Archie.

[Koen5]

Dear Archie,

The 'Update a row' action works by identifying a unique row. The identification takes place via the elements 'Key value' and 'Key column', so you will have to make sure that this always refers to columns/value which are unique. If not, then you get errors as the action does not know which row to select. 

In my experience, it is best to add a separate column to your table purely for the purpose of uniquely identifying the rows rather than depend on existing column/row data.

I hope this helps. For any further questions ... I think I can refer you to the tutorials, instruction videos as this is becoming a bit basic.

Happy flowing,

Koen