Help with data table relationships

[jrubino77]

I am having an issue (I have this a lot) where I am trying to use a field from another table but my custom column does not see the table/field.  I have all of my relationships setup for all of my tables, but the table where I am creating my custome column is not linked/related to this other table.  This other table is linked or related by another link/join from another table.

How do I get my relationships all setup so I can see and use all of the fields in al of the table no matter what table I am trying to create a custom column in?

In the attached picture - the Item Location,  Unit of Measure and Item Branch all have multiple keys.  I need the Item Loactoin table to use the Unit of Measure table for converting OH from LB to RL (rolls).   

Can anyone help me with this?  These are Jd Edwards World tables.

Much appreciated, thank you.

Jim

 

[amitchandak]

@jrubino77 , In case there more than one columns that are key that will be used to join two table.

Create a key column in both tables and join

Key = [Col1] & "-" & [Col2]& "-" & [Col3]

[jrubino77]

No this did not TOTALLY help.  There is not enough unique fields to concat to create a unique key field.  I am still searching for a solution.

[jrubino77]

I have tried to create a unique key by concatenating multiple fields.  There is not enough of a unique group of fields to do this.

The F41021 has the same item number for multiple locations with OH qtys.  The F41002 has same item number for multiple conversion factors (carton to box, roll to pounds, pounds to ea, etc.)  I have a join to the F41002 by the F4101 which has only 1 item number record (Item Master table).  F4101 is joined to F4102 by item number 1 to many.  F41021 is joined to F4102 (I created a unique for this join).  I am at a loss what else to try.  Any other suggestions please?