Table Design: Week Commencing with Value

[RDC]

Hello,

I am trying to create staff resourcing functionality and am having an issue with how best to design how time is allocated to a project by week.

What I need to capture is:

User

Project

Week commencing

Number of Days for that week

Part of the difficulty is that I am looking to replace an existing solution, which has been setup in Excel. In the Excel version, each week is a column, meaning that a user can enter their weekly commitment of days on a project for the entire year with just one row.

Creating each week as a column in a table in dataverse seems like a sub-optimal design. I could create "Week Commencing" as a choice(s) field or even as another table and use a lookup but, as far as I can tell, a new row would have to be submitted for each week. If a user wanted to enter: "Week Commencing 5/5: 4 days, Week Commencing 12/5: 5 Days", it would be two different entries. As a result, our staff complain that the process is slower than entering data in Excel.

If anyone can think of a way this can be achieved, I would be very grateful.

Thanks!

[dpoggemann]

Hi @RDC ,

Agreed the columns would be sub-optimal from an normalization of the design although it is doable and many times would make the application easier to utilize if it is a set number of weeks (52) for the user to enter in a single record...  Although then you are going to want to track history of commitments for prior year etc. and you will hit challenges with this approach as well...

I would create a relationship model like the following if it were me.

1. User - Standard User entity within the Dataverse
2. Week Year - This would be a record for each week for the years.  You would want to preload this...  I would think it could have the Year, Week Number, Work Days that Week
3. Week Commencing - This is an intersect entity between the User and the Project and related to the Week.  Fields on this entity would be something like
   1. Lookup to Week Year
   2. Lookup to User
   3. Lookup to Project
4. Project - This is the Project entity

This approach would allow you to track commitments over years for every user by project.

You could even run a Power Automate flow that when you assign a user to the project it creates the rows automatically for the year and then you could utilize an editable grid for them to set their commitments for each week (or even edit in Excel Online).  This is of course in a Model App, if you wanted to do in a Canvas App then you could customize the entry in many ways to simplify.

[RDC]

Thanks, Drew. That is really helpful. I will give it a try and let you know how I get on.

I should have mentioned that we aren't too concerned with historical data. This is purely for our resourcing in the future. I suspect this means I could leave out the "Week Year" table.

Also, yes, it is for a model app.

Many thanks!

[RDC]

Thanks, Drew. That is really helpful. I will give it a try and let you know how I get on.

I should have mentioned that we aren't too concerned with historical data. This is purely for our resourcing in the future. I suspect this means I could leave out the "Week Year" table.

Also, yes, it is for a model app.

Many thanks!

[dpoggemann]

Hi @RDC ,

You had mentioned the number of days per week so you still might need the Week Year because the holidays can fall different each year (if that is why you are capturing that).  Hopefully it works well for you!