cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lumi
Frequent Visitor

Noob - Filter Get item - Calculated or Choice column

Hi, 

 

I'm trying to build a flow Originally to : 

  1. Everyday
  2. Get item from a SP list 
  3. Filter it ( on my calculated or choice column)
  4. create a new excel file from a template
  5. get some of my values in this excel file 
  6. attach this file to an email
  7. send the email to one or more person, from another list

Mostly, using the following articles I'm managing it : 

1./2. by previous experience & https://docs.microsoft.com/fr-fr/sharepoint/dev/business-apps/power-automate/guidance/working-with-g... 

4./5.  Create and fill an Excel file dynamically with Power Automate » Benedikt's Power Platform Blog (bene... (note the "graph id" part there didn't work, but values are added just by using the ID from my excel file creation)

didn't work on the 6/7 yet but I have done similar so it should work )

 

but still I'm stuck with filtering the item

 

First attempt : get item with filter Query >> Calculated or choice column (even using the"value" after my column name Columnname/value) don't seem to work. 

 

Second attempt : Filter after + compose> I think it can be the most promising but it's there that I'm a noob : how do I built my expression in order to have the correct column name  from my filter ? my filter seems only to have 2 output "item" and "body" (and I don't understand exactly the difference ) . 

2021-02-11_0000.jpg

Third attempt : create a filtered view in my SP list + get item "Limit Column by view" + compose  >> all my item are created in my excel file ( no filter )  >> is it only filtering the column, not using the view's filter ?  

 

in my third attempt, the compose use my previous "apply to each"  >> is it the same way to write it but using my output for array for attempt 2 ?

 

 

{
"Name":"@{items('Apply_to_each')?['ID']}",
"Test1":"@{items('Apply_to_each')?['Query']}",
"Test2":"@{items('Apply_to_each')?['Response']}",
"Test 3":"@{items('Apply_to_each')?['Reference']}"
}

 

 

 

 

2021-02-11_0001.jpg

 

Thanks in advance for your help 

1 ACCEPTED SOLUTION

Accepted Solutions
v-litu-msft
Community Support
Community Support

Hi @Lumi,

 

Sorry for the late response, it seems that the PMC status column is a lookup column, so, you could try this syntax:

Screenshot 2021-02-22 150734.jpg

 

Or use Filter query like this:

@equals(item()?['PMC_x0020_Status/Value'], 'value')

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-litu-msft
Community Support
Community Support

Hi @Lumi,

 

1. Currently, Filter Query does not support configuring the Calculated column as a filter parameter.

{
  "status": 400,
  "message": "The field 'DueDate' of type 'Calculated' cannot be used in the query filter expression.\r\nclientRequestId: 9cbfe98d-f39b-4a32-88be-97b4bed87dc6\r\nserviceRequestId: 23fbc39e-008f-8000-1099-ac01787ba94e"
}

Filter choice column, just use the query column eq 'value', for example:

PS: Please notice the column name never modified, or its system name won't display as shows.

Screenshot 2021-02-12 150813.jpg

 

2. The relationship between body and item is like parent and children, body contains all items.

And each item is one item of returned filtered items extract from Get items action. It contains all key-values:

Screenshot 2021-02-12 151412.jpg

 If you want to get a specified column value, you could use this syntax: item()?['ColumnName']Screenshot 2021-02-12 151750.jpg

 

There is a document about function, it could help you:

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Lumi
Frequent Visitor

Could you explain more the first possibility ? It would be the best if it really work with choice column

 

Is it a problem with the column name ? 2021-02-15_0000.jpg

 

The expression "PMC Status eq 'For Team Lead Approval'" is not valid. Creating query failed.
clientRequestId: 27f34cf1-0ee6-4d48-95ee-8cf9916e4a94
serviceRequestId: 27f34cf1-0ee6-4d48-95ee-8cf9916e4a94

 

 

when I go further and try to get my column name, it's displayed with the value as here ['PMC_x0020_Status/Value'] ( because if I understand correctly there is Id / Value / odata type in a choice column)

 

Lumi
Frequent Visitor

was no response regarding the filter in "get item" I continued with a filter after it, however, it doesn't seem to work ( even while replacing the text in the filter with the one from the output.

 

Can it be an error in how I write it? can it be the reason the "get item"  don't work too? 2021-02-15_0001.jpg2021-02-15_0002.jpg

Lumi
Frequent Visitor

Hi @v-litu-msft could you explain more regarding how to use filter with a choice column ? Is my wording in error ? 

v-litu-msft
Community Support
Community Support

Hi @Lumi,

 

Sorry for the late response, it seems that the PMC status column is a lookup column, so, you could try this syntax:

Screenshot 2021-02-22 150734.jpg

 

Or use Filter query like this:

@equals(item()?['PMC_x0020_Status/Value'], 'value')

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Lumi
Frequent Visitor

Thanks ! it didn't work with the Filter Query in the Get item, but at least by modifying the filter array with yours ( I was using the one from the dynamic list, without "/value") it works ! 

Helpful resources

Announcements
MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (51,088)