EpicorFree referencePDF, Excel, Word exportEpicor Kinetic

Epicor BAQ Calculated Field Examples Reference

Most BAQ problems are small expression problems: a null that wipes out a label, a divide by zero, a date difference that counts the wrong way. This reference collects calculated field patterns you can paste into the Query Builder and adapt.

Download:

Group	Calculated field	Expression	What it does	Watch out for
Dates	DaysLate	datediff(day, JobHead.DueDate, getdate())	Days between the job due date and today	Positive means past due. Closed jobs still return a value, filter on JobClosed.
Dates	OrderAgeDays	datediff(day, OrderHed.OrderDate, getdate())	Age of an order in days	getdate() is server time, not the user's time zone.
Dates	OrderMonth	month(OrderHed.OrderDate)	Month number for grouping	Pair with year(), otherwise two years collapse into one month.
Dates	OrderYear	year(OrderHed.OrderDate)	Year number for grouping	Cast to varchar when concatenating with text.
Dates	OrderDateText	convert(varchar(10), OrderHed.OrderDate, 101)	mm/dd/yyyy text for exports	Text sorts alphabetically, keep the real date column for sorting.
Dates	WeekStart	dateadd(day, 1 - datepart(weekday, OrderHed.OrderDate), OrderHed.OrderDate)	Date of the first day of the week	Depends on the SQL Server DATEFIRST setting.
Quantities	QtyRemaining	JobHead.ProdQty - JobHead.QtyCompleted	Open quantity on a job	Can go negative on overruns. Wrap in case when if not wanted.
Quantities	PctComplete	case when JobHead.ProdQty = 0 then 0 else (JobHead.QtyCompleted / JobHead.ProdQty) * 100 end	Percent complete with divide by zero guard	Decimal types matter. Multiply by 100.0 if you see integer results.
Quantities	IsShort	case when OrderDtl.OrderQty > 0 and JobHead.QtyCompleted < OrderDtl.OrderQty then 1 else 0 end	Flag lines where output is below ordered quantity	Only valid if the tables are joined on the correct job to line link.
Pricing	ExtPrice	OrderDtl.OrderQty * OrderDtl.DocUnitPrice	Extended line amount in document currency	Ignores discounts and price per code. Compare with DocExtPriceDtl.
Pricing	MarginPct	case when OrderDtl.DocExtPriceDtl = 0 then 0 else ((OrderDtl.DocExtPriceDtl - (OrderDtl.OrderQty * Part.UnitPrice)) / OrderDtl.DocExtPriceDtl) * 100 end	Rough margin versus a reference price	Part.UnitPrice is a list price, not cost. Use a cost table for real margin.
Pricing	LineBand	case when OrderDtl.DocExtPriceDtl >= 10000 then 'A' when OrderDtl.DocExtPriceDtl >= 1000 then 'B' else 'C' end	Bucket lines into value bands	Order of WHEN branches matters, the first match wins.
Text	PartAndDesc	Part.PartNum + ' - ' + Part.PartDescription	Combined label for dashboards	A null on either side makes the whole result null. Use coalesce.
Text	SafeDesc	coalesce(Part.PartDescription, '')	Replace null with an empty string	Use isnull only if you accept the first argument's data type.
Text	PartPrefix	left(Part.PartNum, 3)	First three characters of a part number	Part numbers with leading spaces break this. Add ltrim().
Text	TrimmedPart	ltrim(rtrim(Part.PartNum))	Remove stray spaces from keys	Fix the data instead if joins fail because of trailing spaces.
Logic	StatusText	case when OrderHed.VoidOrder = 1 then 'Void' when OrderHed.OpenOrder = 1 then 'Open' else 'Closed' end	Readable order status	Check the flag semantics in your release with the Data Dictionary Viewer.
Logic	HeldFlag	case when OrderHed.OrderHeld = 1 then 'HELD' else '' end	Show a marker for held orders	Booleans are bit fields, compare with 1, not the word true.
Logic	IsPastDue	case when JobHead.DueDate < getdate() and JobHead.JobClosed = 0 then 1 else 0 end	Flag open jobs past their due date	Use cast(getdate() as date) to ignore the time portion.
Aggregates	TotalQty	sum(OrderDtl.OrderQty)	Aggregate used in a Group By query	Every non-aggregate column must be in the Group By list.
Aggregates	LineCount	count(OrderDtl.OrderLine)	Number of lines per order	Use count(distinct ...) if joins multiply rows.
Parameters	FilterByCustomer	OrderHed.CustNum = @CustNum	Criteria pattern using a BAQ parameter	Parameters must be defined on the Phrase Build or Criteria tab with a matching data type.

