Hi Everyone,
I am trying to figure out the syntax for copying tables. This is being perfomred inside an updatable BAQ, with a ‘Custom Action’. In this case, I have UD03 full of data. I want to make a copy of this table into UD04. This is what I have so far:
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
// empty UD04
foreach(var UD04 in (from row in Db.UD04 select row))
{
Db.UD04.Delete(UD04);
}
Db.Validate();
txScope.Complete();
// populate UD04
foreach(var UD03 in (from row in Db.UD03 select row))
{
// how to populate with UD03 contents?
}
Db.Validate();
txScope.Complete();
// empty UD03
foreach(var UD03 in (from row in Db.UD03 select row))
{
Db.UD03.Delete(UD03);
}
Db.Validate();
txScope.Complete();
}
The delete portion works just fine for clearing the tables. How do I copy from one table to another? Do I need to step through each row, or cell and copy one by one? Can I just copy the whole table with one command? I appreciate any ideas!
Thanks!
Nate
This seems like a great option. I just have to figure out the syntax. Here is what I have with the error it produces:
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
// empty UD04
foreach(var UD04 in (from row in Db.UD04 select row))
{
Db.UD04.Delete(UD04);
}
Db.Validate();
// txScope.Complete();
// populate UD04
foreach(var UD03 in (from row in Db.UD03 select row))
{
// how to populate with UD03 contents?
// Create New
Ice.Tables.UD04 UD04 = null;
Db.UD04.Insert(UD04);
BufferCopy.CopyExceptFor(UD03, UD04, UD03.ColumnNames.SysRevID, UD03.ColumnNames.SysRowID); // MAGIC
}
Db.Validate();
// txScope.Complete();
// empty UD03
foreach(var UD03 in (from row in Db.UD03 select row))
{
Db.UD03.Delete(UD03);
}
Db.Validate();
txScope.Complete();
}
And the error created:
Server Side Exception
BPM runtime caught an unexpected exception of 'NullReferenceException' type.
See more info in the Inner Exception section of Exception Details.
Exception caught in: Epicor.ServiceModel
Error Detail
============
Correlation ID: fa227763-61f4-4684-a76e-585b88cfef59
Description: BPM runtime caught an unexpected exception of 'NullReferenceException' type.
See more info in the Inner Exception section of Exception Details.
Program: Epicor.System.dll
Method: AddObject
Line Number: 254
Column Number: 13
Original Exception Type: NullReferenceException
Client Stack Trace
==================
at Epicor.ServiceModel.Channels.ImplBase`1.ShouldRethrowNonRetryableException(Exception ex, DataSet[] dataSets)
at Ice.Proxy.BO.DynamicQueryImpl.RunCustomAction(DynamicQueryDataSet queryDS, String actionID, DataSet queryResultDataset)
at Ice.Adapters.DynamicQueryAdapter.<>c__DisplayClass33_0.<RunCustomAction>b__0(DataSet datasetToSend)
at Ice.Adapters.DynamicQueryAdapter.ProcessUbaqMethod(String methodName, DataSet updatedDS, Func`2 methodExecutor, Boolean refreshQueryResultsDataset)
at Ice.Adapters.DynamicQueryAdapter.RunCustomAction(DynamicQueryDataSet queryDS, String actionId, DataSet updatedDS, Boolean refreshQueryResultsDataset)
at Ice.UI.App.BAQDesignerEntry.BAQTransaction.<>c__DisplayClass383_0.<CallRunCustom>b__0(Int32& rowReturned)
at Ice.UI.App.BAQDesignerEntry.Forms.BAQDiagramForm.ShowQueryResults(DataSet dsResults, getQueryResult getResults, ReportAdditionalInfo additionalInfo)
at Ice.UI.App.BAQDesignerEntry.BAQTransaction.CallRunCustom()
Inner Exception
===============
Object reference not set to an instance of an object.
Well your syntax is a little funny you are doing Db.UD04.Insert(UD04) when UD04 is null…
You need to get
UD03 (also rename your variable from UD03 to UD03Row or you gonna drive yourself and me crazy lol
Then
Make a new UD04Row reocord
then use the BufferCopy function to copy UD03Row to UD04Row
Also you should be using the business objects not the direct Db Writes.
I am having a tough time sorting out the correct BOs to use for this seemingly simple BPM.
I think that I need to use GetByID on every row of UD03, then copy that record into UD04 with something like GetGetNewUD04 and maybe an UpdateTableByQuery. For some reason I can’t get the logic sorted out right.
For example, how do I use the GetByID widget to pull every record from UD03? I don’t want to have to specify the keys every time, I just want it to cycle through each record. Once I have that record, how do I copy it into UD04 with widgets?
Happy New Year!
I am back at it again trying to copy a table. From UD03 to UD04. This is what I have so far. I am trying to use only the widgets to keep myself out of trouble, but I am not sure that is working.
I have a table full of data in UD03 and an empty table in UD04. When I run this custom action, I get this error:
Application Error
Exception caught in: System.Data
Error Detail
============
Message: This row has been removed from a table and does not have any data. BeginEdit() will allow creation of new data in this row.
Program: System.Data.dll
Method: GetDefaultRecord
Can these widgets be used to copy all data from one table to another?
Initially I had set the where criteria for getrows to “”. But after some thinking I deceided to try to make a loop so I could address each row one by one. I start with a variable that equals the number of rows in UD03. I also start a varialbe for the current row =1.
Then I use a condition to loop through this segment, getting a row with criteria: “ttResultsRow.UD03_Key1 = CurrentRow.ToString()”.
The process executes, and continues holding up the entire epicor session until it finally fails in a server error. No rows copied.
A slightly more controlled test is giving me another error. I tried to set the where clause as mentioned above. Then I also limited the iterations within the condition to only the first 10 records. Now the error indicates that The table ttResults has more than one record. I believe it is in reference to the GetRows method. I am clearly missing something.
I have had no luck sorting out the widgets. I am falling back to coding everything into a single custom code widget. It seems like this should be a simple few lines of code, but I can’t get the syntax straight. Can anyone help me clean this up a bit?
UD04BO = Ice.Assemblies.ServiceRenderer.GetService<Ice.Tables.UD04>(Db);
foreach (var ttResults_iterator in (from ttResults_Row in ttResults where ttResults_Row.Unchanged() select ttResults_Row))
// for every record in ttResults, do this stuff. ttResults = UD03
{
//Get the data from the next row of UD03
var myKey1 = ttResults_iterator.UD03_Key1;
var myKey2 = ttResults_iterator.UD03_Key2;
var myKey3 = ttResults_iterator.UD03_Key3;
var myKey4 = ttResults_iterator.UD03_Key4;
var myKey5 = ttResults_iterator.UD03_Key5;
var ShortChar01 = ttResults_iterator.UD03_ShortChar01;
var ShortChar02 = ttResults_iterator.UD03_ShortChar02;
var Number01 = ttResults_iterator.UD03_Number01;
var Number02 = ttResults_iterator.UD03_Number02;
var Number03 = ttResults_iterator.UD03_Number03;
var Date01 = ttResults_iterator.UD03_Date01;
//Create a new record in UD04
var tsUD04 = new UD04Tableset();
UD04BO.GetNewUD04(ref tsUD04);
//Paste the data from the copied row of UD03, to the new row in UD04
tsUD04.Key1 = myKey1;
tsUD04.Key2 = myKey2;
tsUD04.Key3 = myKey3;
tsUD04.Key4 = myKey4;
tsUD04.Key5 = myKey5;
tsUD04.ShortChar01 = ShortChar01;
tsUD04.ShortChar02 = ShortChar02;
tsUD04.Number01 = Number01;
tsUD04.Number02 = Number02;
tsUD04.Number03 = Number03;
tsUD04.Date01 = Date01;
//Update UD04
UD04BO.Update(tsUD04);
}
The primary error I am getting is a compile error indicating the GetNewUD04 and Update methods do not have definitions in UD04. I am sure I just entered the code wrong.
Thanks for your time!
Btw use GetaNewUD04 (notice the special a) GET A NEW not GET NEW, Not sure why I assume the GetNew is obsolete I could never get it to work either. GetaNew works awesome. I remember I had notes on it in the past, if I find them ill share them.
This works great! Thank you so much for the help! Here is the final syntax that I used to copy the data from all records in UD03 to a blank table, UD04.
foreach (var ttResults_iterator in (from ttResults_Row in ttResults where ttResults_Row.Unchanged() select ttResults_Row))
// for every record in ttResults, do this stuff. ttResults = UD03
{
//Get the data from the next row of UD03
var myKey1 = ttResults_iterator.UD03_Key1;
var myKey2 = ttResults_iterator.UD03_Key2;
var myKey3 = ttResults_iterator.UD03_Key3;
var myKey4 = ttResults_iterator.UD03_Key4;
var myKey5 = ttResults_iterator.UD03_Key5;
var ShortChar01 = ttResults_iterator.UD03_ShortChar01;
var ShortChar02 = ttResults_iterator.UD03_ShortChar02;
var Number01 = ttResults_iterator.UD03_Number01;
var Number02 = ttResults_iterator.UD03_Number02;
var Number03 = ttResults_iterator.UD03_Number03;
var Date01 = ttResults_iterator.UD03_Date01;
using (Ice.Contracts.UD04SvcContract ud04Svc = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.UD04SvcContract>(Db))
{
var UD04DataSet = new Ice.Tablesets.UD04Tableset();
ud04Svc.GetaNewUD04(ref UD04DataSet);
var newUD04Row = (from x in UD04DataSet.UD04 where x.RowMod == "A" select x).FirstOrDefault();
if (newUD04Row != null)
{
newUD04Row.Company = Session.CompanyID;
newUD04Row.Key1 = myKey1;
newUD04Row.Key2 = myKey2;
newUD04Row.Key3 = myKey3;
newUD04Row.Key4 = myKey4;
newUD04Row.Key5 = myKey5;
newUD04Row.ShortChar01 = ShortChar01;
newUD04Row.ShortChar02 = ShortChar02;
newUD04Row.Number01 = Number01;
newUD04Row.Number02 = Number02;
newUD04Row.Number03 = Number03;
newUD04Row.Date01 = Date01;
ud04Svc.Update(ref UD04DataSet);
}
}
}
Please let me know if you see any problems with this code. It seems to be working perfectly. Thank you so much!!!
Nate
For some reason I had to add an unused variable into the workflow designer I created a UD04 Tableset variable called tsUD04. As far as I can tell this is not used anywhere, but the error goes away when I added it in. Strange…
