How to filter a table for a gallery using related lookup field from another table and do a bulk insert for the filtered data

[Anonymous]

Hi Experts,

I am having one requirement where I have to filter a table data using a lookup field present in another table. (My data source is CDS/Dataverse).

Below is the scenario:

Table1 (i.e PopulationMaster):

I am adding some dummy/random data

PopulationMaster_Id (this column is Unique Identifier)	Name	Employee Code
rdtfyui87564etd678	Amit	1133
5edtfuyvbhit7reogj	Bikash	1345
3432546y5esgaahy	Tommy	1245

Table2 (i.e Preventive Quarantine):

In below table, we have PopulationMaster_Lookup field which is related to Table1 i.e PopulationMaster.

PreventiveQuarantine_Id (this column is Unique Identifier)	Name	Quarantine Status	PopulationMaster_Lookup (this is a lookup field)
eqwfsgdfbhdse3456	Amit	Reservation	rdtfyui87564etd678
345ygfbxnhcds3trga	Bikash	In-Quarantine	5edtfuyvbhit7reogj

Now, my requirement is to filter the PopulationMaster Table where Quarantine_Status <> "Reservation", here Quarantine_Status is present in Preventive Quarantine Table. So, the output which I want is below:

Name

Bikash

Tommy

Also, once I am able to show the above records in gallery then user wants a bulk insert to Preventive Quarantine Table for the multi-selected records with Quarantine_Status = "Reservation".

How can I achieve this requirement? Any help will be appreciable.

Thanks,

Amit

[ChrisPiasecki]

Hi @Anonymous,

To filter the PopulationMaster Table where Quarantine_Status <> "Reservation":

I'd recommend creating a view in Dataverse. You can use a left outer join in a FetchXML query to get PopulationMaster records where they are "not-in" the Preventative Quarantine table, using the Quarantine_Status equals Reservation as a filter condition. You can use the XrmToolbox FetchXML Builder to help build the fetchxml query. Here are a couple references below to assist:

https://docs.microsoft.com/en-us/powerapps/developer/data-platform/use-fetchxml-left-outer-join-query-records-not-in

https://msdynamicsworld.com/story/xrm/create-advanced-views-microsoft-dynamics-crm-using-fetchxml-builder 

You can then take all the records in the view using a ForAll and add them into a collection using Collect, setting the columns you need for inserting to Preventative Quarantine. 

You can then use a Patch(PreventiveQuarantine, <collectionname>) to do your bulk insertion.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

[MarlonCabrera]

Hi @Anonymous !

In this topic  Solved: Re: Filtering by a search field in a SharePoint li... - Power Platform Community (microsoft.com) 

@WarrenBelz explain the filter.

To save, as the field is complex, there is an ID that is added by sharepoint.

The source list has the sharepoint ID, you need this to save to your list.

