No of days since last entry

[Simon K]

I need to record "machine readings" taken on a periodic basis:

eg:

Date 10/7/2008 ; Reading 202

Date 13/7/2008 ; Reading 199

Sate 14/7/2008 ; Reading 170

etc

The dates are not necessarily taken every day, but the rate of change in the usage readings is affected by the amount of time that has passed

So on entering the second reading I also want to store the number of days since the previous reading ie 3

If reading 2 were deleted then I would want the "days" for reading 3 to change from 1 to 4.

Is it possible to have a calculation field do this or does it need to be some sort of script?

Any pointers please

many thanks

Simon

Edited September 3, 2008 by Guest

[LaRetta]

Hi Simon,

Calculation (result is number) and be sure it is unstored (in Storage Options) and that 'Do Not Evaluate If All Referenced Fields Are Empty' is UNCHECKED at the bottom of the calculation dialog:

Case(

  date ; 

    Case ( 

      Get ( RecordNumber ) = 1 ; 0 ;

      date - GetNthRecord ( date  ; Get ( RecordNumber ) - 1 ) 

    )

  )

LaRetta

[Simon K]

Thanks Laretta - (I was barking up the wrong tree using a self join and lookupnext/lower etc) - I have learned something thanks

Anyway could you help with a slight variation?

really we need the number of days since the previous reading's date as opposed to the previous record entered, as readings may not be entered chronologically

thanks a million

Simon

Edited September 4, 2008 by Guest

[LaRetta]

number of days since the previous reading's date as opposed to the previous record entered

Am I missing something here? Are these records with possibly a VehicleID and mixed with other vehicle readings? Otherwise why would they be out of sequence? Please clarify a bit more on what this table contains.

[Simon K]

The problem arises because the readings for a machine are taken by more more than one person depending on who is "on site" - those people need to update "my" system but do so the next time they are at head office... which may be out of order... hence the need to be able to record "out of date order"

By way of background:

The machines are bar code readers and the request is to record (for analysis) the number of times they have been used - which is available from the readers firmware. (not the number of sales scans etc which is covered by the application software)

Once the reading is taken the machines internal count is automatically zeroed...

Thanks for taking the time Laretta

Cheers

Simon

[comment]

You just need to make sure your records are sorted correctly. However, this method can get slow as the number of records increases.

---

See clarification below!

Edited September 5, 2008 by Guest

[Simon K]

Oooh - because GetNthRecord works on found set based on the way the table has been sorted and its an unstored calc...

Re your comments about speed I think there will be about 840 readings per week at the outset - is there is an alternative method I could look at?

Thanks

Simon

[comment]

there will be about 840 readings per week at the outset

That doesn't tell much - the question is how many will there be after a year, or two years, or...

The alternative is to perform a lookup/auto-enter when a record is created - but it also means that when a record is deleted/modified, you need to perform a relookup on subsequent records.

[Simon K]

Your perception completely passed me by

Yes there are several readers ids in the table

I have to ask - how do you change your calc to find the last reading for a given reader id?

Yours Simon

[Simon K]

Sorry - I can see you don't change the calc - its just about "finding" the correct found set and "sorting".

I will look at this some more - I think I am not getting to the bottom of the requirement properly

S

[comment]

I need to clarify what I said earlier about the solution getting slow as the number of records increases. I was - quite mistakenly! - thinking this was a cascading dependency. But that is NOT the case here, because each record depends ONLY on the previous record's date, and that does NOT depend on anything else.

As with any unstored calculation, if you have a List/Table view with many records, it may take some time to show all the results. But the effect won't be anywhere as dramatic as it would be with a cascading dependency.

Thanks to LaRetta for insisting on an explanation!

[Simon K]

Many thanks for the clarification

Simon