How to quickly delete table ud01?

thanvanhai · December 20, 2021, 1:43am

I want to quickly delete the records in the ud01 table through adapterUD01. Is there a quick conditional delete function that is not for through each data line and then calls the function adapterUD01.DeleteByID(key1,key2,key3,key4,key5); Are not?
Ice.Lib.Searches.SearchOptions opts = new SearchOptions(SearchMode.AutoSearch);
bool morePages = false;
opts.DataSetMode = DataSetMode.RowsDataSet;
string UD01 = string.Empty;
opts.NamedSearch.WhereClauses.Add(“UD01”,UD01);

	DataSet dsUD01 = adapterUD01.GetRows(opts, out morePages);
	for(int j=0;j<dsUD01.Tables[0].Rows.Count;j++)
	{
		string key1=dsUD01.Tables["UD01"].Rows[j]["Key1"].ToString();
		string key2=dsUD01.Tables["UD01"].Rows[j]["Key2"].ToString();
		string key3=dsUD01.Tables["UD01"].Rows[j]["Key3"].ToString();
		string key4=dsUD01.Tables["UD01"].Rows[j]["Key4"].ToString();
		string key5=dsUD01.Tables["UD01"].Rows[j]["Key5"].ToString();
		adapterUD01.DeleteByID(key1,key2,key3,key4,key5);
	}

I want to delete the condition is company data 01

Have a nice day!

NateS · December 20, 2021, 1:12pm

I can’t really understand what you are looking for. It sounds like you need a delete method that doesn’t use the FOR loop. I don’t know how you could do that. But this is the FOR loop that I use to clear out my UD03 table before reloading it.

using (var txScope = IceContext.CreateDefaultTransactionScope())
{

foreach(var UD03 in (from row in Db.UD03 select row))
   {
      Db.UD03.Delete(UD03);
   }
   Db.Validate();
   txScope.Complete();

}

Mark_Wonsil · December 20, 2021, 2:07pm

Is this a one-time thing or are you planning on doing this often?

If one time, and you have DMT, you can create a BAQ that exports the key fields to a file and then run that back through DMT with the delete option.

If this is going to happen often, you have to ensure that only one user at a time will be running this process or there will be a risk of clobbering someone else’s work.

thanvanhai · December 20, 2021, 4:52pm

@NateS
I want to clear the data before we save new data for weekly report every weekend will run the screen once again
@Mark_Wonsil

frequency maybe once a week

this deletion doesn’t affect others i’m sure

my company use ud01 table to store BOM (adapterEngWorkBench.ViewCosts) to get data as weekly or monthly report

