cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tarjani
Advocate III
Advocate III

Filtering OR expression

Hi all,

I'm trying to list all active records that have a field (ec_noc) that has either value of '2' or '3'.  I think what I need to do is to use the connector for List all Records and put an expression into the Filter Rows field.

The two expressions that I've been able to come up with so far (and are not working) are: 

ec_noc eq 948180002 or 948180003

((using this link as reference https://diyd365.com/2019/11/20/every-power-automate-ms-flow-filter-query-you-ever-wanted-to-know-as-... ))

/Certification?$filter=ec_noc eq 948180002 or ec_noc eq 948180003
((using this link as reference: https://docs.mendix.com/refguide/odata-query-options AND https://www.odata.org/documentation/odata-version-2-0/uri-conventions/ ))


Would greatly appreciate feedback on this!

2 ACCEPTED SOLUTIONS

Accepted Solutions
ViditGholam
Responsive Resident
Responsive Resident

Hi @Tarjani  So as I said I tried it in my trail environment and this is how it works.

 Case - 

Entity name - Project Contracts (orders) 

Field name - NOC Type 

Option set values - 

ViditGholam_0-1616658218291.png

 

so in my case I have 3 records (Mentioned in red are the destination types selected in those records)

ViditGholam_1-1616658331927.png

Now I am fetching for active Project contracts who's NOC Type is Operations or Destinations.

ViditGholam_2-1616658516819.png

results of this query (Download this Query from the download fetch XML Option as in the above image)

ViditGholam_3-1616658558023.png

Lets go to the flow for this 

ViditGholam_4-1616658642084.png

ViditGholam_5-1616658702766.png

ViditGholam_6-1616658739147.png

Out put - 

ViditGholam_7-1616658835636.png

ViditGholam_10-1616658929973.png

 

ViditGholam_9-1616658897939.png

I have added compose to show you the record name don't include that in your flow .

 

Hope this helps ! 

Let me know in case any issues.

 

Also Please take some time to mark this post as answered if your issues is resolved and hit me a thumbs up.

 

 

 

 

 

 

View solution in original post

Hi @ViditGholam ,
Just thought I'd let you know that I managed to get the fetch to work.  When the row for the ordering attribute was removed I was able to get the flow to run through the condition successfully.  So the fetch below is what was successful.

 

 

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="vin_certification">
<attribute name="vin_certificationid" />
<attribute name="vin_name" />
<attribute name="createdon" />
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
<condition attribute="ec_noctype" operator="in">

<value>948180003</value>

<value>948180002</value>

</condition>
</filter>
</entity>
</fetch>


Thanks for all your suggestions and guidance on this, it was really appreciated.

View solution in original post

22 REPLIES 22
ViditGholam
Responsive Resident
Responsive Resident

Hi @Tarjani  as per my understanding you want to retrieve Active Records who's field named (ec_noc) has some data i.e. is not empty .

Step -1

you can achieve this by using  filter query i.e. by using advance find fetch your entity record who's record status is active and the field ec_no is not empty .

might look something like this.

ViditGholam_0-1616566444767.png

you will get a fetch XML query from this then use this query in your Flow and use the below condition 

empty(body('Your flow connector name')?['value']) is equal to true 
ViditGholam_1-1616566826421.png

 

and put your next condition in the "no" section as shown above 

 

Let me know if this helps !

 

Hi @ViditGholam ,
Something in my expression might have given a misleading impression.  I have already filtered for active records, I am trying to filter the noc field to look for two specific values which are actually labelled 'Operations' or 'Destinations'.  The goals it to only pick up active records with these values.

The filter for active records is here:

Tarjani_0-1616638854076.png


The filter for the noc (which is actually ec_notype) field is here:

Tarjani_2-1616638958522.png

 

Mira_Ghaly
Dual Super User II
Dual Super User II

@Tarjani 

can you remove highlighted part?remove till 

=

Mira_Ghaly_0-1616640267112.png

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Visit my blog for nice articles: here

HI @Mira_Ghaly ,
Thanks for that feedback.  It seemed obvious to remove that part after someone said this.  The good thing is that it ran, but it is bringing back too many records.  That expression brings back over 2000 records instead of the expected 20.  It should be a simple or expression, so I tried what @ViditGholam said about the fetch xml from advanced find and I get this:

<condition attribute="ec_noctype" operator="in">

<value>948180003</value>

<value>948180002</value>

would I turn that into something like:  ec_noctype in 948180003 or 948180002 ?

ViditGholam
Responsive Resident
Responsive Resident

@Tarjani Why don't you try to filter everything in just a single Fetch XML.

 

ViditGholam
Responsive Resident
Responsive Resident

I will try this out and let you know.

Hi @ViditGholam ,

The whole part - that would be active and noc type filter together.  Yes that would be more efficient.  This is what I get in fetch.  The green is where I imagine it being important.  I've been slow to this path as I hadn't been successful transfering the fetch to a connector so far.


 

<?xml version="1.0"?>

-<fetch distinct="false" mapping="logical" output-format="xml-platform" version="1.0">


-<entity name="ec_certification">

<attribute name="ec_name"/>

<attribute name="createdon"/>

<attribute name="ec_year"/>

<attribute name="ec_account"/>

<attribute name="ec_certificationid"/>

<order descending="false" attribute="vin_name"/>


-<filter type="and">

 


-<condition attribute="ec_noctype" operator="in">

<value>948180003</value>

<value>948180002</value>

</condition>

<condition attribute="statecode" operator="eq" value="0"/>

</condition>

</filter>

</entity>

</fetch>

 

 

ViditGholam
Responsive Resident
Responsive Resident

Yes @Tarjani That's exactly what I was trying to say try it if it works for you 

if it works for you please mark this post as Accepted solution and hit me a thumbs up 

 

Let me know in case any issues

 

ViditGholam
Responsive Resident
Responsive Resident

I will implement it and show you so that its more clear to understand

 

ViditGholam
Responsive Resident
Responsive Resident

Hi @Tarjani  

Here is how you should do it suppose I have an Entity "Projects" and it has a field "Project Classification" 

Case - I want to filter "Active" Projects in which Project Classification is either "Client Project" or "Internal Systems".

Here is the advance find query 

ViditGholam_0-1616642753045.png

ViditGholam_1-1616642785130.png

now I got my Fetch XML as below - 

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="msdyn_project">
<attribute name="msdyn_projectid" />
<attribute name="msdyn_subject" />
<attribute name="createdon" />
<order attribute="msdyn_subject" descending="false" />
<filter type="and">
<condition attribute="statuscode" operator="eq" value="1" />
<condition attribute="cf_projectclassification" operator="in">
<value>100000000</value>
<value>100000002</value>
</condition>
</filter>
</entity>
</fetch>

This is how my flow looks- 

ViditGholam_2-1616642919417.png

 

 

Hi @ViditGholam ,

I put the fetch into the Fetch Xml Query field and I'm getting an error.  The error message is below.  I've also put the fetch query that was used.  I tried changing the query several times until I realised that the field ec_noctype is actually a Global Option Set/Choice field, which has no issues appearing in the advanced find fetch, but seems to be an issue here. 

 

 


Error message
'vin_certification' entity doesn't contain attribute with Name = ' ec_noctype' and NameMapping = 'Logical'. MetadataCacheDetails: ProviderType=Dynamic, StandardCache=True, IsLoadedInStagedContext = False, Timestamp=517723116, MinActiveRowVersion=517723116, MetadataInstanceId=51518957, LastUpdated=2021-03-25 04:01:13.630

 

Initial Fetch from advanced fine

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="vin_certification">
<attribute name="vin_certificationid" />
<attribute name="vin_name" />
<attribute name="createdon" />
<order attribute=" vin_certification" descending="true" />
<filter type="and">
<condition attribute="statuscode" operator="eq" value="0" />
<condition attribute=" ec_noctype" operator="in">
<value>100000003</value>
<value>100000002</value>
</condition>
</filter>
</entity>
</fetch>

 

ViditGholam
Responsive Resident
Responsive Resident

@Tarjani I understood I suppose this field is not present on your entity "Vin_certification"

Mira_Ghaly
Dual Super User II
Dual Super User II

@Tarjani 

Can you share screen of flow history on which step you are getting this error?

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Visit my blog for nice articles: here

It wont' really show much, as it is happening right at the very beginning.

Tarjani_0-1616648258515.png

 

I didn't include it above as it cancels before this condition, but I've expanded it here all the same

 

 

Tarjani_1-1616648344886.png

 

ViditGholam
Responsive Resident
Responsive Resident

Hi @Tarjani I will implement it exactly the way you want in my trail environment and get back to you mean while

check if the Field (attribute) you are trying to fetch is present on that entity form.

Thank you for looking at it @ViditGholam.

In my mind I knew the field was there, but had to get screen shots for my own confirmation.


Tarjani_1-1616649405951.png

 

 

Tarjani_0-1616649384219.png

ViditGholam
Responsive Resident
Responsive Resident

Hi @Tarjani  So as I said I tried it in my trail environment and this is how it works.

 Case - 

Entity name - Project Contracts (orders) 

Field name - NOC Type 

Option set values - 

ViditGholam_0-1616658218291.png

 

so in my case I have 3 records (Mentioned in red are the destination types selected in those records)

ViditGholam_1-1616658331927.png

Now I am fetching for active Project contracts who's NOC Type is Operations or Destinations.

ViditGholam_2-1616658516819.png

results of this query (Download this Query from the download fetch XML Option as in the above image)

ViditGholam_3-1616658558023.png

Lets go to the flow for this 

ViditGholam_4-1616658642084.png

ViditGholam_5-1616658702766.png

ViditGholam_6-1616658739147.png

Out put - 

ViditGholam_7-1616658835636.png

ViditGholam_10-1616658929973.png

 

ViditGholam_9-1616658897939.png

I have added compose to show you the record name don't include that in your flow .

 

Hope this helps ! 

Let me know in case any issues.

 

Also Please take some time to mark this post as answered if your issues is resolved and hit me a thumbs up.

 

 

 

 

 

 

View solution in original post

Hi @ViditGholam ,
First of all thank you for the time you put into testing that and doing the screen shots. 

 

What you have put together is exactly what I did for the advance find fetch xml.  It would work if I was doing it with any other field other than the NOC type field.  When trying to filter with the NOC field, it has the error that the field can't be found on the entity, that I shared earlier - which is my challenge.

Initially I tried to get around this using a variable to filter out the ones that didn't have this NOC Type:

Tarjani_0-1616725241383.png

 

Tarjani_1-1616725250161.png

 

Tarjani_2-1616725258495.png

 





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 (65,997)