Last Modified date for Invoice/LineItems

[Mandu]

Is there a scriptless way to calculate a single Last-Modified date for an Invoice with LineItems shown in a portal? A "modification" would include not only changes to an line item, but deletion and addition of line items as well.

Thanks,

Chap

[Zero Tolerence]

Well, as far as deletion goes, I'm not sure how that would work, but for the rest of it you could do this.

Create one field in your data table (the one that you want to track modifications to) make it a global calculation with this calc:

Field gVariable1

Evaluate(Quote(Cieling(Random * 10000)); [field1; field2; field3; field4; field5])

make sure you have every field that you want to watch for modification in there where I have field1; field2.. ext. 



What this will do, is everytime a record(really just the fields you listed) is changed in any way (including addition of a new record) it will put a new number in there between 1 and 10000 (you can use any number you want, i just normally use 10000 because it looks nicer).



Now then, in your interface table, or the occurance your on when your viewing the portal, create another field called tsMod_Timestamp make it a calc with this Calculation:

Evaluate(Quote(Get(CurrentTimeStamp)); gVariable1)

Then, any time a record is added, or changed in any way, you'll get an updated timestamp.

As far as deletion goes, I'm not sure about that.

[Mandu]

Thanks - hadn't really thought about uses for Evaluate until now; that opens up a lot of possibilities.

I'm not quite sure why you didn't write

Field tsMod_Timestamp is Calculation

Evaluate(Quote(Get(CurrentTimeStamp)); [field1; field2; field3; field4; field5])

where tsMod_Timestamp is a field in the parent (Invoice) record and field# are fields in the related (LineItems) records.

[comment]

I am afraid you will find that an auto-entered field will not update as a result of changes in related records.

[Zero Tolerence]

Thanks - hadn't really thought about uses for Evaluate until now; that opens up a lot of possibilities.

I'm not quite sure why you didn't write

Field tsMod_Timestamp is Calculation

Evaluate(Quote(Get(CurrentTimeStamp)); [field1; field2; field3; field4; field5])

where tsMod_Timestamp is a field in the parent (Invoice) record and field# are fields in the related (LineItems) records.

I wasn't sure that would work with creation of records.

Comment:

This worked fine for me, but these are set up as calc fields not auto enter fields.

[comment]

It doesn't really matter - what matters is if the calculation is stored or not. If it's stored, it won't update when related records are modified. If it's unstored, it can be be much simpler, e.g. Max ( related::LastModified ).

[Mandu]

Here's what I have so far:

Invoices::tsLastModified = Stored / Timestamp / Modification timestamp

LineItems::tsLastModified = Stored / Timestamp / Modification timestamp

Invoices::tsLineItemsLastModified = Unstored / Calculation / Max(LineItems::tsLastModified)

Invoices::tsVeryLastModified = Unstored / Calculation / Max(Invoices::tsLastModified ; Invoices::tsLineItemsLastModified)

The last field is what I want - seems to work okay for changes to Invoice, to any LineItems, or to the addition of a new LineItem. As zeroTol mentioned above, it doesn't work with LineItem deletion.

For that, I'm going to see if I can generate a timestamp when Count(LineItems::ID) changes, and include that in the Max() function for Invoices::tsVeryLastModified.

Any thoughts?

[comment]

I think you will find it difficult to generate a timestamp when Count(LineItems::ID) changes. But you can restrict deletion so that the only way to delete a LineItem is by script - and the script can also write a timestamp.