Add data to different fields according to StartDate and Duration in Months

[Anonymous]

Hi everyone and Happy New Year!

I am building an Application in which we fill out data for different cases/projects. Each project has a starting date (Estimated Starting Date), client Budget (Estimated Client Budget) and duration (Duration in Days).

Ideally I would like to be able to insert the above data and then it will automatically fill out data fields for revenue per month. I have seperate fields for each month of 2020 (Jan-2020, Feb-2020, Mar-2020, etc. etc.)

As an example if I were to insert the data as following:

Estimated Starting Date: 1/1/2020

Client Budget: 90.000

Duration in Days: 90 (3 Months)

With this information it would automatically insert data as following:

Jan-2020: 30.000

Feb-2020: 30.000

Mar-2020: 30.000

May-2020: 0

-//-

Here is an example of what the interface looks like in our Application:

[Anonymous]

Does this need to be editable? Meaning the user can alter the default values, or is this just for display purposes?

[Anonymous]

@Anonymous 

If I understand your question right, you want to know whether the different fields for months can be editable?

If that is the case the answer is no. I am actually hoping to make those to DefaultMode.View rather than .Edit so that it will automatically fill in the different fields in our excelsheet.

[Anonymous]

Got it. So here's one way to achieve that.

I referenced this solution for the first part and got this:

AddColumns(FirstN([1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30],
     DateDiff(ProjectStartDate.SelectedDate,ProjectEndDate.SelectedDate,Months)+0),
"Budget",
     Text(Value(ProjectBudget.Text)/DateDiff(ProjectStartDate.SelectedDate,ProjectEndDate.SelectedDate,Months),"[$-en-US]$#,###.00"),
"Date",
     Month(DateAdd(ProjectStartDate.SelectedDate,Value,Months)) & "/" & Year(DateAdd(ProjectStartDate.SelectedDate,Value,Months))
     )

Where ProjectStartDate.SelectedDate is the begin date of the project and ProjectEndDate.SelectedDate is the estimated ending date of the project. This produced a table with a month number (of the project), the month/year, and the shared budget for that month. Put into a gallery, it looks like this (I added "Month: " and "Budget: " in the gallery)

Next, you can add this to the default month text boxes if you don't want a gallery view:

First, you'll need to set a table variable with the same formula above. You can add this to an OnChange property for your date picker or somewhere that makes sense in your app.

Set(BudgetTable,
AddColumns(FirstN([1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30],
     DateDiff(ProjectStartDate.SelectedDate,ProjectEndDate.SelectedDate,Months)+0),
"Budget",
     Text(Value(ProjectBudget.Text)/DateDiff(ProjectStartDate.SelectedDate,ProjectEndDate.SelectedDate,Months),"[$-en-US]$#,###.00"),
"Date",
     Month(DateAdd(ProjectStartDate.SelectedDate,Value,Months)) & "/" & Year(DateAdd(ProjectStartDate.SelectedDate,Value,Months))
     ))

 Then, on each textbox, you can add this as the default, '3' in the example below would be the index of the month you are displaying (1-12)

Last(FirstN(Gallery1.AllItems,3).Budget).Budget

---
If this answered your question, please click "Accept Solution". If this helped, please Thumbs Up.

[Anonymous]

@Anonymous, thank you very much for the answer! I will try and implement it tomorrow and accept as solution if it works

[Anonymous]

Hi again @Anonymous ,

I have now been trying to follow your method, however, as I understand it you would like me to create a gallery to add the data for the given months. The problem is I need to add the accumulated "Client Budget" to each of the months that the project lasts for within the Editform / Viewform, not a Galleryform.

Fx. as per the example above I would like to for the Client Budget to be divided into the different months that it lasts for as seen below (I have manually added the Client Budget for each of the months as highlighted):

If in another example the project starts in March and spans until June then it would divide into 4 months:

The Month fields are all in the same MainTable of our Excelsheet, in which all the data for each item/project gets stored.

[Anonymous]

The gallery was just a visual example. The example below is all generated automatically.

The formula to set the table we use is as follows, which you can set on an OnChange property of the last field the user fills out, or all the fields the user should fill out (Budget, start date, estimated days):

Set(BudgetTable,AddColumns(FirstN([1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30],
     DateDiff(ProjectStartDate.SelectedDate,ProjectEndDate.SelectedDate,Months)+0),
"Budget",
     Text(Value(ProjectBudget.Text)/DateDiff(ProjectStartDate.SelectedDate,ProjectEndDate.SelectedDate,Months),"[$-en-US]$#,###"),
"Date",
     Month(DateAdd(ProjectStartDate.SelectedDate,Value,Months)) & "/" & Year(DateAdd(ProjectStartDate.SelectedDate,Value,Months))
     ))

Where ProjectStartDate is the start date picker, ProjectEndDate is the end date picker (Which is calculated with the formula below) and ProjectBudget which is the budget textbox (set to numeric input)

//End date picker default calculation
DateAdd(ProjectStartDate.SelectedDate,Value(ProjectLength.Text),Days)

 Then, the labels above the text boxes and the text boxes themselves are set using these formulas:

//Label
If(Count(BudgetTable.Value)>=1,Last(FirstN(BudgetTable,1).Date).Date,"")

//Textbox
If(Count(BudgetTable.Value)>=1,Last(FirstN(BudgetTable,1).Budget).Budget,"")

This way, the labels are dynamic and don't need to be changed around. If your labels are driven off of your Excel file, then you can skip the label part and just use the textbox calculation.

[Anonymous]

@Anonymous, Thank you for the walk-through! I am sorry for the late response, New Years in between.

It seems to be calculating it exactly as it is supposed to. However, right now it seems as if all the month fields get filled with the average monthly:

In this scenario with a project starting from January and spanning until end of February I would like:

January: 50.000

February: 50.000

March: 0

-//-

Likewise if the project starts in March and spans til May it should show 33.333 for March,April,May:

[Anonymous]

Another issue I am trying to deal with is that when a project is less than 1 month (fx 7 days) I would like for the client budget to show in the month field.

I have tried with different formulas, which do not give me any errors but they do not show:

Set(BudgetTable,AddColumns(FirstN([1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30],
     DateDiff(EstimatedStart.SelectedDate,EstimatedFinish.SelectedDate,Months)+0),
"Budget",

If(Value(DurationDaysDataCardValue.Text)>=30,
     Text(Value(CurrentBudgetDataCard.Text)/DateDiff(EstimatedStart.SelectedDate,EstimatedFinish.SelectedDate,Months),"[$-en-US]$#,###"), Value(DurationDaysDataCardValue.Text)<30,Value(CurrentBudgetDataCard.Text)),
"Date",
     Month(DateAdd(EstimatedStart.SelectedDate,Value,Months)) & "/" & Year(DateAdd(EstimatedStart.SelectedDate,Value,Months))
     ))

I tried to add the If() in order to solve the problem, but if the duration in days is less than 30 days it does not show up in the field.

Regarding the main problem of the client budget showing in all the fields as can be seen above I thought it would work to change the Default Property of each month to >=1 and BudgetTable,1 for January, >=2 and Budgettable,2 for February etc, but that also does not work.

If(Count(BudgetTable.Value)>=1,Last(FirstN(BudgetTable,1).Budget).Budget,"")

[Anonymous]

@Anonymous 

Hi Eric, I know I am being difficult, but I am unfortunately stuck at this moment, any chance you have an idea about the above issues I am dealing with at this point?