Lookup Failure

[DanBrill]

What am I doing wrong? I can't make lookups work. I have a field called JournalName that is a calculation based on related fields. I need to use it in a relationship, and so need it to be indexable. Seems like a good place for a lookup.

So I create a text field called JournalNameLookup, set to lookup JournalName based on the 'Self' relationship, which is a self join where the record's unique ID number is related to itself.

Correct me if I'm wrong, but shouldn't JournalNameLookup then be a text field with the value of JournalName once I perform the lookup?

When I put the cursor on JournalName and select Relookup Contents, I get an error message that 'There Are No Fields That Lookup Values Based On Journal Name', even though, I thought, I was setting JournalNameLookup to lookup values based on JournalName. (I get the same message if I have the cursor JournalNameLookup, or even Self::JournalName or Self::JournalNameLookup. I was willing to try anything by this point!)

This isn't the first time that this has happened to me. Do I have a fundamental misunderstanding about how lookups work?

Thanks for any insight,

Dan

[DanBrill]

Ah, Now I see. I need to put my cursor in the ID field, which is the match field for the relationship that the lookup is based on.

But what if I have other fields that have lookups based on ID? What if I don't want to relookup those fields? How do I target the lookup to just one field?

Thanks,

Dan

[Fenton]

For Relookup you put the cursor in the "key" field, not either of the data fields (target and source), which in this case would be the key for the self-join on the ID, which is the ID.

[Fenton]

I see you figured it out. To localize a lookup, you can create a mirror field, equal to the ID, then a new relationship using that instead of the original, a mirror. Use that relationship for the lookup. Never tried it, but it should work.

[DanBrill]

Hey,

I beat you on the first answer. Now you beat me on the second one. I just tried it and then read your suggestion. Good news. It works.

Thanks!

Dan

[DanBrill]

Follow up: According to the FM online help, lookups don't work if you are using a calc field as the match field. You have to re-lookup on one of the fields that that calculation depends on. This would defeat the purpose of making the mirror field.

However, as best as I can tell, it does work, despite what the help file says. Looking up based on my calc field MirrorID = Self::ID works fine. The one thing I did notice is that the match field has to be on the layout for the re-lookup to work.

Dan

[Vaughan]

Oh yeah, Relookup is like Cut and Paste: the fields have to be on the current layout.

Also be mindful of record locking in multi-user causing a scripted relookup to break, or at least leaving the locked records un-relookup-ed.

[Fenton]

That's odd. I can perform a Relookup with a self-relationship on a calculation field, from a calculation field, into a regular field (of course), which is on the current layout, but is not enterable.

Perhaps they meant you can't lookup into a calculation field (duh). That must be what they meant, but they sure could have said it better.

For those who don't want to search the Help:

Calculation fields cannot be used for the Relookup Contents command. If you use the Relookup Contents command, and you're using a calculation field as the matching value, you must specify one of the input fields for the calculation as the field parameter for the Relookup Contents

[Ugo DI LUCA]

Hi,

I didn't catched the "mirror" thing.

May be it's just exactly what I thought was another "odd" thing about lookup.

Here it goes...

Suppose you have a field "n_serialA" and "n_serialB" in another table.

Create a c_serialA = n_serialA

Create a relationship from c_serialA to n_serialB

Now you can relookup by either using n_serialA or c_serialA even if there's no relationship defined between n_serialA to n_serialB.

[Fenton]

Wow. You're right. From Dan's perspective, this means that using the calculation field lookup will be exclusive, as he wanted; but a lookup on the regular ID will also lookup the one based on the calculation. Sounds like that would be fine.

I guess when FileMaker evaluates a Relookup, it looks down the line to see if any calculation fields which are keys for lookups are triggered by a Relookup action (which, as far as I can tell, is the same as resetting the key to itself, hence a change that causes further calculations to reevaluate). If so, then it's not an error, and lookups proceed. That makes sense.

[Jason Goldsmith]

I'm dismayed by what Vaughan added:

"Also be mindful of record locking in multi-user causing a scripted relookup to break, or at least leaving the locked records un-relookup-ed."

I'm creating a database that I intend to use in multiuser mode, and it uses relookups in several important ways. Will the script just freeze or give an error message if another user has the same record open? Is there an alternative solution?

[DanBrill]

Hi guys,

