Lookup behavior-what am I missing?

[Carlisle]

OK, I'm stumped.

I've got a multi-table database. I have 2 tables connected by a join table to allow many-to-many joins between the two. Table 1 has an auto-entered serial number id field as does Table 2; they are joined via Table 3 which has its own serial number field and values for the Table 1 and 2 ids. Table 2 is set to allow creation of records from Table 1 via this relationship. So far, pretty straightforward.

I have a layout in Table 1 that displays a field A from Table 2 that allows entry; entering a value into this field in this layout therefore creates a related record in Table 2.

So here's the question:

I have another field B in Table 2 that is defined as a lookup; it is supposed to look up a value from a field in the related record in Table 1. It isn't looking up the value when the new record is created by entering data in the Table 2 field of the Table 1 layout. Does anybody have any guesses why this might be, and how to make this auto-fill short of using a set field script after the record is created (which is the only solution I've been able to figure out)?

Thanks in advance.

[Ender]

I've had occasional lookup problems with FM7/8 too. Changing them to auto-entered calcs (with the Lookup() function) seemed to work.

[Carlisle]

Thanks. I tried that to0, with no luck.

For the moment I'm going to the related record and invoking a script that uses setfield and the relationship to plug in the values.

It has something to do with using a join table--the same problem occurs in a little 3 table test database that I made.

[comment]

Can you post the test file?

[Carlisle]

Sure! Here it is. As you can see, the table 1 layout has the field "color" from table 2; entry into this field creates a record in table 2. However, the table 2 field "name" doesn't look up its value from table 1. However, if you create a new record in table 2 that has a value in its "id" field that makes it related to table 1, the lookup will work. Play around with it; you'll see what I mean.

Thanks for the help.

Test2.fp7.zip

[comment]

OK, I vaguely see what you mean, though I am not sure how you imagine this should work. It seems like a very weird way to work - after all, you can only create ONE related record in table 2 in this way. But you have a join table, so how will you create the next link?

In any case, I think the problem you describe is a timing problem. To see what I mean, define the id field in table 2 to generate the serial number on commit, rather than on creation.

See also this thread regarding the problem of looking up from phantom records.

[Carlisle]

Thanks again for your help.

OK, well here's the idea.

I have a project database in which a project consists of creating a bin containing a bunch of samples of a single item that is one of a set of items, making another bin of samples (which may or may not contain the same item that is in a separate bin), or retrieving a sample from one of these bins. So there is a project table, a table of the different items, and a table of bins that is joined to the project table via a join table.

Now, when I create a new project, I also sometimes need to create a new bin, which I can do by entering data into one of the bin fields from the projects table; this then creates records in the both the bin table and the join table as long as the record id field in each is an auto-generated value filled on creation.

This is what happens in my example when I enter a value in Table 1 in the field Table2::color. But then Table 2:name doesn't look up its value from Table 1:name. (For simplicity, my example is doing its lookup from Table 1, instead of from a 4th table; this doesn't affect the outcome.)

Now, this bin obviously also needs to be identified as to contents, that is, which of the items does it contain? I've already identified which item is being used in this project, so I want the new bin to simply look up what this item is when the record is created. But it doesn't. The relationship is valid, in the sense that after the record is created, I can then use a SetField script step to retreive the value, or I can replace the contents via a calculation using the relationship to item via the projects table. But it won't do this as a lookup.

In contrast, you *can* look up the correct value if there isn't an intervening join table.

[comment]

I don't think your method can work. A join record needs to know who its parents are. If one of the parents does not exist at the time of the creation, then there's nothing to lookup. Subsequent creation of the missing parent is a non-event, as far as the join record is concerned.