UD Table as child for new data entry only

N8Brown1 · November 15, 2018, 5:02pm

I have a UD Table setup as a child to Supplier Maintenance. I am trying to use it to enter new data records using the VendorID as Key1 and the wizard generated auto-incrementing counter as Key5. I have two date fields and a combo box bound via a customization to three of the UD table fields (ShortChar01, Date01, and Date02). The problem that I am having is that whenever I search a select a supplier record, the custom fields auto populate with the first record from the UD table. How can I stop this from getting the data and displaying it?

I have tried commenting out the GetUD39Data method pictured below, and it keeps the fields from pulling data in. The problem is that when I try to create a new record, I get an error message when I hit save that says the it’s a duplicate entry of an existing record.

Duplicate%20Record%20Error

Can anyone help? I can provide screenshot of the code I am working with if needed.

Thanks in advance!

hkeric.wci · November 15, 2018, 5:18pm

Your keys must be unique. I see you are using VendorID as Key1 in your Get that’s fine, are you certain you are not attempting to create another record? if so you need another Unique Key, it is very common to use Key5 as a Counter Incrementer, to remain Unique in your GetaNewUD39Record method.

There should already be a boiler-plate code, make sure you don’t have it disabled.

Example:

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

editRow["Key5"] = lineNum.ToString();

N8Brown1 · November 15, 2018, 5:43pm

Yeah I am using the VendorID as Key1 and the counter as Key5 which is in the GetNewUD39Record method:

