@Banderson No issue at all, I don’t expect you to read everything lol thats my job! I went back to what I originally had and I think I got it working. Now I believe I just need to edit my partition a little bit, because I realize that I didn’t account for multiple assemblies, so it doesn’t break those apart. I can get that working though(I believe), so I really only have one final question. I’m not really sure when, but it looks like these last three columns got put into my epigrid? Not sure where they came from, because they aren’t in my BAQ, and I would just like to get rid of them.
I don’t know all of the part you are using to make this, but, last resort in the customiztion (I’m assuming that’s where you are?) you can look at the collection if fields and check hidden and those will go away.
But they were probably added into your BAQ somewhere…
I must be missing something in my BAQ? I have 13 selected, which all 13 columns show up. Not sure where the last 3 come in at all.
/*
* 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],
(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 MinMtlSeq1 = 1 THEN PartMtl.PartNum
else ''
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],
(Row_Number() over (partition by Part.Company order by PartMtl.MtlSeq)) as [Calculated_MinMtlSeq1]
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],
(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],
(Calculated_TopLevel) as [Calculated_TopLevel1],
(case
when Hierarchy2 = 1 AND MinMtlSeq2 = 1 THEN PartMtl1.PartNum
else ''
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],
(Row_Number() over (partition by Part1.Company order by PartMtl1.MtlSeq)) as [Calculated_MinMtlSeq2]
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_QtyPer] as [PartMtl_QtyPer],
[SubQuery11].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[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].[Part_UnitPrice] as [Part_UnitPrice],
[SubQuery11].[Calculated_StdTotalCost] as [Calculated_StdTotalCost],
[SubQuery11].[PlantWhse_PrimBin] as [PlantWhse_PrimBin]
from SubQuery1 as SubQuery11
Here is my customization:
// **************************************************
// Custom code for MainController
// Created: 1/25/2023 3:35:57 PM
// **************************************************
using System;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
using System.Collections;
using System.Collections.Generic;
using Ice.BO;
using Ice.UI;
using Ice.Lib;
using Ice.Adapters;
using Ice.Lib.Customization;
using Ice.Lib.ExtendedProps;
using Ice.Lib.Framework;
using Ice.Lib.Searches;
using Ice.UI.FormFunctions;
public class Script
{
// ** Wizard Insert Location - Do Not Remove 'Begin/End Wizard Added Module Level Variables' Comments! **
// Begin Wizard Added Module Level Variables **
// End Wizard Added Module Level Variables **
// Add Custom Module Level Variables Here **
DynamicQueryAdapter dynamicQueryAdapter;
EpiTextBox myPartSearchTextBoxIsSoFancy = null;
public void InitializeCustomCode()
{
// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
// Begin Wizard Added Variable Initialization
// End Wizard Added Variable Initialization
// Begin Wizard Added Custom Method Calls
this.PartSearch.Click += new System.EventHandler(this.PartSearch_Click);
myPartSearchTextBoxIsSoFancy = (EpiTextBox)csm.GetNativeControlReference("40517db4-11bb-49e7-b78b-184521af84b2");
this.GoButton.Click += new System.EventHandler(this.GoButton_Click);
// End Wizard Added Custom Method Calls
dynamicQueryAdapter = new DynamicQueryAdapter(oTrans);
dynamicQueryAdapter.BOConnect();
}
public void DestroyCustomCode()
{
// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
// Begin Wizard Added Object Disposal
this.GoButton.Click -= new System.EventHandler(this.GoButton_Click);
// End Wizard Added Object Disposal
// Begin Custom Code Disposal
this.PartSearch.Click -= new System.EventHandler(this.PartSearch_Click);
dynamicQueryAdapter.Dispose();
// End Custom Code Disposal
}
private void PartSearch_Click(object sender, System.EventArgs args)
{
object ret = ProcessCaller.InvokeAdapterMethod
(oTrans.EpiBaseForm, "QuickSearchAdapter", "ShowQuickSearchForm", new object[]
{oTrans.EpiBaseForm, "PartSearch", false/* multi-select */, new DataTable() });
// user cancelled
if (ret == null) return; //This is a short circuit. If no value was returned, exit method.
myPartSearchTextBoxIsSoFancy.Text = (String)ret;
}
private void GoButton_Click(object sender, System.EventArgs args)
{
if(myPartSearchTextBoxIsSoFancy.Text == "")
{
MessageBox.Show("Please Enter a Part Number");
}else{
RunBAQ();
}
}
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"];
}
}
I’m not sure where I would go in there to hide those either?
Looks like this actually got added with me turning it into an updatable BAQ. Since I no longer need that I turned it off and we should be good now.
Thanks for all your help!