cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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)NameEmployee Code
rdtfyui87564etd678Amit1133
5edtfuyvbhit7reogjBikash1345
3432546y5esgaahyTommy1245

 

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)NameQuarantine StatusPopulationMaster_Lookup (this is a lookup field)
eqwfsgdfbhdse3456AmitReservationrdtfyui87564etd678
345ygfbxnhcds3trgaBikashIn-Quarantine5edtfuyvbhit7reogj

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisPiasecki
Super User
Super User

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.

View solution in original post

10 REPLIES 10
ChrisPiasecki
Super User
Super User

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
Frequent Visitor

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
Not applicable

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
Not applicable

Hi @MarlonCabrera ,

 

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

 

Thanks,

Amit

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
Not applicable

Hi @ChrisPiasecki 

Sure, I will try and let you know.

 

Prakash4691
Super User
Super User

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
Not applicable

Hi @ChrisPiasecki 

 

Thank you for the response!

In my Preventive Quarantine Table data is like below:

PreventiveQuarantine_Id (this column is Unique Identifier)NameQuarantine StatusPopulationMaster_Lookup (this is a lookup field)
eqwfsgdfbhdse3456AmitReservationrdtfyui87564etd678
345ygfbxnhcds3trgaBikashIn-Quarantine5edtfuyvbhit7reogj
46rtdfgitr6768ruyg6AmitFinishedrdtfyui87564etd678

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:

AmitSah_0-1618508456287.png

 

Please help me to get this fetchxml correct.

Thanks,

Amit

 

ChrisPiasecki
Super User
Super User

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.

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (4,618)