Auto Update Invoice Balance

Question

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?

mariahsteckman · Answer

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.

bengillihan · Answer

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

quickbasecoachd · Answer

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.

bengillihan · Answer

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

quickbasecoachd · Answer

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.&nbsp; 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.

Forum Discussion

Auto Update Invoice Balance