retrieving data from a list to complete it in the document library

[ephexote]

Hello Power Automate lovers!

I have two lists:
list 1, a list with doctors' names and their email addresses
list 2, a document library for storing doctors' schedules

list 1 has the following columns: title (Last name First name), email (email address)
list 2 has the following columns: name (name of files with schedules, e.g. “schedule of Dr Bush George.pdf”. File name is a scheme: schedule Dr Last name First name.pdf), email

I want a program in Power Automate, which, after manually turning it on (but it's not necessary, it could be when file is created), will go through all the files that are in the document list 2. Then the program must select from the file name Last name First name, that is, everything that is after “dr ‘ and before ’.pdf” and compare it with the title column in list 1. If it finds the same information, then the program in list 2, that is, the document library, automatically adds the email address from the email column in list 1 to the email column in list 2.

list 1:

list 2:

This is what I did so far:

(after this is Terminate with Succeeded status)

Compose input function: 

substring(triggerOutputs()?['body/{FilenameWithExtension}'], 9, sub(length(triggerOutputs()?['body/{FilenameWithExtension}']), 13))

Condition is:

equals": [
          "@item()?['Title']",
          "@outputs('Compose')"
        ]

Update file is:

ID: ID from Get file properties (document library)
Email: email from Get items (list)

What I do wrong? When I put some schedules to thiz document library, after about 30 secs flow has the status as succeed, but the emails doesn't show in the document library 😞

Please tell and help me 😞

[v-yetonggu-msft]

Hi @ephexote ,

Could you please show me a screenshot of the Compose Outputs in run history?

Best Regards,

Sunshine Gu

[ephexote]

Hi @v-yetonggu-msft !

Thanks a lot for your answer.

Yes, those are the examples:

[v-yetonggu-msft · Accepted solution]

Hi @ephexote ,

I notice your compose outputs contains extra blank in front.

Please use trim function that could be clear extra blank in front.

trim(substring(triggerOutputs()?['body/{FilenameWithExtension}'], 9, sub(length(triggerOutputs()?['body/{FilenameWithExtension}']), 13)))

Best Regards,

Sunshine Gu

[ephexote]

It works, thank a lot! 💕
Such a simple reason...

I have also question which could be only technical side - is it possible to manage in this flow manuall trigger? Just to do it for all the files in folder once? Using "when a file is created..." trigger makes a lot of runs, now I'm not sure my licence allows me to do this taht often as I want

I've been tried but it looks like it's simpler to do trigger "when a file..."

[v-yetonggu-msft · Accepted solution]

Hi @ephexote ,

Yes, but I would suggest that you could use a scheduled flow to run once a day.

And you have to change the action in next step:

Flow Preview:

Flow Configuration Parameters:

length(body('Filter_array'))
item()?['filed_1']

Best Regards,

Sunshine Gu

[ephexote]

Hi @v-yetonggu-msft,

thank a lot for your time. It means a lot to me.

Trying use manuall trigger gave me the same error as in yours resolution:

InvalidTemplate. The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@equals(item()?['Title'], trim(substring(triggerOutputs()?['body/{FilenameWithExtension}'], 9, sub(length(triggerOutputs()?['body/{FilenameWithExtension}']), 13))))' failed: 'The template language function 'length' expects its parameter to be an array or a string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#length for usage details.'.

Is it means something wrong could be with my email list?

[v-yetonggu-msft · Accepted solution]

Hi @ephexote ,

I just make a sample test.

You don't need to change the original Tytul column in your flow to Title column.

And please change this expression in Filter array right:

trim(substring(items('Apply_to_each')?['{FilenameWithExtension}'], 9, sub(length(items('Apply_to_each')?['{FilenameWithExtension}']), 13)))

like this:

Best Regards,

Sunshine Gu

[ephexote]

Hi @v-yetonggu-msft,

ok, now runs are succeeded, but none of emails matched 😞
edit: ok, I did more runs and my conclusion is, that it in some cases works and some doesn't, even for the same files. For example, for the first on the list it works let's say in 80%, but for the last it didn't match at all. What is happening?

edit: ok, i found the mistake:

item()?['filed_1']

instead of:

item()?['field_1']

Thanks a lot @v-yetonggu-msft , everything now works! 😍