Customer Engagement & Dynamics CRM Forum

 View Only
Expand all | Collapse all

Dynamics Multiple Entities Single Form

  • 1.  Dynamics Multiple Entities Single Form

    Posted May 03, 2019 02:18 PM
    Long time lurker and I need some help after looking everywhere I know to on this subject.  Sorry if this is long and winding in advance.

    Overview:  I am at a non-profit community development lending institution that does both consumer and commercial lending.  We have a ton of items that we need to report out to our funders and to the CDFI government branch.  We are currently rebuilding Dynamics 365 on prem from the ground up to launch later this year.  We have roughly 500 fields that we need to track from the time an opportunity arises until it becomes a loan.  We have a base of roughly 150-200ish fields for all loan types, and then another 200-300 that are product dependent. 

    Issue:   How, if at all, can we effectively display multiple entities on a single form?  The other alternative is to create it all into the single entity, is 500+ fields an absolute No, full stop? 

    While best practices on DB structure says to de-normalize as much as possible, in our whiteboarding it has translated to around 12 entities that encapsulate all these data fields we need to track.  These include the main bucket opportunity, and then risk, metrics, and product specific entities.  In this scenario, all related entities would have a 1:1 lookup field on the opportunity form.

    I've played with sub grids and quick view forms to no avail because the sub grids sometimes have 50 fields in them and data entry becomes a mess in those.  Quick view forms being read only prohibit those from being viable either.

    So what's our best options here?  Dump everything into one entity?  Is there a 3rd party provider that can leverage a form that encapsulates multiple entities?  Some alternative that I haven't discovered yet?  

    Any help would be truly appreciated and I can provide more detail if need be.

    Drew Kingery
    Software Developer
    Seattle WA

  • 2.  RE: Dynamics Multiple Entities Single Form

    Posted May 03, 2019 03:38 PM
    @Drew Kingery - Have you thought of using the Business Process Flow to help the users navigate across the multiple entities? Business Process Flows can have logic embedded to auto change the stages based on prior field criteria. For example, if in the first stage a field has a drop-down list, and a certain value is selected, then subsequent stages can be shown/hidden. 

    Also, you can trigger Actions and Workflows from the Business Process Flow to provide additional automation to your process.

    Aaron Back, MCSE
    Sr. Microsoft Dynamics 365 Consultant
    CRMUG Board Member
    CRMUG Chapter Leader - Cincinnati, Ohio

  • 3.  RE: Dynamics Multiple Entities Single Form

    Posted May 03, 2019 05:55 PM
    Edited by Drew Kingery May 03, 2019 05:58 PM
    @Aaron Back

    I've looked at those, and we will have a process flow set up wherein the newly created loan stages will contain a set of fields per stage that we expect to collect.  Where I run into issues is when, for instance, a product that contains, say 45 unique fields is selected, and those fields may or may not be known at any point in the lifecycle prior to it becoming a funded loan so we would want them displayed throughout the life of the opportunity.  As an example, we have two products that need to have the number of living wage jobs created, and that may not be collected until the credit is going to committee or the applicant may know it right at the time of applying.

    I don't mind having one or two entities where user's are navigating too to enter data, but any more of that and I fear they won't know where to go to look for / enter data.

    Q:   if you have a field in one of the stages that is from a different entity, can it be modified from the user from within the business process flow stage of the parent entity?

    Q:   thoughts on a general rule of thumb for max number of fields that should live in an entity?  Obviously the 1024 SQL limit and the additional costs of lookups and currency fields all are hard limits that shouldn't be getting close to approached.

    Drew Kingery
    Software Developer
    Seattle WA

  • 4.  RE: Dynamics Multiple Entities Single Form

    Posted May 03, 2019 05:53 PM

    Hi Drew,

    I probably spent too much time trying to think of a thoughtful response to this but what you are trying to do comes with a lot of question.

    I'll go ahead with some ideas on how I would try to tackle this problem.

    I'd heavily focus on automating the experience as much as possible and make the system smart.  With each field I would consider - how do I hide this field from the user and populate it automatically?  Is dynamics the correct tool to capture all of this data?  Could I capture some of it in a different tool and simply import the data into CRM nightly.  Is there proper reporting in place so I can capture any of my data entry mistakes quick?  I would avoid making too many fields required.  I wouldn't have any data on the form that someone in data entry doesn't need to know.  

    Here are some solutions that we've custom developed to solve these types of issues.

    On our account request custom form we have 8 different Account Request Types (New Dealer, Dealer Change, Parts Dealer, etc) and so I've set up the form where each section is only visible when the request type is chosen.  Microsoft Flow allows us to send approval emails and track dates when an approver clicks 'approve' rather than have the approver come into dynamics and enter the date.

    On our custom scorecards you will choose a 'scorecard configuration entity' which has all of the data to populate the label of these generic fields (but this is harder to report on).

    I'm with you though - If there is a product that allows you to edit data on related entity I'd like to know about it. 

    Travis Judd
    CRM Developer
    Trek Bicycle Corporation

  • 5.  RE: Dynamics Multiple Entities Single Form

    Posted May 03, 2019 06:22 PM
    @Travis Judd

    First, sorry for bringing you down in this wormhole but thank you for going there!  I have feeling I'm going to wind further down it just trying to respond to you.

    I've struggled with this issue in our current CRM 2011 and I'm both embarrassed and frustrated that I still don't feel like I have any better of a proposal of a design to handle this gracefully, as it is the core design decision of our new system for prospects.

    I'll try to address / comment to a few of your points:

    Automation:  I'm all for it and pushing for it wherever I can.  In some places it's clear we can incorporate it, in other places the fringe cases start to push against it.  Example, our consumer applicants all apply online.  From that, we can pull in address info from Google API, automate a lot of default metrics because of the product type, automatically generate their pre-approval loan number etc etc.  Then, once a week, we get a paper application coming in and having those fields that are either hidden or read only because they were already set become a problem.

    Is CRM the right tool for the input:
    This comment a really interesting one.  I actually need to dwell on that a little longer before I can think of what the alternatives are.

    What I can say about CRM, is that we do want it to be the one truth for all preloan data, including the underwriting.  This is because we have fought the one truth issue for so long and the current state of affairs here is a mess with around 100 fields living both in our CRM and then in our Loan Servicing systems (LS).   At one point, CRM didn't exist here so LS was our truth, and then over time CRM came along, grew, and then became essential to pre loan data so a lot of the data for the LS was going into CRM and then pushing into LS.  But now people report from the system they've felt most comfortable with and then data differences arise and then employee confidence in data quality began to drop.

    Looking at our loan stages, I'm thinking we'll only have around 4-8 required fields per stage.  

    Proper reporting:
    For data reporting, we use JET Reporting, which is basically a data warehouse OLAP cube system where now our CRM and LS data are pulled together although direct reporting out of both systems is still done in situations.  the consumer team uses a view in CRM to currently identify missing data points, but finding incorrect data points is more difficult as they are often one off fields that have to be investigated and then input into the system.  There's often not a 'rule' that can be defined for the system to know a data point is inaccurate.

    When you say account request custom form, is that a form built into CRM or does it live outside of the system?

    Thank you so much for the brain waves that you have spent on this, the PM of this project that I interact with is shocked that this isn't something that is easily answered.  His response was "Surely we can't be the only one's needing or fighting this can we?"  I told him we're not alone, but I have not seen a silver bullet for it in all of my time researching it.

    Drew Kingery
    Software Developer
    Seattle WA

  • 6.  RE: Dynamics Multiple Entities Single Form

    Posted May 05, 2019 07:17 AM

    I think you may have been close with the quickview forms.

    Have your parent record as the "summary" view, with quick view forms clearly labelled Section0=>N

    Use ribbon workbench to add buttons Section0=>N which open the child entity forms
    Each button only appears if the relationship exists (or you could add a rule if it doesn't exist to create it)

    The child entities can either have just a return to summary or the same method of navigating to other child entity forms.

    Hope this helps

    Jamie Hirst
    Functional Specialist
    Hirst Dynamics

  • 7.  RE: Dynamics Multiple Entities Single Form

    Posted May 06, 2019 10:56 AM
    Would a task flow be any help it can be run across multiple entities?
    Not sure if this is out of preview yet but would embedding the different entities as canvas apps on the form work?

    Benjamin Terebelo
    Madison CRES

  • 8.  RE: Dynamics Multiple Entities Single Form

    Posted May 06, 2019 01:17 PM
    Hi Drew,

    A few thoughts for each group of fields, based on my own experience managing a system with 500+ fields across multiple entities. 

    Is the field grouping a sub process often not included? You can still keep on a single entity, if it occurs frequently. In this case, the OOB form editor business rules can hide single fields, but it is probably easier to put them on separate sections and tabs, then set their display state using JS.  Keeping the fields on the same entity simplifies reporting, but will take up fields. So, if the fields are seldom used and high in quantity for a sub process, that's where you may wish to move to a separate entity. 

    Is the field grouping a sub-process that can be repeated or have multiple instances at once? If so, it is probably best to configure on a separate entity with a 1:N relationship. Note, this is more accurate, but more complicated reporting. Usefield mappings and/or JS API calls to auto populate the main fields when users create these related records. 

    Do you have specific security requirements for the group of fields? While field level security can isolate them, it may be easier to put them on their own entity. Be aware that if you define it as an activity entity however, the security for those is all grouped together. 

    Are you comfortable reporting and configuring workflows to work multiple relations deep?  Out of the box, system views can filter multiple layers deep, but only show columns from one degree away. Workflows also can't reference more than one relationshp deep.  You can hack this with actions, etc. but be aware that moving fields to a separate entity complicates both reporting and automation.

    How frequently does your process change? The business process flow can span multiple entities, BUT, it has serious bugs relating to process stages being added / removed. If stages are removed, you will likely get invalid traversed path errors.  And if users don't use it, you'll have extra work trying to ensure its stage matches where you are in your process.  

    Happy to chat with you if you have specific questions. 

    Ryan Perry
    Business Systems Analyst
    Auric Solar

  • 9.  RE: Dynamics Multiple Entities Single Form

    Posted May 07, 2019 07:08 AM
    Hi Drew,

    It's possible to use the TKDialogs graphical tool to build custom user interfaces to embed in the CRM UI. The TKDialogs processes can read/write data from multiple CRM entities (including custom CRM entities and even data external to CRM), and can include logic and calculations to show only the questions that are relevant to each user's individual journey.
    Visit for more information.​

    Ian Booth
    Team Knowledge Limited
    Warrington, UK

  • 10.  RE: Dynamics Multiple Entities Single Form

    Posted May 17, 2019 09:53 AM
    Hi Drew,
    I'm newish to the group - but from a user perspective I would ask this - do all users need to see all 500 fields all the time?  I would lean towards probably not - and the users would be mighty grateful that you don't give them a screen like that.  While others have mentioned business process flows (which I would lean towards if you are entering fields across multiple entities) and quick view forms (which I was personally a fan of) - also the new UI is much more aware of related entities and allows the connections to be brought up almost "on demand" as tabs.
    Personally I would also look at role based forms - if the managers need to see all 500 - then build the monster form for them; if someone doing data entry only needs to see sections 2 and 3 when the loan is at status X - build for them.

    Angela Worland
    Microsoft Dynamics CRM Consultant
    Dublin OH

  • 11.  RE: Dynamics Multiple Entities Single Form

    Posted May 17, 2019 02:09 PM
    You should never record the same data in two places. If you have any 1 to 1 relation ships then you should be collapse those into one entity then use different forms/tabs for optimizing the display. There can also be business rules/Flow to hide/un-hide fields and tabs according to what ever logic you might want. Also if you really do have a entity that still has a legitimate 1:1 then you can use a custom Web Resource to display that data, particularly if you create it to take parameters to show different data from different associated entity's upon selection or inference from the form data. It is a lot more investment in dev but might fit your scenario well.

    Nicholas Anderson
    Information Technology
    Wagstaff Inc.
    Spokane WA

If you've found this thread useful, dive deeper into User Group community content by role