Cascading Lookup - M:M relationships

[MJL1]

I'm building a model-driven app.  I want to create a form that uses cascading lookups - lets call them lvl1, lvl2, and lvl3 (there may be more lvls but right now I only have three).  A single term from lvl2 can relate to multiple terms in lvl1 (i.e. 1:M from lvl1 to lvl2), likewise a term from lvl3 can related to multiple terms from lvl2.

If it helps to understand this from a data perspective, think about this; city names are likely unique within a state/province, but may not be unique within a country, likewise state/province names within a country vs. globally. The relationships are inherently many to many.  How do you set up a three level cascading lookup that honors those relationships and allows filtering lvl2 by lvl1 and lvl3 by lvl2?

I have two questions:

1) How do I set up the lookups i.e. define the tables?

2) How do I use them on a data entry form so that lvl2 is filtered by the applicable lvl1 terms and lvl3 filtered by the applicable lvl2 terms?

What I've already tried
Simple lookups
I've created a table with lvl1 terms and a table with lvl2 terms.  The lvl2 terms table has a lookup to lvl1 terms.  When I import data into this table things work fine because lvl1 terms are unique.  I can have "duplicate" lvl2 terms that relate to different lvl1 terms.
I've also created a table with lvl3 terms that has a lookup to lvl2 terms.  When I try to import data into this table I get failures because there are duplicate lvl2 terms and the import doesn't know which lvl2 term the lvl3 term should be related to.  This is the problem that doesn't have a workable solution, so far as I can tell.  On a data entry form, when I create columns that use these lookups I'm able to use filtering to show only the cascaded/applicable values.

Many to Many tables between lvl1 & lvl2 and lvl2 & lvl3

I've created lvl1, lvl2, and lvl3 tables and imported the unique values for each lvl into those tables.  I've then created a M:M for the relationship between lvl1 and lvl2 that has M:1 relationships to lv1 and lv2.  I've done likewise for the relationship between lvl2 and lvl3.  I've imported the relationships into those tables.  So far, so good.  On a data entry form, when I create columns that use these  lookup tables:

- the column for the lvl1 value references the lvl1 lookup table,

- the column for the lvl2 value references the M:M table that joins lvl2:lvl1

- the column for the lvl3 value references the M:M table that joins lvl3:lvl2,

I'm unable to filter lvl3 column based on the choice in lvl2 column (I am able to filter lvl2 column based on the choice in lvl1.  I'm not sure I understand why that works but I'll take it.).  This is the problem that doesn't have a workable solution so far as I can tell.

Can someone point out what I'm doing wrong?  Is there another alternative to this?

[Michaelfp]

I would recommend to use a javascript to handle that, based on change of each lookup field you apply the filter using fetchxml allowing to to use M:M relationships.

addCustomView (Client API reference) in model-driven apps - Power Apps | Microsoft Learn

If my answer helped you, please give me a thumbs up (👍). If solve your question please mark as solution ✔️. This is help the community.

[MJL1]

@Michaelfp 

Thanks for the reply.  I was fearful that I wouldn't receive one.  I've done a hours of research on this and have turned up nothing that directly applies.  A forum post was a last resort.

Based on a read of the link you provided I think this might be a solution, if I was a coder :-).  I don't know how to write javascript.  I think I could figure it out but, I don't know what the syntax of The fetchXml query for the view (in the fetchXml: String parameter would look like or where I can find one?  Is this something I can copy/paste from within the app?  Would you be able to provide an example (The rest I think I can figure out on my own).

[Michaelfp]

@MJL1  you can use the tool XrmToolBox and using the plugin FetchXml Builder you can build the queries using the UI. It will gonna help you.

For the javascript you first need to setup on OnChange() event in the form to call the javascript.

function OnChangeLookup1(executionContext){
 var formContext = executionContext.getFormContext().

  var lookup1Attribute = formContext.getAttribute("new_fieldname");

  if(lookup1Attribute != null)
{
  var looukp1Id = lookup1Attribute.getValue() !== null ? lookup1Attribute.getValue()[0].id : undefined;

   if(looukp1Id) {
    var lookup2Control = formContext.getAttribute("new_field2name");

    if(lookup2Control !== null){
    lookup2Control.addCustomView("<Guid Value", entityName, "View Name", "your fetchxml", layoutXml, true);
}
}
}
}

[MJL1]

To close this off - I wound up not going with the M:M table solution, although I feel like that would have been the most elegant.  I couldn't figure out how to make it work right.  I wound up using a M:1 that references the first table in my hierarchy and then the same for the third.  In the 2nd table I have rows that have duplicate values in the Name column but are distinguished by referencing a different row from the first table.  Likewise with the 3rd table referencing the 2nd table.
I'm not really happy with this (because of the duplication) but it works.  I've run out of time to tinker with this.