We are wanting to put industries and subindustries tied to customers in the Customer Maintenance screen. We have 8 industries, and each of those have at least 2, up to 4, sub industries. For example, if a customer is under the MINING industry, we only want to be able to populate the choices COAL, METALS, NON METALLIC MINERALS, and QUARRIES for the sub industry. If they are in the AGRICULTURE industry, we only want AGRICULTURE and FORESTRY to populate in the sub industry box. Does anyone know how I would go about tying the two combo boxes together so you can only choose the specified sub industry, given the top level industry?
Check out this thread:
Updatable BAQ Drop Down using another BAQ - ERP 10 - Epicor User Help Forum (epiusers.help)
1 Like
I am not sure that this entirely is the solution I am looking for. I don’t need to use a BAQ. I just need fields to populate. Does that make sense? I may be trying to explain what I want wrong.
I see what you mean. The values you want to supply to the combo boxes are not in a table, so you dont have a way to look them up in a BAQ. Since the values aren’t in a table anywhere, there is also no way to associate the sub industries with the parent industries.
In my example, I populate the BAQ combo boxes, by pulling values from a BAQ. In this way I can setup the BAQ and make sure it is returning the correct values before I apply the BAQ to my combo box.
If your request was just a little bit simpler, I would say you don’t need a BAQ.* But the only way I know how to do this is with a BAQ. You also need the data to be in a table in your database so that you can make the BAQ. I would add it to a UD table. With the format: Key1 = Industry Key2 = Sub-industry.
Once you have a UD table loaded with your data (not exactly a straight-forward process), then you can setup the BAQs to populate your combo boxes with the filtered values. Create a BAQ to return a unique list of the industries from your UD table. Only return one column, the Industry name. That will be what populates your first combo box. Lets call that BAQ getInd. Setup a second BAQ that will return just the sub industries that are related to the chosen industry. Use a parameter to filter the industries to only return the subs you want. Call the parameter “industry”. Call that BAQ getSubInd.
OK Epinoob… now we are getting in deep. 
Next, we need to customize your dashboard to add some combo boxes, and some custom code. The first combo box should be just a BAQCombo, and point it to the getInd BAQ you created. That was easy. Next, add an EpiUltraComboBox. We need to setup some custom code to populate this combo box. I try to always start with the event wizard. Add a new OnLeave event to the BAQCombo. Add code to be something like this: My example uses partnum instead…
private void MyPartNum_Leave(object sender, System.EventArgs args)
{
// ** Place Event Handling Code Here **
if (MyPartNum.Text!="")
{
getRevs(); //this executes the code to populate the next combo box
cmbRevs.ForceRefreshList(); // this updates that combo box after it has been populated
}
}
In your case you would create a new function called getSubInds() instead of getRevs(). And where I point to cmbRevs, is where you refer to your sub industry combo box.
Here is the code for getRevs. This has to be modified to fit your needs and your BAQ values and parameters.
private void getRevs()
{
cmbRevs = (Ice.Lib.Framework.EpiUltraCombo)csm.GetNativeControlReference("a03dd350-74a0-4382-b1cb-251c5e41ed9a");
DynamicQueryAdapter dqa = new DynamicQueryAdapter(oTrans);
dqa.BOConnect();
QueryExecutionDataSet qeds = dqa.GetQueryExecutionParametersByID("getPartRev");
qeds.ExecutionParameter.Clear();
qeds.ExecutionParameter.AddExecutionParameterRow("part", MyPartNum.Text, "nvarchar", false, Guid.NewGuid(), "A");
dqa.ExecuteByID("getPartRev", qeds);
if (dqa.QueryResults.Tables["Results"].Rows.Count > 0)
{
cmbRevs.DataSource = dqa.QueryResults.Tables["Results"];
cmbRevs.DisplayMember = "PartRev_RevShortDesc";
cmbRevs.ValueMember = "PartRev_RevShortDesc";
cmbRevs.DropDownStyle = Infragistics.Win.UltraWinGrid.UltraComboStyle.DropDownList;
oTrans.NotifyAll();
}
else
{
cmbRevs.DataSource = "";
cmbRevs.DisplayMember = "";
cmbRevs.ValueMember = "";
oTrans.NotifyAll();
MessageBox.Show("Part Not Found in Any Open Jobs!");
}
}
You will have to replace my parameter “part” with your parameter “industry”, and replace my BAQ name “getPartRev” with your BAQ name “getSubInd”. Other replacements as needed.
*In a simpler case, you can create a EpiCombo box, then populate the EpiStaticDataList. Note the Description indicating that the first line needs to define the columns. This is how you setup a static combo box that has a static list of values. However this can’t be linked to another combo box (to my knowledge).
Phew! Thats a lot. Catch up and let us know how it goes. Happy to hear an easier approach if there is one! Good luck!
1 Like
Look at the property description. I can see it on the bottom of your screenshot “Key~Description”. In your case, you may not need more than one column in your combo box, so you can probably skip it. Try it out, and let us know!
I am not sure what this means. When I tie that combo box to Customer.Industry, it still doesn’t have the data in dropdown. It wont even open. Back to youtube for more instructions!
What do you imagine the process looking like? Will there be two combo boxes, one for Industry, and one for Sub-Industry? You will have to work with the existing control that is already on the form for Industry, but you could hide it and override it with a combo box.
Just ignore that Key Description part. I don’t think it applies here. Show us your combo boxes and properties (screenshots), along with your custom code (paste code text). You may be able to link up Customer.Industry, but there is no field for sub-industry. That will have to be added to the Customer table with UD column maintenance, and a support ticket.
The process I have in mind is that our customer service reps go to the customer, existing or new, and choose what Industry and Sub Industry with separate combo boxes. I know there is a Customer.Industry, and I was just going to use a random field that isn’t currently being used in the customer table for Sub Industry like Customer.Rating or something like that. I thought I could use SQL and do an
INSERT INTO ERP.Customer (Industry)
VALUES ('CONSTRUCTION', 'INDUSTRIAL', 'AGRICULTURE', 'ENERGY PRODUCTION', 'TRANSPORTATION', 'MISCELLANEOUS', 'INTERCOMPANY')
That worked with MINING, but doesn’t seem to work with anything else.
Here is a screenshot of my Sales Person Combo Box. Is this what you wanted to see?
Then here is the code behind that box in the script editor tab.
'//**************************************************
'// Custom VB.NET code for CustomerEntryForm
'// Created: 1/30/2009 9:00:40 AM
'//**************************************************
Imports System
Imports System.Data
Imports System.Diagnostics
Imports System.Windows.Forms
Imports System.ComponentModel
Imports Microsoft.VisualBasic
Imports Erp.UI
Imports Ice.Lib.Framework
Imports Ice.Lib.ExtendedProps
Imports Ice.UI.FormFunctions
Imports Ice.Lib.Customization
Imports Ice.Adapters
Imports Erp.Adapters
Imports Ice.Lib.Searches
Imports Ice.Lib
Imports Erp.BO
Imports Ice.BO
Imports Erp.Proxy.BO
Module 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 **
Sub InitializeCustomCode()
'// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Intialization' lines **
'// Begin Wizard Added Variable Intialization
'// End Wizard Added Variable Intialization
'// Begin Custom Method Calls
'// End Custom Method Calls
End Sub
Sub DestroyCustomCode()
'// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
'// Begin Wizard Added Object Disposal
'// End Wizard Added Object Disposal
'// Begin Custom Code Disposal
'// End Custom Code Disposal
End Sub
End Module
Yikes! I never do SQL code like that in Epicor. I think you might want a different approach. Someone back me up here… 
So, your best course of action here is to get the values in to the database somehow. Hard-coding this stuff into the customization isn’t the best long term fix, for a variety of reasons.
You have a couple of options to do that:
-
user user codes. You can make each parent category a code type ID and you can put the sub categories into those the codes. Then you can use a BAQ combo for the parent categories, and the UD code BO for the sub categories. You’ll just adjust the filter in code with a change of the parent category. The benefit of this is after you code it, you don’t need to make any changes the the customization to adjust the categories or sub categories. Maybe a tweak on the baq to feed in the parents, but that’s it. And, you don’t need to add any UD fields other than the ones on customer maintenance.
-
UD tables, Basically the same thing there, but you can be a little more structured with the heirarchy. If you use the UD.1XX and UD.1XXA tables, you already have the parent child built in. You should add UD fields onto those tables so you can make intelligent field names, but technically you could get by with using the generic ones that are provided (but yuck, you really shouldn’t). Then you can use BAQ combos. Can can also customize a UD entry screen to enter all of your codes. Again this means once you are done making it, you don’t have to go and change any code to adjust the codes you use.
Both of these options will upgrade better, and be better in the long run. Either one, you will need to adjust the filters on the sub category in code with a change or before drop down event, but you’ll need that no matter what.
1 Like
Yeah… do NOT do that… The BO will handle all of that for you, you don’t need to do that, and you shouldn’t.
1 Like
An Update:
We went with @Banderson and his suggestions of the user codes. We have the first Industry box working, but subindustry is not currently working.
For this to work, you’re going to have to use an event on the control, before drop down, so that before the dropdown does a lookup, you’ll need to change the filter on the drop down (with code).
use the wizard to add the event.
Then if you look at the object explorer you can see the search filter property.
To start with, I would either run debug mode in Visual studio if you know how, or else just pop message boxes to see what the property looks like with hard coded values. Then you should be able to replace that with the value from the parent control.
This will allow you to dynamically change this value.
I was finally able to get the Object Explorer, but not sure what you mean message boxes to see what the property looks like. Would a screen shot help you from my end?
This is how you can pop a message box. Put that in your event, then you can put the current filter value in there.
MessageBox.Show(yourControlName.SearchFilter.ToString());
Or something like that. So manually set the filter to work with one of the parents, then do this to see what the property looks like, then match the format to change it.