Lookup field

[DanBrill]

Pretty new to FM, and this is my first time to use a lookup field. What am I doing wrong?

I have a global field (number) called gCurrentFee. When a new record is created I want to lookup gCurrentFee is and place that value in a number field called CurrentFeeLookup. So I create CurrentFeeLookup (number) and select Options -> Auto-Enter -> Looked up value -> Specify. I choose the relationship, a self-join where RecordID = RecordID, and select ::gCurrentFee.

What I think should happen is that CurrentFeeLookup should be set to gCurrentFee, and then retain that value after gCurrentFee changes. But what actually happens is that CurrentFeeLookup is empty. I've tried creating a new record, but still CurrentFeeLookup is empty.

What am I doing wrong? Are globals not allowed for lookups?

Help!

Thanks,

Dan

[Ugo DI LUCA]

You have to do a relookup on the Record_ID.

[DanBrill]

Thanks, that worked. But does this mean that I need to do a relookup on RecordID every time I create a new record? If so, what is the best method for automating this?

Thanks,

Dan

[RussBaker]

I suspect your problem is being caused by the order in which FMP calculates its fields. Lookups only work when the reference field is changed, so, an auto-entered serial number in a record ID will be the first thing that FMP populates. Then, when the fee for this transaction is determined by the relationship - the relationship will not see that the record ID has changed (because it was created first and hasn't changed since then).

The way to fix this is to create another field called AutoEnterKey and set its options to auto enter 1. Now, base your relationship on this field and your problem will go away.

On a different tack, you might want to consider having your fee structure in a separate file, because that might provide you with better security over the fee structure and prevent creative users from changing your fees within the main file. These fees would still be looked up via a relationship.

[BobWeaver]

If you are keeping the fee in a global field, then the simplest solution of all may be to define the CurrentFeeLookup field as auto-enter calculation

(=gCurrentFee), rather than a lookup.

[DanBrill]

Thanks, Russ!

That does the trick. I just want to thank you for going out of your way to help me out. Just so everyone knows, Russ created and sent me an example of how to get this to work. That's twice in 2 days that he's gotten me on track.

I want to thank everyone that uses this site. It is, far and away, the most professional, most useful, and friendliest cyber-group I've ever been a part of. I've never seen anyone flamed, and questions from the most basic, like mine, to the most advanced, which I don't even understand yet, are treated with respect and consideration.

Thanks to everyone who helps fill the very large gaps in FM's documentation.

Dan