Opinions and best way forward

aarong · October 4, 2021, 12:09pm

Hello everyone,

If anyone able to help me or post their opinions on this.

I have this button on UD100 without a binding but the “Credit Hold Found” has a binding of UD100.CreditHoldFound_c

We use Key1 within OrderRel and MscHed for a dashboard but i want to be able to run a BAQ I created which has a parameter using UD100.Key1 and SUM the value of a column if that OrderRel has been shipped.

and update the Credit Hold Found if Credit Hold for one of the customers has been ticked in the dataset returned.

This is my BAQ

/*
 * 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.
 */
 
select 
	[Customer].[CustID] as [Customer_CustID],
	[OrderRel].[OrderNum] as [OrderRel_OrderNum],
	[OrderRel].[PartNum] as [OrderRel_PartNum],
	[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
	[OrderRel].[LoadNum_c] as [OrderRel_LoadNum_c],
	[OrderRel].[DropSeq_c] as [OrderRel_DropSeq_c],
	[OrderHed].[PONum] as [OrderHed_PONum],
	[JobProd].[JobNum] as [JobProd_JobNum],
	[SerialNo].[SerialNumber] as [SerialNo_SerialNumber],
	[OrderDtl].[ShipComment] as [OrderDtl_ShipComment],
	[OrderRel].[OrderLine] as [OrderRel_OrderLine],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[OrderRel].[SellingReqQty] as [OrderRel_SellingReqQty],
	[OrderRel].[ShipToNum] as [OrderRel_ShipToNum],
	[Customer].[Name] as [Customer_Name],
	[Customer].[Address1] as [Customer_Address1],
	[Customer].[Address2] as [Customer_Address2],
	[Customer].[Address3] as [Customer_Address3],
	[Customer].[City] as [Customer_City],
	[Customer].[State] as [Customer_State],
	[Customer].[Country] as [Customer_Country],
	[Customer].[PhoneNum] as [Customer_PhoneNum],
	[UD100].[LoadName_c] as [UD100_LoadName_c],
	[UD100].[LoadingMeters_c] as [UD100_LoadingMeters_c],
	[UD100].[TrailerType_c] as [UD100_TrailerType_c],
	[UD100].[Carrier_c] as [UD100_Carrier_c],
	[UD100].[Key1] as [UD100_Key1],
	(CONCAT(ShipTo.Name,'  ',
            ShipTo.Address1,'  ',
            ShipTo.City,'  ',
            ShipTo.State,'  ',
            ShipTo.Zip,'  ',
            ShipTo.Country) + Dummy) as [Calculated_Address],
	(COALESCE(CONVERT(nvarchar, UD100.DateCustContact_c, 105), CONVERT(nvarchar, UD100.CollectionDate_c, 105), CONVERT(nvarchar, UD100.AvailableDate_c, 105), (CASE 
 --Jan
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),1,1) then 'Early January' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),1,14) then 'Mid January'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),1,27) then 'Late January' 
 --Feb  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),2,1) then 'Early Feburary' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),2,14) then 'Mid Feburary'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),2,27) then 'Late Feburary' 
   
 --Mar  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),3,1) then 'Early March' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),3,14) then 'Mid March'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),3,27) then 'Late March' 
 --Apr  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),4,1) then 'Early April' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),4,14) then 'Mid April'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),4,27) then 'Late April' 
 --May  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),5,1) then 'Early May' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),5,14) then 'Mid May'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),5,27) then 'Late May' 
 --Jun  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),6,1) then 'Early June' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),6,14) then 'Mid June'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),6,27) then 'Late June' 
 --Jul  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),7,1) then 'Early July' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),7,14) then 'Mid July'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),7,27) then 'Late July' 
 --Aug  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),8,1) then 'Early August' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),8,14) then 'Mid August'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),8,27) then 'Late August' 
 --Sept  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),9,1) then 'Early September' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),9,14) then 'Mid September'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),9,27) then 'Late September' 
 --Oct  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),10,1) then 'Early October' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),10,14) then 'Mid October'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),10,27) then 'Late October'   
 --Nov  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),11,1) then 'Early November' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),11,14) then 'Mid November'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),11,27) then 'Late November' 
 --Dec  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),12,1) then 'Early December' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),12,14) then 'Mid December'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),12,27) then 'Late December'   
   else 
   'No date' 
 end))) as [Calculated_PlannedDate],
	[UD100].[LoadDesc_c] as [UD100_LoadDesc_c],
	[ShipTo].[Name] as [ShipTo_Name],
	[ShipTo].[Address1] as [ShipTo_Address1],
	[ShipTo].[Address2] as [ShipTo_Address2],
	[ShipTo].[Address3] as [ShipTo_Address3],
	[ShipTo].[City] as [ShipTo_City],
	[ShipTo].[State] as [ShipTo_State],
	[ShipTo].[ZIP] as [ShipTo_ZIP],
	[ShipTo].[Country] as [ShipTo_Country],
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
	[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
	[OrderHed].[OrderNum] as [OrderHed_OrderNum],
	('') as [Calculated_Dummy]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on 
	OrderHed.Company = OrderDtl.Company
	and OrderHed.OrderNum = OrderDtl.OrderNum
	and ( not OrderDtl.PartNum like 'CONTRACT%'  )

inner join Erp.OrderRel as OrderRel on 
	OrderDtl.Company = OrderRel.Company
	and OrderDtl.OrderNum = OrderRel.OrderNum
	and OrderDtl.OrderLine = OrderRel.OrderLine
	and ( OrderRel.OpenRelease = 1  or OrderRel.OpenRelease = 0  )

left outer join Erp.JobProd as JobProd on 
	OrderRel.Company = JobProd.Company
	and OrderRel.OrderNum = JobProd.OrderNum
	and OrderRel.OrderLine = JobProd.OrderLine
	and OrderRel.OrderRelNum = JobProd.OrderRelNum
left outer join Erp.JobHead as JobHead on 
	JobProd.Company = JobHead.Company
	and JobProd.JobNum = JobHead.JobNum
left outer join Erp.SerialNo as SerialNo on 
	JobHead.Company = SerialNo.Company
	and JobHead.JobNum = SerialNo.JobNum
inner join Ice.UD100 as UD100 on 
	OrderRel.LoadNum_c = UD100.Key1
	 OrderRel.Company = UD100.Company
	and ( UD100.Key1 = @LoadNum  )

inner join Erp.ShipTo as ShipTo on 
	OrderRel.Company = ShipTo.Company
	and OrderRel.ShipToCustNum = ShipTo.CustNum
	and OrderRel.ShipToNum = ShipTo.ShipToNum
inner join Erp.Customer as Customer on 
	OrderHed.Company = Customer.Company
	and OrderHed.CustNum = Customer.CustNum
union
select 
	[Customer_Msc].[CustID] as [Customer_Msc_CustID],
	[MscShpDt].[PackNum] as [MscShpDt_PackNum],
	[MscShpDt].[PartNum] as [MscShpDt_PartNum],
	[MscShpDt].[LineDesc] as [MscShpDt_LineDesc],
	[MscShpHd].[LoadNum_c] as [MscShpHd_LoadNum_c],
	[MscShpHd].[DropSeq_c] as [MscShpHd_DropSeq_c],
	(convert(nvarchar(15), MscShpHd.PONum)) as [Calculated_PONum],
	[JobHead_Msc].[JobNum] as [JobHead_Msc_JobNum],
	[SerialNo_Msc].[SerialNumber] as [SerialNo_Msc_SerialNumber],
	[MscShpHd].[ShipComment] as [MscShpHd_ShipComment],
	[MscShpDt].[PackLine] as [MscShpDt_PackLine],
	(0) as [Calculated_MiscLine],
	[MscShpDt].[Quantity] as [MscShpDt_Quantity],
	[MscShpDt].[ShipToNum] as [MscShpDt_ShipToNum],
	[MscShpHd].[Name] as [MscShpHd_Name],
	[MscShpHd].[Address1] as [MscShpHd_Address1],
	[MscShpHd].[Address2] as [MscShpHd_Address2],
	[MscShpHd].[Address3] as [MscShpHd_Address3],
	[MscShpHd].[City] as [MscShpHd_City],
	[MscShpHd].[State] as [MscShpHd_State],
	[MscShpHd].[Country] as [MscShpHd_Country],
	[Customer_Msc].[PhoneNum] as [Customer_Msc_PhoneNum],
	[UD100_Msc].[LoadName_c] as [UD100_Msc_LoadName_c],
	[UD100_Msc].[LoadingMeters_c] as [UD100_Msc_LoadingMeters_c],
	[UD100_Msc].[TrailerType_c] as [UD100_Msc_TrailerType_c],
	[UD100_Msc].[Carrier_c] as [UD100_Msc_Carrier_c],
	[UD100_Msc].[Key1] as [UD100_Msc_Key1],
	('') as [Calculated_Dummyfield],
	('') as [Calculated_DummyField2],
	[UD100_Msc].[LoadDesc_c] as [UD100_Msc_LoadDesc_c],
	('') as [Calculated_Dummy3],
	('') as [Calculated_Dummy4],
	('') as [Calculated_Dummy5],
	('') as [Calculated_Dummy6],
	('') as [Calculated_Dummy7],
	('') as [Calculated_Dummy8],
	('') as [Calculated_Dummy9],
	('') as [Calculated_Dummy10],
	(0) as [Calculated_Dummy11],
	(0) as [Calculated_Dummy12],
	(0) as [Calculated_Dummy13],
	(CONCAT(MscShpHd.Name,'  ',
            MscShpHd.Address1,'  ',
            MscShpHd.City,'  ',
            MscShpHd.State,'  ',
            MscShpHd.Zip,'  ',
            MscShpHd.Country)) as [Calculated_MiscAddress]
from Erp.MscShpHd as MscShpHd
inner join Erp.MscShpDt as MscShpDt on 
	MscShpHd.Company = MscShpDt.Company
	and MscShpHd.PackNum = MscShpDt.PackNum
left outer join Erp.JobHead as JobHead_Msc on 
	MscShpDt.Company = JobHead_Msc.Company
	and MscShpDt.JobNum = JobHead_Msc.JobNum
left outer join Erp.SerialNo as SerialNo_Msc on 
	JobHead_Msc.Company = SerialNo_Msc.Company
	and JobHead_Msc.JobNum = SerialNo_Msc.JobNum
inner join Erp.Customer as Customer_Msc on 
	Customer_Msc.Company = MscShpHd.Company
	and Customer_Msc.CustNum = MscShpHd.CustNum
inner join Ice.UD100 as UD100_Msc on 
	UD100_Msc.Key1 = MscShpHd.LoadNum_c
	and UD100_Msc.Company = MscShpHd.Company
	and ( UD100_Msc.Key1 = @LoadNum  )

where (MscShpHd.ShipDate >= dateadd (year, -2, Constants.Today))
order by OrderRel.OrderNum Desc

Banderson · October 4, 2021, 1:38pm

What’s your question? You can use the dynamicQueryAdapter to run a query.

aarong · October 4, 2021, 1:55pm

There isn’t an question to be asked.

It’s more of advice from someone with a lot more experience than me what route they would take to achieve this.

Would a dynamic Query be able to do this?

Mark_Wonsil · October 4, 2021, 2:32pm

Honestly, what I’ve learned from the smart people on this list is to start with the business need first. Often people come to the group with a solution that they’ve started and can’t finish. It may be the right solution and it may not. Sometimes, people were working on a solution because they didn’t know there was one built into Epicor. Or they think the business says they want one thing but really need a different thing.

Users are often like a person who drives into a auto maintenance shop and asks for an oil change and a tune up. What they didn’t tell the mechanic is that the Check Engine light is on.

Knowing the pain point we’re trying to solve not only helps me but also aids the people whose help I’m asking for.

Matt_Belshaw · October 4, 2021, 4:29pm

“Don’t state the solution, state the problem”

aarong · October 7, 2021, 11:04am

Hi Mark,

I’m not after a solution or a complete tutorial on how to preform this task.

I have looked into dynamicQuery, BPMs, BAQDataView but I don’t know how write the data back to a UD Field to use it elsewhere or maintain the data on the screen as from what i’ve read dynamicQuery is the best way for this.

PS. Thank you for the lecture I’ll be sure next time not to directly ask for help

Thanks
Aaron

Mark_Wonsil · October 7, 2021, 11:33am

Understood. I hope you didn’t take that as a lecture and certainly not as criticism. You know what you want to do but in order for others to know what you want to do, it really helps us understand why you want to do it. When we define the “why” we need to do something, it reveals the outcome and helps people approach the proposed solution with proper perspective.

Cheers,

Mark

aarong · October 7, 2021, 12:19pm

Hi Mark,

No it wasn’t taken as lecture or criticism I understand I didn’t get straight to the point and posted the ideal solution rather than the problem.

I hope you didn’t take it as me being sarcastic…

We have a dashboard which is based off UD100 for it’s data

There is a field (Value) currently manually calculated but I have been asked can it be automated on “Load” or “Button Press” and have the BAQ written to perform this task as well as tick a box stating whether or not Credit Hold has been ticked for one of the rows returned in the dataset of the BAQ. If credit hold has been found in any of the rows then update “CreditHoldFound” as true.

If I was going to use a baqDataView it would be only for that session and not retain the data if I’m correct and same principle for dynamicQuery?

Why? To prevent manual tasks and try to automate processes and enhance the dashboard

I hope that clarifies things.

Cheers,
Aaron

Topic		Replies	Views
BAQ Custom Action - moving from C# client side to BPM Epicor ERP 10 e10 , bpm , customization , baq	1	915	May 9, 2020
Passing Parameter/Filter in Dynamic Query Epicor ERP 10	27	22880	October 19, 2018
BAQ Dataview Example Video Experts' Corner e10 , customization	39	11360	September 9, 2024
Possible solution for silent BAQ parameter passing (working now) Kinetic ERP baq , kinetic , baq-parameter , application-studio	30	8441	November 7, 2023
BAQ Grouping Epicor ERP 10 e10 , epicor10_0	32	6694	May 10, 2024

Opinions and best way forward

Related Topics