Epicor syntax samples

_Epicor Syntax Samples.md

In this gist, we explore some common syntax for Epicor 10

Access a UD field from a temptable from within code.cs

var myShipVia = (from ShipVia_row in ttShipHead
                  where ShipVia_row.ShipViaCode=="12345"
                 select ShipVia_row).FirstOrDefault();
                 
if (myShipVia!=null)
{
  ShipViaDisabled = myShipVia.UDField<bool>("Disabled_c"); 
}

/*
You can access UD columns by using the .UDField<T>({Name}) method, where T is the data type for the column, and {Name} is a string matching the full column name (needs to include the "_c" suffix).

Use with caution...this can easily throw a runtime error if you misspell the field name or reference a non-existent field.
*/

BAQ with Parameters Execute by Code.cs

//ORIGINAL: https://gist.github.com/hasokeric/f931950997107e87e33c1416c3b656c0
DynamicQueryAdapter queryAdapter = new DynamicQueryAdapter(oTrans);
queryAdapter.BOConnect();

QueryExecutionDataSet queryExecutionData = queryAdapter.GetQueryExecutionParametersByID("CC-PartBin-AvailableInventory");
queryExecutionData.ExecutionParameter.Clear();

queryExecutionData.ExecutionParameter.AddExecutionParameterRow("@IncludeExternalData", "0", "bit", false, Guid.NewGuid(), "A");

queryAdapter.ExecuteByID("CC-PartBin-AvailableInventory", queryExecutionData);
AvailableInventoryGrid.DataSource = queryAdapter.QueryResults.Tables["Results"];
		
//CUSTOM: From PartTracker Customization: CC_AvailableInventory_001
	// Get filter values
	EpiCheckBox IncludeAlloTrak = (EpiCheckBox)csm.GetNativeControlReference("1ef85aa9-7184-421d-842a-0a273646ab69");
	EpiCheckBox IncludeAdditionalPartInformation = (EpiCheckBox)csm.GetNativeControlReference("1c9f31ea-b309-4236-ada3-95e56440852e");
	EpiCheckBox IncludeAttributes = (EpiCheckBox)csm.GetNativeControlReference("d99598ca-f06f-4e26-8e94-c9c550b2181d");
	EpiTextBox PartNumberFilter = (EpiTextBox)csm.GetNativeControlReference("326e9921-92c2-4343-b6fd-8be7426c70c5");
	EpiTextBox WarehouseFilter = (EpiTextBox)csm.GetNativeControlReference("5da144b4-5413-40ff-b13c-b32292ec6d38");

	// Clear AvailableInventoryGrid
	DataTable dtTest = new DataTable();
	dtTest.Rows.Clear();
	AvailableInventoryGrid.DataSource = dtTest;

	try
	{
		// Initialize Query Adapter
		DynamicQueryAdapter queryAdapter = new DynamicQueryAdapter(oTrans);
		queryAdapter.BOConnect();

		// Get list of parameters
		QueryExecutionDataSet queryExecutionData = queryAdapter.GetQueryExecutionParametersByID("CC-PartBin-AvailableInventory");

		// Set parameters
		queryExecutionData.Clear();
		queryExecutionData.ExecutionParameter.AddExecutionParameterRow("IncludeExternalData",IncludeExternalData.Checked.ToString().ToLower(),"bit", false,Guid.NewGuid(),"A");
		queryExecutionData.ExecutionParameter.AddExecutionParameterRow("IncludeAdditionalPartInformation",IncludeAdditionalPartInformation.Checked.ToString().ToLower(),"bit", false,Guid.NewGuid(),"A");			
		queryExecutionData.ExecutionParameter.AddExecutionParameterRow("IncludeAttributes",IncludeAttributes.Checked.ToString().ToLower(),"bit", false,Guid.NewGuid(),"A");
		if (PartNumberFilter!=null && !String.IsNullOrEmpty(PartNumberFilter.Text))
		{
			queryExecutionData.ExecutionParameter.AddExecutionParameterRow("PartNumberFilter",PartNumberFilter.Text.ToString(),"bit", false,Guid.NewGuid(),"A");
		}
		if (WarehouseFilter!=null && !String.IsNullOrEmpty(WarehouseFilter.Text))
		{
			queryExecutionData.ExecutionParameter.AddExecutionParameterRow("WarehouseFilter",WarehouseFilter.Text.ToString(),"bit", false,Guid.NewGuid(),"A");
		}

		queryAdapter.ExecuteByID("CC-PartBin-AvailableInventory", queryExecutionData);
		AvailableInventoryGrid.DataSource = queryAdapter.QueryResults.Tables["Results"];
		AvailableInventoryGrid.Refresh();
	}
	catch (Exception ex)
	{
		ExceptionBox.Show(ex);
	}

