Matching Several IDs in one table

[gfisch13]

Good Afternoon All,

I'm working with a Purchase Order workflow table that contains a unique ID for the Requester, Preparer, and Approver of the Purchase Order.   Each of these unique ID's corresponds to an Active Employee ID number.   There are times when these three values are completely different, but there are also situations where 1/3 or 2/3 are the same.   

I have a separate HR Table that includes these Active Employee ID numbers along with associated names, job titles and departments.

I'm unsure how to relate these records.   I can associate one ID with no issue, do I need to have 2 additional instances of the HR table (named differently I'd assume) to associate the other two ids? 

Thank you.

George

[juraj_misina]

Hi,

the question here is whether or not you need to associate the whole HR table, or are you OK with employee name. If employee name is enough, then I'd create a mapping table mapping EmployeeID to Employee Name and map that instead of IDs in the PO table. On the other hand, if you need the whole HR table connected to all three IDs, then you have two options:

• separate identical tables with qualified attributes (this is what you suggest)
• create a bridge table between PO and HR tables with three attributes: PO_ID, EmployeeID and EmployeeRole (Requester || Preparer || Approver). You can then use EmployeeRole field as a dimension, filter or in set analysis to select respective employee data.

Hope this helps.

Juraj

[gfisch13]

Thank you Juraj - it does seem that separate instances of the HR table is the way to go as I do need access to the full file.  This messages was an attempt to understnad what other users would do in similar situations.  I doubt I'm the only one that has tried this.

Appreciate your input!

[juraj_misina]

Hi,

have a look at Canonical Date. You can use this approach in your case as well.

Juraj

[gfisch13]

Juraj - this is very interesting and I just need some time to evaluate it and apply, I'm a junior user but learn quick.  This has possibilities.   Thanks again!