Discussions

Expand all | Collapse all

Auto Update Invoice Balance

  • 1.  Auto Update Invoice Balance

    Posted 12-19-2018 17:37
    We are going to track our AR collection with Quickbase and I want to automatically update the balance for each past due invoice.  I have table A with the invoice number and other details and I have connected table B with the invoice number and the current balance owed.  Table B will be a CSV in dropbox that will update daily.  How do I connect the tables so that table A has a current balance field that updates for that invoice record based on the current balance amount in table b?


  • 2.  RE: Auto Update Invoice Balance

    Posted 12-19-2018 17:42
    Have you tried connecting them with a "table to table relationship"? It might be the case were you have to make a "many to many relationship". A many to many relationship is a little more tedious but quickbase university has good example videos for this. Hope this helps.


  • 3.  RE: Auto Update Invoice Balance

    Posted 12-19-2018 17:54
    I don't think it is a many to many because I based on the example I see.  Both tables will have a list of invoice numbers.  I just want to automatically pull 1 field from table B to table A based on a match of the invoice numbers.  I assume the invoice number will need to be the key field for both tables, but I may be wrong.

    Thanks.

    Ben


  • 4.  RE: Auto Update Invoice Balance

    Posted 12-19-2018 18:25
    The Key field need to be Invoice # for the Invoices table. 

    Now for the Dropbox sync table, will there be multiple entries for the same Invoice# or just 1. If just 1 , then yes, unusual as it seems, you can set the Key field there to be the invoice # as well. 


  • 5.  RE: Auto Update Invoice Balance

    Posted 12-19-2018 18:42
    10-4.  I had the invoice # as the key field for both tables.  The invoice # will only appear once in each table.  It is the next step(s) or connection the tables and setting the proper properties for the balance field so it returns automatically that I can't seem to get right.


  • 6.  RE: Auto Update Invoice Balance

    Posted 12-19-2018 19:06
    Yes, make a relationship based on the invoice field and after you set the Key field of the invoices table to be the Invoice #.

    You do not need to set the Key field in the Invoices balances Sync table to anything.  Its fine how it is.

    Then just make a relationship and hen make a summary field on the left side of the relationship to summarize up the Maximum of the balance value.

    There will only be one child record so minimum, maximum or average will all be the same result.


  • 7.  RE: Auto Update Invoice Balance

    Posted 12-19-2018 19:42
    Perfect.  I remembered that when I read it.  Thanks for all your help on this question and everywhere else on the boards.  I am in my first 3 weeks with Quickbase, but with ODBC access, Crystal Reports, and CRD I have already created 6 active databases and automatic a number of workflows.

    If anyone is using Crystal Reports and wants to automate the report generation, CRD is amazing.  Been using it 7+ years.'

    Thanks again!