Bind epiUltraCombo to UDCodes table.cs

EpiUltraCombo cboDeveloper = (EpiUltraCombo)csm.GetNativeControlReference("c0b023c6-58ee-49c3-b383-13a56f631b47");
		
UserCodesAdapter users = new UserCodesAdapter(this.oTrans);
users.BOConnect();
users.GetByID("SYSProcess");

DataTable udc = users.UserCodesData.Tables["UDCodes"].AsEnumerable().OrderBy(c=>c["CodeDesc"]).CopyToDataTable();
udc.Columns["CodeID"].ColumnName = "ValueMember";
udc.Columns["CodeDesc"].ColumnName = "DisplayMember";

cboDeveloper.DataSource = udc;	

//Alternate Method for ANY Adapter
DataSet dsUser = Ice.UI.FormFunctions.SearchFunctions
						.listLookup(oTrans, "UserFileAdapter", out x, false, "");

BPM Data Form Accessing BPM Data.cs

/*
Covers syntax requirements for using a BPM Data Form to access BPM Call Data from the calling method
*/
using Erp.Adapters;

// Declare and Initialize EpiDataView Variables
EpiDataView edvBPMData = ((EpiDataView)(this.oTrans.EpiDataViews["BPMData"]));
EpiDataView edvClientContextData = ((EpiDataView)(this.oTrans.EpiDataViews["CallContextClientData"]));

// Check if valid EpiDataView Row(s) are selected - cancel data form if not
if ((edvBPMData.Row < 0) || edvClientContextData.Row < 0)
{
	return;
}

//Simplify variables
var BPMData = edvBPMData.dataView[edvBPMData.Row];
var CallContextClientData = edvClientContextData.dataView[edvClientContextData.Row];

// Find BPMData and ClientContextData
string PartNum = ((string)(BPMData["ShortChar01"]));
string LotNum = ((string)(BPMData["ShortChar02"]));
string Company = ((string)(CallContextClientData["CurrentCompany"]));


// Declare and create an instance of the Adapter.
LotSelectUpdateAdapter adapterLotSelectUpdate = new LotSelectUpdateAdapter(this.oTrans);
adapterLotSelectUpdate.BOConnect();
SearchOptions opts = new SearchOptions(SearchMode.AutoSearch);
string sopts = "PartNum='" + PartNum + "' AND Company='" + Company + "' AND LotNum='" + LotNum + "'";
opts.PreLoadSearchFilter = sopts;
opts.DataSetMode=DataSetMode.RowsDataSet;
var x = true;
DataSet ds = adapterLotSelectUpdate.GetList(opts,out x); // For some reason, this appreas to be returning all results instead of filtered results


// To re-filter results, I am creating a new DataSet and copying a filtered version of the original DataSet into it
DataTableCollection collection = ds.Tables;

var dv = ds.Tables[0].DefaultView;
dv.RowFilter = sopts;
var newDS = new DataSet();
var newDT = dv.ToTable();
newDS.Tables.Add(newDT);

// Use the discovered values (In this case, write out a summary of the transaction on the BPM Data Form)
var msg = "";
msg += "Company = '" + newDS.Tables[0].Rows[0]["Company"].ToString() + "'\r\n";
msg += "Part = '" + newDS.Tables[0].Rows[0]["PartNum"].ToString() + "'\r\n";
msg += "Lot = '" + newDS.Tables[0].Rows[0]["LotNum"].ToString() + "'\r\n";
msg += "Expires = '" + newDS.Tables[0].Rows[0]["ExpirationDate"].ToString() + "'\r\n";


EpiLabel   Message = (EpiLabel)csm.GetNativeControlReference("6efb1b7a-e3c0-4263-ba3c-41f62e1d2f5f");

Message.Appearance.TextHAlign = Infragistics.Win.HAlign.Left; // To Left Align the text (Labels are right-aligned by default)
Message.Text = msg;

BPM.WorkingWithData.cs

using Erp.Adapters.Part
using Erp.Contracts.BO.Part

//Reference the table and assembly
Erp.Tables.Part Part; 

try
{
  //Select a single value for part based on criteria defined elsewhere
	Part = (from Part_row in Db.Part
					where Part_row.Company == Session.CompanyID 
					&& Part_row.PartNum == PartNum
					select Part_row).FirstOrDefault();
					
	//Grab UD Fields for this record
	bool PartBatch = Part.UDField<bool>("BatchLotQty_c");

  //Set UD Fields for this record
  Part.SetUDField<bool>("BatchLotQty_c", true); 
  
  //Work with the record and the UD fields
	Message += "You have selected Part " + PartNum + ": " + Part.PartDescription 
	            + ". (" + PartBatch.ToString() + ")";
}
catch (Exception ex)
{
	Message += "There was a problem processing your request:\r\n" + ex.ToString();
}

