Filtering OR expression

[Tarjani]

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!

[ViditGholam]

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.

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 

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

Let me know if this helps !

[Tarjani]

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:

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

[Mira_Ghaly]

@Tarjani 

can you remove highlighted part?remove till 

=

[Tarjani]

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]

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

[ViditGholam]

I will try this out and let you know.

[Tarjani]

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]

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]

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

[ViditGholam]

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 

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- 

[Tarjani]

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]

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

[Mira_Ghaly]

@Tarjani 

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

[Tarjani]

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

[Tarjani]

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

[ViditGholam]

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.

[Tarjani]

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.

[ViditGholam · Accepted solution]

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 - 

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

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

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

Lets go to the flow for this 

Out put - 

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.

[Tarjani]

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: