BAQ Question for keeping results separate

Anthony_Mattice · January 18, 2023, 2:25pm

Backstory: I have a BAQ that is used to grab an indented BOM for a part. When they search for the part, they are able to use a wildcard so they can return back multiple boms at once. We wanted a way to make sure we knew where one bom ended and another began.

Currently, I have this setup here.

That is a simple IF statement that says IF the mtl seq is 10 and the BOM level is 1, display the parent part.

Now this would probably work fine if my company could make up its mine on what sequence they want to use, but it seems like some of our sub-assemblies start out at mtl seq 100. So now, I have to include another if statement in order for that to show up.

Now the issue there comes in when there is at least 10 items on a final assembly and the sub-assembly is one of them. Since there is now a mtl seq 10 and 100, it will show the top level assembly on both parts, and we don’t want this.

Just wanted to see if there is a better alternative here?

NateS · January 18, 2023, 3:13pm

I think this is a fine approach. However, in an attempt to make sure I always grabbed the first material sequence, I would try to pull the materials in a subquery, then you can iterate over the sub-query results to find the min(MtlSeq). Good luck!

Anthony_Mattice · January 18, 2023, 3:21pm

So Nate,

Do you think I should have a calculated field in my anchor that goes through and looks for the lowest mtl seq?

I’m not exactly sure how to accomplish that in a calc field

NateS · January 18, 2023, 3:38pm

I am always a little fuzzy on multi-level BAQs, so I won’t pretend to have the right answer here. Using a subquery you should be able to find the lowest material sequence using the simple calculation min(MtlSeq). But then you have to find a good way to massage that min MtlSeq back into your data.

I am not quite sure the right way to do it with an indented BOM style BAQ like you have, but in my simplified testing, that calculation is the only thing I needed to get the lowest sequence number. You will have to make sure you pull that minimum from the JobHead > JobAsmbl > JobMtl table. Grouping by job and assembly sequence to make sure you get the right material for that assembly.

Anthony_Mattice · January 18, 2023, 3:57pm

Yea…seeing how it is a recursive BAQ I would assume I can’t iterate over the whole result unless I bring the results into another query and do it that way. Maybe I have to think of another method.

Anthony_Mattice · January 18, 2023, 4:03pm

Currently, the way I do this is that I call the BAQ with parameters from a form using code customization, and display the [“RESULTS”] in an epiGridView. Would it be possible to iterate over the results then, add a field, and display the new field?

gpayne · January 18, 2023, 8:34pm

@Anthony_Mattice You can access the results in the baq post processing on getlist and set any fields.

Anthony_Mattice · January 19, 2023, 9:11pm

@gpayne Could you give me some documentation on this or help me figure out how to add that to my returned list?

gpayne · January 19, 2023, 9:26pm

@Anthony_Mattice I would have just this simple piece of code in post processing of getlist.

/* set parent */

string currentPart = string.Empty;

foreach (var ttr in result.Results)
{

if (currentPart != ttr.Part_PartNum)
{

    ttr.Calculated_Assemblies  = ttr.Part_PartNum;
}

currentPart = ttr.Part_PartNum;

}

EDIT: If you ae 10.1 it is still ttResults

Anthony_Mattice · January 23, 2023, 2:16pm

@gpayne Thanks for the continued help and sorry for the late reply I have been insanely busy.

This is my current code, and I must be doing something wrong here, just not sure where I am supposed to get “result.Results” as it returns a context error.

    private void RunBAQ()
    {
        string myPartNum = myPartSearchTextBoxIsSoFancy.Text;
		QueryExecutionDataSet queryExecutionDataSet = new QueryExecutionDataSet();
        queryExecutionDataSet.Tables["ExecutionParameter"].Rows.Add("PartNum", myPartNum, "nvarchar", false, null, "A");
        dynamicQueryAdapter.ExecuteByID("DA-IndentedBOMBAQ", queryExecutionDataSet);
    	epiUltraGridC1.DataSource = dynamicQueryAdapter.QueryResults.Tables["Results"];
		
		string currentPart = string.Empty;

		foreach(var ttr in result.Results)
		{
		if(currentPart != ttr.Part_PartNum)
		{
		ttr.Calculated_Assemblies = ttr.Part_PartNum;
		}
		currentPart = ttr.Part_PartNum;
		}
	}

gpayne · January 23, 2023, 3:57pm

This is in the BAQ. If the BAQ is not updatable check it as updatable and then in update > update processing Set it as advanced BPM only.
Open BPM Directives Configuration.

This technique has been around since the E9 days to address things the query can’t quite pull off.

Anthony_Mattice · January 23, 2023, 4:00pm

Do I keep my code the same in my BPM form designer? Also, do I remove my current calculated field - “Calculated_Assemblies”? Or what do I change that field to so that it still appears but nothing gets put in there from within the query.

And my bad for not knowing this stuff, still trying to get a handle on the design process around BAQs

And I’m also assuming I’ll need extra access for this as I don’t see it on my menu. What do I all need to have access for to do that?

gpayne · January 23, 2023, 5:16pm

