I’d like to create a dashboard for our CAD department to identify which parts DON’T exist in Epicor. I’d like to paste-insert a list of part numbers (could be from Excel), and then identify which parts are missing, or a pop-up showing “You’re good to go! All parts are in Epicor.” What’s the right approach? Is it as simple as a BAQ with PartNum and Description columns? How does the dashboard do the validate after paste-insert?
Found this from @Banderson:
I’m not certain about using a dashboard for this, but I did something kind of similar with a custom form/code to consume a list of lot numbers from an excel sheet and then compare those inputs to values in the db to get back a result. In your case, they would paste insert a list of part nums into this input grid and then the code would compare each of those inputs to the db and give back an answer as to if it exists or not.
You could probably get away with some other method, but I’d be happy to share my solution with you if you’re interested
Yes please! 
Ok I’ll post it in an hour or so, brb
You can also use a Dashboard, UBAQ and UBAQ BPMs and set Calculated_StatusText and RowRules to color the row if needed.
You can get creative with UBAQs as much or as little as you want if you are on-prem.
Few Screenshots of purely using Expressions:
@askulte do it Haso’s way, it’s a lot more self contained
But for cross reference, what I did was use a UD01 form with all the native controls hidden to start.
In this situation, I am going to consume a list of Donor, OPO Name, and OPO Number. Then, I’m going to compare the donor number to any UD100 records where the first 6 characters of Key1 match the input of the Donor. Then, I am going to update each record it finds with the corresponding OPO Name and OPO Number into each of those UD100 records. Then it moves on to the next row in the inputs.
// **************************************************
// Custom code for UD01Form
// Created: 6/1/2018 12:10:31 PM
// **************************************************
using System;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
using Ice.BO;
using Ice.UI;
using Ice.Lib;
using Ice.Adapters;
using Ice.Lib.Customization;
using Ice.Lib.ExtendedProps;
using Ice.Lib.Framework;
using Ice.Lib.Searches;
using Ice.UI.FormFunctions;
using Infragistics.Win.UltraWinToolbars;
using Infragistics.Win.UltraWinGrid.ExcelExport;
using Ice.Proxy.Lib;
using Ice.Core;
using Ice.Tablesets;
using System.Collections;
using System.Text;
using System.ComponentModel;
using System.Linq;
public class 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 **
DataTable dtInput = new DataTable();
private EpiDataView edvInput;
private StringBuilder builder;
private BackgroundWorker worker;
private System.Windows.Forms.ProgressBar pbProgress;
private int totalRecCount = 0;
public void InitializeCustomCode()
{
// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
// Begin Wizard Added Variable Initialization
// End Wizard Added Variable Initialization
// Begin Wizard Added Custom Method Calls
this.grdInput.InitializeLayout += new Infragistics.Win.UltraWinGrid.InitializeLayoutEventHandler(this.grdInput_InitializeLayout);
this.btnProcesss.Click += new System.EventHandler(this.btnProcesss_Click);
this.btnClear.Click += new System.EventHandler(this.btnClear_Click);
// End Wizard Added Custom Method Calls
dtInput.Columns.Add("Donor",typeof(string));
dtInput.Columns.Add("OPO Name", typeof(string));
dtInput.Columns.Add("OPO Donor Number", typeof(string));
edvInput = new EpiDataView();
edvInput.dataView = dtInput.DefaultView;
oTrans.Add("edvInput", edvInput);
grdInput.EpiAllowPaste = true;
grdInput.EpiAllowPasteInsert = true;
//grdInput.InsertNewRowAfterLastRow = true;
builder = new StringBuilder();
SetUpPB();
this.worker = new BackgroundWorker();
this.worker.DoWork += new DoWorkEventHandler(this.worker_DoWork);
this.worker.ProgressChanged += new ProgressChangedEventHandler(this.worker_ProgressChanged);
this.worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(this.worker_RunWorkerCompleted);
this.worker.WorkerReportsProgress = true;
this.worker.WorkerSupportsCancellation = false;
}
public void DestroyCustomCode()
{
// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
// Begin Wizard Added Object Disposal
this.grdInput.InitializeLayout -= new Infragistics.Win.UltraWinGrid.InitializeLayoutEventHandler(this.grdInput_InitializeLayout);
this.btnProcesss.Click -= new System.EventHandler(this.btnProcesss_Click);
this.btnClear.Click -= new System.EventHandler(this.btnClear_Click);
// End Wizard Added Object Disposal
// Begin Custom Code Disposal
// End Custom Code Disposal
pbProgress.Dispose();
pbProgress=null;
this.worker.DoWork -= new DoWorkEventHandler(this.worker_DoWork);
this.worker.ProgressChanged -= new ProgressChangedEventHandler(this.worker_ProgressChanged);
this.worker.RunWorkerCompleted -= new RunWorkerCompletedEventHandler(this.worker_RunWorkerCompleted);
}
private void SetUpPB()
{
pbProgress = new System.Windows.Forms.ProgressBar();
//Draw a textbox where you'd like the PB to be and get its Location from there then remove textbox
pbProgress.Location = new System.Drawing.Point(14, 335);
pbProgress.Name = "pbProgress";
//Draw a TextBox where you'd like the PB to be and get its Size from there then remove textbox
pbProgress.Size = new System.Drawing.Size(688, 20);
//Get a Hold of the Parent Container where you'd like the PB to be
Ice.UI.App.UD01Entry.DetailPanel pnl =(Ice.UI.App.UD01Entry.DetailPanel)csm.GetNativeControlReference("d5488fbc-e47b-46b6-aa3e-9ab7d923315a");
//Add PB to the above container
pnl.Controls.Add(pbProgress);
}
private void UD01Form_Load(object sender, EventArgs args)
{
// Add Event Handler Code
// Hide Native Toolbar Controls
baseToolbarsManager.Tools["NewTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["RefreshTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["DeleteTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["SaveTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["EditMenu"].SharedProps.Visible=false;
baseToolbarsManager.Tools["HelpMenu"].SharedProps.Visible=false;
baseToolbarsManager.Tools["ToolsMenu"].SharedProps.Visible=false;
baseToolbarsManager.Tools["ActionsMenu"].SharedProps.Visible=false;
baseToolbarsManager.Tools["FileMenu"].SharedProps.Visible=false;
baseToolbarsManager.Tools["AttachmentTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["ClearTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["CopyTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["CutTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["PasteTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["UndoTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["PrimarySearchTool"].SharedProps.Visible=false;
dtInput.Clear();
txtProgress.Text = builder.ToString();
}
private void grdInput_InitializeLayout(object sender, Infragistics.Win.UltraWinGrid.InitializeLayoutEventArgs args)
{
// ** Place Event Handling Code Here **
grdInput.EpiAllowPaste = true;
grdInput.EpiAllowPasteInsert = true;
grdInput.InsertNewRowAfterLastRow = false;
}
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
//sender is BackgoundWorker
//this is where the background operation goes
using(UD100Adapter adapterUD100 = new UD100Adapter(oTrans))
{
adapterUD100.BOConnect();
builder.Append("Start Process...").AppendLine();
//foreach(DataRow dr in dtInput.Rows)
for(int i = 0; i<dtInput.Rows.Count; i++)
{
var dr = dtInput.Rows[i];
string whereClause = string.Format("Key1 like '{0}-%'", dr["Donor"].ToString());
//start log
builder.Append(string.Format("Looking up Donor {0}...", dr["Donor"].ToString())).AppendLine();
//the Hashtable stores the runtime search criteria
System.Collections.Hashtable wcs = new Hashtable(1);
wcs.Clear();
wcs.Add("UD100", whereClause);
Ice.Lib.Searches.SearchOptions opts = Ice.Lib.Searches.SearchOptions.CreateRuntimeSearch(wcs, Ice.Lib.Searches.DataSetMode.RowsDataSet);
adapterUD100.InvokeSearch(opts);
int rowCount = adapterUD100.UD100Data.UD100.Rows.Count;
if(rowCount>0)
{
foreach(DataRow udRow in adapterUD100.UD100Data.UD100.Rows)
{
//modify here
udRow.BeginEdit();
udRow["Character04"] = dr["OPO Name"];
udRow["Character05"] = dr["OPO Donor Number"];
udRow["RowMod"] = "U";
udRow.EndEdit();
//update log
builder.Append(string.Format("{0}: Success!", udRow["Key1"].ToString())).AppendLine();
totalRecCount++;
}
//update
adapterUD100.Update();
worker.ReportProgress(i);
}
else
{
//update log
builder.Append(string.Format("******Failed to find UD100 recs for {0}******", dr["Donor"].ToString())).AppendLine();
}
txtProgress.Text = builder.ToString();
}
adapterUD100.Dispose();
worker.ReportProgress(100);
}
}
private void worker_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
//notifies progress bar when changed
pbProgress.Value = e.ProgressPercentage;
}
private void worker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
//On completed, do the appropriate task
if(e.Error !=null)
{
MessageBox.Show("Error while performing tasks!");
}
else
{
MessageBox.Show(string.Format("Complete. {0} records updated!", totalRecCount.ToString()));
}
//re-enable the UI interface to prevent weird threading issues
btnProcesss.ReadOnly = false;
btnClear.ReadOnly = false;
}
private void btnProcesss_Click(object sender, System.EventArgs args)
{
// ** Place Event Handling Code Here **
if (dtInput.Rows.Count>0)
{
try
{
worker.RunWorkerAsync();
builder.Clear();
pbProgress.Value = 0;
totalRecCount = 0;
btnProcesss.ReadOnly = true;
btnClear.ReadOnly = true;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("Add Inputs");
}
}
private void btnClear_Click(object sender, System.EventArgs args)
{
// ** Place Event Handling Code Here **
dtInput.Clear();
builder.Clear();
txtProgress.Text = string.Empty;
pbProgress.Value = 0;
totalRecCount = 0;
}
}
In your case, you’re not updating any records nor are you finding multiple records for each input, but you could still take the input, perform a lookup on the Part table to see if a record exists, and if so either write a message to a log or add a row to another table of “Part exists/does not exist” and then move to the next record.
@hkeric.wci how do you get your gifs to upload? Mine are too big 
Pretty slick, I use SnagIt usually but this is nice
@hkeric.wci & @Aaron_Moreng - Thanks for the suggestions! I feel way over my head, but will give Haso’s method a try. Always learning…
To break this down in smaller steps:
Create a UD Form - How do you create a ud form - #4 by ckrusen. Looking in SSMS, UD10 is the first table without any records. Is there any way to see if it’s being used for other customization to hold temp records, but is empty? I was thinking I could paste-insert the PartNum as Key1 on UD10.
UBAQ, using the open UD10 table to load in the partnum? I’m not sure what I’m updating from the UBAQ if it’s just pulling part records and comparing them.
2A. Do I use the default UpdateExt BO, or figure out how to attach GetList?
2B. Which fields need to be updatable, even though I’m not updating part records? Leave “AddNew” unchecked and “Allow Multiple Dirty Rows” checked since I want to parse multiple rows?
2A. Add a calculated field to the UBAQ to show row status?
Enough for now, I’ll get to creating the UD Table form… Thanks again.
So you wouldn’t create a UD Form, just create a Dashboard-Assembly and you can still customize it later if you need.
For your UBAQ don’t add any tables… create for starters 3 Calculated Fields
Calculated_PartNumSource
Calculated_PartNumTarget
Calculated_RowStatus
Then under the Updatable Tab you can pick “Advanced BPM” and in the Pre-Update you can get the values ttResults table, or you can go to the attribute on the field or is it under Update tab and check the fields to “Raise Events” then you can even use the FieldUpdate and the FieldValidate BPMs in the UBAQ and even validate before the Update, whichever is easier.
Start there, get your UBAQ going
I’ve used the empty dashboard assembly approach too, either way is fine.
I have a template for the empty form here that I use, let’s see if it attaches App.UD01Entry.UD01Form_Customization_UD01Template_CustomExport.xml (29.5 KB)
Change the value in the msprop:Company value to your company ID prior to using
And in this scenario, I’m not using the underlying table at all so it doesn’t matter which UD form you use. I just use it as a interface to build on
Here is example of a UBAQ I use in Code to write to a UNC Path as the Epicor Service Account. Remember BPMs run server side.
var fileRows = (from tt in ttResults
where tt.Added()
select tt).GroupBy(r => new
{
filePath = r.Calculated_FilePath,
fileName = r.Calculated_FileName
}).ToList();
//etc
AKA: Life Before Efx we always just hijacked UBAQs to be functions.
if you’re starting with a list of part numbers to check in excel, you could create an ODBC connection to the SQL DB to populate a sheet with all the part numbers. Then use VLOOKUP() in excel to find the engineering supplied part number in that sheet containing the full list of parts.
And this way the person doing the check doesn’t even need to have access to E10.
If that’s the approach that will be used, why not just bring all parts in via the search box on Part - then export the grid to Excel?
I’m thinking that it’s possible that the OP wants to avoid entering part numbers with syntax differences as new part numbers when they exist already (underscore instead of dash, 0 instead of O, etc.)
@Gil_V - We’ve got over a million parts, that would take a while! We use AbleBit to sync a list from Sharepoint, but it takes a few minutes every time. The goal is to streamline work for the BOM writers. They want Engineering to notice they missed entering some parts into Epicor before the task is passed to the BOM Writers (who would have to kick it back to engineering to correct).
@ckrusenThank you. We currently use AbleBits to sync a parts list from Sharepoint to Excel to do the vlookup, but it takes a few minutes to sync, and our BOM Writers wanted an “instant” process (since they would need to sync multiple times a day as they get assignments and may have new parts).
Is it possible to set up ODBC as read-only? Any security concerns? Would the user/pwd be hard coded plain text into the excel spreadsheet and available for folks to find and connect with outside this spreadsheet?
I’m leery of direct db connections, but it would solve the issue since Engineering is already using a spreadsheet to auto-generate the part numbers. Searching the edu docs for ‘ODBC’, I found a setting in Company Maintenance.
!!! - I forgot to consider the size of your database. Clearly my idea was not appropriate for your issue.
I am frequently spoiled by the rather small size of our database at my current employer.
Excel can take in an ODATA feed via rest as well. Not sure if that is a feasible solution or not
Good point! I need to learn to work with Rest anyways… Would it be querying a row at a time and returning a match True/False? Or would this be a full dump of our parts list into a tab, with the few minutes to sync penalty, and doing a VLOOKUP?
So many ways. I’m attempting Haso’s way at the moment, but intrigued about ODBC and REST 
ODBC? 
Microsoft is moving away from it and it’s generally not set up securely for most users.
If you’re doing REST, you might as well do Epicor Functions. Send in a JSON string of the part numbers in one call, return a JSON string with the parts, active status, existence, etc. One call does it all.
My ODBC setting are strictly on the workstations. That area of the Company Maint is for something else.
SQL Server and hit Next
I use:
Then in MS Query, I select the ODBC connection I just made
Make the query, then click Return Data
Excel will ask for a location, and will create a table with the query results.
Refreshing the Part table shouldn’t take more than a few seconds.
Once you add that to an Excel file, it will remember the connection info. So it could a sheet on the template used for generating P/N’s. Then the VLOOKUP could say if the proposed P/N exist, is InActive, or doesn’t exist.
As for being Read Only, That would probably require some setting in the ODBC connection that I glossed over. While there isn’t a straightforward way to write to the DB via the ODBC, you can manually change the SQL phrase in MS Query, to an UPDATE phrase and that seems to execute.