Updating an "Accounts" file from an Invoice?

[gumstic]

Hi to all!

I have an Invoices file in a DB system and what i need is to update an Accounts file each time an invoice is created for each record individually.

Exp: If i create an invoice of "2 apples @ $2,00 each" for "George", this invoice should then be added to "George's" "Accounts" so i can keep track of his purchaes, balances, credits, etc.

Could the records of invoices be automatically updated in the "accounts" for each individual customer?

Thanks for any help...

[stanley]

I'm assuming that you've already got both the "Invoices" and "Accounts" files in place. All you need to do is create a relationship in the "Invoices" file, based on customer ID number (rather than basing on the name, which is no good - it can just be an auto-entered serial number) and then put a related field (such as customer name, from the "Accounts" DB) into your invoice layout so that you can select a customer from your "Accounts" database when you're creating each invoice - you can make it a pull-down menu, so you can just select from the list. This way you know each invoice is actually assigned to a proper customer. In the "Invoices" database, create a field which will copy the customer ID so that it is actually stored in the individual record in "Invoices." You can do this a number of ways, for example as a lookup value.

Second, in your "Accounts" database, create a relationship to the "Invoices" file which will link the customer ID in "Accounts" to the newly created customer ID field in "Invoices" (the one which takes its info from the other relationship.) Now, in "Accounts" you can see all the orders for each account - all you need to do is create a portal which will list items from the relationship you've created.

I hope that makes sense and that it helps.

-Stanley

[gumstic]

Thank you Stanley!

I'm working on it and it seems i'll figure out the whole thing.

Thanks for the advice.