Convert SysDate and SysTime to DateTime.sql

/*This converts SysDate and SysTime to a DateTme format*/
SELECT (DATEADD(s, SysTime, CAST(SysDate AS DateTime))) AS Timestamp, * FROM Erp.PartTran

Customization.GetSetControl.cs

EpiTextBox txtbox = (EpiTextBox)csm.GetNativeControlReference("YOUR GUID HERE");

Erp.UI.App.VendorEntry.MainDock mainDock1 = (Erp.UI.App.VendorEntry.MainDock)csm.GetNativeControlReference("YOUR GUID HERE");

EpiUltraGrid grid = (EpiUltraGrid)csm.GetNativeControlReference("YOUR GUID HERE"); 
//reference it
grid.Rows[0].Cells[1].Value = txtbox.Text; //Row 0, Column 1 as an example OR
grid.Rows[0].Cells["column name"].Value = txtbox.Text;

Epicor SQL Samples.sql

-- Convert SysDate and SysTime in Epicor (PartTran) to "real" Date and Time Values
SELECT
convert(varchar, PartTran.SysDate) 
+ ' '
+ (SELECT RIGHT('00' + convert(varchar, PartTran.SysTime / 3600 ), 2)) 
+ ':' 
+ (SELECT RIGHT('00' + convert(varchar, ( PartTran.SysTime % 3600 ) / 60),2)) 
+ ':' 
+ (SELECT RIGHT ('00' + convert(varchar, (( PartTran.SysTime % 3600 )% 60)),2))
AS STime
FROM Erp.PartTran

FormControl.Actions.txt

AfterExitEditMode
ValueChanged

Generic.GetSessionData.cs

Ice.Core.Session session = (Ice.Core.Session)LotNumberForm.Session;
string company = session.CompanyID;
string user = session.UserID;
MessageBox.Show(company + "/" + user");

Get previous value of a changed record.cs

foreach (var currentRow in ttTFOrdHed)
{
	if (String.IsNullOrEmpty(currentRow.RowMod)) // This is the original, or unchanged row
	{
		var oldvalue = currentRow.ShipComment;
	}
}

InformationMessages.cs

// Popup window with custom message
/*
This piece of code will queue up an informational popup message to display to the user. The message is displayed asynchronously, so don't use this to stop or delay processing code until the user responds.
*/
//this.PublishInfoMessage({Message}, {MessageTypeEnum}, {MessageDisplayModeEnum}, {MessageTitle}, {MessageName})
this.PublishInfoMessage("Exception: " + ex.Message, Ice.Common.BusinessObjectMessageType.Warning, Ice.Bpm.InfoMessageDisplayMode.Individual, "Stage Ship Confirm", "Shipping");

// Status Bar message
/*
This piece of code will update the status bar text on the current window
*/
oTrans.PushStatusText("Refreshing availability...", false);

// Write to log file
// Append line to file:
System.IO.File.AppendAllText(@"{SERVER-PATH}\{FileName}", "{Custom Message}" + Environment.NewLine);

Loop through UltraGrid and ignore GroupBy rows.cs

//http://help.infragistics.com/Help/Doc/WinForms/2013.1/CLR4.0/html/WinGrid_Loop_Through_the_Entire_Grid.html

using Infragistics.Win.UltraWinGrid;

/*
Option 1:

*/
UltraGridBand band = this.ultraGrid1.DisplayLayout.Bands[1];

foreach (UltraGridRow row in band.GetRowEnumerator(GridRowType.DataRow))
{
	// Write code to process the row
}

/*
Option 2:
 This will loop through all the rows of the grid, including rows from child bands.
*/
foreach ( UltraGridRow row in this.ultraGrid1.Rows.GetRowEnumerator( GridRowType.DataRow, null, null ) )
{
	// Write code to process the row
}

/*
Option 3:
  This will loop through all vilible rows in a filtered grid, excluding groupby rows
*/
foreach (UltraGridRow row in this.ultraGrid1.Rows.GetFilteredInNonGroupByRows())
{
  // Write code to process the row
}
}

Open Search Form on Button Press.cs

