Hi i'm new in this forum, i'm italian and i'm sorry for my english.

 

The question is this, i've a TO, where i save payments and invoices even for customers and suppliers.

 

When i save a new invoice, i enter datas in this field:

- Customer_supplier_name

-customer or supplier (auto)

-invoice number

-invoice date

-invoice amount

 

When i save payment, i ente:

- Customer_supplier_name

-customer or supplier (auto)

-payment amounts

 

Now i'd like to create a script that take "payment amount" and set the first invoice wich is not "closed"

an example occurred:

 

#save invoices

customer_supplier_name: PincoPallo

customer o supplier: supplier (auto entered)

invoice number: 456

invoice amount: $ 1000.00

 

 

customer_supplier_name: PincoPallo

customer o supplier: supplier (auto entered)

invoice number: 737

invoice amount: $ 2000.00

 

customer_supplier_name: PincoPallo

customer o supplier: supplier (auto entered)

invoice number: 1246

invoice amount: $ 500.00

 

#save payments

customer_supplier_name: PincoPallo

customer o supplier: supplier (auto entered)

payment amounts: $ 1500.00

 

i'd like script take payment amounts and put $1000.00 to close the older invoice (invoice number 456), than, goes to the next and put 500.00 in account

 

Can i do this? Hope someone help me. thanx

 

 

 

OOOOOOPPPPPPPS i was OT...SORRY....can someone put this in "Scripting" Section? thanx

Are you SURE you want to do it that way? It can get really complicated when you start splitting payments, and then what if the check bounces and you have to back partial payments out of multiple invoices... etc. What if the customer overpays (I wish) -- what do you do with that extra part of the payment?

 

My preference is to store the current balance on the customer record. When a new invoice is created or a payment is received, those become records in the invoices and payments tables, and they are added or subtracted to the customer balance. (Stored balances perform much better than unstored calculated totals.)

 

You may also want to create a Statements table, so each month you can assign a statement ID to the payments and invoices produced since the previous statement, and move stored balances into "buckets" for 30, 60, 90 days overdue. Etc. You print that out and show the client, here's what you owed before; here are your new invoices; here are your payments; here is your new balance.

 

I really don't ever want to work on a system again where payments are broken out into assigned sub-payments. Clients don't care about that and I don't think you should either.

thank you for your answer, i forgot to write some important things.

I don't make invoice from this DB, i import from another, as list ("invoice DB" -->excel-->Fm), "invoice DB" is specific for my business and i bought it.

I use only one "TO" for registration.

Is a problem if i ask you an example about

My preference is to store the current balance on the customer record. When a new invoice is created or a payment is received, those become records in the invoices and payments tables, and they are added or subtracted to the customer balance. (Stored balances perform much better than unstored calculated totals.)

 

as i can understand better what you mean

Thank you

I'd have 3 tables: customer, invoices, and payments.

Customer is related to invoices by customer ID,

and customer is related to payments by customer ID.

customer balance = Sum( invoices::amount ) - Sum( payments::amount )

 

However, if you have a lot of records, your system will eventually get very slow if you use a calculated field for the balance. Not terrible when looking at a single customer in form view, but bad when looking at lists, portals, reports, that kind of thing. Therefore, I recommend that when you make an invoice, you add that amount to the current balance, stored in a number field, not a calculation field. Same thing with payments.

 

Hope that helps.

Thanx for your answer, but i already have that situation in another TO, called "Customer Situation", there is a portal wich show me the "history" (invoices and payments) and the balance.

What i'd like to do is to auto-set a field that show me if that invoice is paid or not, and the difference.

Ex.:

invoice_nr: 578

invoice_import: 2.000,00 $

payment: 1.500,00

invoice_balance: 500

invoice_situation: not paid

 

than when i'll reg another payment for 500,00, invoice_situation: Paid

If you insist on going down that road, you'll probably want to create a Payments Applied table where you split up your payments and note which invoices you're attaching to which payments.

 

As I said before, this is a nightmare way to handle accounting. It does not matter which payment belongs to which invoice. What matters is whether the customer owes you any money.

 

I believe this is much better handled with a Statements table, and assigning a Statement ID to each invoice and payment. This easily accommodates adjustments by creating a record in invoices or payments as appropriate, it gives you a way to deal with aging, and both customers and customer service people can understand it.

thank you, i'll follow your way, i don't believe it's so important create that situation. I'll create only if some customer ask me a specific situation.

 

Thanx a lot