How to most easily minimize the amount of fields required for a table like single row item

[Anonymous]

Hi all,

I am sorry if my subject name is confusing, I simply did not know what to call it.

My scenario is the following:

My PROJECT-LIST pulls data from a another list called PIPELINE. Within the pipeline data recording possible projects are stored. Here we calculate many things such as the total cost of a given consultant, which various based on the role of the consultants. For example Partner has a cost of 5000, price of 8000 whereas Associate has a cost of 1000 and price of 2000, etc. - All the costs/prices are calculated as DAILY.

In the PROJECT-LIST, we want to track the costs related to the project over the period that the project spans over so we can compare to our estimated costs of the project.

We do this by writing the amount of days in a week a given consultant was working on the project. Therefore, each week the manager will write each consultants working days (between 0 and 5 days in a week).

We can have up to 6 different consultant types and a number of each, so this suddenly turns into an excelsheet on its own. I am worried this will first of all create application performance problems as there will be many fields:

A simple calculation in a project with 6 different consultants and 5 of each in a project spanning over 12 month (52 weeks):

6 * 5 * 52 = 1560

Therefore, I am wondering if any of you can help me come up with an idea of how to most easily solve this problem?

Currently I am trying to minimize the total columns I need to create in the SharePoint List by adding a custom card within PowerApps and then have 4 textinput fields inside (accounting for 4 weeks in a month), but I cannot keep the data within those textinputs saved when submitting the editform. How do I do this?

[EvanTse]

Thanks for further clarification.

You will need to store that information in SharePoint so there is no way around it.

It sounds like you should consider creating a separate list to store the consulant work weeks and relate them through the ID of the project.

I would recommend creating a separate SharePoint list that would have the following columns:
Project ID
Consultant type
Week start
Work days
Etc.

You can then use a gallery to show the entries that are relevant to the project by filtering by project ID.

[Anonymous]

@EvanTse,

I was afraid that was what it would come down to 😞

I might try to look into how to minimize the amount of fields necessary in another way, as the whole point of using a PowerApp is to take them away from having to work in the "backend" of creating sharepoint lists etc. Otherwise they might as well do it from excel.

[EvanTse]

You may have misinterpreted my suggestion here.

My recommendation is for you to create a separate SharePoint list and store all that consultant work week information in there for all projects.

This will allow the user to add/edit/remove that data in powerapps.

So you would have one SharePoint list that stored the project information and a separate SharePoint list that stored all consultant work information.

In the consultant work week table you would have a column that stores the project ID as well so you can differentiate which project each record belonged to.

Please let me know if any of the above does not make sense.

[Anonymous]

@EvanTse,

I think I am starting to understand now. Thank you for the elaboration!

So currently, the user has a LookUp column connected to another list called: PIPELINE, this lookup drags exisiting information from that list. Everything from the Consultant Types, their prices, the number of consultant etc. etc.

This means that the user can simply type the number of days a given consultant worked in a week and it calculates the costs (based on that consultants rate). Is it possible to do this with the suggestion you are talking about here, or would the user of the application need to manually choose the different consultants and then provide the days worked in the weeks?

[EvanTse]

@Anonymous  That is definitely possible. I will leave the architecture design to you. 

You will probably want to be using something like this:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup