Getting Data from UDxx Table When Not All Five Keys Are Known

Kinetic ERP
,
1

Hello.
I am attempting to retrieve a value from our UD23 table, but the developer who designed it used a GUID in Key4, so I am unable to use GetByID. So, I am now trying to use a ProLoadSearchFilter, but it is not successfully finding the record. Here is the code I have right now:

private decimal GetUD23Num(string colName, int ordNum, int cusNum, string pproID)
{
	bool ud23OK = false;
	decimal colValue = 0;
	if(colName == "") return colValue;
	string ordStr = ordNum.ToString();
	string cusStr = cusNum.ToString();
	try
	{
		adp23 = new UD23Adapter(this.oTrans);
		adp23.BOConnect();
		int invNum = FetchInvoiceNumFromOrder(ordNum);
		string invStr = invNum.ToString();
		SearchOptions ud23Opts = new SearchOptions(SearchMode.AutoSearch);
		ud23Opts.DataSetMode = DataSetMode.RowsDataSet;
		ud23Opts.SelectMode = SelectMode.SingleSelect;
		ud23Opts.PreLoadSearchFilter = String.Format("Key1 = '{0}' AND Key2 = '{1}' AND Key3 = '{2}'",ordStr,invStr,cusStr);
		adp23.InvokeSearch(ud23Opts);
		ud23OK = (adp23.UD23Data.UD23.Rows.Count > 0) ? true:false;
	}
	catch (Exception ex) {}
	finally
	{
		if(ud23OK == true)
		{
			colValue = Convert.ToDecimal(adp23.UD23Data.UD23[0][colName]);
		}
		adp23.Dispose();
	}
	return colValue;
}

If I use Visual Studio to debug the code, my ud23OK boolean is always returning false. Is it possible I didn’t code the Rows.Count conditional correctly?

2

I haven’t worked a ton using the InvokeSearch from an adapter, but looking at some of my code, it’s set up a little different. Give the below a try:

private decimal GetUD23Num(string colName, int ordNum, int cusNum, string pproID)
{
	bool ud23OK = false;
	decimal colValue = 0;
	if(colName == "") return colValue;
	string ordStr = ordNum.ToString();
	string cusStr = cusNum.ToString();
	try
	{
		adp23 = new UD23Adapter(this.oTrans);
		adp23.BOConnect();
		int invNum = FetchInvoiceNumFromOrder(ordNum);
		string invStr = invNum.ToString();
		SearchOptions ud23Opts = new SearchOptions(SearchMode.AutoSearch);
		ud23Opts.DataSetMode = DataSetMode.RowsDataSet;
		
		ud23Opts.NamedSearch.WhereClauses.Add("UD23", String.Format("Key1 = '{0}' AND Key2 = '{1}' AND Key3 = '{2}'",ordStr,invStr,cusStr));

		adp23.InvokeSearch(ud23Opts);
		ud23OK = (adp23.UD23Data.UD23.Rows.Count > 0) ? true:false;
	}
	catch (Exception ex) {}
	finally
	{
		if(ud23OK == true)
		{
			colValue = Convert.ToDecimal(adp23.UD23Data.UD23[0][colName]);
		}
		adp23.Dispose();
	}
	return colValue;
}
3

Thanks so much for the good effort, Kevin. If anything, you encouraged me to keep trying other methods. Here’s what I ended up with:

private decimal GetUD23Num(string colName, int ordNum, int cusNum, string pproID)
{
	decimal colValue = 0;
	bool recSelected = false;
	if(colName == "") return colValue;
	string ordStr = ordNum.ToString();
	string cusStr = cusNum.ToString();
	int invNum = FetchInvoiceNumFromOrder(ordNum);
	string invStr = invNum.ToString();
	
	string whereClause = String.Format("Key1 = '{0}' AND Key2 = '{1}' AND Key3 = '{2}'",ordStr,invStr,cusStr);
	DataSet dsUD23 = SearchFunctions.listLookup(this.oTrans, "UD23Adapter", out recSelected, false, whereClause);
	if(recSelected)
	{
		DataRow adapterRow = dsUD23.Tables[0].Rows[0];
		colValue = Convert.ToDecimal(adapterRow[colName]);
	}
	return colValue;
}

Cheers,
Tony G.
1 Like