Answered

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

(0) Share

Report

Posted on by 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.

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

Categories:

Building flows

All responses (4)

Answers (1)

Sort by

tom_riha 10,187 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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_ 19 on at

Like
a
(0)

Report
Copy link

Link copied!

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
Verified answer

tom_riha 10,187 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
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_ 19 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks, tom_riha. That did work perfecly well.

Regards,
Markus