lookup criteria

[walking]

Hi all, 

I want to do a lookup in List A where trim(List A. ProjectName) = trim(List B.ProjectCode).

But flow doesn't allow me to use expression trim(List A. ProjectName) . I just only can use List A. ProjectName= trim(List B.ProjectCode).

The error msg is: 

The template validation failed: 'The 'inputs' property of template action 'Get_items' at line '1' and column '2275' is not valid. The action cannot reference itself.'.

I attached my flow. Could somebody please help with this?

Thank you!

[ScottShearer]

@walking 

As you found out, you can only reference a column name as the first part of the OData filter - you can't use an expression.  Have you considered using a Flow to trim the project name column in project A when the item is created or changed? 

[walking]

Hi @ScottShearer 

Thank you for your reply.

I think your way would work if I trim project code first when the item is created. and then use the trimed column as the left part of the OData filter.

Actually trim is just the first step, I am going to do where clause with 

tolower(List A. project name) = tolower(List B. project code).

I can't transform all project code to lowercase because it will change the user's input value . The only way I can think is to store tolower(List A. project name) in a new filed.

What do you think? any other option? 

Thank you.

[v-bacao-msft]

Hi @walking ,

Could you share more details about the Flow configuration?

The parameters on the left side of the Filter Query cannot be decorated with expression.

You could consider trying to use Filter array to decorate the parameters and filter out the expected items.

Best Regards,

[walking]

Hi @v-bacao-msft ,

Thank you for your reply.

I uploaded the flow.

Could you please provide an example of how to`use Filter array to decorate the parameters and filter out the expected items`？

Thank you.