Passing Time

[The Headache]

Hi

Hope this is in the correct forum. Please excuse if not.

I have a table with a field "Date Received". These records span several years.

I want to know how many days have passed from one Date recieved to another.

In other words Clients send in jobs to be processed. I record the date. I want to know how long between shipments.

Thanks in advance. You guys always are a help.

PS This is on XP w/8.5 Advanced.

[NovaChan]

Headache,

One way to do this is with a new relationship and an an auto-enter field in the same table.

1. Create a new table occurrence, e.g., "JobsPrevious". You want the most recent job for that client to be the first related record. Use the client ID and whatever else is necessary for the key fields. Then sort the relationship by DateReceived descending. (It's usually not recommended to sort your relationships, but we don't plan on using this relationship for much else.)

2. Create a new field in your table of jobs/shipments, I'll call it DateDifference.

3. Define an auto-enter calc in the new field like this:

Let([

dateCurrent = DateReceived ; //your current field

datePrevious = JobsPrevious::DateReceived ;

dateDifference = datePrevious - dateCurrent

];

dateDifference

)

Note, both fields need to be defined as Date fields. If you set the new field to allow replacement of current values, it will recalculate every time the DateReceived field in the same record is changed.

The calculation won't show until you create new records. To get it to show for previous records, you'll have to reset the DateReceived field in all the records to itself with a Replace Field Contents or a looping script. It's always important to backup your files before doing something like that though.

I didn't run a test, but I'm pretty sure that will work. Let us know how it goes.

[comment]

I believe it would be better to use a calculation field instead of auto-enter: this way there's no need to replace anything, and it will also update automatically if a previous record is modified.

[Lee Smith]

Since you have Advance, you can use a Custom Function. Brian Dunning Site, or do a search for [color:blue]+Elapse +Time Here in the Forum for regular calculations.

Edited September 3, 2009 by Guest

[NovaChan]

Good point comment. I suppose I automatically went to Lookup and Auto-Enter because that would be the normal case with order and invoice data that need to be specific to that point in time. I don't see any reason for it not to be a regular calc, unless you don't want the previous record updating the result for some reason. This also implies that the calc field will be evaluated more frequently, but that's negligible.

Edited September 3, 2009 by Guest
Removed redundant statement

[The Headache]

Thanks everyone for the help. I have to jump off this project for now but will try implementing the solutions when I return to it. I'll let you know the result.

Thanks again.