Unanswered

Issue with Filter Array

(0) Share

Report

Posted on by TrevorSmith

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 :)

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (0)

Mark Nanneman 991 Moderator on at

Like (1)

Report
Copy link

Link copied!

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'])}))

Was this reply helpful? Yes No
TrevorSmith 19 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Mark Nanneman 991 Moderator on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
TrevorSmith 19 on at

Like (0)

Report
Copy link

Link copied!

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",

Was this reply helpful? Yes No
Mark Nanneman 991 Moderator on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
TrevorSmith 19 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
TrevorSmith 19 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
AA-20120837-0 2 on at

Like (1)

Report
Copy link

Link copied!
@Mark Nanneman

This:

@not(notcontains(...))
sadly seems to be a bug in the new designer. Even if you change "notcontains" to "contains" it will change back to "notcontains" after saving!

Was this reply helpful? Yes No
eliotcole 4,388 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi, Trevor!

Another user has experienced this, here.

I also experienced it (in PROD!!) on a Logic App. :angry:

Anyway, it looks like the fix I posted here (and here on Github), helps to work around (not fix) this (at least for a single update).

Let me know if this is still bothering you, and/or if my suggestion fixed it. Am not asking for 'fix points' I don't care about that stuff ... I'd rather just show evidence that this is a clear, endemic, thing, to the Logic Apps folks.

( sorry that I cannot tag you ... testing in other browsers doesn't happen here. :weary: )

E

Was this reply helpful? Yes No