Forum Discussion

WadeMyers1's avatar
WadeMyers1
Qrew Cadet
4 months ago
Solved

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 differen...
formula query
  • MarkShnier__You's avatar
    MarkShnier__You
    4 months 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's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
4 months 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's avatar
WadeMyers1
Qrew Cadet
4 months 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's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 months 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.  

     

    • WadeMyers1's avatar
      WadeMyers1
      Qrew Cadet
      4 months ago

      Not giving up. Just maybe using your idea slightly differently. 

      I already have [Budget Warning] formula Checkbox field:

                 [Delivered]>([Labor Budget]+[Solution Budget])

      The field [Delivered] is a summary from tasks.

      I also created [ALERTS] on the budget table based on your idea:

      If(
         [Budget Warning]=true, "<font color=red> BUDGET OVERRUN: The hours entered for the " & [Type] &" Role, Task# " & [Task#] & "<font color=red> exceeds the budget available. Please correct the entry and try again"
      )

      What I'd like to try to do is pass that alert back to the DSR, and if the Alert field is not null use a form rule to prevent progression through the workflow by hiding the [save & continue] button and only show the [update] option until it is resolved.

      I'm concerned about getting repeats of the same text. I would obviously want both alerts if two tasks originate from different budgets and both are overdrawn, but if I tried to pass it down the alert via the path Budgets>Milestones>Tasks through a summary field DSR<Tasks that I'd get multiple alerts on the same budget.