Add an updateable dashboard

Epicor ERP 10
,
15

OK, here’s the core code we use - I’ll detail some things to note after it:

DynamicQueryAdapter yourbaq = new DynamicQueryAdapter(this.oTrans);
DataTable results;
yourbaq.BOConnect();
string baqname = "BAQNAME";
Ice.BO.DynamicQueryDataSet dsQuery = termsAdapter.DynamicQueryData;
if (dsQuery.DynamicQuery.Rows.Count == 0)
{
	Ice.BO.DynamicQueryDataSet dsQDesign = termsAdapter.QueryDesignData;
	DataRow targetRow;
	foreach (DataTable table in dsQuery.Tables)
	{
		foreach (DataRow sourceRow in dsQDesign.Tables[table.ToString()].Rows)
		{
			targetRow = table.NewRow();
			targetRow.ItemArray = sourceRow.ItemArray;
			table.Rows.Add(targetRow);
		}
	}
}
Ice.BO.QueryExecutionDataSet dsBAQ = yourbaq.GetQueryExecutionParameters(dsQuery);
dsBAQ.ExecutionParameter[0].ParameterID = "YOURPARAMETERNAME";
dsBAQ.ExecutionParameter[0].IsEmpty = false;
dsBAQ.ExecutionParameter[0].ParameterValue = VALUE AS A STRING;
dsBAQ.AcceptChanges();
yourbaq.Execute(dsQuery, dsBAQ);
if (yourbaq.QueryResults != null && yourbaq.QueryResults.Tables.Count > 0)
{
	results = yourbaq.QueryResults.Tables["Results"];
}
else
{
	results = new DataTable();
}
EpiDataView edv = (EpiDataView)oTrans.EpiDataViews[baqName];
if (!(edv != null))
{
	edv = new EpiDataView();
	oTrans.Add(baqName, edv);
}
edv.dataView = results.DefaultView;

This is the version you need to use to have the resulting EpiDataView etc updateable. Using ExecuteByID doesn’t allow you to update. Calling

yourbaq.Update(yourbaq.DynamicQueryData, results.DataSet, false)

writes back your changes to the uBAQ assuming everything is working as it should.

Obviously you need to persist the adapter rather than declaring it and disposing.

What we tend to do is set an UltraGrid datasource to the DataTable directly via code, because it opens up some possibilities that we make use of, although it does mean you lose some EpiMagic. But if you get this code in place and then close and re-open your form in customzation mode then you’ll find the BAQ appears in the standard listing of EpiDataViews and you can EpiBind it to a grid (or the fields to anything else) exactly as any normal view and it will behave in the same way, including being updateable.

You do need to call it again with new parameter(s) when whatever you want to keep it synced with changes, of course. Again, we’ve streamlined this a bit with some wrappers, keeping the parameter names and values in a Dictionary, but it works as well for simple cases just to call the same code above again.

If you look at the trace log when using the DynamicQuery.Execute method rather than ExecuteByID then it shows a frightening amount of data being passed every time. But we’ve found it’s actually faster overall in spite of that, compared to ExecuteByID.

This code has survived without needing attention from 10.1.400 to 10.2.200 so far, too.

8 Likes