Choices vs Lookup Tables in Microsoft Dataverse

[@paulkroon]

He Joe, first many many thanks for setting up this video, I really didn't expect to get this answer and so soon. I watch the video three times to really take the content in. Your insights surely will be helpful as are the five-question to consider and will help me get a better grasp on the topic. Also, I surely understand your point about it depends, so there is not one conclusive answer to be given. What I also left out in my question, so I noticed, is that we are dealing with choices like record types, phases, and similar questions you would like to have selected in a Combobox or based on a phase step (trigger kind of approach). I general these options won't change a lot, but still can change over time, so in this case, based on your logic you would say choice. The problem I have is that you then lack the opportunity to disable choices without losing the history (or capability to find them). Now I understand de-activation of choices is something MS is working on as a future feature, so maybe in time, this is not a downside we have to consider. Another thing that is kind of a problem (in some sense) is the lack of a sorting option, which you of course would like in phase, status, or type choices. This is btw a topic when you leverage the choices in Canvas Apps or external Apps, but I think it is safe to say we use Canvas App a lot in the future, won't you agree. For your second question. When it comes to phase, status, or type choices I would say to let the user make changes is not even a real option. Basically, I do not really believe in this vision on Citizen Developers. I believe developers need to take more control of what people can do and especially what they shouldn't be able to do, simply because if they can they will crash your applications. Of course, this is different for lookup tables they need to be able to alter. For the third question, I am not really sure I agree with you on this. Of course, a choice column would be easier to implement in Power BI I agree. But when you set up the Power BI model in a proper way the Lookup table will give you a lot of benefits in sense of dicing and slicing your visuals. Now I made just a few Power BI report myself, so it good be quite well the case that I am totally wrong here for sure. The fourth question is a good one and one to consider. I have a lot of experience with Canvas Apps and sometimes we use SharePoint here as a data source. What I do is simulate a SQL-like database model. Here I never use choices, partly because they lack an integer key, but always lookup lists. When this lookup list is driving status or phase then I put a lot of logic in this status/phase lookup list. The reason is this status/phase is saying something about the record inside of the primary table to which it is connected. Then based on the selection I fill columns of the primary table based on settings in the lookup table. This can be an active field, but also a total price calculation even. So metadata can surely be something you like to store now or in the future. Another thing here is that when I visualize my model (before I start building it out) I try to keep future development in mind. So I consider reporting, flexibility and scalability because I believe simple apps which are successful will be extended with additional features. Of course, we can not predict everything, but we surely can model in a way we don't have to rebuild (parts) of our model, forcing us to rebuild (parts) of our to the model-connected solutions. I hope this makes sense to you in a way. The fifth question is a good pointer also and in my opinion maybe even a topic for another video. Didn't get in touch with this multi selection question a lot till now, but when I did I decided to create a separate table myself to handle the many-to-many relationship. In my case, the reason is that when you like to use the N-N somewhere else then in the model-driven app this will provide a headache in who to set things up. For example in Power Automate out-of-the-box N-N relationship tables are not to find when searching, so you need to get the behind-the-scenes names to reference the table. Now I found out who you can find this quite easily at the moment I needed to use this, but I am not a robot and can't remember everything always. So when I need this knowledge again in let's say a year then I need to figure it out again right. On the demo part. You are right that with a small list a choice field works absolutely easier from a user perspective. Then again, maybe MS should provide options to let the control behave like it is a choice field, beïng only able to pick one option and not able to search in the options. Kind of similar to how they build the Combobox in the Canvas App. Won't you agree that this would benefit us all? Maybe there is already a PCF that provides this functionality, do you know one? Overall a Long story I know. Really interested in your thoughts on this. Again, really happy with the video, so a lot of thanks to the great content you bring to the community.

[@CRMChap]

No problem Paul, it's a good debate to have and I enjoyed making the video, so thanks for the inspiration 😃 Choice fields are limited by the fact that you can't disable them without removing them completely. This then has unintended consequences and could cause integers to randomly appearing on records. A potential workaround to disable a choice is to hide it completely using JavaScript - not ideal, but certainly doable. Sorting is also missing, but you have options at your disposal to work around this via a PowerFX formula from a Canvas App perspective. There is no issue with developers taking that level of control, provided that this is rooted in sound business justification and is not impeding the business unnecessarily. Unfortunately, I do come across situations where this is entirely out of kilter, so it's something you always need to be on guard for. IT and the business should always be working for the benefit of all. Sorting, grouping, and general reporting involving aggregate data will see massive improvements if you are working with integer-based data sources. Of course, the lookup table option gives you the scope to implement this yourself if you want, but a choice could be argued as being a more straightforward and easy route for us to build out. Correct, there are some particular frustrations with native N:N relationships, and I've hit this issue before when working with tools such as Azure Data Factory. I tend to go down the manual route when I know I will need to perform some SDK operation against the intermediary table in the future. I don't know of a particular PCF control that would achieve the functionality you mention, but again, undoubtedly doable. But yes, having a combo box type option here would help, particularly if you're working with a huge choice field I think I've answered everything 😅 , But let me know if not. Again, a great topic, so thank you for raising your original question on it.

[@yodu9908]

Great video!

[@kranthidn]

If I have multiple tables say employees ( emp Id , emp name) , projects ( project id, emp Id, project name, billing date tenure) , Client (project id, clientid, client name) Now my employee table is connected to project table and project table is connected to billing table. How to create view for each client id to list employee names ? I am able to display all fields of project table since in view I can get all related fields but I am unable to bring all values of employees as employees table is not related directly to client table. Please change tables if required for better understanding , I am basically looking on how to create views to show data from multiple tables as we don't have option to write join in dataverse

[@CRMChap]

Hi Kranthi, for this type of scenario, I would suggest investigating custom FetchXML queries or see if it's possible to use a Power BI embedded report/view to achieve your requirements.

[@adil933]

Would you be able to go further into detail on what you mentioned in response to Question 5? So we can use a lookup to allow multiple selections using the PCF tag picker. What if we want that selection to already be filtered down based on a previous selection? For example, hierarchy is Client/Product/Project . If a user is creating a new Project, if must be tied to a specific Client, but can apply across many Product lines. So first, the user chooses the Client, then he can should be able to pick one or many Products which that Project applies to.

[@CRMChap]

Hi Adil, in that situation, you would need to implement logic within your control itself to only show the data you want to associate - from there, you would then perform the appropriate Web API request to populate the data accordingly into Dataverse.