Unsure how to approach this task as of yet.
The client wants to:
Pull all open jobs
Swap out one part for another
Unsure how to approach this. BPM isn’t the path. I suppose you could use a BAQ to pull down the open jobs but unsure on the swapping of parts part.
My initial thought is to use the REST API and do it that way with a BAQ to get all the jobs.
Any ideas?
Maybe there is a way to do an update with a BAQ but I’ve never done that.
I don’t think this possible, or a very good idea. Changing part numbers for open jobs would break a lot of links and likely invalidate your financials. I would consider closing those jobs and creating new jobs to replace them.
You may be able to use the DMT to mass update your open jobs. I recently updated thousands of open jobs with the DMT. However, I was only adjusting operations and resource groups, not the part number.
If you have a process in mind, give it a shot in Pilot to see if you get the expected results before committing to the production database.
The part in question would not be the job part number but one of the material part numbers, if that matters.
If you haven’t made any material transactions, then this might be possible. But as soon as you have material transactions on a job you are sort of stuck with that material. Same goes for labor transactions, or any other part transactions. Once records get added to the trans tables you really don’t want to change the internals for the job. In many cases you simply can’t change them once you have transactions.
Good luck! This is a tough place to be in!
You can change the part numbers in a job. You just can’t delete the material sequence if there is transactions against it.
The best way to do that is DMT.
@NateS , just an FYI, you can issue whatever parts you want to the job as long as the UOM is the correct class. That means if you have Part A on the job, you can issue Part B. This is for substitution purposes. (for say, different sheet sizes with steel). So while the part number drives the demand, it doesn’t actually affect the financials, because it looks at what was actually issued to the material sequence.
I’m pretty sure the part’s aren’t pulled so there won’t be any transactions against it.
I want to do it via the REST API so I can check things prior to updating anything. Which would mean everytime they need to swap parts, they would need to contact me unless I built an interface of some sort.
But knowing the client, I’m guessing they will want to go the DMT route so someone in house does the updates. I worry things could be messed up easier as not many in the office are up to running DMT.
DMT is generally safe than API. DMT utilizes all the proper methods and business objects required to maintain data integrity. REST API requires you to know all the methods that need to be executed and do them all in the right order. There is a lot less work and worry using DMT.
If it’s not a one time thing, you can make a UBAQ to look up and make the change. And you can put the code into the BAQ BPM to do the checks.
However… I feel like since you are asking about it, you don’t know how to do that. Am I correct?
I have a rough idea. Done one or two in the past but it’s a bit blurry.
I’m probably wrong but there isn’t away to specify a parameter that would be used as the swap out part.
I would have two parameters. One for the part to search for. The other is the part to swap with.
Examples:
ABC.1000 - the original part
ABC.2000 - the new part
In a perfect world, I’d want a form to pop up, ask for the original part and then the new part. Hit search/go whatever and bam, it’s all handled.
With the way I’ve done UBAQs in the past you get the query results, then you can edit the results. And then click update.
You can ask for a parameter, and that would pop up with a box to type in both the to and from. Then you can use that info to do what you need to do. You can show a calculated row and simple show the parameter in that column. Then when you loop through the rows, you have that part number available.
Usually what I do when I have stuff like this, is make a check box (calculated field to false and make that updateable) then you check the box if you want to make a change, then when you loop through, you check for that. Then I do a customization on the dashboard so that you can have a button that will check the selected rows to make it easier to select.
You can take a look at this: Add or Edit any Operation - My Custom Dashboards - ERP 10 - Epicor User Help Forum (epiusers.help) Where I utilize BPMs and UBAQs to make changes to operations. IF nothing else, it shows the basics of the kind of input you’re looking for. Good luck! This is a tough nut!
I appreciate both of your responses.
I got it to work in a UBAQ to an extent. It pulls down the jobs and I can go row by row and edit them.
What I haven’t figured out is how to do what @Banderson was saying about a calculated row and the loop through the rows part.
Here is what I got. Granted I don’t have the second parameter defined as of right now.
What I said won’t work with the basic UBAQ where you let Epicor pick the BO and stuff. You have make a custom BPM to do that. It depends on how automated you want to make it. The way you have it there will work fine, you just have to copy paste the part number over and over again.
I see what you’re saying I think. Waiting on the client to see what they say about it at this point.
They threw a wrench in it and sent an email about adding parts and changing quantities with this same setup.
Something like this?
// **************************************************
// Custom code for MainController
// Created: 2/16/2021 11:06:55 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 Ice.Core;
using Infragistics.Win.UltraWinToolbars;
using Infragistics.Win.UltraWinGrid;
using System.Collections;
using System.Collections.Generic;
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 **
readonly bool True = true;
readonly bool False = false;
readonly string uBAQName = "q4dsbJobMtlReplace";
readonly string[] colsToHide = new string[] { "JobMtl_RequiredQty","RowMod", "RowIdent", "SysRowID" };
string CompanyID = "";
EpiDataView edv;
DynamicQueryAdapter uBAQ;
DataSet dsPart;
DataTable results;
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
// End Wizard Added Custom Method Calls
etbOldPart.Validating += new System.ComponentModel.CancelEventHandler(etbOldPart_Validating);
etbNewPart.Validating += new System.ComponentModel.CancelEventHandler(etbNewPart_Validating);
ebSelectAll.Click += new System.EventHandler(ebSelectAll_Click);
ebGetJobList.Click += new System.EventHandler(ebGetJobList_Click);
ebUpdateParts.Click += new System.EventHandler(ebUpdateParts_Click);
uBAQ = new DynamicQueryAdapter(this.oTrans);
uBAQ.BOConnect();
uBAQ.GetByID(uBAQName);
CompanyID = ((Ice.Core.Session)(oTrans.Session)).CompanyID;
ebSelectAll.ReadOnly = true;
ebUpdateParts.ReadOnly = true;
ebGetJobList.ReadOnly = true;
}
public void 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
etbOldPart.Validating -= new System.ComponentModel.CancelEventHandler(etbOldPart_Validating);
etbNewPart.Validating -= new System.ComponentModel.CancelEventHandler(etbNewPart_Validating);
ebGetJobList.Click -= new System.EventHandler(ebGetJobList_Click);
ebUpdateParts.Click -= new System.EventHandler(ebUpdateParts_Click);
ebSelectAll.Click -= new System.EventHandler(ebSelectAll_Click);
uBAQ.Dispose();
edv.Dispose();
results.Dispose();
dsPart.Dispose();
}
private void ebSelectAll_Click(object sender, System.EventArgs args)
{
DataView dvMtl = edv.dataView;
eugJobsList.BeginUpdate();
eugJobsList.SuspendRowSynchronization();
foreach (DataRowView row in dvMtl)
{
if (!Convert.ToBoolean(row["Calculated_Select_Row"]))
{
row.BeginEdit();
row["Calculated_Select_Row"] = true;
row.EndEdit();
}
}
eugJobsList.ResumeRowSynchronization();
eugJobsList.EndUpdate();
}
private void ebGetJobList_Click(object sender, System.EventArgs args)
{
eugJobList_GetList();
if(eugJobsList.Rows.Count>1) ebSelectAll.ReadOnly = false;
}
private void eugJobList_GetList()
{
Ice.BO.QueryExecutionDataSet dsBAQ;
Ice.BO.DynamicQueryDataSet dsQuery;
Ice.BO.DynamicQueryDataSet dsQDesign;
if (((string)etbOldPart.Value!=null) && ((string)etbOldPart.Value != string.Empty) && (((string)(etbOldPart.Value)+" ").Trim()!=string.Empty)) {
try {
dsQuery = uBAQ.DynamicQueryData;
if (dsQuery.DynamicQuery.Rows.Count == 0)
{
dsQDesign = uBAQ.QueryDesignData;
DataRow targetRow;
foreach (DataTable table in dsQuery.Tables)
{
foreach (DataRow sourceRow in dsQDesign.Tables[table.ToString()].Rows)
{
targetRow = table.NewRow();
targetRow.ItemArray = sourceRow.ItemArray;
table.Rows.Add(targetRow);
}
}
}
dsBAQ = uBAQ.GetQueryExecutionParameters(dsQuery);
dsBAQ.ExecutionParameter[0].ParameterID = "SearchPart";
dsBAQ.ExecutionParameter[0].IsEmpty = false;
dsBAQ.ExecutionParameter[0].ParameterValue = ((string)(etbOldPart.Value)+" ").Trim();
dsBAQ.ExecutionParameter[1].ParameterID = "UnFirmJobs";
dsBAQ.ExecutionParameter[1].IsEmpty = false;
dsBAQ.ExecutionParameter[1].ParameterValue = ckbUnFirmJobs.Checked.ToString();
dsBAQ.AcceptChanges();
uBAQ.Execute(dsQuery, dsBAQ);
if (uBAQ.QueryResults != null && uBAQ.QueryResults.Tables.Count > 0)
{
results = uBAQ.QueryResults.Tables["Results"];
}
else
{
results = new DataTable();
}
edv = (EpiDataView)oTrans.EpiDataViews[uBAQName];
if (!(edv != null))
{
edv = new EpiDataView();
oTrans.Add(uBAQName, edv);
}
edv.dataView = results.DefaultView;
eugJobsList.DataSource = edv.dataView;
// Hide select columns
foreach(string colToHide in colsToHide)
{
if (edv.dataView.Table.Columns.Contains(colToHide))
{
eugJobsList.DisplayLayout.Bands[0].Columns[colToHide].Hidden = true;
}
}
}
catch (Exception ex) {
ExceptionBox.Show(ex);
}
} else { MessageBox.Show((etbOldPart.Value+" ").Trim()); }
}
private void ebUpdateParts_Click(object sender, System.EventArgs args)
{
DataView dvMtl = edv.dataView;
string msg = string.Empty;
string sJobsListUnFirm = string.Empty;
List<string> unFirmRows = new List<string>();
eugJobsList.BeginUpdate();
eugJobsList.SuspendRowSynchronization();
if (ckbUnFirmJobs.Checked)
{
foreach (DataRowView row in dvMtl)
{
if (!Convert.ToBoolean(row["JobHead_JobFirm"]) && Convert.ToBoolean(row["Calculated_Select_Row"]))
{
unFirmRows.Add(Convert.ToString(row["SysRowID"]));
row.BeginEdit();
row["JobHead_JobFirm"] = true;
row.EndEdit();
}
}
uBAQ.Update(uBAQ.DynamicQueryData, results.DataSet, false); // Update Jobs to Firm to enable part replacement
}
foreach (DataRowView row in dvMtl)
{
if (Convert.ToBoolean(row["Calculated_Select_Row"]))
{
row.BeginEdit();
row["JobMtl_PartNum"] = etbNewPart.Value;
row["JobMtl_Description"] = etbNewDesc.Value;
row.EndEdit();
}
}
uBAQ.Update(uBAQ.DynamicQueryData, results.DataSet, false);
if (ckbUnFirmJobs.Checked)
{
foreach (DataRowView row in dvMtl)
{
if (unFirmRows.Contains(Convert.ToString(row["SysRowID"])))
{
row.BeginEdit();
row["JobHead_JobFirm"] = false;
row.EndEdit();
}
}
uBAQ.Update(uBAQ.DynamicQueryData, results.DataSet, false); // Restore Job Firm Level
}
eugJobsList.ResumeRowSynchronization();
eugJobsList.EndUpdate();
}
private bool isValidPart(string pn)
{
bool recSelected = false;
string whereClause = "Company = '" + CompanyID + "' AND PartNum = '" + pn + "'" ;
dsPart = Ice.UI.FormFunctions.SearchFunctions.listLookup(this.oTrans, "PartAdapter", out recSelected, false, whereClause);
return recSelected;
}
private void etbOldPart_Validating(object sender, System.ComponentModel.CancelEventArgs args)
{
// Lookup Description for OldPartNum and unset ReadOnly if all good :)
if ( isValidPart(etbOldPart.Text) )
{
if(ebGetJobList.ReadOnly) ebGetJobList.ReadOnly = false;
}
else
{
if(!ebGetJobList.ReadOnly) ebGetJobList.ReadOnly = true;
MessageBox.Show(etbOldPart.Text + " is NOT a vaild part number.");
//args.Cancel=true;
}
}
private void etbNewPart_Validating(object sender, System.ComponentModel.CancelEventArgs args)
{
// Lookup Description for NewPartNum and unset ReadOnly if all good :)
if ( isValidPart(etbNewPart.Text) )
{
etbNewDesc.Value = dsPart.Tables[0].Rows[0]["PartDescription"];
if(ebUpdateParts.ReadOnly) ebUpdateParts.ReadOnly=false;
}
else
{
if(!ebUpdateParts.ReadOnly) ebUpdateParts.ReadOnly=true;
MessageBox.Show(etbNewPart.Text + " is NOT a vaild part number.");
//args.Cancel=true;
}
}
Dang late to the party, I found this code I had same thing for…
Just had no time until now to post this.
Outside the scope of what I have posted sorry 
My post above should illustrate looping and calculated field and using a uBAQ, dashboard & epiUltraGrid.
Adding parts and changing quantities exists! It’s called Job Entry 