I think Ugo's comment is more disturbing than comforting. I'm trying to shield data from being re-looked-up by creating c_serialA from n_serialA. If I create the lookup from c_serialA to n_serialB so that I can lookup n_serialB based on c_serialA, but then it gets re-looked-up whenever I perform a re-lookup on n_SerialA, then I could be looking up n_SerialB when I didn't mean to. (If some other entirely unrelated lookup was based on n_serialA, I'd be looking up n_SerialB when I didn't want it changed.)

I guess the lesson is to make sure that every lookup has an independant match field unless you are sure you always want to have 2 or more lookups occuring simultaneously.

Thanks for pointing this out Ugo.

As for Vaughn's comment, I don't think comes into play in this particular situation. The lookup is based on self-join to a unique record ID, then the only record that is going to be changed is the one that the user has control of at that time.

But in general, where the relationship is one to many as opposed to self-referential, this is something that needs to be taken into account. So Jason, you'll probably have to script in some error trapping to find out if some records are locked when you want to re-lookup. This is something I know very little about, though I suspect I'll get more familiar with it in the coming months.

Thanks everyone,

Dan

[Fenton]

It is the match field on the left that must be independent. In the case of auto-entered serial ID's this is not too big of a problem; just define 2 of them; they will stay in sync. Seems kind of odd, but I see no problems, other than labelling so you can tell which is which.

As far as record-locking, that's a whole different problem. Both Relookup and Replace depend on the fact that there is only 1 user currently IN ANY RECORD of the found set. In a record means with the cursor parked in a field; just looking with no fields activated doesn't stop anything.

Unfortunately there's no "kick 'em out of the fields" step. That would be annoying in any case. So Relookup and Replace are not really safe in a multi-user situation.

What you can do instead is a Loop. It cannot kick 'em out, but it can tell you which records were locked. The way to test, as the 1st step inside the Loop is Go To Field [any enterable field on the layout]. The error is 301. But a test for Error <>0 is pretty much the same at this point. The test must be immediately after the test.

One thing you can do if it fails is capture that ID(s). Then check the field afterwards and try another loop using just the ID's missed (Go To those records). If it fails again, then you could at least toss up a message. You'd still have whatever IDs failed in the global.

I've attached a small file which shows both of the things we've been talking about.

Lookup wCalc.fp5.zip

[-Queue-]

To be safe I wouldn't use a Relookup in a multi-user scenario unless the data is minor. A Set Field loop is much more secure and easier to track failures, as Fenton indicated. In addition, if a Relookup failed on the first record, the remaining would fail, too. And there is no way (AFAIK) to trap a Relookup's failure by individual record. So a Set Field ["lookupkey", "lookupkey"] loop is highly advantageous over a Relookup. I think this is to what Fenton is referring.

[Vaughan]

I've manually performed the relookup command on multiuser database when some records were locked, and to my surprise the command ended with an elegant alert stating that "X records could not be updated because they were in use" (or something very similar).

It may be that scripted Relookup steps will handle locked records equally elegantly nd not break the script, but the issue is that the developer has to be *aware* that not all records will be updated and plan to do something about it.

[Jason Goldsmith]

In my case, the relookup is based on one record at a time. It is likely that several users might try to access the same record simultaneously. Because I've never worked in multiuser mode, I need to ask a basic question: Can several users access a single record simultaneously?

[DanBrill]

Nope.

[DanBrill]

To elaborate a bit, several can look at a record at the same time, but only one can edit it.

[Jason Goldsmith]

OK, so if one user opens and edits a record and then leaves their workstation (and the record) idle, can another user open and edit the same record on a different workstation without having the first user close the record?

[DanBrill]

Not if they leave their cursor in a field. And unfortunately, there's no way to kick them off.

[DanBrill]

I noticed another thing with lookups today. I had 2 different fields based on the same lookup key. I performd the re-lookup to refresh one of the fields. (Of course, both variables will re-lookup -- I just cared about one at the moment, and it didn't bother me if the other one re-looked up too.) However, I got an error message saying that some fields could not be re-looked up because they were in use by another user. The funny thing is, I was the only user.

At the time, I did have a found set smaller than the full set. I have a hunch that this is a result of having 2 fields looking up on the same key. Apparently with 2 variables based on the same lookup key it produces the network-like error message if some of the records that it is trying to re-lookup are not in the found set.

Odd. But it goes back to my earlier statement that it is better to have each lookup based on a unique lookup key.

Dan

[Ugo DI LUCA]

There must be something else here Dan.

If that was true, then we certainly would have heard of it already. I have a file iin which a lookup key sets about 20 other fields. Since the file is working, there may have been a million of re-lookup made. I've never heard of such a behaviour.

Can you tell more about the keys you used for this re-lookup ?