Forum Discussion

frnmj's avatar
frnmj
Qrew Member
3 years ago

How to update field value that's in a different table?

I have 2 tables (Invoice and Payments table) which has a one-to-many relationship. 
The Invoice table has an Invoice Amount field, and the Payments table has an Amount Paid field. Basically, I want the Amount Paid to be deducted from the Invoice Amount automatically once the Payment record is added.

------------------------------
frnmj
------------------------------
  • No Problem.
    On the relationship create a summary field which is Total Amount Paid.
    Now the Invoice has the Invoice Amount and the total Paid.  Create a new field called balance owing which is a formula field and do the subtraction there by formula.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
  • No Problem.
    On the relationship create a summary field which is Total Amount Paid.
    Now the Invoice has the Invoice Amount and the total Paid.  Create a new field called balance owing which is a formula field and do the subtraction there by formula.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • frnmj's avatar
      frnmj
      Qrew Member
      Thank you so much for responding! I understood most part of it, I just want to make sure I got everything right. In the balance field, the formula will be [Invoice Amount]-[Total Amount Paid], right?

      ------------------------------
      frnmj
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        Yes, Exactly,
        It's very similar to an Excel formula.

        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------