EngWorkBenchAdapter adapterEngWorkBench ;
adapterEngWorkBench = new EngWorkBenchAdapter(this.oTrans);
adapterEngWorkBench.BOConnect();
//adapterEngWorkBench.BOConnect();
int key=1;
DataTable tbl = GetBAQData(“DMS_GetAllPart”);
string Part=“”;
string Rev=“”;
string PartParent=“”;
string BomSequence=“”;
for(int a=0;a<tbl.Rows.Count;a++)
{
//if(Part!=tbl.Rows[a][“Part_PartNum”].ToString()&&Rev!=tbl.Rows[a][“PartRev_RevisionNum”].ToString())
//{
session.PlantID=tbl.Rows[a][“Part_Plant_Plant”].ToString();
adapterEngWorkBench = new EngWorkBenchAdapter(this.oTrans);
adapterEngWorkBench.BOConnect();
string ipGroupID = String.Empty;
string ipPartNum = tbl.Rows[a][“Part_PartNum”].ToString();
string ipRevisionNum = tbl.Rows[a][“PartRev_RevisionNum”].ToString();
string ipAltMethod = String.Empty;
System.DateTime ipAsOfDate = DateTime.Now;//Convert.ToDateTime(tbl.Rows[a][“PartRev_EffectiveDate”].ToString());
decimal ipQuantity = 1.0M;
int ipMaxLevel = 999;
bool ipAssembliesOnly = false;
bool isApproved = Convert.ToBoolean(tbl.Rows[a][“PartRev_Approved”]);
// Call Adapter method
bool result = adapterEngWorkBench.ViewCosts(ipGroupID, ipPartNum, ipRevisionNum, ipAltMethod, ipAsOfDate, ipQuantity, ipMaxLevel, ipAssembliesOnly);
{
int BomLevelPre=0;
int BomLevelCur=0;
string PartParentCur=“”;
//string PartParentPre=“”;
List PartPar=new List(); //Part Parent
List Bomseq=new List(); //Bom Sequence Part Parent
for(int i=0;i<adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows.Count;i++)
{
result = adapterUD01.GetaNewUD01();
if(result)
{
string PartArr = adapterEngWorkBench.EngWorkBenchCostData.Tables[“PartRevCostsDetail”].Rows[i][“MtlPartNum”].ToString().Split(‘.’);
int PartIndex=PartArr.Length-1;
BomLevelCur=Convert.ToInt32(adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i][“BOMLevel”]);
if(Convert.ToInt32(adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i][“BOMLevel”])==0||Convert.ToInt32(adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i][“BOMLevel”])==1)
{
PartParent=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i][“PartNum”].ToString();
BomSequence=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[0][“BomSequence”].ToString();
BomLevelPre=Convert.ToInt32(adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i][“BOMLevel”]);
if(BomLevelPre==1)
{
PartPar=new List();
Bomseq=new List();
PartParentCur=PartArr[PartIndex];
PartPar.Add(PartArr[PartIndex]);
Bomseq.Add(adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i][“BomSequence”].ToString());
}
//BomLevelCur=0;
}
else{
if(BomLevelCur>BomLevelPre)
{
if(BomLevelCur>PartPar.Count)
{
PartPar.Add(PartArr[PartIndex]);
Bomseq.Add(adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i][“BomSequence”].ToString());
}
else{
PartPar[BomLevelCur-1]=PartArr[PartIndex];
Bomseq[BomLevelCur-1]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i][“BomSequence”].ToString();
}
}
else if(BomLevelPre==BomLevelCur)
{
PartPar[BomLevelPre-1]=PartArr[PartIndex];
Bomseq[BomLevelPre-1]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i][“BomSequence”].ToString();
}
else{
PartPar[BomLevelCur-1]=PartArr[PartIndex];
Bomseq[BomLevelCur-1]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i][“BomSequence”].ToString();
}
PartParent=PartPar[BomLevelCur-2];
BomSequence=Bomseq[BomLevelCur-2];
BomLevelPre=BomLevelCur;
}

						adapterUD01.UD01Data.UD01.Rows[0]["Company"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["Company"];
						
						int l=key.ToString().Length;
						string Mykey="000000000"+key.ToString();
						
						adapterUD01.UD01Data.UD01.Rows[0]["Key1"]=string.Format("{0}",Mykey.Remove(0,l));
												
						adapterUD01.UD01Data.UD01.Rows[0]["Key2"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["PartNum"];
						adapterUD01.UD01Data.UD01.Rows[0]["Key3"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["RevisionNum"];
						adapterUD01.UD01Data.UD01.Rows[0]["Key4"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["MtlPartNum"];
						adapterUD01.UD01Data.UD01.Rows[0]["Key5"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["BOMLevel"];
						
						adapterUD01.UD01Data.UD01.Rows[0]["Character01"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["Quantity"];
						
						adapterUD01.UD01Data.UD01.Rows[0]["Character03"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["BOMType"];
						adapterUD01.UD01Data.UD01.Rows[0]["Character04"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["MaterialCost"];
						adapterUD01.UD01Data.UD01.Rows[0]["Character05"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["LaborCost"];
						adapterUD01.UD01Data.UD01.Rows[0]["Character06"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["BurdenCost"];
						adapterUD01.UD01Data.UD01.Rows[0]["Character07"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["SubcontractCost"];
						adapterUD01.UD01Data.UD01.Rows[0]["Character08"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["BomSequence"];
						adapterUD01.UD01Data.UD01.Rows[0]["Character09"]=PartArr[PartIndex];
						adapterUD01.UD01Data.UD01.Rows[0]["Character10"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["PartDescription"];
						adapterUD01.UD01Data.UD01.Rows[0]["Number01"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["MaterialBurdenCost"];
						adapterUD01.UD01Data.UD01.Rows[0]["Number02"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["TotalCost"];
						adapterUD01.UD01Data.UD01.Rows[0]["Number03"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["MaterialUnitCost"];
						adapterUD01.UD01Data.UD01.Rows[0]["Number04"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["LaborUnitCost"];
						adapterUD01.UD01Data.UD01.Rows[0]["Number05"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["BurdenUnitCost"];
						adapterUD01.UD01Data.UD01.Rows[0]["Number06"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["SubcontractUnitCost"];
						adapterUD01.UD01Data.UD01.Rows[0]["Number07"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["MaterialBurdenUnitCost"];
						adapterUD01.UD01Data.UD01.Rows[0]["Number08"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["TotalUnitCost"];
						adapterUD01.UD01Data.UD01.Rows[0]["Number09"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["QtyPer"];
						adapterUD01.UD01Data.UD01.Rows[0]["Number10"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["RequiredQty"];
						adapterUD01.UD01Data.UD01.Rows[0]["Number11"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[0].Rows[0]["MaxLevel"];				
						adapterUD01.UD01Data.UD01.Rows[0]["Date01"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["EffectiveDate"];
						adapterUD01.UD01Data.UD01.Rows[0]["ShortChar01"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["MtlRevision"];
						adapterUD01.UD01Data.UD01.Rows[0]["ShortChar02"]=tbl.Rows[a]["PartRev_Plant"].ToString();
						adapterUD01.UD01Data.UD01.Rows[0]["ShortChar03"]=PartParent;
						adapterUD01.UD01Data.UD01.Rows[0]["ShortChar04"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["PartNum"].ToString()+adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["RevisionNum"].ToString()+PartParent+BomSequence;;
						adapterUD01.UD01Data.UD01.Rows[0]["ShortChar05"]=adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["PartNum"].ToString()+adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["RevisionNum"].ToString()+PartArr[PartIndex]+adapterEngWorkBench.EngWorkBenchCostData.Tables[1].Rows[i]["BomSequence"].ToString();
						adapterUD01.UD01Data.UD01.Rows[0]["CheckBox01"]=isApproved;
						adapterUD01.UD01Data.UD01.Rows[0]["Character02"]=isApproved;
						adapterUD01.UD01Data.UD01.Rows[0]["Date02"]= DateTime.Today;
						adapterUD01.Update();							
					}
					else
					{
						PartError += ipPartNum +", ";
					}
					adapterUD01.UD01Data.Clear();
					//adapterUD01.Dispose();
					key+=1;
				}		
			}
			adapterEngWorkBench.EngWorkBenchCostData.Clear();
			adapterEngWorkBench.Dispose();
			//Part=tbl.Rows[a]["Part_PartNum"].ToString();
			//Rev=tbl.Rows[a]["PartRev_RevisionNum"].ToString();
			PartParent="";
			BomSequence="";
		//}			
		// Cleanup Adapter Reference
	}
	adapterUD01.Dispose();

Thank you so much have a nice day!

timshuwy · December 20, 2021, 6:45pm

You might be able to use the UpdateExt method for UD01… UpdateExt does support datasets. If you run UpdateExt with UD01.RowMod = “D” on every record in the dataset, then it should delete all the records.
IF this works… then you would need to:

do a line of code that extracts all the data, including modifying the RowMod to “D”
call UpdateExt with that data that was extracted.
This would basically make this minimal lines of code with no looping (with additional lines for setting up the UpdateExt, and finalizing the work).

thanvanhai · December 21, 2021, 2:41am

@timshuwy
using(var svc = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.UD12SvcContract>()) {

//Build and add the UD12 record
var UD12 = new Ice.Tablesets.UD12Row {
Company = "MyCompany",
Key1 = "Some Value",
Key2 = "AnotherValue",
Key3 = "",
Key4 = "",
Key5 = "",
};

UD12.SetUDField<System.String>("UDField1_c", "Some Value");
UD12.SetUDField<System.String>("UDField2_c", "Some Valueb");
UD12.SetUDField<System.String>("UDField3_c", "Some Valuec");
//to delete the record uncomment this line: //UD12.RowMod = "D";

ds.UD12.Add(UD12);

BOUpdErrorTableset boUpdateErrors = svc.UpdateExt(ref ds, false, true, out errorOccurred);

}

this is your code in another post and i also get the error that the dll file cannot be found?
I understand the UpdateExt function and it will execute according to RowMod which is great but I have not found the dll to run your custom code and I can modify it to suit my company’s requirements.
Thank you so much have a nice day!

jdtrent · December 21, 2021, 8:52pm

You’ll need to add a reference to the UDxx adapter.

In the customization:

Go to Tools/Assembly Reference Manager
Select Add Custom Reference
At bottom right, select All files (.)
Search for the UD01 adapter: Ice.Contracts.BO.UD01.dll and select it.

See what that does for you.

Joe

thanvanhai · December 22, 2021, 2:31am

@jdtrent

i have added dll of table Ice.Contracts.BO.UD01.dll here the error message is
“The type or namespace name ‘Assemblies’ does not exist in the namespace ‘Ice’ (are you missing an assembly reference?)”
I don’t know if I’m confusing In the customization and BPM? here exactly i use customization!
i want to find a solution to delete the data of a table through a condition instead of having to foreach through each line of data the UpdateExt method seems to be a good solution but i can’t find the dll to run my code @timshuwy

Thank you very much! have a nice day!

LarsonSolutions · March 28, 2022, 5:18pm

@thanvanhai
Did you ever resolve this?
Based on what you are discribing, you could create an executive query and populate the date into the cube.
Every week, put on a schedule to reset the table with new data.

Topic		Replies	Views
Clearing a Table Epicor ERP 10 e10 , bpm , baq , ud-table	18	2513	July 30, 2021
Delete Rows UD Table Epicor ERP 10 bpm	10	9601	February 13, 2019
Record Not Found Error Epicor ERP 10 customization	24	5508	June 1, 2021
uBAQ to UD table help Kinetic ERP kinetic , ubaq	21	1282	October 27, 2023
Udashboard for UD tables Epicor ERP 10	18	1991	August 29, 2017

How to quickly delete table ud01?

Related Topics