Patch (datasource; complexField: {ID: myID}

[Anonymous]

Hi @ChrisPiasecki 

I was looking into the link shared to know how we can create view using fetchxml but I am not very clear on this? Is that from xrmtool I will get an option to create a view? But where it will store? In CDS only as a table?

And is that view will reflect the recent change data in canvas app in gallery?

Thanks,

Amit

[Anonymous]

Hi @MarlonCabrera ,

I checked the link but it is not same or near to my requirement.

Thanks,

Amit

[ChrisPiasecki]

Hi @Anonymous,

You would first go create a view for your appropriate table:

• https://make.powerapps.com > select environment > Data > Tables > Population Master > Views > Add View.
• Add your columns as needed and save. You won't be able to setup this advanced filtering through the view designer, so at this point you'll need to use the tool mentioned.
• Download the XrmToolbox if you don't already have it. From the tool library, find and install FetchXml Builder. Launch the tool and connect to your environment. 
• In the FetchXML builder, click Open > Select view. Choose your Population Table table and the view you just created. Use the designer to add your links (outer join) and filter conditions as needed per your requirement.
• Save the view

In your Canvas app where your gallery is for Population Master, you can select the view you created so that you'll now have your filtered records.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

[Anonymous]

Hi @ChrisPiasecki 

Sure, I will try and let you know.

[Prakash4691]

Hi @Anonymous ,

I hope gallery you mean the requirements is in canvas app.

If so, for gallery items property use: Filter(PopulationMaster, 'Preventive Quarantine'. Quarantine Status <> 'Quarantine Status (Preventive Quarantine)'.Reservation)

for bulk update use: for all with patch, this is just an example. Please do relate to it with your table.

With( { InactiveAccounts: Filter(Accounts, 'Primary Contact'.Status <> 'Status (Contacts)'.Inactive ) },
ForAll( InactiveAccounts,
Patch( Contacts, ThisRecord.'Primary Contact', { Status: 'Status (Contacts)'.Active } ) ) )

Regards,

Prakash

[Anonymous]

Hi @ChrisPiasecki 

Thank you for the response!

In my Preventive Quarantine Table data is like below:

PreventiveQuarantine_Id (this column is Unique Identifier)	Name	Quarantine Status	PopulationMaster_Lookup (this is a lookup field)
eqwfsgdfbhdse3456	Amit	Reservation	rdtfyui87564etd678
345ygfbxnhcds3trga	Bikash	In-Quarantine	5edtfuyvbhit7reogj
46rtdfgitr6768ruyg6	Amit	Finished	rdtfyui87564etd678

You can see that for "Amit" there are 2 records one is having "Finished" Status and other is "Reservation".

So, I don't want to get the Population Master Table record for Amit.

I was trying to get the desired Fetchxml but not sure of how to get it.

I am trying to build my fetchxml similar to below sql:

In below query "cre08_quarantinestatus = 985440003" is for Reservation records.

SELECT cre08_globalid, cre08_populationmasterid, cre08_department, cre08_cedula, cre08_company
FROM cre08_populationmaster
WHERE cre08_populationmaster.cre08_populationmasterid not in
(select cre08_populationmaster from cre08_preventativequarantine 
where cre08_quarantinestatus = 985440003 )
ORDER BY cre08_cedula DESC

My Fetchxml is like below currently:

<fetch distinct="true" returntotalrecordcount="true" >
  <entity name="cre08_populationmaster" >
    <attribute name="cre08_globalid" />
    <attribute name="cre08_populationmasterid" />
    <attribute name="cre08_department" />
    <attribute name="cre08_cedula" />
    <attribute name="cre08_company" />
    <order attribute="cre08_cedula" descending="true" />
    <link-entity name="cre08_preventativequarantine" from="cre08_populationmaster" to="cre08_populationmasterid" link-type="outer" alias="PQPMLink" />
    <filter type="and" >
      <condition entityname="PQPMLink" attribute="cre08_quarantinestatus" operator="neq" value="985440003" />
    </filter>
  </entity>
</fetch>

Below is the xrmtoolbox screenshot:

Please help me to get this fetchxml correct.

Thanks,

Amit

[ChrisPiasecki · Accepted solution]

Hi @Anonymous,

You need to outer join the records you want to exclude, which in your scenario is those with a quarantine status is Reservation. Then you need to add a filter outside of that query to only return PopulationMaster records where the join condition is not met (PreventativeQuarantineId = null)

Couple things:

• Change the operator on the quarantine status to eq (equals)
• In the population master entity, add a filter condition (entityname="PQPMLink" attribute="cre08_preventativequarantineid" operator="null")

<fetch distinct="true" returntotalrecordcount="true" >
  <entity name="cre08_populationmaster" >
    <attribute name="cre08_globalid" />
    <attribute name="cre08_populationmasterid" />
    <attribute name="cre08_department" />
    <attribute name="cre08_cedula" />
    <attribute name="cre08_company" />
    <filter>
      <condition entityname="PQPMLink" attribute="cre08_preventativequarantineid" operator="null" />
    </filter>
    <order attribute="cre08_cedula" descending="true" />
    <link-entity name="cre08_preventativequarantine" from="cre08_populationmaster" to="cre08_populationmasterid" link-type="outer" alias="PQPMLink" />
    <filter type="and" >
      <condition entityname="PQPMLink" attribute="cre08_quarantinestatus" operator="eq" value="985440003" />
    </filter>
  </entity>
</fetch>

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.