New to UD tables

Epicor ERP 10
1

Everything seems to work but is this the RIGHT way to do this? Any caveats to keep in mind?

                UD39Adapter a = new UD39Adapter(oTrans);	
		a.BOConnect();
	        string pn = "mypart";
		string job = "myjob";
		if(a.GetaNewUD39())
		{
			a.UD39Data.Tables[0].Rows[0]["Key1"] = pn;
			a.UD39Data.Tables[0].Rows[0]["Key2"] = job;
			a.UD39Data.Tables[0].Rows[0]["Key3"] = Guid.NewGuid().ToString();
			a.UD39Data.Tables[0].Rows[0]["Character01"] = "MyChar";
			a.Update();
		}
		else MessageBox.Show("Failed to create record");

image.png725×55 1.81 KB

How do I delete a record?

2

Essentially yes, but holy moly, using a GUID as a key field is going to make searching for that record a complete nightmare…

3 Likes
3

just run a.DeleteByID(Key1,Key2,Key3,Key4,Key5);

1 Like
4

Ooooooh, I have to match ALL key Fields? Many thanks for that tidbit, you’re right, that would be craziness!

1 Like
5

You can getrows with partial keys

6

For some reason I thought that had to ensure that all records had unique set of keys. Since PN and JOB weren’t unique (there would be multiple records) I was thinking I needed the GUID to be the unique field on every record - but I surely don’t want to HAVE to enter a GUID to grab a record.

7

You are right, you need to have unique keys. The way you did this really won’t cause you any issues and you just use GetRows, as @josecgomez mentioned to retrieve records.

1 Like
8

Additionally,all UD tables will create unique SysRowID’s automatically, so if you do need a GUID for a record, you have one available.

1 Like
9

Just to be clear here:

Do I NEED to add my own GUID to a key field to ensure unique keys?

10

Key1-5 (Combined) need to be unique for each record.
So you can have a record like
Key1=‘hi’, Key2=’’
Key1=‘hi’ Key2=‘how’
Those are two different records and valid… but you cannot have 2 of the same records with all the same 5 keys

11

OK so I will need a GUID in a key field in my case. I can see the pain - I’ll have to GetRows() to find my guid of a record, then use it DeleteByID(). Hmm maybe I should think this one through some more

12

Alternatively, you can use other fields to store information about the record in the case where you might have similar “types” of records but need to maintain uniqueness for each.
Key fields are just that: keys. If you can get away with generalized keys and store more unique values in non-key fields, you will be much better off.

1 Like
13

If you have multiple rows by key, you may want to look at one of the UD10XA tables, the ones with a parent and child records. The same restriction applies to the header and you have five additional child keys but it may work out better for you.

Mark W.

14

This is the code that the wizard adds to get a unique number for Key5 when addind a UD table as a child table within a customization. In this case, the UD03 added as a child to the ShipDtl table.

private void GetNewUD03Record()
{
	DataRow parentViewRow = this.edvShipDtl.CurrentDataRow;
	// Check for existence of Parent Row.
	if ((parentViewRow == null))
	{
		return;
	}
	if (parentViewRow["PackLine"].ToString() == "0")
	{
		this.oTrans.Update();
		return;
	}
	if (this._ud03Adapter.GetaNewUD03())
	{
		string packnum = parentViewRow["PackNum"].ToString();
		string packline = parentViewRow["PackLine"].ToString();
		string ordernum = parentViewRow["OrderNum"].ToString();
		int quantity = Convert.ToInt32(parentViewRow["OurInventoryShipQty"]) + Convert.ToInt32(parentViewRow["OurJobShipQty"]);

		// Get unique row count id for Key5
		int rowCount = this._ud03Adapter.UD03Data.UD03.Rows.Count;
		int lineNum = rowCount;
		bool goodIndex = false;
		while ((goodIndex == false))
		{
			// Check to see if index exists
			DataRow[] matchingRows = this._ud03Adapter.UD03Data.UD03.Select("Key5 = \'" + lineNum.ToString() + "\'");
			if ((matchingRows.Length > 0))
			{
				lineNum = (lineNum + 1);
			} else
			{
				goodIndex = true;
			}
		}

		// Set initial UD Key values
		DataRow editRow = this._ud03Adapter.UD03Data.UD03.Rows[(rowCount - 1)];
		editRow.BeginEdit();
		editRow["Key1"] = packnum;
		editRow["Key2"] = packline;
		editRow["Key3"] = ordernum;
		editRow["Key4"] = string.Empty;
		editRow["Key5"] = lineNum.ToString();
		editRow["Number01"] = quantity;
		editRow.EndEdit();

		// Notify that data was updated.
		this._edvUD03.Notify(new EpiNotifyArgs(this.oTrans, (rowCount - 1), this._edvUD03.Column));
	}
}
15

I had considered this, using a record number a key. Unless I am misinterpreting the code, it seems like we would have to iterate every record in the table until we found the next available line. That seems really heavy. Maybe either using a flatfile with last record used or a UDCode would be more efficient. Any thoughts?

16

No dont use a flat file that’s crazy. Just use a guid or a SQL Sequence.

17

What about a BAQ that just pulls the highest line number from that field?

18

That would be fine

1 Like