Can anyone provide me with some code snippets for adding rows to an Erp table? Specifically, I’m looking to add attachments from the part master to a PO line when a new part is added.
I have used the trace log to understand how it’s supposed to happen, but I’m lacking the syntax to do so.
Hi Aaron,
From Carson’s BPM that prints all attachments, here’s how he’s listing the attachments for a Shipment. Here he’s grabbing all documents attached to a Customer Shipment but you can do the same for Part.
string clawz = "RelatedToFile = 'ShipVia'";
// build up the where clause using the collection of Keys
if (keys != null && keys.Count > 0)
{
string andOr = " AND ";
andOr += keys.Count > 1 ? "(" : "";
// enum the collection of Key sets
foreach (string key in keys)
{
clawz += andOr + "Key1 = '" + key+ "' ";
andOr = " OR ";
}
clawz += keys.Count > 1 ? ")" : "";
}
// use the AttachmentAdapter to init the list of XFileAttchRow's
Epicor.Mfg.UI.Adapters.AttachmentAdapter adapter = new Epicor.Mfg.UI.Adapters.AttachmentAdapter(oTrans);
Hashtable wc = new Hashtable();
wc.Add("XFileAttch", clawz);
adapter.InvokeSearch(SearchOptions.CreateRuntimeSearch(wc, DataSetMode.RowsDataSet));
// enum the list of all the attachments for the requested parent keys
foreach (DataRow row in adapter.AttachmentData.XFileAttch.Rows)
{
Epicor.Mfg.BO.AttachmentDataSet.XFileAttchRow xRow = row as Epicor.Mfg.BO.AttachmentDataSet.XFileAttchRow;
if (xRow != null)
{
// verify there is DocAssoc with Print option for the file item
string FileName = row["XFileRefXFileName"].ToString();
if (!string.IsNullOrEmpty(FormFunctions.GetDocAssoc(oTrans,
FileName.Substring(FileName.LastIndexOf('.') + 1), FormFunctions.DocAssocType.print)))
printAttachment(xRow);
}
}
And here’s where he builds the keys
// print all available attachments
void printAttachments()
{
// build list of keys
List<string> keys = new List<string>();
foreach (DataRowView drv in edvShipVia.dataView)
if (!keys.Contains(drv["ShipViaCode"].ToString()))
keys.Add(drv["ShipViaCode"].ToString());
if (keys.Count>0) printAttachments(keys);
}
You’ll have to add at least:
using Epicor.Mfg.UI.Adapters;
Mark W.
Mark, this may be where I’m running into troubles. All of the examples are from 9. Trying to add “using Epicor.anything” doesn’t seem to exists in E10. They changed the names of the assemblies.
Right.
Here’s a post that shows the E10 using statements.
There appears to be a difference between the code in a BPM and a customization, thought, because when I try to add those, I get this:
Code Message
CS0234 The type or namespace name 'Adapters' does not exist in the namespace 'Erp' (are you missing an assembly reference?)
CS0234 The type or namespace name 'Adapters' does not exist in the namespace 'Ice' (are you missing an assembly reference?)That is correct. Whenever you use a new using statement, there is usually an associated library you must reference.
Check out the Customization Guide (Sign In) and go page 721 for an example on how to do this.
Mark W.
I’m in a BPM, though, not a customization.
So, I got the XFileRef assembly, but Ice.Contracts.Bo.XFileAttch doesn’t seem to exist.
How do I reference the XFileAttch assembly so I can update the table?
Try Ice.Contracts.BO.Attachment
(Did you put the Triggers references in there or was that automatic?)
Alright, so apparently it’s Erp.Contracts.Drawings.
I have this code that compiles, but it doesn’t seem to add a row.
Am I missing something?
//Create new row in XFileAttch
var xFileRefSvc = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.XFileRefSvcContract>(Db);
Erp.Tablesets.DrawingsTableset xfats = new Erp.Tablesets.DrawingsTableset();
xFileAttchSvc.GetNewDrawings(ref xfats, "PODetail", poNumStr, poLineStr, "", "", "", "");
var newXAttchRow = xfats.Drawings.FirstOrDefault();
newXAttchRow.Company = Session.CompanyID;
newXAttchRow.Key1 = poNumStr;
newXAttchRow.Key2 = poLineStr;
newXAttchRow.Key3 = "";
newXAttchRow.Key4 = "";
newXAttchRow.Key5 = "";
newXAttchRow.Key6 = "";
newXAttchRow.RelatedToFile = "PODetail";
newXAttchRow.AttachNum = attachNum;
newXAttchRow.XFileRefNum = lastRef;
xFileAttchSvc.Update(ref xfats);
//Create new row in XFileRef
var xFileAttchSvc = Ice.Assemblies.ServiceRenderer.GetService<Erp.Contracts.DrawingsSvcContract>(Db);
Ice.Tablesets.XFileRefTableset xfrts = new Ice.Tablesets.XFileRefTableset();
xFileRefSvc.GetNewXFileRef(ref xfrts);
var newXRefRow = xfrts.XFileRef.FirstOrDefault();
newXRefRow.Company = Session.CompanyID;
newXRefRow.XFileRefNum = lastRef;
newXRefRow.XFileName = fileName;
newXRefRow.XFileDesc = fileDesc;
xFileRefSvc.Update(ref xfrts);I didn’t add those triggers. I just did a filter of the assemblies for anything that started with XFile.
So now you’re into basic BPM debugging:
- Add a message box action to see if the method you chose is actually firing.
- Add some BPM Tracing to see how far your code is getting (See the ICE manual)
- Add Try-Catch blocks to see if there are errors
- Check the Windows Event Log on your application server:
PLEASE look in the 'Flight Data Recorder' - ERP 10 - Epicor User Help Forum
Mark W.
This is the error I’m receiving in the logs:
Query failed to run
The underlying provider failed on EnlistTransaction.
(ctx, company_ex, relatedToFile_ex, key1_ex, key2_ex, key3_ex, key4_ex, key5_ex, attachNum_ex) => ctx.XFileAttch.Where(row => ((((((((row.Company == company_ex) AndAlso (row.RelatedToFile == relatedToFile_ex)) AndAlso (row.Key1 == key1_ex)) AndAlso (row.Key2 == key2_ex)) AndAlso (row.Key3 == key3_ex)) AndAlso (row.Key4 == key4_ex)) AndAlso (row.Key5 == key5_ex)) AndAlso (row.AttachNum > attachNum_ex))).OrderBy(row => row.Company).ThenBy(row => row.RelatedToFile).ThenBy(row => row.Key1).ThenBy(row => row.Key2).ThenBy(row => row.Key3).ThenBy(row => row.Key4).ThenBy(row => row.Key5).ThenByDescending(row => row.AttachNum).FirstOrDefault()
The queries I have defined all seem to generate data (as shown by a message box).
Not sure about the EnlistTransaction bit. Am I supposed to be using a transaction scope? I thought the business objects were supposed to have their own?
Okay, so I got it so it adds the records to the table. I figured it would be automatic that it would show up under that attachments, or at least a refresh required. Is there anything special that needs to happen there?
If you add one manually, how do the records compare to the ones you added through the BPM?
You might have to add a customization that refreshes the screen after the BPM fires. Others may know how to refresh from a BPM. Usually, if custom code is only going to run in a single command, I use a customization but if it has to run no matter what the source of control, then I use a BPM.
The lines in the table appear the same. The only thing I can’t get to populate is the file description.
Additionally, it appears that the Ice.Contracts.BO.XFileRef doesn’t do much. Everything is getting populated through the Erp.Contracts.BO.Drawings service contract.
And how does that compare to your original trace? You’re running the same BO.method combinations that the trace is?
Right–I forgot that bit.
So, I rewrote it to follow the BO in the trace. (GetByID, GetNewPODetailAttch, Update).
Now I’m getting a business layer exception that says it cannot find the parent of the attachment?
I didn’t see anything in the trace that shows it’s adding it to the XFileAttch table prior to calling the GetNewPODetailAttch object.
Trace: Business Logic Error
Business Layer Exception
BLException
Description: Parent of attachment not found
Program: Epicor.Ice.dll
Method: InnerAttachmentBeforeUpdate
Line Number: 2318
Column Number: 17
Table: PODetailAttch
Field: {&PRIMARY_KEY_NAME_{&LOGICAL_TABLE_{&TableNum}}_1}
Server Trace Stack: at Ice.TablesetBound`3.InnerAttachmentBeforeUpdate(XFileAttch attachment, LinqRow parent, IceRow ttAttachment, String companyID, String key1, String key2, String key3, String key4, String key5, String key6) in C:_Releases\ICE\3.1.500.8\Source\Framework\Epicor.Ice\Services\TablesetBound.cs:line 2318
Credit To Luke McAvoy:
Updating records in the Epicor database.
Below is a code example I have put together to demonstrate how to update an existing record in the Epicor database using C# code in a BPM. If you look closely at the “foreach” statement you will notice a section of this statement contains a “LockHint”. This is an important point that you need to always keep in mind when updating records. The inclusion of “LockHint” ensures that the record remains locked until the update to the database is triggered at the end of the transaction block (see the note at the end which explains the transaction block).
“LockHint” also ensures that your code will wait for a locked record to be released.
The example below shows how to update the value in the ShortChar01 field on a particular record in the UD01 table:
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
foreach(var UD01 in (from row in Db.UD01.With(LockHint.UpdLock) where
row.Company == Session.CompanyID && row.Key1 == "12345"
select row))
{
UD01.ShortChar01 = "abcde";
}
Db.Validate();
txScope.Complete();
}