Forum Discussion

MikeRorstad's avatar
MikeRorstad
Qrew Cadet
6 years ago

Is an automation the best way to tackle this?

Hello,
I am trying to solve a problem and I believe automation may do the trick.  I just don't know how to do automations very well.

An example of what I'm trying to do is similar to a bank balance.

If I have a Bank Account table that has a related Check Book Table, I want the remaining balance to be calculated for each time a record is completed in the Check Book Table.

Is this best done by an automation or is there another way to do this?

Thank you,
Mike

------------------------------
Mike michael.rorstad@ameebay.com
------------------------------
Quickbase Automations
  • QuickBaseJunkie's avatar
    QuickBaseJunkie
    Qrew Commander
    6 years ago
    Hi Mike!

    I have an app very similar to yours.

    I have an Account table that is the parent to a Transaction table. Then in the relationship, I have a summary field that sums up all the transactions which effectively gives me my account balance. 

    Now, if you want that balance to also show up on the child record when the transaction is recorded you could use an automation or you could use a snapshot field.

    A snapshot field would pull the current balance onto the record and make a 'snapshot' of it so that when the actual balance changes, this field stays the same.

    An automation, in contrast, would 'stamp' the current balance on the record based on a trigger (such as the record being saved or modified).

    In either scenario, you will need to add a lookup of the summary 'balance' field to the child record.

    For a snapshot:
    Add a numeric field and at the bottom of the settings set it as a snapshot field and choose the balance 'lookup field'. This will pull the CURRENT balance before this transaction is registered... so you likely will also need a formula field to calculate the 'ending balance'.

    For an automation:
    TRIGGER: when the record is added
    ACTION: modify the record to copy the balance from the 'lookup field' to a static Numeric field filtering to where the Record ID = Record ID.

    In either scenario, there could be a HUGE issue if a historical transaction was recorded incorrectly as it won't automatically 'recalculate' all the records that came after it.

    If that's a concern you can just stick to a summary report that includes a running total.

    Let me know if this helps 👍😊

    Sharon



    ------------------------------
    Sharon Faust (QuickBaseJunkie.com)
    Founder, Quick Base Junkie
    https://quickbasejunkie.com
    ------------------------------
    • MikeRorstad's avatar
      MikeRorstad
      Qrew Cadet
      6 years ago
      Sharon, Thank you for getting back to me!  I was able to figure out how to use the snapshot for calculating a balance in a checkbook, and I can definitely see the problem you pointed out about incorrect transactions and the historical values.

      I was trying to use the bank account scenario to learn how to get a running balance, but my real problem is a bit more complex.

      I have various jobs that have a certain number of employees assigned to them with a certain number of hours authorized for each employee.

      For example, Job 1 has Joe and Anne assigned and each of them is authorized to charge 80 hours of regular time and 10 hours of overtime.

      The main table is the Job Log - there will be numerous job log records

      I have a one-to-many relationship between the Job Log record and a table called Time Authorization Table.  

      I have a one-to-many relationship between an Employee Roster table and the Time Authorization table. 

      From the Job Log table, I can push the "Add Employee to Time Authorization" button and the number of RT and OT hours can be added.

      For each day that a job is being performed, we have a daily report where the employees that worked on that job during that day, and how many hours were spent on that job is listed.

      I am trying to get the running balance for each employee on that job at the end of that day to be displayed on that daily status report.



      ------------------------------
      Mike michael.rorstad@ameebay.com
      ------------------------------