How to quickly delete table ud01?

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!

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();

}
1 Like

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.

1 Like

@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!

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:

  1. do a line of code that extracts all the data, including modifying the RowMod to “D”
  2. 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).
3 Likes

@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!

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

1 Like

@jdtrent

image

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
:thinking: :grinning: :grinning: :thinking: :wink:
Thank you very much! have a nice day!

@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.