Preventing Budget Overrun

Question

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.

Best answer by MarkShnierYou

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.

View original

MarkShnierYou · Accepted Answer

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.

MarkShnierYou · Answer

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.

Sign up

Login to the community