private void GetNewUD39Record()
{
	DataRow parentViewRow = this._edvVendorDetail.CurrentDataRow;
	// Check for existence of Parent Row.
	if ((parentViewRow == null))
	{
		return;
	}
	if (this._ud39Adapter.GetaNewUD39())
	{
		string vendorid = parentViewRow["VendorID"].ToString();

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

		// Set initial UD Key values
		DataRow editRow = this._ud39Adapter.UD39Data.UD39.Rows[(rowCount - 1)];
		editRow.BeginEdit();
		editRow["Key1"] = vendorid;
		editRow["Key2"] = string.Empty;
		editRow["Key3"] = string.Empty;
		editRow["Key4"] = string.Empty;
		editRow["Key5"] = lineNum.ToString();
		editRow.EndEdit();

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

But for whatever reason, whenever I hit save after entering the data in the fields for the new record, I get the error stating that it is a duplicate.

ckrusen · November 15, 2018, 6:10pm

For what its worth, should be using VendorNum instead of VendorID as the key. A vendor’s VendorID can be changed.

N8Brown1 · November 15, 2018, 6:15pm

Yeah, that’s fine and makes sense, but right now I’m more worried about getting these fields to be used for pushing data to the UD Table, not displaying displaying it. I feel like I’m close but I just can’t get passed the Duplicate Entry error. Any thoughts on that?

ckrusen · November 15, 2018, 6:18pm

Do you need multiple records per supplier? If not, just make UD fields in the Vendor table.

Or if you really want to use the UD table, remove the autonumber field from key5. having just one record in UD39 per vendor.

N8Brown1 · November 15, 2018, 6:27pm

Yes, we are looking to have the ability to add multiple records per supplier, but we do not want to edit the record after it is created and saved.

Basically we are trying to use the three custom fields as data entry fields that clear out after we’ve entered the data and saved it to UD table. We would then use a BAQ report or dashboard to retrieve the data as needed.

Our other option would be to add a button to Supplier Maintenance that launches the UD table menu item and we do the entry in there, but for that to work, we would need to add code that creates a variable that automatically increments, that we could tie to one of the key fields, thereby creating a unique key value for each record.

N8Brown1 · November 15, 2018, 6:28pm

I am currently playing around with adding some code via a form load event that could possible handle that. I just have to figure out which methods to call and the syntax for that.

ckrusen · November 15, 2018, 6:31pm

I think I get it. Those controls added to the customization shouldn’t be bound to UD39 table, but rather have their values added to a new record in UD39, upon some event - like saving or a button press. That right?

N8Brown1 · November 15, 2018, 6:32pm

Yes, that’s right. I’m just looking to create new entries to the UD39 table that I can link back to a specific vendor.

ckrusen · November 15, 2018, 6:43pm

Do you need to prevent users from changing the previously created records in UD39?

If not, maybe add a tab to Vendor entry with an updatable dashboard that filters to the vendor.

That would show all the U39 records that match that vendor, and allow you add new records as needed.

N8Brown1 · November 15, 2018, 6:44pm

Yeah, we would want to prevent any changes to previously created records.

ckrusen · November 15, 2018, 6:47pm

Do you need to hide them as well?

If not, you could add a BPM that only allows creating the UD39 Records and prevents changes to existing ones.

N8Brown1 · November 20, 2018, 2:50pm

I have found a solution that works. I determined that the following code was populating the fields with data from the UD table after a Supplier was selected:

private void VendorDetail_DataView_ListChangedForUD39(object sender, ListChangedEventArgs args)
{
	// ** add ListChanged event handler
	string vendornum = VendorDetail_DataView[0]["VendorNum"].ToString();
	string vendorid = VendorDetail_DataView[0]["VendorID"].ToString();
	GetUD39Data(vendornum, vendorid, string.Empty, string.Empty, string.Empty);
			
}

By commenting out this function I was able to select a supplier without the fields being populated with data from the UD table. The next thing I did was to add the following code to the GetNewUD39Record() function:

	string vendnum = VendorDetail_DataView[0]["VendorNum"].ToString();
	string vendid = VendorDetail_DataView[0]["VendorID"].ToString();
	GetUD39Data(vendnum, vendid, string.Empty, string.Empty, string.Empty);

This appeared to allow the function to get the data it needed in order to create a new record without pre-populating the custom fields because the GetUD39Data() function doesn’t run prior to creating a new record. the final working code looked like this:

private void GetNewUD39Record()
{
	string vendnum = VendorDetail_DataView[0]["VendorNum"].ToString();
	string vendid = VendorDetail_DataView[0]["VendorID"].ToString();
	GetUD39Data(vendnum, vendid, string.Empty, string.Empty, string.Empty);

	DataRow parentViewRow = this._edvVendorDetail.CurrentDataRow;
	// Check for existence of Parent Row.
	if ((parentViewRow == null))
	{
		return;
	}
	if (this._ud39Adapter.GetaNewUD39())
	{
		string vendornum = parentViewRow["VendorNum"].ToString();
		string vendorid = parentViewRow["VendorID"].ToString();

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

		// Set initial UD Key values
		DataRow editRow = this._ud39Adapter.UD39Data.UD39.Rows[(rowCount - 1)];
		editRow.BeginEdit();
		editRow["Key1"] = vendornum;
		editRow["Key2"] = vendorid;
		editRow["Key3"] = string.Empty;
		editRow["Key4"] = string.Empty;
		editRow["Key5"] = lineNum.ToString();
		editRow.EndEdit();
		
		//MessageBox.Show(lineNum.ToString());
		// Notify that data was updated.
		this._edvUD39.Notify(new EpiNotifyArgs(this.oTrans, (rowCount - 1), this._edvUD39.Column));
	}
}

hkeric.wci · November 20, 2018, 4:35pm

Great you got it, I remember I had to make sure the row’s existed in my ListChanged Events, because it was firing too soon sometimes without the key-values such as VendorNum existing yet. Probably not your issue, you got it after-all…

But for reference of thread: You might check the Clear Button comment:

private void QuoteDtl_AfterRowChangeForUD05(EpiRowChangedArgs args)
{
	if (args.CurrentRow > -1)
	{
		// ** add AfterRowChange event handler
		string quotenum = args.CurrentView.dataView[args.CurrentRow]["QuoteNum"].ToString();
		string quoteline = args.CurrentView.dataView[args.CurrentRow]["QuoteLine"].ToString();
		GetUD05Data(string.Empty, string.Empty, quotenum, quoteline, string.Empty);
	}
}

private void QuoteDtl_DataView_ListChangedForUD05(object sender, ListChangedEventArgs args)
{
	// Protects us from the Clear button or when the dataView does not exist
	if ((QuoteDtl_Row.dataView.Count - 1) < QuoteDtl_Row.Row)
	{
		return;
	}

	if (QuoteDtl_Row.Row > -1 && QuoteDtl_Row.dataView.Count == 1)
	{
		// ** add ListChanged event handler
		string quotenum = QuoteDtl_Row.dataView[QuoteDtl_Row.Row]["QuoteNum"].ToString();
		string quoteline = QuoteDtl_Row.dataView[QuoteDtl_Row.Row]["QuoteLine"].ToString();
		GetUD05Data(string.Empty, string.Empty, quotenum, quoteline, string.Empty);
	}
}