Relationship back to previous most recent transaction

[SlimJim]

I have a transactions table in which each transaction is associated with an invoice number. Each record in the transaction table has a serial ID number. What I would like to do is create a one-to-one self join where a transaction is related to the previous most recent (that is previous highest transaction number) transaction on the same invoice number. I can relate to all previous transactions by matching invoice numbers and using transaction number > transaction number as a second condition.

I can think of no way of modifying this, or doing it a different way, to produce a match to the single most recent previous transaction.

All suggestions gratefully received.

[-Queue-]

Create your relationship, sorting it by Serial descending, then use a calculation of GetNthRecord( NewRelationship::Serial; 1 ) to retrieve the first related record's Serial. You can create another relationship matching this field to Serial.

If you only need a value from the previous record, then use GetNthRecord( NewRelationship::FieldToRetrieve; 1 ) and cut out the middleman.

[Fenton]

There's probably several ways to do this. I can think of 3, none perfect. [Queue adds a 4th -]

1. The Last() function will get you the Last entry of a self-relationship. You could alternatively just sort the self-relationship descending by SerialID.

The critical fact is that an auto-enter of this value will occur before the self-relationship has time to reevaluate the Last() value for the current record. The auto-enter will get the value before this occurs.

This method produces a stored value. It is vulnerable to deletes, which would need to be scripted. It can store a kind of "history", allowing you to see the "previous" entry from any later entries.

2. The value list items of the self-relationship contains the value you want, either the next to last, or the next to first, depending on whether the self-relationship is sorted descending. You can pull it out via a text calculation.

This method produces an unstored value. Not vulnerable to deletes. It cannot store a history, as it only really makes sense when viewed from the latest entry for the self-relationship. It may be slow, especially on a network. (At least it was in 6, painfully so; may be fine in 7/8.)

3. Run a script to populate a "previous SerialID" field for the self-relationship.

Last.zip

Edited October 7, 2005 by Guest

[SlimJim]

Hi Queue & Fenton

Many thanks for your responses. I will sit down later with the files and try these out. You rightly guessed that I was trying to pull values from the previous records and I want them to be stored. I was trying to set up a relationship in order to auto-enter updated values and store them for use in other relationships.

[SlimJim]

A quick update - this is working perfectly many thanks.

Now that I can update the transactions easily by your methods I was able to resolve the question that started this off: when a payment is made covering several invoices how do you update the outstanding amounts on the invoices in situations where the full amounts are not paid so that the next time a payment comes in for that invoice it presents only the outstanding amount (accounts programs routinely deal with this but I couldn't). The problems turned out to be going from a payment to the related payment transactions and back "up" to the related Invoice. Along the way things got "lost" because I was having to do calculations in different found sets of transactions for each invoice

[Fenton]

It would probably be better to script this, with a Loop through those Invoices, even if it requires a plug-in triggered by the Paid field (which would likely be in another table).

I can think of a way to do with a calculation, to see "how far the money goes", but it would be (very) slow. You could use the position of an Invoice ID in the ValueListItems() of the Customer self-relationship in (or relationship to) Invoices, to get itself and previous Invoice IDs, resulting in an unstored multi-line calculation field.

In other words the IDs for the 3rd invoice, in series of 5 would be:

ID1

ID2

ID3

Ender's GetNthRecord() could alternatively be used (I think, I'm waiting for my 8 Advanced).

A relationship from this to the Invoice ID could be used for a Sum() of the amount due up to that point. This could be compared with the total payments, and you would see whether any particular invoice was fully paid.

But it would be painfully slow to use for anything like a Find.

(P.S. I think that business people tend to overrate the idea of "which invoice was paid". Because, as you say, people can make multiple and/or partial payments. I think it's often a hold-over from paper systems, where you really have to mark a particular Invoice paid, as you may not have a good way to get an overall view.

What really matters usually is whether someone owes you money overall or not. Perhaps each invoice is important in more complex systems, with interest based on the due date of each Invoice, etc.. But for many small business it's less important, IMHO. If it's done at all, it should be done right, capable of multiple or partial payments.)

 

[SlimJim]

I learned a great deal from both your post and Queue's post. I made use of the GetNthRecord to run an initialization of our transactions table, updating all the OS amounts and then I used your Last method (I did not know it applied to related fields. In the calculation box of FMP it is listed as Last(repeatingfield) which is not strictly accurate) to set up auto-enters via the relationships to keep the records up-to-date.

Regarding the scripting you are right. When filling in a payment I have a NewItem script which offers a custom dialog asking for the invoice number (a list of unpaid invoices for the client is present on the layout and eventually I shall script a button against an invoice to ensure that numbers are not incorrectly transcribed) This invoice number is the key to a new line-item as it sets off auto-enters of all the details from outstanding amounts which can then be altered to match the payment being made. We always print out and send receipts and I have piggy-backed the script updating the invoices onto the Save & Print button. This now works very well and quickly due to the input I had from this post. There is virtually no calculating to do it is simply a matter of transferring outstanding amounts into the correct fields on the correct invoices.

One problem I had was that of changing layouts during the scripting and I did not realize until now how significant the "open a new window" step is in GTRR. I think some of the problems I was having were due to not opening a new window.

Regarding invoice status. We are subcontractors and we have to give 28 days credit or no work. Once an invoice becomes overdue then a system of chasing letters comes into operation and so our status reports pick out not only whether an invoice is overdue but what stage we have reached in chasing the payment. This has to be updated on a daily basis and again this runs much faster - in fact it is down to a few seconds from two or three minutes.

Many thanks again to you both.

[Sanjai]

Hi Fenton, Queue,

Can you guide me on how to find a the previous most related field using relationship and in version 5.5?

I have a parent, child file and after creating another child, I want to show one of the field from the last child record for that particular parent. I could not understand how to use your previous replies for the same post. Can you explain giving an example? I would like to use a relationship for it and a calculation field to show the related last field.

[-Queue-]

For 5.5 you have to use a value list and parse the previous serial. See attached for sample technique.

ParentChild.zip