various table loockup field

[Kesh]

Hi everybody,

I have an issue with a specific dataverse architecture:

I have 4 tables:

- Project

- Sub project A (with 9 columns) with a Project loockup field

- Sub project B (with 17 columns) with a Project loockup field

- Sub project C (with 5 columns) with a Project loockup field

- Comments 

Each sub project are different an defines sub-projects very differents from sub project A to C. This is why I cannot group sub projects in a single table.

just a little sketch:

Comments can be done on sub project A, B or C and also on projects.

How could I (in an elegant way) link comments to various tables avoiding having 4 loockup fields ?

Thank you for your help

kind regards

Quentin

[rampprakash]

Hello @Kesh 

As you have Mentioned it is LINKING, so Linking will be happened with a REFERENCE, so we need a Reference LOOKUP field here in each entity

Please mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com

[GuidoPreite]

from your post is not so clear the architecture you want to achieve, but if you are looking for a solution where inside a project you want to associate a SINGLE record that can be A, B or C you can use a polymorphic lookup.

Polymorphic lookups are a special type (think of them like the Customer lookup where you can associate an Account or a Contact) and right now can be created with a tool like Polymorphic Lookup Creator (inside XrmToolBox)

hope it helps

[DeviKrishna]

Hi @Kesh,

Can you elaborate your purpose for comments field? Is it going to be a free text to get the user comments at project or sub project level? or It is going to be some pre-defined values which user needs to pick from list  at project/sub project level??   I assume you are trying to achieve from pre-defined values. In this scenario as @rampprakash suggested you need to use look up fields at each entity level. 

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

My Blog: Dynamics 365 Key Topics – https://d365topicsbydk.com/

My YouTube Channel : https://www.youtube.com/channel/UCxSIryP2ah2VpEFr-Z72t1A

Regards
Devi

[dpoggemann]

Hi @Kesh ,

One thought here is to create the Comments table as an "Activity" Type Table.  This will allow you to attach the Comments table to any of the different sub project tables (and other tables) without having multiple lookup fields.  Just like an Email and a Task can relate to multiple tables the same thing with the Comments table.  

The next step would be to be able to show the rollup view of all of these Comments on the Project table in a grid I expect...  The Rollup View for Activities does not work out of the box unless it starts with Account or Contact entity so you might want to look at this PCF control (https://pcf.gallery/pcf-grid-activity/) as an option or do something custom to show the related activities from your child sub project tables.

If you do go down this path, please review security as this is different for Activity tables vs. Standard tables

[dpoggemann · Accepted solution]

Hi @Kesh ,

Please review the following as well with Polymorphic Lookups...  Might be better option but not sure if this is still in preview...  https://readyxrm.blog/2021/07/15/using-multi-table-lookups-polymorphic-relationships-in-microsoft-da...