Balance Due takes too long

[kgriff55]

This is kind of a complicated issue and I'm new to all of this, so hopefully it fits this forum.

Invoices are created and are related to contact information by a Contact ID. Each contact can have a credit limit that is stored in the contacts database. If the sum of the balance due for every invoice for that contact is > the credit limit, we want to halt the creation of a new invoice. The calculation is as follows:

Credit Limit Calc = If(Sum(Invoices::Balance Due) >Credit Limit , "No New Invoices!", "")

The problem I have occurs when trying to create a new invoice for a dealer that has a large number of older invoices. The calculation of the balance due can take a very very long time due to calculation of all invoice line items, etc...

What we would like to do is limit this calculation to only the last 5 or 10 invoices for that contact so that the time is reduced. I had some ideas, but the following seems to make it difficult:

-the invoices for a certain contact are not necessarily in numeric order at all

I can't figure out how to make a script or calculation that only considers the last five or last ten invoices (by date).

Any insight or help would be greatly appreciated.

[overrider]

since it is possible that your database grows really large, it might not be a good idea to have a constant calculation which summarizes unpaid totals for each contact.

a possible way: make a field called invoice_total in the contacts database and create a script which loops trough only once a night to calculate each contacts totals. then, during the day you script your invoice creation button so it performs a check whether or not the credit value is surpassed. if it isnt, and the system allows you to make a new invoice, script the invoice creation so that once a user is posting it (posting by means of clicking a finish button or so), it automatically increases the invoice_total field in the contacts database. if you think of it, it is a similar situation to what i made in a beta stationary module which i posted in the sample files section a few days ago. once a day an inventory script runs, the rest of the days inventory is adjusted via scripting directly after creating a purchase order or sales order.

[kgriff55]

I like your idea, but have a couple questions. First, won't this take a very long time? Second, could you point me toward your example or give me a little bit more direction? I am not sure how to make a script run only once a day at night.

Thanks for your help.

[kgriff55]

I just thought of another question in reply to your idea. How would I make the script first find all contacts that actually have invoices so that it can sum the balance due for those contacts?

[comment]

Why don't you simply archive old invoices? By archiving, I mean define a field called Archived (number) and set it to 1 for old invoices. Then add a calculation field =

Case ( not Archived ; ContactID )

and change your relationship to use this field instead of ContactID.

This way you willl have only the latest invoices in your portal, and the balancing calculation will be quick. Any time a customer settles their bill, archive their invoices.

[kgriff55]

I think I only partially understand what you are saying when you say "change your relationship to use this field instead of ContactID". Does this " =

Case ( not Archived ; ContactID )" make sure that it is only checking non-archived invoices for that contact ID? I guess I just don't know the sytax that you used (the semicolon). Thanks for all the information.

[comment]

Sorry, for version 6 the semicolon should be a comma.

The calculation field returns the ContactID only if the invoice is not marked as archived. Let's name the calc field cActiveID. Now, if your current relationship from Contacts to Invoices is:

Contacts::ContactID = Invoices::ContactID

change it to:

Contacts::ContactID = Invoices::cActiveID

Now the relationship will only see active (i.e. not archived) invoices.

[kgriff55]

I'm only about halfway done testing this, but I think it is going to work. Thank you very much. One last question. I assume that now I can just write a script to go through and change all invoices with zero balance to Archived. Is this correct?

[kgriff55]

I guess I really do need some hand holding here. Where would I change this relationship? Also, once an invoice is archived, will it still be easily accessible in case there is a problem down the road?

[comment]

I assume that now I can just write a script to go through and change all invoices with zero balance to Archived. Is this correct?

I think so, but I cannot be sure because you never told us how you record payments, and how a balance is computed.

Where would I change this relationship?

In the same place where you created it - File > Define > Relationships... (while in the Contacts file). The change will affect the calculation that sums the invoices thru this relationship, and any other element in the Contacts file that uses the same relationship to Invoices.

Nothing will be changed in the Invoices file (except the addition of the 2 fields that are required for the relationship). The invoices are not really archived - that's just a name for invoices that are marked as "archived".

[kgriff55]

I actually got all this figured out before I read your latest response and am currently running a script to change all of the old invoices to include the change. I am just doing it as a test right now, but it appears to be the solution that I will end up using. Thank you very much.