Filter the JSON records directly from SharePoint List in PowerAutomate

Bujjibabu KJ

Actually I am creating app for canteen to order the food for for employees.

Food requirement - Tiffin, Lunch, Snacks & Dinner. Users are ordering the food what they need.

Now I want to send a scheduled mail with filter all the Tiffin data and sent a mail at 8 AM, Lunch data and sent a mail at 12 Noon,

Snacks data and sent a mail at 4 PM and Dinner data and sent a mail at 8:30 PM.

I am patching the data as records in JSON format from canvas app to Share Point List as follows (dummy data)

 [{"Date":"2023-02-20","Employee_ID":"500891","Employee_Name":"H DHARMENDER","Meal_For":"Tiffin","Meal_Type":"DUTY","Remarks":"12","Shift":"A"}]

 [{"Date":"2023-02-20","Employee_ID":"501653","Employee_Name":"S BALARAJU","Meal_For":"Tiffin","Meal_Type":"DUTY","Remarks":"123","Shift":"A+B"}]

 [{"Date":"2023-02-20","Employee_ID":"500891","Employee_Name":"H DHARMENDER","Meal_For":"Tiffin","Meal_Type":"DUTY","Remarks":"12","Shift":"A"}]

 [{"Date":"2023-02-20","Employee_ID":"501412","Employee_Name":"EPETER PAUL","Meal_For":"Tiffin","Meal_Type":"DUTY","Remarks":"21","Shift":"A+B"}]

 [{"Date":"2023-02-20","Employee_ID":"521821","Employee_Name":"Pallapu Madhu","Meal_For":"Dinner","Meal_Type":"DUTY","Remarks":"Night continued","Shift":"B+C"}]

 [{"Date":"2023-02-20","Employee_ID":"520629","Employee_Name":"M VAMSIKRISHNA","Meal_For":"Dinner","Meal_Type":"DUTY","Remarks":"For beml assy","Shift":"G+B"}]

 [{"Date":"2023-02-20","Employee_ID":"BML0088","Employee_Name":"K.Madhu kumar reddy","Meal_For":"Dinner","Meal_Type":"DUTY","Remarks":"For beml assy","Shift":"A+B"}]

 [{"Date":"2023-02-21","Employee_ID":"521290","Employee_Name":"MANUGONDA HARISH KUMAR","Meal_For":"Lunch","Meal_Type":"DUTY","Remarks":"No","Shift":"A+B"}]

 [{"Date":"2023-02-21","Employee_ID":"521899","Employee_Name":"Sunkari Prashanth","Meal_For":"Dinner","Meal_Type":"DUTY","Remarks":"INSPECTION","Shift":"A+B"}]

 [{"Date":"2023-03-03","Employee_ID":"BML0079","Employee_Name":"M.Shankar","Meal_For":"Lunch","Meal_Type":"CP","Remarks":"For beml assy","Shift":"A"},{"Date":"2023-03-03",

"Employee_ID":"BML0080","Employee_Name":"V.Ajay","Meal_For":"Lunch","Meal_Type":"CP","Remarks":"For beml assy","Shift":"A"},{"Date":"2023-03-03","Employee_ID":"BML0090",
"Employee_Name":"P.Jahangeer baba","Meal_For":"Lunch","Meal_Type":"CP","Remarks":"For beml assy","Shift":"A"},{"Date":"2023-03-03","Employee_ID":"BML0085",
"Employee_Name":"M.Arun kumar goud","Meal_For":"Lunch","Meal_Type":"CP","Remarks":"For beml assy","Shift":"A"},{"Date":"2023-03-03","Employee_ID":"BML0091","Employee_Name":"Sibatullah",
"Meal_For":"Lunch","Meal_Type":"CP","Remarks":"For beml assy","Shift":"A"}]

All the records (JSON Format) are patching by different users

At present I am able to send email with one JSON record directly from Sharepoint List by using flow

ParseJSON
Select
Create HTML Table etc.

Request your support to filter the data in all JSON records directly from sharepoint list (Filter with Date or Employee ID or Shift etc) and send email in Power Automate.

Thanks & Regards

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bujjibabu KJ3y ago
Hi

Request your help

Thanks & Regards
0
Bujjibabu KJ3y ago
Dear All

Thanls for your reply

Each recored is patching in JSON format with [ ] brakets in sharepint list column "canteenJson"

I need to get the all recirds in Get Items and filter Date wise, Meal_For & Shift wise etc.

Thanks & Regards
0
Tuhin Paul3y ago
In addition to what Abhijeet said, I am adding little more details
- Create a new Power Automate flow or open an existing one that will retrieve data from a SharePoint list.
- Add the "Get items" action to the flow by selecting it from the list of available actions.
- In the "Site Address" field, enter the URL of the SharePoint site where the list is located.
- In the "List Name" field, enter the name of the SharePoint list you want to retrieve data from.
0
Tuhin Paul3y ago
Also check once if you are following the below steps also.
- Expand the "Advanced Options" section and click on "Filter Query".
- In the "Filter Query" field, enter your filter expression in the OData syntax. For example, if you want to retrieve only items where the "Status" field equals "Approved", you would enter "Status eq 'Approved'".
- Click "Save" to save the flow.
- Test the flow by running it and verify that the filtered data is retrieved from the SharePoint list.
0
Abhijeet Jadhav3y ago
To filter JSON data from a SharePoint list in Power Automate, you can use the "Get items" action and add a filter query to the action. Here are the steps to follow:
1. Add the "Get items" action to your flow and select the site and list where the data is stored.
2. Click on "Show advanced options" and expand the "Filter Query" field.
3. In the "Filter Query" field, enter the filter expression in the OData syntax. For example, if you want to retrieve only items where the "Status" field equals "Approved", you can enter "Status eq 'Approved'".
4. Save the flow and run it to retrieve the filtered data from the SharePoint list.
0