Global and Foreign ID
I really thought I had a handle on this topic, but keep getting quirky results. Much of what I’ve read pertaining to table relationship suggests creating a serialized ID on the “one” table and a non-serialized foreign ID on the “many” table; and diligently trying to avoid “many to many” relationships.
For simplicity I extracted the two troublesome tables just to isolate the errors (all other relationships and tunneling work perfectly)….
Take two tables, one “Images” and one “Inventory”. Within “Images” are 3 fields “Images ID” “Inventory Type” and a container field for a jpg. Within “Inventory” are fields “Inventory ID”, "Image ID", “Inventory Serial Number” “Inventory Type”. By the way, the first table “Images” has a value list for “inventory type” that draws from table “Inventory” to ensure no input discrepancies. Incidentally, “Inventory Type” is unique and can qualify as a ID but from a purist sense what am I overlooking? 2 IDs? In my design, this is supposed to be a O-T-M; Images (one) Inventory (many); one image per inventory type to a whole bunch of inventory items each serialized in "Inventory".
When I join in several combination using the ID fields I get the one-many relationship in the graph, but no jpg on the layout. F(iguring a OTM is better than MTM.)We’re talking each direction, serializing on and off, indexing on and off, have tried about 10 variations. No jpg.
When I link “Inventory type” to “Inventory type” the relationship graph shows Many to Many, it works fine but have to exit FM to get updates if I add records in either of the two tables; sometimes updates don't update. Again, playing by the rules I’m trying to avoid the M-T-M; but wonder if having two unique IDs per table is the problem and whether a MTM is a workable solution. Thanks John.