bool recSelected;
string whereClause = string.Empty;
System.Data.DataSet dsPartAdapter = Ice.UI.FormFunctions.SearchFunctions.listLookup(this.oTrans, "PartAdapter", out recSelected, true, whereClause);
if (recSelected)
{
	System.Data.DataRow adapterRow = dsPartAdapter.Tables[0].Rows[0];

	// Map Search Fields to Application Fields
	EpiDataView edvCallContextBpmData = ((EpiDataView)(this.oTrans.EpiDataViews["CallContextBpmData"]));
	System.Data.DataRow edvCallContextBpmDataRow = edvCallContextBpmData.CurrentDataRow;
	if ((edvCallContextBpmDataRow != null))
	{
		edvCallContextBpmDataRow.BeginEdit();
		edvCallContextBpmDataRow["Character01"] = adapterRow["PartNum"];
		edvCallContextBpmDataRow.EndEdit();
	}
}

OpenCustomSearchFromFormButton.cs

// Use the "Simple Search" wizard to build the code sshell, then tweak as needed.

//Example: from LotTracker 01_LotTrackerWithAttributes)
private void SearchOnLotSelectUpdateAdapterShowDialog(string whereClause)
	{
		// 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 = "LotNum Like '" + txtSearchLot.Text + "%'";
		System.Data.DataSet dsLotSelectUpdateAdapter = Ice.UI.FormFunctions.SearchFunctions.listLookup(this.oTrans, "LotSelectUpdateAdapter", out recSelected, true, whereClause);
		
		if (recSelected)
		{
			System.Data.DataRow adapterRow = dsLotSelectUpdateAdapter.Tables[0].Rows[0];
			PopulatePartAndLot(adapterRow["PartNum"].ToString(), adapterRow["LotNum"].ToString());
		}
	}

Show Custom Dialog in Epicor Customization.cs

// https://e10help.com/t/bpm-code-to-display-message-on-the-mes/38360/5

private static DialogResult ShowInputDialog(ref string input)
    {
        System.Drawing.Size size = new System.Drawing.Size(350, 70);
        Form inputBox = new Form();

        inputBox.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedDialog;
        inputBox.ClientSize = size;
        inputBox.Text = "Comments";

        System.Windows.Forms.TextBox textBox = new TextBox();
        textBox.Size = new System.Drawing.Size(size.Width - 10, 23);
        textBox.Location = new System.Drawing.Point(5, 5);
        textBox.Text = input;
        inputBox.Controls.Add(textBox);

        Button okButton = new Button();
        okButton.DialogResult = System.Windows.Forms.DialogResult.OK;
        okButton.Name = "okButton";
        okButton.Size = new System.Drawing.Size(75, 23);
        okButton.Text = "&OK";
        okButton.Location = new System.Drawing.Point(size.Width - 80 - 80, 39);
        inputBox.Controls.Add(okButton);

        Button cancelButton = new Button();
        cancelButton.DialogResult = System.Windows.Forms.DialogResult.Cancel;
        cancelButton.Name = "cancelButton";
        cancelButton.Size = new System.Drawing.Size(75, 23);
        cancelButton.Text = "&Cancel";
        cancelButton.Location = new System.Drawing.Point(size.Width - 80, 39);
        inputBox.Controls.Add(cancelButton);

        inputBox.AcceptButton = okButton;
        inputBox.CancelButton = cancelButton; 

        DialogResult result = inputBox.ShowDialog();
        input = textBox.Text;
        return result;
    }

WebLinks.md

Video Training:

• Embed a BAQ with parameters into a customized form: Calling BAQ from Code Epicor ERP 10 (YouTube)

in your BPM Data Form Accessing BPM Data.cs script id you run adapterLotSelectUpdate.InvokeSearch(opts); it will run the search on the adaptor, then GetList() will return the results with the search criteria.

@lockworld - Great code samples. I appreciate it. Is there a way to do this... EpiCheckBox IncludeAlloTrak = (EpiCheckBox)csm.GetNativeControlReference("1ef85aa9-7184-421d-842a-0a273646ab69"); ...but to use the control "NAME" rather than its EpiGuid? I have several environments (Dev1, Dev2, Stage1, Stage2, Prod, Etc) and the EpiGuid can change between them so it is hard to make methods that can be moved from system to system, etc. Do you know a way to GetNativeControlReference by Name rather than EpiGuid? Please advise. Thanks. -- Mark Kamoski

@mkamoski personally I would export/import the customizations between you enviroments. We have 4 enviroments and do just that.

This ensures all control refs, variables code/script styling always match.

@mkamoski personally I would export/import the customizations between you enviroments. We have 4 enviroments and do just that.

This ensures all control refs, variables code/script styling always match.

@alphanu1 - Ok, I will use export/import for it. I appreciate the tip. Thanks. -- Mark Kamoski