Answered

How to trim query in power automate

(0) Share

Report

Posted on by Gul123

I have a query that depends on a filter values from powerapps. First of all the flow will be call by powerapps. In the flow all of the following variable initialized as a string value (Request.no, PersonName,Department,RequestType,Status and Priority).

Then I used Compose to write the expression of the query as shown below:

concat(if(not(IsBlank(variables('Request.no'))), concat('Request.no eq ''', variables('Request.no') ,''' and '),''),
if(not(IsBlank(variables('PersonName'))), concat('PersonName eq ''', variables('PersonName') ,''' and '),''),
if(not(IsBlank(variables('Department'))), concat('Department eq ''', variables('Department') ,''' and '),''),
if(not(IsBlank(variables('RequestType'))), concat('RequestType eq ''', variables('RequestType') ,''' and '),''),
if(not(IsBlank(variables('Status'))), concat('Status eq ''', variables('Status') ,''' and '),''),
if(not(IsBlank(variables('Priority'))), concat('Priority eq ''', variables('Priority') ,''''),''))

After that I used Initialize variable with the following expression:

if(endsWith(outputs('Compose'),' and '), trim(outputs('Compose'),sub(length(outputs('Compose')),5), length(outputs('Compose'))), outputs('Compose'))

Then I used Get Items with the output of the compose.

When the user input in powerapps was only for the Req-no as S1 and the other fields he keep them as blank, the query will be:

Request-no = 'S1' and PersonName='' and Department='' and RequestType='' and Status='' and Priority='' and it will return 0 value because no value will have only request number and the other fields empty in it.

So, what I want to do is whenever any field was empty I want to trim the query and to keep the other fields that have values.

So, when the user search only for Request number S1 and the other fields are empty the query will be:

Request-no = 'S1'

When he search for request number and Person name only the query will be:

Request-no='S1' and PersonName='Joe'

How to do this in power automate?

anyone can help me?

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (1)

VictorIvanidze 13,079 on at

Like (0)

Report
Copy link

Link copied!

Just modify your Compose logic.
When the user search only for Request number S1 create this query:
Request-no = 'S1'

Was this reply helpful? Yes No
Gul123 10 on at

Like (0)

Report
Copy link

Link copied!

Dear Victorlvanidze,
I don't want to search only with this variable (Request number). I have 6 variables. and I want to get the items depending on the passed values from powerapps. Suppose that one of the users has been searched for the following values for each variable:
Request-no = 'S1' and PersonName='Joe' and Department='IT' and RequestType='Sc' and Status='New' and Priority='High'. The query in this case will be correct because all of the variables have a value.
What I want to do is when one of those variables doesn't have a value, I want to remove it from the query by default. for example I want to get items that are done by PersonName='Joe' and Department='IT'
so the query will be:
PersonName='Joe' and Department='IT' without the other values.

Any idea? Please show me how to do step by step

Was this reply helpful? Yes No
Verified answer

v-xiaochen-msft on at

Like (0)

Report
Copy link

Link copied!
Hi @Gul123 ,

I did a sample for you.

concat(if(not(empty(variables('Request.no'))), concat('Request.no eq ''', variables('Request.no') ,''' and '),''), if(not(empty(variables('PersonName'))), concat('PersonName eq ''', variables('PersonName') ,''' and '),''), if(not(empty(variables('Department'))), concat('Department eq ''', variables('Department') ,''' and '),''), if(not(empty(variables('RequestType'))), concat('RequestType eq ''', variables('RequestType') ,''' and '),''), if(not(empty(variables('Status'))), concat('Status eq ''', variables('Status') ,''' and '),''), if(not(empty(variables('Priority'))), concat('Priority eq ''', variables('Priority') ,''''),''))
if(equals(last(split(trim(outputs('Compose')),' ')),'and'),slice(outputs('Compose'),0,lastIndexOf(outputs('Compose'),' and')),outputs('Compose'))

Best Regards,

Wearsky

Was this reply helpful? Yes No
VictorIvanidze 13,079 on at

Like (0)

Report
Copy link

Link copied!

Have a look at this:

Was this reply helpful? Yes No