referencing prior records

[_Pete]

I have a database with records containing date information (a 'date' field). I want to define a calculation field that determines the amount of time (days elapsed) between the date in one record and the date in the prior record.

No doubt this is ridiculously easy, but I don't see how to do it! Thanks for your help...Pete

[Fitch]

The only way to do this is to use a script. There are a few ways to do it. One is to put the date from the current record into a global field, then go to the next record, then do your calculation. I.e., you'll Insert Calculated Result into a standard date field.

Now, if the records in question have a relationship, you could do it a different way. Maybe you use a script to set a field in each record to say, the id of the first record. Then you could base a calc field on the related values.

[-Queue-]

Do you mean previous record in creation order or current sort order with currently found set?

[_Pete]

Tom...thanks, though I don't see how I can use a Global field as this (as I understand it) can contain only a single value referenced by all records within a file. I need to define a calculation field that references each previous record. Where do I learn about creating scripts?

-Queue-...I would be happy with whichever is simpler! I can pre-sort the records if necessary.

Thanks, Pete

[-Queue-]

This thread may be of interest to you then.

[Fitch]

Let's say you have 2 date fields, Date and DatePrevious. Also a global date field, gDate.

Now, I'm not clear if you want to Find certain records first or Sort them. I'll just assume you've taken care of that already, maybe with just a Show All Records and no Sort. So a script might look like this:

Go to record (first)

Set field (gDate, Date)

Loop

. Go to record (next, exit after last)

. Set field (DatePrevious, gDate)

. Set field (gDate, Date)

End Loop

Now all you need is a calculated field, Date - DatePrevious.

You'll probably also want a script to control record creation, e.g.

Show all records

Go to record (last)

Set field (gDate, Date)

New Record

Set field (DatePrevious, gDate)

Of course, you could just use Copy and Paste commands instead of a global, but then you'd be messing with the user's clipboard, and also would require the fields to be on the current layout. Set field() is just a more robust way to script.

[CobaltSky]

There are various ways to approach this that don't involve the use of scripts.

For one such example, you might like to look at a the demo on 'Accessing Values from Adjacent Records' which can be downloaded from:

http://www.nightwing.com.au/FileMaker/demos2.html#d5

The example in the demo passes values between records in creation order throughout the whole file. Alternative constructions are possible - eg to work with sub-sets of records and/or with alternative sort orders.

[Fitch]

Ray, you never cease to amaze me.

[-Queue-]

It's definitely a nifty technique. But, if you paid someone to proof-read the demo, you should fire them. Even the method name in the header is wrong! I've never seen your files look slovenly before. Methinks you would want to clean this up before posting for the world to see.

[Ugo DI LUCA]

Hi Queue,

You could get more insight about this file by reading this thread.

In this post, while driving me step by step to the process, Ray also offered a link to a Techinfo article, which isn't online anymore.

Ray, do you have a copy of this article somewhere ?

[Ugo DI LUCA]

OOps, not the good link. Here's the link to Ray's "lecture"...

thread ,

Prior or Next record...No problem, Ray is here.

while the other was somehow related to subject and also had a link to this technique overview. Kicks ass for sure.

BTW, Thanks again Ray, I'm using it daily since that day.

[-Queue-]

Thanks, Ugo. I'd love to be able to use a similar technique for currently found records, based on sort order, without resorting to a Copy All Records, Paste script. From what I've read though, this doesn't seem possible.

Too bad.

[Ugo DI LUCA]

So you're still stuck with this ? I thought you somehow had came with a workaround wit your sampler...

Hmm. May be you could elaborate again to see if someone has a clue for this. Could be interresting.

[-Queue-]

Nope. I do the Copy/Paste, then two calc fields parse through the result to grab the previous and next id, based upon the current record's position in the field, similar to the way Ray's using ValueListItems()

I only have to test that the number of found records doesn't exceed 6400 and the number of unique invoices in that set doesn't exceed 250. But it would still be much nicer if the process was automatic and there were no restrictions.

[Ugo DI LUCA]

Sort orders and relationships aren't probably the best friends on earth.

Even if you could temporarily store the Status(CurrentRecordNumber), I'm not sure this would be an advantage in a multi-user scenario, compared to the Copy All Records.

Another solution could be a loop script that sets relationship sort key(s) involving the GetField() function. Having it stored, you could have a Value List stored by this later field and new relationship.

At least, I think this would keep the sort order as it was for all other users, while you could have some dynamic display of your records in the order.

But I've heard somewhere that Loop scripts are slower than CopyAll Records. Then...