Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Did you tried with Joins or applymap
Have you seen this blog on BOMs? Bill of Materials
Joins are a problem.. I would have to load the Job Field from both tables (JOBS and MATLREQ) as Parent_Job and Component_Job.. However, i believe this will create a Loop during the load script and cause for loosely coupled tables
ApplyMap() is not a viable option as it only returns a single value
BILLJOBS table has all of my Parent-Child relationships for Jobs..
JOBS table holds all of the part information for a given Job (Parent and Child can be found here)
MATLREQ hols all the materials used to make a part in a single job (This can be a one-many relationship)
I must connect these based on Job Number, and the end result must be a Parent-Child table of Parts (including materials as parts)
I have, And i dont believe that method Applies, as his BoM is already in a single table.. Whereas mine is broken down into
BILLJOBS table has all of my Parent-Child relationships for Jobs..
JOBS table holds all of the part information for a given Job (Parent and Child can be found here)
MATLREQ hols all the materials used to make a part in a single job (This can be a one-many relationship)
I must connect these based on Job Number, and the end result must be a Parent-Child table of Parts (including materials as parts)
Perhaps I do not understand your problem. Why can you not simply join the tables on the two keys? Like this:
BILLJOB:
SQL SELECT "Component_Job",
"Last_Updated",
"Manual_Link",
"Parent_Job",
"Relationship_Qty",
"Relationship_Type",
"Root_Job";
SQL SELECT "Component_Job",
"Last_Updated",
"Manual_Link",
"Parent_Job",
"Relationship_Qty",
"Relationship_Type",
"Root_Job"
FROM PRODUCTION.dbo."Bill_Of_Jobs";
Left Join (BILLJOB)
LOAD
"Top_Lvl_Job" as Root_Job,
Job,
"Part_Number",
Rev,
Status;
SQL SELECT Job,
"Part_Number",
Rev,
Status,
"Top_Lvl_Job"
FROM PRODUCTION.dbo.Job WHERE Status Like '%Temp%';
Left Join (BILLJOB)
LOAD Job,
Material,
"Material_Req",
"Material_ReqKey",
"Quantity_Per";
SQL SELECT Job,
Material,
"Material_Req",
"Material_ReqKey",
"Quantity_Per"
FROM PRODUCTION.dbo."Material_Req";
Or perhaps you are having problem with the hierarchy of components? You may need to load the first table with a hierarchy load. See this article for more information on how to do that: Unbalanced, n-level hierarchies
Unfortunately, the ERP System we are moving to does not associate Bill of Material based on Job Numbers.. Its associated by Part Numbers.. On top of this, Our current ERP system has our Parts and Materials broken up into seperate tables, hence the "Jobs" and "Materials" (You would think that values would be unique to their respective tables, but it is possible for a Part to be in both the "Jobs" table as well as the "Materials" table
Because of this Unique structure the current BILLJOBs table i have shown does not actually reflect all of the child parts, Simply the Final produce and all part we are producing that go into it.. It does not take into consideration any raw materials used in the process. I can relate the JOBS table to the BILLJOB table and get the part numbers associated for that, I however am not able to link the raw materials in and still keep it to a 2 Column Parent/Child table
I dont believe a Hierarchy load will work either, as both the Parts and Materials can be used in different places, I believe that article (Or the comments) mentions issues with 1-Many relationships
Maybe something along these lines?
JOBS:
MAPPING
LOAD * INLINE [
F1, F2
ParentJobA, PartA
ChildJobA, PartB
ChildJobB, PartC
ChildJobA-A, PartD
ChildJobA-A-A, PartE
];
BILLJOBS:
LOAD ApplyMap('JOBS',Parent) as Parent, ApplyMap('JOBS',Child) as Child
INLINE [
Parent, Child
ParentJobA , ChildJobA
ParentJobA, ChildJobB
ChildJobA, ChildJobA-A
ChildJobA-A, ChildJobA-A-A
];
MATLREQ:
LOAD *
Where Exists(Parent) or Exists(Child, Parent);
LOAD Applymap('JOBS',Job) as Parent, Material as Child INLINE [
Job, Material
ParentJobA , MaterialA
ParentJobA , MaterialB
ParentJobA , MaterialC
ChildJobA , MaterialD
ChildJobB , MaterialE
ChildJobA-A , MaterialF
ChildJobA-A-A , MaterialG
ChildJobA-A-A , MaterialH
];
Thank you for this, While this directly did not solve my problem, It provided the insight i needed to get it working.
One of those moments where you can't believe you didn't figure it out sooner...
Thank you for that code visual swuehl, It was a big help!!