Unless you are doing something else in the bpm form you don’t need any code there this is all done in the baq.

You need the field to put the value in. You can set a calculated field to single quotes for nvarchar or zero if it is decimal or int.

BAQ Advanced User will need to be set in your user.

Anthony_Mattice · January 25, 2023, 9:21pm

gpayne:

/* set parent */

string currentPart = string.Empty;

foreach (var ttr in result.Results)
{

if (currentPart != ttr.Part_PartNum)
{

    ttr.Calculated_Assemblies  = ttr.Part_PartNum;
}

currentPart = ttr.Part_PartNum;

}

I got all the permissions setup and am ready to continue with this.

I created the post-processing path for this, but am receiving the following errors:

Does this mean my rows in my query aren’t setup right?

gpayne · January 25, 2023, 11:31pm

You just need to match up to your fields in the baq. Whatever the Part and Assemblies fields are is what you need. If you do not have the Part and now that I am thinking possibly the rev to know when to set the first column. If you do need the rev to be sure then current Part would be part and rev together.

I you want to post or DM me your baq I will send you the changes needed.

Anthony_Mattice · January 26, 2023, 2:20pm

Ah ok, no issue at all I will post it. The first image is my CTE, second is Union, third is TopLevel. I also put the query below that, please let me know if there is more you need. Or let me know if you actually need the BAQ file.

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
with [SubQuery1] as 
(select 
	[PartMtl].[Company] as [PartMtl_Company],
	[PartMtl].[PartNum] as [PartMtl_PartNum],
	[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
	[PartMtl].[MtlSeq] as [PartMtl_MtlSeq],
	[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
	[PartMtl].[QtyPer] as [PartMtl_QtyPer],
	[PartMtl].[RelatedOperation] as [PartMtl_RelatedOperation],
	[PartMtl].[PullAsAsm] as [PartMtl_PullAsAsm],
	[PartMtl].[ViewAsAsm] as [PartMtl_ViewAsAsm],
	[PartMtl].[PlanAsAsm] as [PartMtl_PlanAsAsm],
	(1) as [Calculated_Hierarchy],
	(case
   when PartMtl.MtlSeq < 10 then cast(concat('000', PartMtl.MtlSeq) as nvarchar(25))
   when PartMtl.MtlSeq < 100 then cast(concat('00', PartMtl.MtlSeq) as nvarchar (25))
   when PartMtl.MtlSeq < 1000 then cast(concat('0', PartMtl.MtlSeq) as nvarchar (25))
 end) as [Calculated_Ind1],
	(PartMtl.PartNum) as [Calculated_TopLevel],
	(case
   when Hierarchy = 1 AND PartMtl.MtlSeq = 1 then(PartMtl.PartNum)
   when Hierarchy = 1 AND PartMtl.MtlSeq = 10 then(PartMtl.PartNum)
   else null
 end) as [Calculated_DisplayedTopLevel],
	[PartXRefVend].[VendPartNum] as [PartXRefVend_VendPartNum],
	[PartXRefVend].[MfgPartNum] as [PartXRefVend_MfgPartNum],
	[PartWhse].[OnHandQty] as [PartWhse_OnHandQty],
	[PartWhse].[DemandQty] as [PartWhse_DemandQty],
	[PlantWhse].[PrimBin] as [PlantWhse_PrimBin],
	[Part].[UnitPrice] as [Part_UnitPrice],
	(PartCost.StdLaborCost+ PartCost.StdBurdenCost+ PartCost.StdMaterialCost+ PartCost.StdSubContCost+ PartCost.StdMtlBurCost) as [Calculated_StdTotalCost],
	[Part].[PartDescription] as [Part_PartDescription]
from Erp.PartMtl as PartMtl
left outer join Erp.PartXRefVend as PartXRefVend on 
	PartMtl.MtlPartNum = PartXRefVend.PartNum
left outer join Erp.PartWhse as PartWhse on 
	PartWhse.PartNum = PartMtl.MtlPartNum
left outer join Erp.PlantWhse as PlantWhse on 
	PartMtl.MtlPartNum = PlantWhse.PartNum
inner join Erp.Part as Part on 
	PlantWhse.Company = Part.Company
	and PlantWhse.PartNum = Part.PartNum
left outer join Erp.PartCost as PartCost on 
	PartCost.PartNum = PartMtl.MtlPartNum
where PartMtl.PartNum like @PartNum

union all
select 
	[PartMtl1].[Company] as [PartMtl1_Company],
	[PartMtl1].[PartNum] as [PartMtl1_PartNum],
	[PartMtl1].[RevisionNum] as [PartMtl1_RevisionNum],
	[PartMtl1].[MtlSeq] as [PartMtl1_MtlSeq],
	[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum],
	[PartMtl1].[QtyPer] as [PartMtl1_QtyPer],
	[PartMtl1].[RelatedOperation] as [PartMtl1_RelatedOperation],
	[PartMtl1].[PullAsAsm] as [PartMtl1_PullAsAsm],
	[PartMtl1].[ViewAsAsm] as [PartMtl1_ViewAsAsm],
	[PartMtl1].[PlanAsAsm] as [PartMtl1_PlanAsAsm],
	(Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],
	(case
   when PartMtl1.MtlSeq < 10 then cast(concat(Calculated_Ind1, '-', '000', PartMtl1.MtlSeq) as nvarchar(25))
   when PartMtl1.MtlSeq < 100 then cast(concat(Calculated_Ind1, '-', '00', PartMtl1.MtlSeq) as nvarchar(25))
   when PartMtl1.MtlSeq < 1000 then cast(concat(Calculated_Ind1, '-', '0', PartMtl1.MtlSeq) as nvarchar(25))
 end) as [Calculated_Ind2],
	(SubQuery1.Calculated_TopLevel) as [Calculated_TopLevel1],
	(case
   when Hierarchy2 = 1 AND PartMtl1.MtlSeq = 1 then(PartMtl1.PartNum)
   when Hierarchy2 = 1 AND PartMtl1.MtlSeq = 10 then(PartMtl1.PartNum)
   else null
 end) as [Calculated_DisplayTopLevel2],
	[PartXRefVend1].[VendPartNum] as [PartXRefVend1_VendPartNum],
	[PartXRefVend1].[MfgPartNum] as [PartXRefVend1_MfgPartNum],
	[PartWhse1].[OnHandQty] as [PartWhse1_OnHandQty],
	[PartWhse1].[DemandQty] as [PartWhse1_DemandQty],
	[PlantWhse1].[PrimBin] as [PlantWhse1_PrimBin],
	[Part1].[UnitPrice] as [Part1_UnitPrice],
	(PartCost1.StdLaborCost+ PartCost1.StdBurdenCost+ PartCost1.StdMaterialCost+ PartCost1.StdSubContCost+ PartCost1.StdMtlBurCost) as [Calculated_StdTotalCost1],
	[Part1].[PartDescription] as [Part1_PartDescription]
from Erp.PartMtl as PartMtl1
inner join  SubQuery1  as SubQuery1 on 
	PartMtl1.PartNum = SubQuery1.PartMtl_MtlPartNum
	and PartMtl1.Company = SubQuery1.PartMtl_Company
inner join Erp.PartXRefVend as PartXRefVend1 on 
	PartXRefVend1.PartNum = PartMtl1.MtlPartNum
inner join Erp.PartWhse as PartWhse1 on 
	PartWhse1.PartNum = PartMtl1.MtlPartNum
inner join Erp.PlantWhse as PlantWhse1 on 
	PartMtl1.MtlPartNum = PlantWhse1.PartNum
inner join Erp.Part as Part1 on 
	PlantWhse1.Company = Part1.Company
	and PlantWhse1.PartNum = Part1.PartNum
inner join Erp.PartCost as PartCost1 on 
	PartCost1.PartNum = PartMtl1.MtlPartNum)

select 
	[SubQuery11].[Calculated_DisplayedTopLevel] as [Calculated_DisplayedTopLevel],
	[SubQuery11].[Calculated_Hierarchy] as [Calculated_Hierarchy],
	[SubQuery11].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
	[SubQuery11].[Part_PartDescription] as [Part_PartDescription],
	[SubQuery11].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
	[SubQuery11].[PartMtl_QtyPer] as [PartMtl_QtyPer],
	[SubQuery11].[PartXRefVend_VendPartNum] as [PartXRefVend_VendPartNum],
	[SubQuery11].[PartXRefVend_MfgPartNum] as [PartXRefVend_MfgPartNum],
	[SubQuery11].[PartWhse_OnHandQty] as [PartWhse_OnHandQty],
	[SubQuery11].[PartWhse_DemandQty] as [PartWhse_DemandQty],
	[SubQuery11].[Calculated_StdTotalCost] as [Calculated_StdTotalCost],
	[SubQuery11].[Part_UnitPrice] as [Part_UnitPrice],
	[SubQuery11].[PlantWhse_PrimBin] as [PlantWhse_PrimBin]
from  SubQuery1  as SubQuery11
order by SubQuery11.Calculated_TopLevel, SubQuery11.Calculated_Ind1

gpayne · January 26, 2023, 2:23pm

Use PartMtl_PartNum and Calculated_DisplayedTopLevel

Anthony_Mattice · January 26, 2023, 2:53pm

That seems to have gotten the errors to go away. When I do the query now, I get this. It doesn’t seem to only put the assembly down for the first part, and it also is putting its part number instead of its parents part number. I’m assuming this is because I used PartMtl.MtlPartNum. When I tried using PartMtl_PartNum, I got an error, so I’m assuming I will have to display the parent part in a row?

gpayne · January 26, 2023, 3:17pm

You will need the parent part in a row in the baq to set the calculated field, but you will not have to show it in the dashboard.

Anthony_Mattice · January 26, 2023, 3:27pm

This is where it is at now. Changed everything back to PartMtl_PartNum, and added the parent part row.

So from here, not sure why it is displaying the “IAQ” sub assembly in there, and it is duplicating the top level