How to add a blank option to an EpiCombo?

Epicor ERP 10
1

I have a bunch of EpiCombos that get their values from BAQs that query Ice.UDCodes. (Values and descriptions are both from CodeDesc.) The fields that the EpiCombos are bound to are initially blank, and users would like to be able to set them back to blank. How can I get a blank option to appear in the EpiCombo? I can’t create a blank choice in UDCodes because CodeDesc cannot be empty. I also tried to modify the BAQ to return an additional blank row, but couldn’t figure out how to do that. (It would be so simple in SQL…) I’m looking for a simpler solution than changing all the BAQs to use Ice.UD01.

2

So here is a Trick I have done… it works!

  1. create your BAQ:
  • Query 1: Top Level - it only shows the Inner Sub Query of Query 2. Sort this by the value you want to have the blank value in.
  • Query 2: This is a UNION ALL query, it does NOT have ANY tables in it… just one (or more) calculated fields that are return blank data.
  • Query 3: This is an Inner-Sub query, and it actually queries the data you want to return. Remember that the number and type of columns you return here must match Query 2.
    image
    In the example below, I queried the ABC Code Table… you will note that it has one blank value at the top.

Here is the resulting SQL statement:image

5 Likes
3

I’m having trouble setting up Subquery 2. When I try to create a calculated field whose expression is a constant, I get an error message saying, “Must specify table to select from.”

4

Aha… the solution to the error I was getting is to ignore it. Or don’t click the “check syntax” button.

I made this work with only two subqueries. The top level queries the data from Ice.UDCodes and defines the sort order, and the subquery is a union all with the blank fields. The SQL is rendered as

select 
	[UDCodes].[CodeID] as [UDCodes_CodeID],
	[UDCodes].[CodeDesc] as [UDCodes_CodeDesc]
from Ice.UDCodes as UDCodes
where (UDCodes.CodeTypeID = 'ATRISK')
union all
select 
	('') as [Calculated_CodeID],
	('') as [Calculated_CodeDesc]
order by UDCodes.CodeDesc
1 Like
5

Perfect…
Yes, the validate feature is over-validating… probably needs an enhancement request into Epicor.
I have used this feature to create “constants” in my query that can be changed… example, I create a sub-query that has calculated (or entered) constants… then use this sub-query on upper queries to filter things with. seems to work well.

6

Tim,
This is super useful for those of us creating tables for CPQ using a BAQ. CPQ always selects the first item in the table as the default. It does not allow you to start with a null value and force the user to select something. You may wish to post this on the odd CPQ forum.

7

@timshuwy I just found out that this does not work for CPQ.

I just found out that there is a bug, where CPQ cannot sync a BAQ with a union because it sees the matching union columns as duplicates. Epicor support tells me there is a workaround to just rename my columns. That does not work for a union.