Auto Update Invoice Balance

  • 1
  • 1
  • Question
  • Updated 7 months ago
  • Answered
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?
Photo of Ben Gillihan

Ben Gillihan

  • 120 Points 100 badge 2x thumb

Posted 7 months ago

  • 1
  • 1
Photo of Mariah Steckman

Mariah Steckman

  • 82 Points 75 badge 2x thumb
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.
Photo of Ben Gillihan

Ben Gillihan

  • 120 Points 100 badge 2x thumb
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
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. 
Photo of Ben Gillihan

Ben Gillihan

  • 120 Points 100 badge 2x thumb
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.
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.
Photo of Ben Gillihan

Ben Gillihan

  • 120 Points 100 badge 2x thumb
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!