Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save lockworld/c3daf18b02758c594606c589d3011caf to your computer and use it in GitHub Desktop.
Save lockworld/c3daf18b02758c594606c589d3011caf to your computer and use it in GitHub Desktop.
Epicor syntax samples

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

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.
*/
//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);
}
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, "");
/*
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;
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();
}
/*This converts SysDate and SysTime to a DateTme format*/
SELECT (DATEADD(s, SysTime, CAST(SysDate AS DateTime))) AS Timestamp, * FROM Erp.PartTran
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;
-- 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
AfterExitEditMode
ValueChanged
Ice.Core.Session session = (Ice.Core.Session)LotNumberForm.Session;
string company = session.CompanyID;
string user = session.UserID;
MessageBox.Show(company + "/" + user");
foreach (var currentRow in ttTFOrdHed)
{
if (String.IsNullOrEmpty(currentRow.RowMod)) // This is the original, or unchanged row
{
var oldvalue = currentRow.ShipComment;
}
}
// 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);
//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
}
}
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();
}
}
// 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());
}
}
// 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;
}
@alphanu1
Copy link

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.

@mkamoski
Copy link

mkamoski commented Sep 20, 2019

@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

@alphanu1
Copy link

@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
Copy link

@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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment