Preventing Budget Overrun

My objective is to prevent users from exceeding the available budget when logging time. Users can attach multiple task records to the DSR (Daily Status Report), and those tasks can belong to different budgets.

I want to use a Formula query to obtain the value in the [Remainder (Available)] Field 64 of _dbid_budget, and compare the total of hours logged in [Units Delivered] Field 103 of _DBID_TASKS. Once all tasks are attached to a DSR, the Total Units Delivered Field 108 of _DBID_TIME_CARD (where the DSRs reside), I would want to compare available budget to hours consumed on tasks, and if there is an overage i will stop the flow of the report until it is resolved.

I used a summary [Budget Line - Record ID# (Text)] field 267 on the DSR form configured for combined text, and was able to validate that I can see two correct record ID's when attaching 3 tasks and two of them using the same budget.

The [Total Available Budget] field 268 formula on _DBID_TIME_CARD has no errors, but fails to render anything other than zero:

SumValues(
GetRecords(
"{3.IN.('" & [Budget Line - Record ID# (Text)] & "')}",
[_DBID_BUDGET]
),
64
)

Where 3 is the [record ID#] and 64 is [Remainder (Available)], both on the budget.

Any input will be appreciated.

formula query

MarkShnier__You
24 days ago
Well, let's not give up on the relationship and summary field method just yet.
Just because the budget is second level up from tasks, you still should be able to summarize total task hours up to milestones and then total task hours on the milestone up to the budget and the budget record will then be able to self flag itself to know if it is over budget. Then you could have a flag or message, maybe a message is best to identify which budget is over and then look up that warning field down to milestones and from there down to tasks.

MarkShnier__You
Qrew Legend
24 days ago
I'm not seeing why you need to use a formula query.
If one Budget has many Tasks then you can make a summary field on that relationship to total up the task hours. Now the budget record can flag itself if the task hours exceed the budget and that flag or message can be looked up down to the Tasks table and shown in the Embedded report on the DSR report and from there can be summarized up to the DSR to put a warning on the DSR record.
- WadeMyers1
  Qrew Cadet
  24 days ago
  There's no direct relationship Budget>Tasks, just Budget>Milestone>Tasks, and then DSR>Task.
  I tried creating a summary field on DSRs that looks at field [Available] on Tasks. Field [Available] looks-up to the budget field [Remainder(Available)], but it multiplies the budget by the number of attached tasks. I changed it to an Average, which "could" have worked if we never had two tasks attached with different budget lines.
  The idea behind the FQ was to look up Budget:[Remainder(Available)] for each of the Record ID# fields found in attached tasks, and then once tasks have been updated with the hours worked I was going to use an [Update] save and redisplay before offering a save and continue to the next form if any budget line had a result that isn't =>0 then it would throw a warning that the hours logged exceeds budget and they would then have to [update] again with a valid number.
  The real challenge in all this is trying to get the numbers back to the DSR for comparison on all potential budget lines individually.
  I had considered making a relationship DSR>Budget. I guess if I did that I could pull up any budget alerts (which are already enabled), and instead of trying to compare at the DSR level I could just look for the presence of ANY budget error and stop them. The only twist then is to define the alert in a way that tells the user where the problem is.
  - MarkShnier__You
    Qrew Legend
    24 days ago
    Well, let's not give up on the relationship and summary field method just yet.
    Just because the budget is second level up from tasks, you still should be able to summarize total task hours up to milestones and then total task hours on the milestone up to the budget and the budget record will then be able to self flag itself to know if it is over budget. Then you could have a flag or message, maybe a message is best to identify which budget is over and then look up that warning field down to milestones and from there down to tasks.
MarkShnier__You
Qrew Legend
24 days ago
Well, if you create a combined text summary field of that warning message and summarize the different warning messages up from tasks up to the DSR, Combined Text summary fields only result in unique children. So if you as a parent had six children, and you chose to name three of them Bob and three of them Mary, (which would be an unusual thing to do) the combined text summary field would say that your children's names are Bob and Mary.

Does that solve your concern about duplicate warnings on the DSR as to what the problem is?
- WadeMyers1
  Qrew Cadet
  24 days ago
  Actually, now that I think about it that makes perfect sense. I already have an example of that were I rolled up the [Related Budget - Record ID#] from the task to the DSR and it shows only two of the 3 attached tasks because 2 of them are the same.
  I can change change the target of that field, and instead of putting the alert at the budget I can have it located on the tasks based on the budget warning flag.
  I can already see how I'll accomplish the rest of it. Thanks Mark!!!
  - WadeMyers1
    Qrew Cadet
    16 days ago
    I found a gap in testing where the user could "trick" the system. I'm thinking "maybe" a Formula query could help, looking for insights.
    Use Case. The user enters a value of 11 hours on a budget of 10. At this point, [Save & Continue] button is hidden by a form rule, and in it's place an [Update] button is required to save and redisplay the record in edit mode. If no Alerts exist, [Save & Continue] is visible. The problem being that at this point, the user "could" opt to edit the hours again, and potentially those hours could exceed the budget as shown below because [Save & Continue] is visible.
    I modified the button formula as follows to redisplay form 1 which is 16 instead of proceeding to form 2 which is 20.
    var text DisplayFormInEditNoAlerts =
    If([Active Form]=1 and [Qualifies to Show Save & Continue]=false,
    URLRoot() & "db/" & Dbid() & "?a=er&dfid=16&ifv=1&rid=",
    URLRoot() & "db/" & Dbid() & "?a=er&dfid=20&ifv=1&rid="
    );
    Maybe the simplest solution would be to take them to Form 2 and use a rule to take away buttons and present the alert with the only option to go back, and let the form rule 1 lock them into place; however, a more pristine solution would simply refresh using the above code. The problem is that even feeding up a budget warning with a relationship doesn't work because it is not real time with the task budget until it saves.
    I'm curious if a Formula query would be more dynamic/real-time? If so, what would be a good syntax for looking at Units delivered on the task table from the current DSR?

Forum Discussion

Preventing Budget Overrun

Related Content

Preventing Scheduling Conflicts

Prevent save a record - New forms

Monthly Budget Vs Cost Tickets

Prevent save not working

Quicken Integration or Budget Template