How to compare paramater condition in Field column?

i want to compare the condition of paramater with a predefined value if that is true i will ignore the condition like i usually write in SQL what should it be



If I write SQL, I have a sentence like this:
declare @Posted int = null

select
[InvcHead].[Company] as [InvcHead_Company],
[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[InvcHead].[FiscalYear] as [InvcHead_FiscalYear],
[InvcHead].[FiscalPeriod] as [InvcHead_FiscalPeriod],
[InvcHead].[ApplyDate] as [InvcHead_ApplyDate],
[InvcHead].[InvoiceAmt] as [InvcHead_InvoiceAmt],
[InvcHead].[DocInvoiceAmt] as [InvcHead_DocInvoiceAmt],
[InvcHead].[LegalNumber] as [InvcHead_LegalNumber],
[InvcHead].[SysRowID] as [InvcHead_SysRowID]
from Erp.InvcHead as InvcHead
inner join Erp.Customer as Customer on
InvcHead.Company = Customer.Company
and InvcHead.CustNum = Customer.CustNum
where (InvcHead.Posted = @Posted or @Posted is null)

this is just an example, i want to ask how in the file column when designing the BAQ I can call the paramater there!
:thinking: :thinking: :thinking: :wink: :wink: :grinning: :grinning:
Thank you so much have a nice day!

When you use menu item define parameters you can ignore if parameter is unset at execution with checkbox.

1 Like

thank you!


I get what you mean but not i want to do that but here i am trying to use 1 parameter as a condition like case when in the where clause did you take a look at the SQL I wrote?
Have a nice day!

Yes, I did look at it. I am not sure what you are attempting to accomplish with the ‘or @Posted is null’ as it would in essence be all records based on the query you wrote and skip condition checkbox does the same thing. So, without more information it would be difficult to assist with the appropriate logic needed to answer your need/desired query.

@CSmith

case i write @Post is null for example i have this condition Type int =0
if I pass Type=0 then I can get all data lines, I pass Type=1 I get only the posted lines, I pass 2 I get the unposted rows, … at Here, I just want to ask how can I compare the paramater when designing the BAQ, it will be possible to choose in the field column because there are many other logical expression conditions!

I can write 2 or more BAQ sentences that are almost exactly the same, only differing in the condition of taking out based on the paramater passed in, but I don’t want to because doing so sometimes it generates many difficult BAQ sentences because the This BAQ takes same data type only different based on paramater passed type like borrow paramater as condition if else hat case when where you know what I mean?

This if written in T-SQL statement, I can write 1 query without having to split into many queries.
Thank you very much! Have a nice day

After defining the @Posted parameter you can use it like this in a table criteria statement:

This should do what you want when you check the Skip condition if empty check box.

@CSmith

Thank you, this is just an example I describe in case I see you, the command will be more clear about what I want to ask.
declare @Type int = 0
–0. getALL
–1. get APInvHed is Post
–2. get APInvHed is not Post
–…
select * from APInvHed
where @Type=0 or (@Type=1 and Posted=1) or (@Type=2 and Posted=0)
there will be a lot of queries I use in this style I don’t want to split into many BAQ and I want to ask how can I choose where condition is 1 paramter on File column when dragging and dropping BAQ like above epicor has 1 solution Another solution would be to write a BAQ take all and then filter it again but that would be very long I would like to be able to write as short as I use T-SQL like this example I hope you understand what I mean. :thinking: :thinking: :thinking:

                  Thank you so much have a nice day!

I’m pretty sure, but I believe if you check and uncheck that would give you your (2-NotPost), Checking it would be (1-Post) and not touching it at first run would leave it undefined and ignored (0-GetAll) as it will remove the simple condition where your parameter is being used.

Your only other option would be use a int value:
0 = 0-Get All
1 = 1-is Post
2 = 2-is not Post

Just enter what you want it to do and add the proper logic to your query based on the parameter’s request value. This is one way to handle it using your own logic if so needed and just enter a
0,1, or 2 for your value it really depends on how you choose to accomplish this as it can be done multiple ways.

No, I am not really not understanding your request as the answer I gave fits the conditions you asked about, and is the simplest method to do what you are asking. The BAQs SQL code is not really what gets executed. That code is just a approximation of the result set you should expect from the query designed, but there are other things which will be done in the backend to your query design like adding a security layer to it and some other optimizations.

I believe you are overthinking this or not presenting the true question you are attempting to obtain an answer for.

1 Like

You want to use the criteria field, then use a comparison to an expression field. Here are some examples of using logic on parameters as conditions on tables. You would want to check if it’s equal to an expression, then do the case logic in that expression.

Edit* I believe he is asking to basically have a parameter that controls what is getting filtered based on what the parameter is set to. He could do a begins with expression then return an empty string if he doesn’t want the filter to be set. This way, sometimes a certain field can be filtered, sometimes another field can be filtered, sometimes both, etc. I’m sure there is some tricky way to do it with skip if empty, but honestly, some people are more comfortable with straight SQL logic.


1 Like

@LoganS

This is what i need
You get what I mean and I figured it would mean bridging one step further if using epicor’s BAQ is a hassle to use straight as SQL is easier to understand.
@CSmith
Thank you I said it was just a simple example. I have written a lot of T-SQL statements that use logical expressions to solve the cases of getting the same data but will rely on 1 parameter to determine. The definition is how to get the data lines, so the parameter here will be numeric and have many values, I have predefined these values ​​for each specific case.
Before i do other system i write T-SQL command into procedure and call on my system epicor also use this method. I also just re-develop on epicor’s system I still follow epicor’s security procedures just i am developing the features required by the company I use epicor
Thank you very much! Have a nice day!

While I understand why some want to utilize already learned skills vs learning new ones, the BAQ does more than what T-SQL can do:

  • BAQs respect Company/Site/Tenant/Territory/Table/Field security. T-SQL does not.
  • BAQs work with the caching mechanism built-into E10+. I have recently seen a programmer use a stored procedure to pull data into a BPM to print labels but the data hadn’t flushed yet so the labels were missing data. The data printed just moments later upon reprint.
  • BAQs will be easier to upgrade when there are schema changes since ERP knows the names of the fields you’re accessing and won’t validate. You won’t find that out with T-SQL until you get a runtime error.
3 Likes

Interesting, didn’t know expressions in Epicor could bypass the field security checks. I will keep that in mind in the future, maybe make it a point to always include the appropriate field checks if I need to use an expression. Thanks for the info!

1 Like

I don’t think that expressions in the BAQ designer is the issue. It’s when we go to SSMS and create VIEWS, STORED PROCEDURES, etc. instead of using the BAQ is the issue. Good point, I want to be clear about that.

2 Likes

I was misleading with saying SQL logic, I mean the SQL-like logic that you can throw in the expression criteria inside of a BAQ.

1 Like

TL;DR : YES, you can with properly written criteria…

And usually in that situation I would create a bit flag telling my BAQ what filtering I wanted in his example: Bit 1 : Posted, Bit 2 : UnPosted so a value of 3 would be both bits and all results or his (0) value and you could parse this same singular bit flag for multiple yes, no, or aggregated parameters/flags. Like I said it really depends on the need, but with ‘SQL-like’ logic and the ability to write SQL-like-ish code in expression logic you can find a way to achieve what you are asking about.

Are you sure there are no calculations that need to use CURSOR to sequentially query the results of the above line to be taken as the result for the next line. This can be done in code, but for the reporting systems I have worked with I write T-SQL and export it to xtrareport. :grinning: :grinning:
BAQ actually generates T-SQL code for you to call running under that SQL :thinking: :thinking: :thinking
Everything has its advantages. There are problems that I’m sure BAQ can’t solve. It’s not that I don’t want to learn new things, but here the problem is just saying that the epicor should develop, how can it be developed. What I’m asking BAQ can be split into multiple BAQs, or using bridging or filtering the results again, I just want it to be as simple as how I write a T-SQL command using logical expressions so that it doesn’t have to be. rewrite many select sentences with different content but different where condition for each case.
I also got the answer! :wink: :wink: :grinning: :grinning: :grinning_face_with_smiling_eyes: :grinning_face_with_smiling_eyes:
:grinning: :grinning: :thinking: :thinking:
Thank you everyone have a good time!

Yes. That is very true. Poor choice of words on my part. My point was, and still is, hitting the database directly in Epicor may not give one the results they expect and is not recommended by Epicor.

One can use subqueries in BAQs and BAQ Parameters can be used just like defines in a Stored Procedure.

1 Like

here is a simpler way I did it which combines paramater and column Calculated

with [DMRactn] as 
(select 
	[DMRActn].[Company] as [DMRActn_Company],
	[DMRActn].[DMRNum] as [DMRActn_DMRNum],
	[DMRActn].[ReasonCode] as [DMRActn_ReasonCode],
	(max (DMRActn.ActionDate)) as [Calculated_RecentActionDate]
from Erp.DMRActn as DMRActn
where (DMRActn.ActionType = 'D')
group by [DMRActn].[Company],
	[DMRActn].[DMRNum],
	[DMRActn].[ReasonCode])

select 
	[APInvHed].[InvoiceNum] as [APInvHed_InvoiceNum],
	[APInvHed].[DebitMemo] as [APInvHed_DebitMemo],
	[APInvHed].[InvoiceDate] as [APInvHed_InvoiceDate],
	[APInvHed].[GroupID] as [APInvHed_GroupID],
	[APInvHed].[InvoiceComment] as [APInvHed_InvoiceComment],
	[APInvHed].[CurrencyCode] as [APInvHed_CurrencyCode],
	[APInvDtl].[OurQty] as [APInvDtl_OurQty],
	[APInvDtl].[UnitCost] as [APInvDtl_UnitCost],
	[APInvHed].[DocDiscountAmt] as [APInvHed_DocDiscountAmt],
	[APInvHed].[InvoiceAmt] as [APInvHed_InvoiceAmt],
	[APInvHed].[Posted] as [APInvHed_Posted],
	[APInvHed].[GLPosted] as [APInvHed_GLPosted],
	[Vendor].[VendorID] as [Vendor_VendorID],
	[Vendor].[Name] as [Vendor_Name],
	[DMRHead].[DMRNum] as [DMRHead_DMRNum],
	[PORel].[PONum] as [PORel_PONum],
	[DMRHead].[PartNum] as [DMRHead_PartNum],
	[PODetail].[DocUnitCost] as [PODetail_DocUnitCost],
	[PODetail].[UnitCost] as [PODetail_UnitCost],
	(APInvDtl.OurQty * PODetail.UnitCost) as [Calculated_PORelAmt],
	(PODetail.DocUnitCost * APInvDtl.OurQty) as [Calculated_PORelDocAmt],
	[DMRHead].[PartDescription] as [DMRHead_PartDescription],
	[DMRHead].[TotRejectedQty] as [DMRHead_TotRejectedQty],
	(DMRHead.AvgMtlUnitCost + DMRHead.AvgLbrUnitCost + DMRHead.AvgBurUnitCost + DMRHead.AvgSubUnitCost + DMRHead.AvgMtlBurUnitCost) as [Calculated_AvgTotalUnitCost],
	(AvgTotalUnitCost * DMRHead.TotRejectedQty) as [Calculated_AvgAmount],
	[Reason].[Description] as [Reason_Description],
	[Reason].[ReasonType] as [Reason_ReasonType],
	(-- @GenDMRToPOALL = -1 lất tất cả các DMR (đã được gen PO và chưa được gen PO)
 -- @GenDMRToPOALL = 0 lất tất cả các DMR chưa được gen PO
 --  @GenDMRToPOALL = 1 lất tất cả các DMR đã được gen PO 
   
 case  
     when @GenDMRToPOALL =-1 then -1 
     when  @GenDMRToPOALL=0 and isnull(PODetail.PONUM,0)=0 then 0
     when  @GenDMRToPOALL=1 and isnull(PODetail.PONUM,0)>0 then 1
     else -2
 end) as [Calculated_GenPOAllow]
from Erp.APInvHed as APInvHed
inner join Erp.Vendor as Vendor on 
	APInvHed.Company = Vendor.Company
	and APInvHed.VendorNum = Vendor.VendorNum
inner join Erp.APInvDtl as APInvDtl on 
	APInvDtl.Company = APInvHed.Company
	and APInvDtl.VendorNum = APInvHed.VendorNum
	and APInvDtl.InvoiceNum = APInvHed.InvoiceNum
inner join Erp.DMRHead as DMRHead on 
	DMRHead.Company = APInvDtl.Company
	and DMRHead.DMRNum = APInvDtl.DMRNum
inner join  DMRactn  as DMRactn1 on 
	DMRHead.DMRNum = DMRactn1.DMRActn_DMRNum
	and DMRHead.Company = DMRactn1.DMRActn_Company
inner join Erp.Reason as Reason on 
	Reason.ReasonCode = DMRactn1.DMRActn_ReasonCode
	and Reason.Company = DMRactn1.DMRActn_Company
	and ( Reason.ReasonType = 'D'  )

left outer join Erp.PORel as PORel on 
	PORel.Number02 = DMRactn1.DMRActn_DMRNum
	and PORel.Company = DMRactn1.DMRActn_Company
left outer join Erp.PODetail as PODetail on 
	PORel.Company = PODetail.Company
	and PORel.PONum = PODetail.PONUM
	and PORel.POLine = PODetail.POLine
where (APInvHed.Company = @CurrentCompany  and APInvHed.GroupID = 'DMRTAM'  and APInvHed.Posted = False)
 and (-- @GenDMRToPOALL = -1 lất tất cả các DMR (đã được gen PO và chưa được gen PO)
 -- @GenDMRToPOALL = 0 lất tất cả các DMR chưa được gen PO
 --  @GenDMRToPOALL = 1 lất tất cả các DMR đã được gen PO 
   
 case  
     when @GenDMRToPOALL =-1 then -1 
     when  @GenDMRToPOALL=0 and isnull(PODetail.PONUM,0)=0 then 0
     when  @GenDMRToPOALL=1 and isnull(PODetail.PONUM,0)>0 then 1
     else -2
 end) = @GenDMRToPOALL

Have a good day! :smiling_face_with_three_hearts: :smiling_face_with_three_hearts: