Referencing Previous Record

McCormick · July 30, 2003

I thought I posted this before, but seem to have lost it in transit, so please forgive me if I'm re-posting.

Is there any way to reference fields in the previous record for Calc purposes? This is the previous record in the found set, so it will not necessarily have an adjacent record number or adjacent field value [i can't, for example, refer to (date - 1) or (ID.Number - 1) ]. I wish the Status(CurrentRecordNumber) function would do this, but it seems to calculate a value when the record is created, and never change it after that. It would work if it were always active, so that when I re-sorted the list, that value changed.

My goal right now is to be able to check whether an event has the same date as the one before it, so that I can set a certain field accordingly (such as "First Event Today" or something). I would be happy to hear alternate solutions to this need, but I have found myself wishing for the refer-back possibility before. Any ideas?

Fitch · July 30, 2003

You'll have to use a script for this, just go to previous record, set a global field with the data you need, then go to next record and do your comparison against the global.

McCormick · July 30, 2003

Yes, I should have mentioned that I don't want to use a script. That would work but it will not give me what I really want here. It may be impossible to do what I really want, which would involve an active calculation field. Thanks, though.

-Queue- · July 30, 2003

I'm working on the same idea. I'll let you know if I'm successful.

McCormick · July 30, 2003

Thanks, Queue. As I mentioned, I have wished for this feature more than once. It can be very useful in the interface between FMP and the user - manually checking data, for instance. I have year's worth of events I want to scan - only a few hundred - and a calc field like this would allow me to pack in more visual cues on the layout, so that it's easier to see patterns and flaws.

This gets off-topic (sorry), but would address some of these problems: is there a way to make sub-summaries appear in Browse mode? Some of the options that only show up on a printout would be very useful as a Browse layout.

-Queue- · July 30, 2003

Status functions have to be 'unstored' or else they won't update. So your Status(CurrentRecord) will update when you resort, if you have it unstored. The problem is then trying to index this result somehow, actually index this value minus one, so the following record can reference it. FileMaker doesn't appear to allow this, even when a relookup is done on a GetField() function. Phooey!

Ugo DI LUCA · July 30, 2003

It does, I've done it several times....

-Queue- · July 30, 2003

It does what, Ugo? I've only gotten this to update when all records are shown. If you have a subset, it looks up based on the original order for all records in the file. Do you have a workaround?

Ugo DI LUCA · July 30, 2003

Not sure I totally got what you were both looking for, but...see attached what I would do.

Foundset.fp5.zip

-Queue- · July 30, 2003

With a script, sure. We were trying to avoid that though.

-Queue- · July 30, 2003

Summaries in Browse mode require either GetSummary() calcuations or Sum( self::field ) calculations.

Ugo DI LUCA · July 30, 2003

Not possible....

You'd only index one record at a time, as soon as any change would be made on a field. That's the classic limitation.

Though, inserting the update script in a scripted Find with Update, Find Mode, Pause, Perform Find and Re-Update, would lead to this result.

-Queue- · July 30, 2003

I see. It just sucks that you have to clear out all existing records before performing the find. That adds some serious time when one has tens or hundreds of thousands of records. It's not a good idea to use Replace in a multi-user situation either. So a loop is in order here.

-Queue- · July 30, 2003

Crap, you need a UserName concatenated with the Record Number, unless you want to be referencing someone else's found set. And you don't want to clear out all records if someone else happens to be using them.

kennedy · July 30, 2003

Well, one approach is to use a script following Find or Sort to readjust the record numbers, and then everything else can work script-free. Since Find and Sort are script-ish anyway, this isn't too bad a burden and allows all the rest to be coded as simple calculations.

I just did this very thing... might be useful for you...

We are doing a major cleanup of a charity's database.

Many duplicate records and records relating to the same entities but with different (overlapping) sets of information... all of which needs to be verified and merged by hand. To facilitate this, I created a layout that shows all the fields of the record in a column on the left, all the fields of any other record in a column on the right, and a field in between each that tests for inequality and prints a red "<>" if the fields are not equal. That way, its easy to see what's different.

The easiest way to do this was with Calculation fields... but I needed to be able to reference some other record. Usually the next or previous record, but sometimes another.

What I did was define a "RecordNumber" field that was a calculation "If(RecordTrigger, Status(CurrentRecordNumber), 0)". Then a global "CompareToRecordNumber" that specifies which record in the found set should be displayed on the right side and thus compared to by all the comparison fields.

Then I have a script "RefreshRecordNumbers" that does a replace on all the RecordTrigger values (the omitted records get 0's, the found records get 1's). Setting RecordTrigger triggers the recomputation of RecordNumber, getting around the fact that Status(CurrentRecordNumber) doesn't auto-update.

Thus, my user, after doing a Find and/or a Sort, will hit the Refresh button to invoke RefreshRecordNumbers script. Then they can use the fancy layout and comparison and auto-flipping functionality script-free... until they re-find or re-sort.

HTH.

P.S. This is a single-user solution; you'd need to move everything into globals to make it work multi-user since each user can have his own found set and sort.

-Queue- · July 30, 2003

Replace is still bad though. You don't want to replace values in someone else's found set before finding, and you don't want to do the same thing after finding. So you loop before finding and don't clear records with someone else's username. And you loop after finding and omit ones in use by others, and provide a message indicating that not all records fitting the criteria could be shown. Does this sound logical?

Ugo DI LUCA · July 30, 2003

JT,

There are about a thousand ways to go about the replace, copy all records, lookup fields instead of related fields, or other script process. I went to the quicker here, but surelly this could be made with other means that suits the "Multi-user" scenario.

But as soon as you create that index, your user either would have to clear the indexes at the end of the search process. So that you won't have duplicates next time you perform a find.

Ugo DI LUCA · July 31, 2003

This one should take into account the Multi-user scenario and really will be quicker I think.

Foundsetv2.fp5.zip

Ugo DI LUCA · July 31, 2003

Even simplier, and works with the first value this time.

Foundsetv2.fp5.zip

-Queue- · July 31, 2003

Now that's something I can use! Sweet! Thanks, Ugo. Much easier than I was envisioning.

Ugo DI LUCA · July 31, 2003

OK, so now I can pack-up the luggages and go back home in Italy. I swear Adriatic Coast is as cool as California

See ya all in september.

kenneth2k1 · July 31, 2003

It's raining in San Diego

Ugo DI LUCA · July 31, 2003

Just back here a second you pisser

It's not rain, it's the Charger's fan club cries for Keathley and Parker injuries. I guess there are plenty of others. Would there be a valid team for the start of the season....

You deserved it Ken !!!

kenneth2k1 · July 31, 2003

No big deal. They'll be ready when it's go time.

BTW, where is home in Italy?

Lee Smith · July 31, 2003

Sounds like he's in Orange County to me.

kenneth2k1 · July 31, 2003

I know, sounds like he was reporting from training camp

Ugo DI LUCA · July 31, 2003

Venice, not the one on your Coast though, a few miles accross the Atlantic

I had a little chat on phone with Marty, all is ready for you fans on Sunday !!!

McCormick · July 31, 2003

Jeez, this is what I get for not logging in for a few hours. I think I followed all that. Gives me something to chew on, anyway.

-Queue- · July 31, 2003

I'm using Ugo's example and another one I found on sliding calculated subsummaries in a body in combination to provide a nifty dynamic invoice layout with a minimum of summary parts and built-in protection against orphaning subsummary and grand totals. Using a next/previous relationship, there's no need to worry about Min() or Max() or Last() functions, which take much longer to calculate. I think I'll make a sample file of this sometime.

McCormick · August 1, 2003

I'd be interested in that sample. If you put it up, mention here, would you?

-Queue- · August 1, 2003

Sure thing. I've got a truckload on my plate for this weekend. But I'll see if I can squeeze a little fun time in. hehe

Sign In

Referencing Previous Record

Recommended Posts

Create an account or sign in to comment

Create an account

Sign in

Important Information