cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joseph4dev
Frequent Visitor

Best way to handle multiple levels of lookup inheritance - brain exploding :)

Hi everyone,

 

Rather than go down what I think is a very complicated rabbit hole, I wanted to check with the community the best way to achieve my scenario:

 

This is what my main table relationships looks like:

joseph4dev_0-1664758967501.png

 

Each table has a lookup column to the one above it. Every table has information that the child-table needs.

 

If Invoice needs info from Contract, a simple quick view control achieves it.

 

My predicament occurs because Invoice also needs info from Project and Organisation.

 

I tried adding N:1 relationships but while that let me select the relevant table in the quick view, it did not show the related data.

 

The problem is I want to avoid also having Project and Organisation lookup columns in the Invoice table. You would then have to make sure you are selecting the right combination of rows. Messy and prone to error.

 

So I then tried solving it using Power Automate Flows but it got extremely messy trying to account for the different scenarios.

 

Appreciate any advice on the best way to handle this. It can't be a rare situation?

 

Cheers.

1 ACCEPTED SOLUTION

Accepted Solutions
joaodsantos
Resolver III
Resolver III

Hi,

so, agree with 1. makes perfect sense.

 

I'm a bit unsure about 2., I'm not sure what you mean by "checks the higher level associations and sets them as additional lookup fields on the root object". If you are creating a new invoice from the main form, you could let the user on create (or force it by making it mandatory) select the Contract it belongs to and then lock the field after the first save. Also, if you indeed add a Lookup to the Organization on all tables like I suggested I would just grab the account value from the contract and use it on the invoice account field.

Not sure if you are talking about more than this, I feel like this would be enough.

 

for 3. that makes sense and in line with what I was talking about but there's no way to map fields for 2+ levels above, just the immediate tables.

 

So, just to try and summarize my suggested approach (which I think is slightly different from your because of the Organization lookups):

  • Table: Organization
  • Table: Project
    • Lookup to Organization (N:1 relationship)
  • Table: Contract
    • Lookup to Project (N:1 rel with mapping on the Organization field)
    • Lookup to Organization (N:1 rel)
  • Table: Invoice
    • Lookup to Contract (N:1 rel with mapping on the Organization and Project fields)
    • Lookup to Organization (N:1 rel)
    • Lookup to Project (N:1) - this is the one you're trying to avoid but I think that unless you duplicate the values you need from the Project on the Contract table you'll need this to get the related data out. Using the steps you mentioned above should minimize any issues.

In terms of guided way of doing this, any step in particular you need help? I think all the mappings should be straightforward from the relationship properties to add. Business rules should also be straightforward as well, here an example.

 

hope that helped and my "summary" didn't add to much confusion 🙂

View solution in original post

5 REPLIES 5
joaodsantos
Resolver III
Resolver III

Hi,

Not sure what the right solution here is, part of if feels like an data model design decision but it's also something that you should be able to achieve with power automate, though it could become very messy very quickly like you said.

 

I'm incline to suggest the Lookup fields on the invoice (and maybe contract?), data model wise, I think it's the best option. It also replicates the type of structure Microsoft uses on the Dynamics 365 apps (Customer Engagement, Sales, etc). You have multiple entities (Sales Order, Invoice, Quote, Contract, etc), and although they're linked together by their preceding table as you have on your model at the moment, but they all also all link back to account.

That way to can easily from the Account list all Contracts, all Invoices without having to navigate down the relationship tree.

You could also use a mix of some functionalities to make it easier to copy the values to those lookups. Mapping fields on relationships, filtered lookups, power automate/workflows to copy the correct values without forcing the users to fill them out.

i.e. you're on the account, create a Project. From the project hit the create contract and if you have a relationship mapping configured the Account is copied automatically to the Account lookup on the Contract. If you're creating it directly from the Contract form then set the account automatically based on the selected project or limit the projects based on the selected Account. Then same logic for invoice, if you create from the Contract just use mapping to bring some fields across.

This if you go for the changes to the data model.

 

