Skip to main content

Notifications

Power Automate - Building Flows
Unanswered

Find records in the array that match on a value and filter out those with a certain date value

Like (0) ShareShare
ReportReport
Posted on 18 Dec 2024 03:52:15 by 170
Hello -
 
Having trouble figuring out how to filter for the following. Trying to find records in the array that have matching store numbers and of that set filter out those with "Promotion End Date" = '9999-12-31T00:00:00'.  There can be numerous sets of records with the same store number in the parsed JSON input.
 
Here is sample of the data. In this sample there are 5 records. After the filter(s) run there should only be 4 records with the third store number 55 record filtered out.
 
 {
            "Store Number": 45,
            "Store Name": "abc",
            "Price": 4.44,
            "Price Type": "STORE",
            "Price Start Date": "2025-01-01T00:00:00",
            "Promotion Type": null,
            "Promotional Price": 4.44,
            "Promotion Start Date": "2025-01-01T00:00:00",
            "Promotion End Date": "9999-12-31T00:00:00",
            "Coupon Number": null,
            "Price Status": "FUT"
        },
        {
            "Store Number": 55,
            "Store Name": "xyz",
            "Price": 1.11,
            "Price Type": "STORE",
            "Price Start Date": "2025-02-03T00:00:00",
            "Promotion Type": "M",
            "Promotional Price": 1.11,
            "Promotion Start Date": "2025-02-03T00:00:00",
            "Promotion End Date": "2025-02-28T00:00:00",
            "Coupon Number": null,
            "Price Status": "FUT"
        },
        {
            "Store Number": 55,
            "Store Name": "xyz",
            "Price": 2.22,
            "Price Type": "STORE",
            "Price Start Date": "2025-01-06T00:00:00",
            "Promotion Type": null,
            "Promotional Price": 2.22,
            "Promotion Start Date": "2025-01-06T00:00:00",
            "Promotion End Date": "2025-02-02T00:00:00",
            "Coupon Number": null,
            "Price Status": "FUT"
        },
        {
            "Store Number": 55,
            "Store Name": "xyz",
            "Price": 2.22,
            "Price Type": "STORE",
            "Price Start Date": "2025-03-01T00:00:00",
            "Promotion Type": null,
            "Promotional Price": 2.22,
            "Promotion Start Date": "2025-03-01T00:00:00",
            "Promotion End Date": "9999-12-31T00:00:00",
            "Coupon Number": null,
            "Price Status": "FUT"
        },
        {
            "Store Number": 129,
            "Store Name": "def",
            "Price": 0,
            "Price Type": "STORE",
            "Price Start Date": "2025-01-01T00:00:00",
            "Promotion Type": null,
            "Promotional Price": 0,
            "Promotion Start Date": "2025-01-01T00:00:00",
            "Promotion End Date": "9999-12-31T00:00:00",
            "Coupon Number": null,
            "Price Status": "FUT"
        }
 
Appreciate any assistance.
Thanks!
 
 
  • Cgangweg01 Profile Picture
    Cgangweg01 170 on 22 Dec 2024 at 15:59:52
    Find records in the array that match on a value and filter out those with a certain date value
    Hi!
     
    If anyone is following this post here is an update. 
     
    With help from outside the community I've got this advanced mode filter array query working.
     
    Here it is:
     
    not(and(greater(nthIndexOf(string(outputs('Filter_array_stores_with_future_specials')), string(item()?['Store Number']), 2), 0), equals(item()?['Promotion End Date'], '9999-12-31T00:00:00')))
     
    Not exactly sure how this nthIndexOf expression works but I need to tweak it. The store 45 record in the screenshot below is the only record in the From output with store number 45 and should not be filtered out even though it has promotion end date '9999-12-31T00:00:00'.
     
    Any ideas?
     
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November 2024 Newsletter…

November 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #9 Get Recognized…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 144,563

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 64,396

Leaderboard
Loading started