Adding Task ID to an existing Excel table or using another key-id

[Markus_]

Hi everyone,

I have an Excel table with 250 devices for maintenance with all customer data. One column includes a text field as ID with a succeeding entry (PPM001, PPM002, etc.) For each entry a task in MS Planner was created through a flow called "for a selected row". So the Excel table and the tasks in the Planner already exists.

When the task is being closed I want to add the completion date of the task into the Excel table into a column called "completed at" using flow "update row in Excel".

I am not quite sure how define the key-id to find the matching row to update.

I would either have to

a) add the task id to the existing excel table in a separate column or

b) be able to extract the "PPM00x" id from the subject string of the task

to use this as key-id to find the matching row to update.

In both cases I don't know how to do it, can anyone give me a suggestion how to go about it best?

A) How can I add the ID into the existing Excel table as separate column.

or

B) How can I extract the "PPM00x" id from the subject / title of the task and use this as key-id to match in the ID-column (text field) of the excel table.

The result to achieve is to have the completed date of the task in the "completed at" row/column in the Excel table.

The table looks somehow like this example

Thanks for your help.

Best regards,

Markus

[tom_riha]

Hello @Markus_ ,

the trigger 'When a task is completed' provides you the dynamic content 'Title', it should contain the PPM00x from the Planner task. You can use the 'Title' as the 'Key Value' in the 'Update a row' action.

The other information it needs is the 'Key Column'. That's the column where it should search for the 'Key Value', in your case you want to search for the PPM00x in the ID column: 'Key Column' = ID.

The just update the 'Completed' column to the 'Completed Date Time' dynamic content.

[Markus_]

Hi tom_riha,

thanks for your reply. Generally this would work and I tried it but the problem is, the subject / title in the planner task includes more than just the ID as "APP002". It is a string containing the ID + Customer Name and Adress (e.g. "APP002 - CustomerName, Street, ZIP City / etc". I would habe to extract the first 6 characters of the string and use it as key-id. Do you have any other suggestio or know how to extract the first 6 characters?

Thank you.

Best regards,

Markus

[tom_riha · Accepted solution]

Hello @Markus_ ,

you can use the substring(...) expression. You give it a string as the first parameter, the position of the first character to take, and how many characters it should take.

substring(triggerOutputs()?['body/title'],0,6)

The example above will take the task title, e.g. 'APP002 - CustomerName, Street, ZIP City / etc', start from the position 0 character = A, and take the 6 characters from position 0 = APP002.

[Markus_]

Thanks, tom_riha. That did work perfecly well.

Regards,

Markus