Look up specific value in another Table

[Stevieed]

Hi guys, got stuck for some basic problem if you can help.

I have below 3 tables with their columns:

User table

{Name (primary ID), Position}

Position

{Position (primary ID), Hourly Rate}

Timesheet

{Activity (primary ID), Name, Duration (in hours), Hourly Rate, Total $}

All "Position", "Name", "Hourly Rate" are looking up each other, I need to make this happen:

For every new timesheet record, i want to select a person's "Name", then the person's "Position" and the "Position's Hourly Rate" can show up in my Timesheet table, so I can do calculation in the Timesheet table.

This can be easily done with SharePoint list with the ability to select which column to look up, but this is not possible with Dataverse, I just dont understand how do you implement this feature?

I don't understand why when Microsoft present Dataverse as a more "mature relational database", they just delete some features that is useful because they think it doesn't fit the logic behind it??

[dpoggemann · Accepted solution]

Hi @Stevieed ,

The relation setup in Dataverse is done with Lookup columns that you would add to the Timesheet table.  

Example is you would add a column of type "Lookup" to the Timesheet table and you would select the table "User" as your lookup table.  This will create the 1:N relationship from the User table to the Timesheet table.  You add the new lookup column to your form and then it will allow you to select the user when entering the Timesheet data.  

Also, if you are using a model app you can add a tab or subgrid to the form to display all the timesheets for the User from the User page and if you create a new Timesheet from that tab or subgrid it will fill in the User to the Timesheet automatically.  

Not sure your relation to the Position / User / Timesheet table but assuming that the Position is tied to the User you would have the Lookup column added to the User table that would be to the Position table.  I would then add Lookup to the Timesheet table as well to the Position and default this from the User's Position at the time of the timesheet creation, you can set this through Mappings from the User to the Timesheet or a real-time workflow.  This would be good in case the user changes positions over time that you are setting the position on the Timesheet row.  You can then use a real-time workflow to also set the hourly rate defined on the Position to the Timesheet.

General Steps (to clarify a lot of the text above):

1. Create Lookup column on the User for the Position
2. Create Lookup columns on the Timesheet for Position and User
3. Setup column mapping for relationship from User to Timesheet (https://www.marksgroup.net/blog/dynamics-365-mapping-fields/)
4. Setup real-time workflow to populate Position on Timesheet from User if not populated and also set the hourly rate on the Timesheet from the Position in this same workflow.
5. Setup another real-time workflow to change the hourly rate on the Timesheet if the Position on the Timesheet changes.

[Stevieed]

Hi Drew,

Thanks for your answer! It is very instructive! But just a question, as I understand this still doesnt looks like a "relational database" isn't it? Because with a relationship like in the question:

"timesheet" N:1 -> "User" N:1 -> "Position"

The attributes in "position" table cannot be carried across into the "timesheet" table naturally, this drives me crazy...am I able to achieve an easier link and implement instant filed auto population when creating new records in "timesheet" using power fx in custom page in model driven app?

My idea is...I am trying to make all my calculation and logics stay in one place, at the moment I have some calculated column in my dataverse, and some in work flow, but it is really a pain to use dataverse built in calculation features becuase it only support very few operations, not to mention the business flow only support "+ - x /" (yes wtf?), and you always gonna find new "Dataverse incapabilities" a long the way...

I haven't tried power fx in custom page in model driven app yet, just wondering can it implement the below features?

1, set up fields to look up grandparent table values (or in another words, looking up fields across more than one table levels), like what i descriped in the question, and then patch() back to dataverse 

2, use power fx to conditionally roll up values in child table. At the moment I am using dataverse' roll up feature which in the documentation it says it will only rolling up every 12 hours (wtf far worse than excel??)

Thanks

[Stevieed]

It is also a pain in the ASS to just constantly switching to Dynamic 365 pages to add flows, set up mapping, etc, waiting on webpages to load alone is killing my life span, and Microsoft doesn't care about click count experience...

[dpoggemann]

Hi @Stevieed ,

I understand some of your concerns.  I have worked with Dataverse for the past 9 years (before it was called Dataverse) and Microsoft has continually added new functionalities / features and trying to make it easier to work with.  It takes a bit to understand the current challenges and Microsoft is addressing some of them (i.e. PowerFX) which the formula fields are still only available in Dataverse for Teams as far as I know so this is probably not an option for you yet.  

Some specific items:

1. Carry grandparent to the grandchild entity as a lookup.  i have done this on many customer projects and I know it is not perfect because you should not have to do this but it simplifies the world dramatically in Dataverse currently.  I am hoping with PowerFX formula columns this might get easier.
2. Rollup fields - Yes, this has always been a challenge and I rarely utilize them as they are not real time.  Yes, PowerFX will help this dramatically.
3. Legacy Pages - Microsoft continues to work to replace these.  Example, Advanced Find just moved to the new modern user interface.  Yes, legacy screens are very slow and I am hoping they will all be retired soon.

[dpoggemann]

Hi @Stevieed ,

I understand some of your concerns.  I have worked with Dataverse for the past 9 years (before it was called Dataverse) and Microsoft has continually added new functionalities / features and trying to make it easier to work with.  It takes a bit to understand the current challenges and Microsoft is addressing some of them (i.e. PowerFX) which the formula fields are still only available in Dataverse for Teams as far as I know so this is probably not an option for you yet.  

Some specific items:

1. Carry grandparent to the grandchild entity as a lookup.  i have done this on many customer projects and I know it is not perfect because you should not have to do this but it simplifies the world dramatically in Dataverse currently.  I am hoping with PowerFX formula columns this might get easier.
2. Rollup fields - Yes, this has always been a challenge and I rarely utilize them as they are not real time.  Yes, PowerFX will help this dramatically.
3. Legacy Pages - Microsoft continues to work to replace these.  Example, Advanced Find just moved to the new modern user interface.  Yes, legacy screens are very slow and I am hoping they will all be retired soon.

[Stevieed]

Thanks so much for understanding and replying Drew,

Do you think all these requirements can be doen with Dataverse plugin at the moment?

So still as I have mentioned, all I want is to gather all my custom operations/procedures/calculation in ONE place so I can easily do maintenance and continue to add module to my apps, so if custom page in power fx is not an option at the moment, my last hope is to learn and write a plugin 😞

 Do you think plugin is able to do all of these?

[dpoggemann]

Hi @Stevieed ,

Sorry, I don't write plugins but I think if you keep the relationships in place as I mentioned that you could have a single real-time workflow that fires on create or modification of the position on the Timesheet to set the fields you need.  

Not saying plugins are not the answer but I don't think it is needed here.

[Stevieed]

Thanks, I will try that.