Expressions use T-SQL functions, which the BAQ calculated field editor passes to SQL Server. Table and column names shown follow common Epicor naming (OrderHed, OrderDtl, JobHead, Part). Confirm every column in your release with the Data Dictionary Viewer before relying on it.

Want your BAQs documented automatically?

Optional: we can walk through how a read-only assistant could explain and test your BAQs against your own Epicor data.

Optional. The tool and every export work without this. No spam.

How to use this reference

Search for the situation you are in, such as date, case or coalesce, then copy the expression into Query Builder on the Calculated Fields tab. Replace the table and column names with the ones in your own query, because a calculated field can only reference tables already added to the query. Set the data type of the calculated field to match the result: decimal for amounts, int for flags, nvarchar for text, datetime for dates. Test with a small criteria filter first, then remove it once the output looks right.

Add the table to the query before referencing it in an expression
Match the field data type to the expression result
Test on a handful of rows before running a full extract
Name fields in PascalCase so they read well in dashboards

Why calculated fields behave the way they do

A BAQ calculated field is evaluated by SQL Server as part of the generated query, so the rules of T-SQL apply: integer division truncates, any arithmetic with a null returns null, string concatenation with a null returns null, and CASE branches are checked in order. Most surprises come from one of those four rules. When a result looks wrong, test the expression in a SQL client against the same tables, or simplify it in the editor until the faulty part is isolated.

Reading the examples

Each row lists the expression, the purpose and the main thing to watch for. The pricing examples use document currency fields, so they are correct for single currency sites and need review when the base currency differs. The margin example compares against a list price only to show the pattern. For real margin use a cost source such as part cost or job costs. Treat every row as a starting point, since field availability changes between releases.

Common mistakes

The most frequent errors are using a calculated field inside a join condition, building very large nested CASE blocks that nobody can read, and forgetting that a Group By query needs aggregates over columns, not over other calculated fields without care. Another trap is formatting dates as text early and then sorting on the text. Keep raw values in the query and format at the dashboard or report layer.

Do not nest more than three CASE levels, split into two fields instead
Guard every division with a zero check
Wrap nullable text in coalesce before concatenating
Keep date columns as dates and format in the report

Frequently Asked Questions

Where do I add a calculated field in an Epicor BAQ?

Open the BAQ in Query Builder, go to the Display Fields or Calculated Fields area, add a new calculated field, name it, choose its data type and enter the expression. Then select it on the Display Fields tab so it appears in the output of the query.

Why does my BAQ calculated field return null?

In SQL Server any arithmetic or string concatenation involving a null returns null. Wrap nullable columns in coalesce with a safe default such as zero or an empty string, and check that the joined table actually has a matching row for each result.

Can I use SQL functions in a BAQ expression?

Yes, T-SQL functions like datediff, dateadd, coalesce, convert, left and case expressions are commonly used. Functions that are not allowed by the BAQ designer or by your security settings will raise an error when you test the query.

Should I use a BAQ calculated field or a function library?

A calculated field is fine for short expressions that stay in one query. If the same logic is needed in several BAQs, reports and customizations, a function in a library is easier to test and maintain, though it costs more setup.

If your team maintains dozens of BAQs and wants help documenting or testing them against live ERP data, Netray can talk through an approach that stays read-only and runs on your own infrastructure.

Talk to an Expert Explore More Free Tools

Related Tools

Epicor

More Epicor tools

See all

Back to All Tools