AnsweredAssumed Answered

Global and Foreign ID

Question asked by keycoachjohn on Jul 13, 2009
Latest reply on Jul 13, 2009 by keycoachjohn


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.