Another newb question regarding self relations

[Esmo2000]

Hi all, sorry to be a bother but I am afraid I have run into a bit of a snag. Here is my problem.

In my database, I have a set of invoices each with due dates on them. For each of those due dates, the invoice will be one of three obvious choices:

1) It has been paid (whether on time or not is not important)

2) It has not been paid yet but is at present not late

3) It has not been paid and is now late, by some amount of time.

I also have a field which calculates from there if they are option 3 exactly how late they. However, to do this was rather difficult. Many entries are made under one invoice number (invoice #s are not unique, they have many calculations that happen under them. For example, an invoice may have a payment made, underneath that it could have a charge, etc.) The gist of it is, to calculate whether an invoice is paid, ALL the values for that invoice must be summer (or something equally simple).

How I did this was to create a self relation between the invoice field and itself. When this happened, and I went and summed up all the values in my field, it would stay invoice specific, and then I could calculate whether the bill was late easily enough.

When I did this however, things started becoming very slow, and it seems that it is obviously not the correct solution! Can someone tell me what the correct way to go about doing this would be? Thanks a lot, I am forever indebted!

Jim

[Ender]

Welcome Jim!

Your question is not a bother. Invoice billing can be a bit tricky, especially when you have to deal with late payments. I think we do need some clarification on your structure and what you mean by:

>>Many entries are made under one invoice number (invoice #s are not unique, they have many calculations that happen under them. For example, an invoice may have a payment made, underneath that it could have a charge, etc.) The gist of it is, to calculate whether an invoice is paid, ALL the values for that invoice must be summer (or something equally simple).

Usually an Invoice has many line items that are actually related records in a Line Items file. Is this what you have here?

How do you apply credits and payments--do you put them in a payment table (if so, is this linked to the Customer or the Invoice) or are they entered in some other way?

[Esmo2000]

No, im afraid its nothing so complex. Basically, an invoice has credits and payments placed in by entering a new entry with the same invoice number. When a payment or credit is made, it is specified under an account type. For example, a sample line might look like,

Invoice Number Type CDN dollars US dollars

892138929812 Credit 10 4

892138929812 Payment 5 0

892138929812 Payment 3 1

892138929812 Payment 2 3

892138929814 Credit 7 0

892138929814 Payment 3 0

As you can see, there have been a series of entries under this invoice number which have eventually been paid off. Obviously, when I am determining if this top invoice has been paid off or not, I would want to check the credits and payments, but only for that one invoice. it would be meaningless to check the invoice 892138929814 as it is not related (and vice versa... we see that for 892138929814 there are still outstanding monies, and thus it is not closed).

Does anyone have any suggestions, I would really appreciate it!

[ESpringer]

OK: I think you need a related file (for FM6) in which each record is exactly one invoice, and a relation built from that invoice number to all of the specific "payment" records in the file you've described above. The file you've described above might be called a "LineItems" file, since each one tells about one "line" related to a larger transaction. Generally, we'd reserve the name "Invoices" for a file where each record represents exactly one invoice.

Are you comfortable with relation-building?

You would be able, in this new official Invoices database, to set up calculation fields that do things like track the sum of related credits and payments on each invoice separately. You'll probably want to set up two more calc fields in the LineItems DB for the absolute Amount of this LineItem: it should return the CDN and USD field amts multiplied by -1 just in case the Type field shows "credit". That way, when you make a Total of these LineItems for each Invoice, the Invoice will be able to work the math more smoothly.

Then, your invoices database can have calc flags that do things like notice whether the balance (for both currencies) is zero, whether it's negative but date hasn't come yet, whether it's positive (needs refund), or whether it's overdue. ...

I hope that's a start-- sorry not to spell out in more detail yet... Keep asking about whatever's not clear...