Custom Code to Get Last Value From BAQ into Customization

Kinetic ERP
1

I am trying to pull the last value from a BAQ into a customization. I think this code should work, or something close to it. The return value line is wrong, and I am not sure the correct syntax.

private int getLastUNID()
{
		
		DynamicQueryAdapter dqa = new DynamicQueryAdapter(oTrans);
		dqa.BOConnect();		
		QueryExecutionDataSet qeds = dqa.GetQueryExecutionParametersByID("LastCPIUNID");
		qeds.ExecutionParameter.Clear();

		dqa.ExecuteByID("LastCPIUNID", qeds);
		if (dqa.QueryResults.Tables["Results"].Rows.Count > 0)
		{
			return dqa.QueryResults.Tables["Results"].[Calculated_LastUNID].Value;
			oTrans.NotifyAll();
		}
		else
		{
		MessageBox.Show("No Previous Records!");
			return 1;
		}
}
2

Try this one (messy but it should work):

return dqa.QueryResults.Tables["Results"].Rows[dqa.QueryResults.Tables["Results"].Rows.Count-1]["Calculated_LastUNID"].Value
1 Like
3

I typically use a using statement, as it will dispose when complete. Also not sure what the oTrans.NotifyAll() is doing for you. For your return, you’ll need to get the row. Something like this

(int)dqa.QueryResults.Tables["Results"].Rows[0]["Calculated_LastUNID"]

note the cast. You’ll get a compile error otherwise because the compiler only knows the property is an object, but not anything more specific since “Calculated_LastUNID” isn’t evaluated until runtime.

Here’s one I just did for a BPM attached to Ice.BO.UD03.GetaNewUD03:

string baqName = "Form_GetNext";

using (var svc = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.DynamicQuerySvcContract>(Db))
{
    //Get parameter object from the BAQ that's about to be called.
    var qeds = svc.GetQueryExecutionParametersByID(baqName);
    var baqds = svc.ExecuteByID(baqName,qeds);
    ttUD03[0].Key1 = baqds.Tables["Results"].Rows[0]["Calculated_Next"].ToString();
}

The BAQ just grabs the max of all UD03.Key1’s and adds one.

For custom forms, it’s pretty similar, here’s using the service:

using (var svc = WCFServiceSupport.CreateImpl<Ice.Proxy.BO.DynamicQueryImpl>
				((Ice.Core.Session)oTrans.Session,
				Epicor.ServiceModel.Channels.ImplBase<Ice.Contracts.DynamicQuerySvcContract>.UriPath)
			)
		{
			var qeds = new QueryExecutionDataSet();
			DataSet baq = svc.ExecuteByID("Form_List_ActiveEmployees", qeds);
			edvEmployees.dataView = baq.Tables[0].DefaultView;
			oTrans.Add("Employees", edvEmployees);
		}

And using the adapter. This example is passing a parameter into the qeds manually.

using (var adapter = new DynamicQueryAdapter(oTrans))
		{
			adapter.BOConnect();

			var qeds = new QueryExecutionDataSet();
			qeds.ExecutionParameter.AddExecutionParameterRow("Job", job, "nvarchar", false, Guid.Empty, "");
		
			adapter.ExecuteByID("Form_Jobs_Issues", qeds);
			var results = adapter.QueryResults.Tables["Results"].Rows;
		}
1 Like
4

Sort Your BAQ and Take first value only :grinning:

1 Like
5

6

My BAQ actually only returns one row the max of number04. So I shouldn’t need to worry about first or last, or min or max, as there is only one record.

7

Thank you all for your input. After some trial and error, I got this to work great!

private int getLastUNID()
{
using (var svc = WCFServiceSupport.CreateImpl<Ice.Proxy.BO.DynamicQueryImpl>((Ice.Core.Session)oTrans.Session,Epicor.ServiceModel.Channels.ImplBase<Ice.Contracts.DynamicQuerySvcContract>.UriPath))
		{
			var qeds = new QueryExecutionDataSet();
			DataSet baq = svc.ExecuteByID("LastCPIUNID", qeds);
			if (Convert.ToString(baq.Tables["Results"].Rows[0]["Calculated_LastUNID"]) == "")
				{			
				return 0;
				}
			else
			{
			return (int)baq.Tables["Results"].Rows[0]["Calculated_LastUNID"];
			}
		}
}