Assign Parent/Child Heirarchy in Indented BOM BAQ

So recently in my new job they have asked me to create a BAQ in epicor that will allow them to view an Indented bill of materials. I’ve never used Epicor before this, but I’ve had experience with other ERP systems. I was able to get everything up to this point by playing around and following tutorials, but I am really stuck on this.

Right now, I have a query that will return all parts under a top level, assign them a BOM level, and show me the parent part and the current part. For the life of me, I can not figure out how to sort it so that my child part goes under the parent part in this table. I have tried doing a calculated field that creates an ID of every part going down the list, incorporating its parent’s calculated field as well, but I can’t get it to work and I feel like there has to be an easier way.

Here is my current query results:

I uploaded my BAQ for reference. Any help would be appreciated!
IndentBOMdev.baq (34.7 KB)

I’ve done this with a calculated field. I use the material sequence number in the PartMtl table and create something like Parent Seq - Child Seq. Then I sort by that calculated field. Here’s what I did. In my CTE subquery, the sequence (Ind1) is like this.

Then I do the lower level sequence (Ind2) like this:

It makes something that looks like this:

1 Like

Would right clicking the grid, show group by, and dragging the parent part and then bom level work for you?

1 Like

That’s a neat approach. I hadn’t considered that but it might work just as well. I know it still exports to Excel and keeps those groupings, which is useful.

1 Like

I also did not think about this. I don’t think grouping will quite work for me. I don’t want it to separate everything out. I don’t want a child of 1 item shown as a separate item that shows it’s childs.

But thanks for the idea!

1 Like

Can you lay out what you are trying to get it to look like in excel? I can’t really understand exactly what you are trying do with just your description.

Actually, this is exactly what I was looking for! Just couldn’t figure out a way to do this and sort properly. As long as it take its parent “ID”, and adds it’s own, it sorts. Thanks so much, you’re a lifesaver!!

1 Like

I know this is a little bit of an old post, but I wanted to say “THANK YOU” for this. Exactly what I needed to create a properly indented BOM where everything displays in a logical order. THANKS!!!

1 Like