You may want to add that the bom level is 1 for displaying if you do not have a way to keep the top parent part.
1 Like
I suppose but that still brings me back to my original issue, which is that both IAQ-REF**** and 510-037 are BOM Level 1, and are both showing the top level assembly. I only want it to show once.
And since my mtl sequence doesnāt match up over all parts, I was hoping there was some way to distinguish what the first part that is returned is. Or, we could possibly still use the Min Mtl sequence? I just need to know, IF BOMLEVEL = 1 and PartMtl.MtlPartNum = Min(MtlSeq). Not sure the syntax on that or if it would even work? I just want the part shown on top of these to be the one that shows my top level, that way it is separated when I search multiple parts.
What about a window function similar to a previous suggestion usin min() over (partition by) ?
Iām open to anything, Iām just not sure how to implement it.
See if this resource helps:
@Anthony_Mattice Here is an example that @timshuwy posted with his exploded bom query that has a calculated Indented view that may give you some clues on how to determine when
I looked into this as well while trying to get my BAQ to work originally. I never was able to get his BAQ up and running in our system. This would work if my company would use the correct MTL seq setup, instead of using 1 in some places and 10 in others.
@CSmith so maybe something like this? I know this is not correct but I donāt know how to make everything align properly. Also, when I check syntax it says possible SQL injection. Will the BAQ allow me to use this?
SELECT PartMtl_MtlSeq,
MIN(PartMtl_MtlSeq) OVER
(PARTITION BY PartMtl_MtlPartNum ORDER By PartMtl_PartNum)
AS top_level
WHERE PartMtl_MtlPartNum = PartMtl_MtlPartNum
Your calculated field something like:
CASE WHEN BOMLEVEL = 1 AND PartMtl.MtlSeq = Min(MtlSeq) OVER (PARTITION BY PartMtl.PartNum) THEN PartMtl.PartNum ELSE '' END
I havenāt examined your query very well, but something like this should work for you in determining when to display the PartNum.
Instead of using the actual material number, you can use the windowing function to give you a row number ordered by the material seq.
example:
ROW_NUMBER() OVER (PARTITION BY jobnum, AssemblySeq ORDER BY MtlSeq)
Then you donāt care what they use for the material sequence, the first one is always 1 and so on.
2 Likes
Looks like whatever this is doing is somehow breaking my query and I return nothing. I changed BOMLEVEL to my Calculated_Hierarchy field, and fixed the MtlSeq to match my tables Iām calling. Not sure what in this would cause it to return nothing though.
So with this, how would I set this up?
ROW_NUMBER() OVER (PARTITION BY PartMtl_PartNum, Calculated_Assemblies ORDER BY MtlSeq)
Would it be something like this? Also, Iām assuming we are no longer in āPost-Processingā, but within my calculated field?
Yeah, just make a calculated field and use that instead of your material sequence that you were using, then you should be able to just adjust from met seq 10 and use the number 1 with what you already had going on with your query before you noticed the discrepancies.
So you get this something like this. I donāt have any multi level BOMs in this data base, but you get the idea. Even though the MTL seq starts with a 5, the first one is still 1.
This is the calculated field
Do you understand what the partitions are? Does it make sense to you?
I understand for the most part, except for the AssemblySeq. Not sure where I can find that on my end, unless I can just remove that altogether and partition by my company and part number?
I was just using the jobMtl table, and thatās how you would do it there. Youāll need to decide how to partition in in whatever tables you are using, I was just giving you the basic tool to be able to do it.
This is the basics of what a āPartitionā is. Basically, any time any on of the partition changes, the grouping starts over. And you can combine them so that any piece of the partition changes, the row number starts over. So if the excel example, you can see the row numbers that you would get. I changed the color for every time the rows start over.
So choose your partitions so that they start over as necessary. If you are using a recursive query, Iām assuming that they will start over with each recursion.
@Banderson this seems to work for me now, I just did a partition by company, and then ordered by Mtl Seq. This seems to be what I am looking for! Now, I went back to my post processing, and wanna do something like IF Calculated_MinMtlSeq1 = 1 AND BOMLEVEL = 1, then PartMtl_PartNum.
I have this, but it is yelling at me about converting type āintā to āstringā.
Any ideas? Or should I be doing this within the calculated field? It didnāt seem to want to work when I tried it that way.
foreach (var ttr in result.Results)
{
if(ttr.Calculated_MinMtlSeq1 = 1)
{
ttr.Calculated_DisplayedTopLevel = ttr.PartMtl_PartNum;
}
}
Itās a type mismatch. Somewhere you are trying to compare and int to a string. Check your calculated fields and make sure you set the types correctly.
Also, you went through all this stuff with updatable stuff, but if you go back to what you originally had in your first post, and just use this calculated field instead of the material sequence, you should be good. (I think)
to be honest, all of this stuff was a bit TLDR for me. (sorry)