Skip to main content

Notifications

Power Automate - Building Flows
Unanswered

Issue with Filter Array

Posted on 19 Nov 2024 22:01:16 by 19
I'm trying to compare a sharepoint list of items with a dataset that is retrieved from PowerBI Query and stored in a Parse JSON.
The unique ID that I'm trying to compare is contained in both datasets, but the one in the ParseJSON has a trailing comma and is not in quotes.
here is a sample output of my SHarepoint array:
 
"1737280",
"1737287",
"1737264",
"1737279",
 
Here is the output of the Parse JSON array
 
1737280,
1737287,
1737292,
1737229,
 
when i create the filter to compare the 2, they obviously do not match.
 
How do i adjust the data to get them to match so that i can identify the items in the ParseJSON that have not yet been added to the SHarepoint site?
 
I'm using Not contains filter:
@not(notcontains(@{body('Select_GetItems_Parse_JSON')},@{item()?['CIR']}))
 
Thank you for your assistance :)
  • TrevorSmith Profile Picture
    TrevorSmith 19 on 21 Nov 2024 at 19:33:42
    Issue with Filter Array
    Hi Mark,  I've tried 2 different filter arrays.  see attached image. The first filter array does not return any results.  The second one returns identical results. Its not filtering out the CIR number that does not match.  
     
  • TrevorSmith Profile Picture
    TrevorSmith 19 on 21 Nov 2024 at 17:08:18
    Issue with Filter Array
    Thank you for this, Mark!  I feel like I'm getting closer. 
     
    I have done as instructed, but still having issues, because it's creating duplicate records in the sharepoint list.   would you mind showing me how you setup your filter array?  And how you setup the sharepoint create new item for the CIR records are not in the sharepoint list?
  • Mark Nanneman Profile Picture
    Mark Nanneman 324 on 20 Nov 2024 at 23:17:36
    Issue with Filter Array
    Trevor, my appologies I led you astray.

    Converting the CIR property to an integer with int() won't work because we're doing a contains operation that only works on strings / arrays.

    I've also experienced the same "not(notcontains(" typo when saving and reopening the app in the "new designer".  I'd recommend editing the flow in the old desiger, flip the toggle or put "v3=false" in your url parameters section.

     

    Your goal I think is to instead convert your array of numbers (from your Select GetItems Parse JSON) action into strings. 

    You could do this by modifying the expression you're using in your "Select GetItems Parse JSON" action. 
    ​​​​​​​

    Whatever expression you have there (I'm guessing it's something like "item()?['Submitted CIR[CIR ID]']") needs to be wrapped in a string() function.

    Here below I did an test using data like yours and got it to work.

    I have an "Array A" which are the numbers in a string format (inside quotes) and an "Array B" with the CIR numbers as integers.

    When I select the CIR numbers out of Arrray B, I wrap the expression in a "String()" function to make sure they come out as strings like Array A and not numbers.

    string(item()?['Submitted CIR[CIR ID]'])

     Here it works.



  • TrevorSmith Profile Picture
    TrevorSmith 19 on 20 Nov 2024 at 20:39:34
    Issue with Filter Array
     
    Thanks, Mark.
     
    Regarding the @not(notcontains.  the Filter array keeps adding this, even after I manually remove it. Tried it both in new designer and legacy designer and in basic and advanced mode.  However when i look at the code of the results of the test I see that it is ok: 
    {
      "type": "Query",
      "inputs": {
        "from": "@outputs('Get_items')?['body/value']",
        "where": "@not(contains(body('Select_GetItems_Parse_JSON'),int(item()?['CIR'])))"
      },
      "runAfter": {
        "Select_GetItems_Parse_JSON": [
          "Succeeded"
        ]
      }
    }
     
    But the Filter returns zero results.  The flow runs successfully, but the output shows this:
    {
        "body": []
    }
     
    Could the issue be that my JSON value output  has a trailing comma
    [
      null,
      1737278,
      1737286,
      1737292,
     
    and my sharepoint GetItems output does not:
    [
      "1737280",
      "1737287",
      "1737264",
      "1737279",
  • Mark Nanneman Profile Picture
    Mark Nanneman 324 on 19 Nov 2024 at 23:51:15
    Issue with Filter Array
    Okay, does it work if you fix the typo and wrap your "item()?['CIR']" with an int()?

    1.  Fix the extra "not" typo.

    @not(notcontains(@{body('Select_GetItems_Parse_JSON')},@{item()?['CIR']}))

    2.  wrap item()?['CIR'] with an int

    Do an "int()" function on your SharePoint "CIR" value.

    @not(contains(@{body('Select_GetItems_Parse_JSON')},@{int(item()?['CIR'])}))

    2. (Alternate) Modify your "Select GetItems Parse JSON" to do a string() function on all the data points.
  • TrevorSmith Profile Picture
    TrevorSmith 19 on 19 Nov 2024 at 23:19:46
    Issue with Filter Array
    Thanks, Mark.
     
    Here is my Parse JSON and output 
    {
        "type": "array",
        "items": {
            "type": "object",
            "properties": {
                "Submitted CIR[Turbine Number]": {
                    "type": "integer"
                },
                "Submitted CIR[Site Name]": {
                    "type": "string"
                },
                "Submitted CIR[Description (of activity)]": {
                    "type": "string"
                },
                "Submitted CIR[Created On]": {
                    "type": "string"
                },
                "Submitted CIR[CAT Text]": {
                    "type": "string"
                },
                "Submitted CIR[Name]": {
                    "type": "string"
                },
                "Submitted CIR[CIR ID]": {
                    "type": "integer"
                },
                "[Last_Refresh]": {
                    "type": "string"
                }
            }
        }
    }
     
    Example of an output
    {
        "body": [
            {
                "[IsGrandTotalRowTotal]": true,
                "[Last_Refresh]": "18 November 2024 08:24 PM"
            },
            {
                "Submitted CIR[Turbine Number]": 24663,
                "Submitted CIR[Site Name]": "Elkhorn Ridge",
                "Submitted CIR[Description (of activity)]": "DroneBase drone inspection",
                "Submitted CIR[Created On]": "2024-10-24T00:00:00",
                "Submitted CIR[CAT Text]": "CAT 3",
                "Submitted CIR[Name]": "Elkhorn Ridge_Blade_24663_2024-10-24_-1",
                "Submitted CIR[CIR ID]": 1737278,
                "[IsGrandTotalRowTotal]": false,
                "[Last_Refresh]": "18 November 2024 08:24 PM"
            },
     
    See below for the Select Action where I'm taking the array and choosing just the Value I needed (CIR ID) to filter against my sharepoint list
     
     
  • Mark Nanneman Profile Picture
    Mark Nanneman 324 on 19 Nov 2024 at 22:22:43
    Issue with Filter Array
    A few things.  There's definitely a syntax error/typo with your Filter Query.  It should read "@not(contains("  but you have "@not(notcontains(".

    Additionally, I'm not sure if you have the correct reference to your Parse JSON in your filter.  I'd have to see what your Parse JSON and its output looks like, but it doesn't look quite right.  You need to be inputing an array into your filter.

    Finally, if you are trying to compare a number in string format with a number in value/number format, you need to transform one of them.  

    It looks like your SharePoint values are numbers in string format, so you could potentially fix your filter by doing something like the following to convert your sharepoint value into an integer.  
    @not(contains(@{body('Select_GetItems_Parse_JSON')},@{int(item()?['CIR'])}))



     

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

October 2024 Newsletter…

October 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #4 How to Conntact Support…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 143,061

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,795

Leaderboard