Using power automate I'm sure you can get all the details, you might just have to do a few "get row by id" actions to bring the records one by one.

 

I'm also not sure from your description what use you're going to give to those fields/values, display on the form, use for reporting, other uses?

dpoggemann
Super User
Super User

Hi @joseph4dev ,


The approach identified by @joaodsantos is exactly what I have done in a number of projects.  I carry the hierarchy down for each entity so when you get to Invoice  you have lookups to the Contract, Project and Organization.  

 

The main watch out of this approach is if you change the Project on the Contract, the Invoice entity's Project would not be updated.  I have done this in the past by restricted this type of change from being completed.

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew
joseph4dev
Frequent Visitor

Thank you both @joaodsantos @dpoggemann I appreciate your input and help.

 

Considering your advice, how does this sound as a proposed plan?

 

1. To prevent "butterfly effect" style problems, an object cannot alter it's associated lookups once it has been created. (Business rule lock)

2. When an object is created independently of the associated lookup (e.g. creating an invoice on the main page vs the specific contract form) it automatically checks the higher level associations and sets them as additional lookup fields on the root object. Because of (1) there is no risk to corrupt data from updates to lookups. (Power Automate rule-set)

3. When an object is created via the form of it's parent (e.g. on a contract form, "new invoice" button) the lookup is auto-set to the parent, and similarly any "grandparent" lookups are set. (Field mapping)

 

How does that sound? My only uncertainty is around step 3 - can mapping be accomplished for levels 2+ above? (e.g. invoice maps to contract, and then maps to project, and finally maps to client organisation). And is there a guided way to achieve this?

 

Thank you so much, take care.

joaodsantos
Resolver III
Resolver III

Hi,

so, agree with 1. makes perfect sense.

 

I'm a bit unsure about 2., I'm not sure what you mean by "checks the higher level associations and sets them as additional lookup fields on the root object". If you are creating a new invoice from the main form, you could let the user on create (or force it by making it mandatory) select the Contract it belongs to and then lock the field after the first save. Also, if you indeed add a Lookup to the Organization on all tables like I suggested I would just grab the account value from the contract and use it on the invoice account field.

Not sure if you are talking about more than this, I feel like this would be enough.

 

for 3. that makes sense and in line with what I was talking about but there's no way to map fields for 2+ levels above, just the immediate tables.

 

So, just to try and summarize my suggested approach (which I think is slightly different from your because of the Organization lookups):

  • Table: Organization
  • Table: Project
    • Lookup to Organization (N:1 relationship)
  • Table: Contract
    • Lookup to Project (N:1 rel with mapping on the Organization field)
    • Lookup to Organization (N:1 rel)
  • Table: Invoice
    • Lookup to Contract (N:1 rel with mapping on the Organization and Project fields)
    • Lookup to Organization (N:1 rel)
    • Lookup to Project (N:1) - this is the one you're trying to avoid but I think that unless you duplicate the values you need from the Project on the Contract table you'll need this to get the related data out. Using the steps you mentioned above should minimize any issues.

In terms of guided way of doing this, any step in particular you need help? I think all the mappings should be straightforward from the relationship properties to add. Business rules should also be straightforward as well, here an example.

 

hope that helped and my "summary" didn't add to much confusion 🙂

Hey @joaodsantos 

 

For 2, I think we are saying the same thing, I just worded it in a confusing way. Here is a screenshot of the rule that I set up in power automate and it is working nicely:

joseph4dev_0-1665161430935.png

joseph4dev_1-1665161452300.png

 

What happens is... When an invoice or a contract is created, it checks the level above it, and takes the lookup value and copies it to its own lookups. Hope that makes sense

 

I ended up not needing point 3 because point 2 worked really well. Just have to deal with the limitation than when an invoice, contract or project is being created, it's related table lookups are locked once created. Which point 1 achieved.

 

I think we're actually on the same page...! except instead of using any mapping, I fulfilled it all through 2 power automate rules (one for invoice, one for contract).

 

Thank you so much for your input, seriously.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Users online (2,007)