BOM baq

Kinetic ERP
,
12

yeah, I believe you need to figure out the rev of the top level part then join PartMtl tabel on the top level part number and top level revision. Then you would add part mtl again to the first part mtl table and join it on the PartMtl.MtlPartNum = PartMtl2.PartNum and then you’d have to get the most current rev. at that point you’d be two levels down into the bom. Left outer join part mtl.

Yash, full disclosure, I am thinking out loud here and haven’t done this in a minute. Start playing with the partmtl table and you’ll see where you need to go.

1 Like
13

Could I directly link Part to OrderDtl and build the partmtl’s off of that?

14

Yes.

15

You will start to get duplicates in your top level query Yash so you just have to play around until you get the results you want. At that point you may want to think about using a subquery or a grouping…

16

1
11920×1040 114 KB

17

~If you want to recursively traverse the BOM you may need a CTE query?~ - Kinetic 2021 - Epicor User Help Forum (epiusers.help)

18

yeah try that and see what you get.

19

And then I displayed the PartMtl1_MtlPartNum value, analyzed it, got 0 rows returned

20

try this, what did you get?

21

Query returned 0 rows

22

I don’t know what joins you have between the two part mtl tables.

are you sure they are correct?

23

Should all of the join types remain as matching?

24

Company to Company, MtlPartNum to MtlPartNum

25

company to company MtlPartNum to Partnum

26

Still getting 0 rows returned

27

If you only want rows that match the joins then yeah, otherwise you should do a left outer join.

28

okay, what is your join on part to part mtl?

29

Company to Company, PartNum to MtlPartNum

30

Partnum to PartNum Yash…

You should take a second and understand what the partmtl table is because it may help you in the future.

31

Ahh that one actually defaulted on its own

1 Like