Hi all!
I just started to use Epicor (10.2.400.8) and I’m starting to do some customizations with custom code. I am not fluent in C# or VB (but I can code in other languages such Java).
For my first project I was trying to complete the Customer ZIP and State based on the City input. I created a UD18 table with the same 3 fields for the completion. I wanted to make sure that Epicor itself looked for the City entered as Customer in my custom table (UD18) and complete the other 2 fields for the Customer. I don’t know how to start it tho, I only know how to reach the ‘Script Editor’ so I can write the code. Is there a manual for all the functions and how to use them? I wanted to use the “Customer_AfterFieldChange” form, is that right? Sorry if my question seems kinda stupid but I want to start to script in Epicor.
Thanks all for your attention!
I would not use a UD table for that. Integrate with UPS street level validation and typically you will get city/state from zip since a city can have multiple zip codes.
Hi Leonardo,
Time to do some reading bud I would get started with the Epicor Customization Guide on Epic Web and go from there.
That will get you started with the basics, I would look as @jgiese.wci suggests into an API of sorts. But beyond that before you jump in and start writing code you should get some basic understanding of the underlying architecture and such first.
I am doing this for Italy and not the US. Here every City has only one zip code. Is using an UD table still wrong?
I’ll read the Customization Guide in the meantime as suggested by @josecgomez.
Thanks for the reply!
I would still try to use an external datasource that is maintained by someone else at a national/international level. Otherwise if there are any changes you all have to manage that manually.
I don’t think it’s a terrible idea to use a UD table. Every little project is a chance to get more familiar with Epicor customization, even if you eventually replace it with something better.
Since you only need to pull the zip code when the city is entered or changed, Customer_AfterFieldChange makes sense as the triggering event. You can use the customization wizard to add a UD adapter method to fetch data (GetRows or GetList) and, if you get a result, set the zip code field of the active Customer row.
< funFact>
A Singapore postal code is six digits. The first two represent a zone/sector and the remaining four identify a particular delivery point, i.e. a building!
< /funFact >
So yes, postal codes are quite different around the world.
Hi Ashley, thanks for the answer.
Can you please elaborate on the “add a UD adapter method to fetch data”?
I don’t fully understand
When you’re in Customization Mode, go to Tools menu, Wizards, Customization Wizards.
Then choose “Business Logic Method Call” and launch the wizard. You want the adapter for whichever UD table you’re using (if it’s UD01, choose the UD01Adapter, etc). Once you have an adapter, you’ll get a list of the methods available. Fetching data is usually a “Get” method. When you finish the wizard, it will insert code into the Script Editor as a method you can modify, then call from the Customer_AfterFieldChange event.
I would advise going through the examples in the customization guide; I’m pretty sure they have one of this wizard, as well as an example of how to set a new value in a loaded dataview. We can help if you get stuck. 
Thank you for the help Ashley.
I managed to get the ‘City’ value that is in the box but now I don’t understand how I can get the values of ‘Country’,‘ZIP’ and ‘State’ from the UD18 with the ‘City’ key (as identifier for the row) from the Customer Table and after insert them in the correct values of the same Customer Table.
Were you able to run the wizard to add a GetRows or GetList method to your code? Can you show me what you have so far? (Formatting Code block - #2 by hkeric.wci for info on formatting code here)
Yeah I was able to add the CallUD18AdapterGetRowsMethod() and the CallUD18AdapterGetDataMethod(). In the Customer_AfterFieldChange I recovered the ‘City’ entered by the user but I can’t find a way to use it.
private void Customer_AfterFieldChange(object sender, DataColumnChangeEventArgs args)
{
// ** Argument Properties and Uses **
// args.Row["FieldName"]
// args.Column, args.ProposedValue, args.Row
// Add Event Handler Code
switch (args.Column.ColumnName)
{
case "City":
MessageBox.Show("Selected City: " + args.Row["City"].ToString());
City = args.Row["City"].ToString();
break;
}
}
private void CallUD18AdapterGetRowsMethod()
{
try
{
// Declare and Initialize EpiDataView Variables
// Declare and create an instance of the Adapter.
UD18Adapter adapterUD18 = new UD18Adapter(this.oTrans);
adapterUD18.BOConnect();
// Declare and Initialize Variables
// TODO: You may need to replace the default initialization with valid values as required for the BL method call.
Ice.Lib.Searches.SearchOptions opts = null;
bool morePages = false;
// Call Adapter method
System.Data.DataSet dsUD18 = adapterUD18.GetRows(opts, out morePages);
// Cleanup Adapter Reference
adapterUD18.Dispose();
} catch (System.Exception ex)
{
ExceptionBox.Show(ex);
}
}
private void CallUD18AdapterGetDataMethod()
{
try
{
// Declare and Initialize EpiDataView Variables
// Declare and create an instance of the Adapter.
UD18Adapter adapterUD18 = new UD18Adapter(this.oTrans);
adapterUD18.BOConnect();
// Declare and Initialize Variables
// TODO: You may need to replace the default initialization with valid values as required for the BL method call.
string stringId = City;
// Call Adapter method
System.Data.DataSet dsUD18 = adapterUD18.GetData(stringId);
// Cleanup Adapter Reference
adapterUD18.Dispose();
} catch (System.Exception ex)
{
ExceptionBox.Show(ex);
}
}
I would offer a simple search wizard instead. It is a wizard that writes all the code for you except the “whereClause”.
I’m not sure how the GetData method is meant to work, to be honest. Brace yourself for a lot of experimenting when it comes to Epicor methods.
Someone else might pop in with additional info on performance, but I’d use the GetByID method, and tell the wizard you’re going to pass in the parameter. You’ll notice this method doesn’t give you a dataset at all. The data is loaded into the adapter itself. You can explore methods and properties of the adapter under Tools, Object Explorer.
So under the “City” case in your switch statement, you’re going to call the generated method and pass the city in, like CallUD18AdapterGetByIDMethod(args.Row["City"].ToString()).
Now, what the wizard screws up in the GetByID method is that UD tables have 5 keys. It won’t find any results if you only pass in one. So update the line under // Call Adapter method like this: bool result = adapterUD18.GetByID(stringId,string.Empty,string.Empty,string.Empty,string.Empty);
Add an if-statement to verify result is true so you don’t throw an index error when you access the data, which is available like this: adapterUD18.UD18Data.Tables[0].Rows[0]["Character01"].ToString()
Does Java have out parameters? That might be the simplest way to bring back three values. So here’s some rough code:
private void Customer_AfterFieldChange(object sender, DataColumnChangeEventArgs args)
{
switch (args.Column.ColumnName){
case "City":
string country;
string state;
string zip;
if (GetUD18Data(args.Row["City"].ToString(), out country, out state, out zip)){
args.Row["Country"] = country;
args.Row["State"] = state;
args.Row["Zip"] = zip;
oTrans.Update(); // optional, depending on your workflow. it will attempt to save the row with the new values
}
break;
}
}
private bool GetUD18Data(string city, out string country, out string state, out string zip)
{
UD18Adapter adapterUD18 = new UD18Adapter(this.oTrans);
adapterUD18.BOConnect();
bool result = adapterUD01.GetByID(stringId,string.Empty,string.Empty,string.Empty,string.Empty);
if(result){
country = adapterUD18.UD18Data.Tables[0].Rows[0]["ShortChar01"].ToString();
state = adapterUD18.UD18Data.Tables[0].Rows[0]["ShortChar02"].ToString();
zip = adapterUD18.UD18Data.Tables[0].Rows[0]["ShortChar03"].ToString();
}
adapterUD18.Dispose();
return result;
}
The Wizard I mentioned simplifies the code requirements:
private void SearchOnUD01AdapterShowDialog()
{
// Wizard Generated Search Method
// You will need to call this method from another method in custom code
// For example, [Form]_Load or [Button]_Click
bool recSelected;
string whereClause = string.Empty; //Add your own filter
System.Data.DataSet dsUD01Adapter = Ice.UI.FormFunctions.SearchFunctions.listLookup(this.oTrans, "UD01Adapter", out recSelected, false, whereClause); //changed this to "false" to prevent the popup to search being visible.
if (recSelected)
{
System.Data.DataRow adapterRow = dsUD01Adapter.Tables[0].Rows[0];
// Map Search Fields to Application Fields
EpiDataView edvAbcCode = ((EpiDataView)(this.oTrans.EpiDataViews["AbcCode"]));
System.Data.DataRow edvAbcCodeRow = edvAbcCode.CurrentDataRow;
if ((edvAbcCodeRow != null))
{
edvAbcCodeRow.BeginEdit();
edvAbcCodeRow["ShipToCustNum"] = adapterRow["Character01"];
edvAbcCodeRow.EndEdit();
}
}
}
Not sure how that’s simpler. He still needs the field change event handler to trigger the search. If he brings back the values he needs, he can take advantage of args.Row in that method for updating fields, rather than initializing another EpiDataView within a method that should only fetch data.
Though he could try setting country, state, and zip as objects rather than strings and skip some conversion.
The “simpler” part is that you are using a smaller method (GetList vs GetByID) and depending on the table you are accessing, the search returns less data too (potentially faster). Additionally, the code is build for you so you don’t have to know all of the functions. This also doesn’t create a new EpiDataView, but should only reference the existing one. You will need the Field Change Event either way.
I tried a UD GetList in the BL tester and it brought back all columns, so I figured it wouldn’t make much difference. However, I see that GetByID brings back an attachment table, too.
Leo, if you want to use the GetList method, your where clause would be “key1 = '” + city + “’” (or your string concatenation/interpolation of choice). Should evaluate to Key1 = 'Milan' for example. It was very slightly slower for me when I included Key2 = '' and Key3 = '' and... but probably insignificant.
Here I am again, I tried both of your codes without success tho.
When I tried @Ashley’s:
private void Customer_AfterFieldChange(object sender, DataColumnChangeEventArgs args)
{
switch (args.Column.ColumnName)
{
case "City":
MessageBox.Show("Selected City: " + args.Row["City"].ToString());
City = args.Row["City"].ToString();
string country;
string state;
string zip;
if (GetUD18Data(args.Row["City"].ToString(), out country, out state, out zip)) {
args.Row["Country"] = country;
args.Row["State"] = state;
args.Row["Zip"] = zip;
oTrans.Update();
}
break;
}
}
private bool GetUD18Data(string city, out string country, out string state, out string zip)
{
UD18Adapter adapterUD18 = new UD18Adapter(this.oTrans);
adapterUD18.BOConnect();
bool result = adapterUD18.GetByID(stringId, string.Empty, string.Empty, string.Empty, string.Empty);
if(result) {
country = adapterUD18.UD18Data.Tables[0].Rows[0]["ShortChar01"].ToString();
state = adapterUD18.UD18Data.Tables[0].Rows[0]["ShortChar02"].ToString();
zip = adapterUD18.UD18Data.Tables[0].Rows[0]["ShortChar03"].ToString();
}
adapterUD18.Dispose();
return result;
}
It didn’t work cause “The name stringId does not exist in the current context”
I tried @Jason_Woods code too:
private void Customer_AfterFieldChange(object sender, DataColumnChangeEventArgs args)
{
switch (args.Column.ColumnName)
{
case "City":
MessageBox.Show("Selected City: " + args.Row["City"].ToString());
City = args.Row["City"].ToString();
SearchOnUD01AdapterShowDialog();
break;
}
}
private void SearchOnUD18AdapterShowDialog() {
bool recSelected;
string whereClause = "'" + City + "'";
System.Data.DataSet dsUD18Adapter = Ice.UI.FormFunctions.SearchFunctions.listLookup(this.oTrans, "UD18Adapter", out recSelected, false, whereClause);
if (recSelected) {
System.Data.DataRow adapterRow = dsUD18Adapter.Tables[0].Rows[0];
EpiDataView edvAbcCode = ((EpiDataView)(this.oTrans.EpiDataViews["AbcCode"]));
System.Data.DataRow edvAbcCodeRow = edvAbcCode.CurrentDataRow;
if ((edvAbcCodeRow != null)) {
edvAbcCodeRow.BeginEdit();
edvAbcCodeRow["City"] = adapterRow["Chracter01"];
edvAbcCodeRow.EndEdit();
}
}
}
The code succesfully compiles but in the Customer section if I try to change the Customer.City it throws me 'The process caller failed to invoke method InvokeSearch in Ice.Lib.Searches.EpiSearchEngine in Ice.Lib.EpiClientLib.dll
Also I have a new doubt now (how conveninent from me).
I know that my UD18 table has 5 keys, but I added from UserCode (and UD column manteniance) my custom fields for city, state, zip, country. So when I tried to add from the DMT my values I needed to set a table like this.
| Company | Key1 | Key2 | Key3 | Key4 | Key5 | City_c | State_c | Zip_c | Country_c |
|---|
Is this wrong? Do I need to use the Key values for my custom fields?
If you are using Custom Fields (not needed, but easier to read), you must use @Ashley version.
Change “stringID” to “city” (without quotes).