Hi Lawson,
Try this:
yourbaq.Update(baqName,results.DataSet);
Ross
How are you using the UBAQ in your solution? Is this a grid in a customization that retrieves data that is updated and has a save button? If so, can you add it to a click event?
Ross
Love a DynamicQuery use, even now!
You say you’re putting this in a grid. So it’s in a customisation of an existing screen?
If your EpiDataView is a Script-level variable, the logical thing to do, usually, is to hook into the existing form “save”, and do the update as @rosshughes suggests when that happens. That’s what the user expects.
1 Like
Ok, to try and answer both, in Project Entry I used the Sheet Wizard to add a sheet, I placed an EpiUltraGrid in it and then pieced together the UBAQ and the DQA
I have it firing at Initialization, to get that part to work I had to hardwire a value for the ProjectID and then change the ProjectID if the Row count was not “0”
string projID = "0"; // <<< Added this part and the "if" statement so it would create the EpiDataView below on form load
if(edvProject.dataView.Count != 0)
{
projID = edvProject.dataView[edvProject.Row]["ProjectID"].ToString();
}
I have a button on the screen that after the ProjectID is present on the main sheet the user can click the button and it will then load the data into the grid (the data only loads to the grid if the grid is hardwired to the results, if I use EpiBinding from the DataView the grid doesn’t populate/refresh)
if (yourbaq.QueryResults != null && yourbaq.QueryResults.Tables.Count > 0)
{
results = yourbaq.QueryResults.Tables["Results"];
epiUltraGridC1ShipID.DataSource = yourbaq.QueryResults.Tables["Results"];
Below is the full code that is fired off at Initialization and at button click
public void ShipLinesMisc()
{
DynamicQueryAdapter yourbaq = new DynamicQueryAdapter(this.oTrans);
DataTable results;
yourbaq.BOConnect();
string baqName = "TWG_PJ_JOBS_DQA";
Ice.BO.DynamicQueryDataSet dsQuery = yourbaq.DynamicQueryData;
yourbaq.GetByID("TWG_PJ_JOBS_DQA");
if (dsQuery.DynamicQuery.Rows.Count == 0)
{
Ice.BO.DynamicQueryDataSet dsQDesign = yourbaq.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);
}
}
}
string projID = "0"; // <<< Added this part and the "if" statement so it would create the EpiDataView below on form load
if(edvProject.dataView.Count != 0)
{
projID = edvProject.dataView[edvProject.Row]["ProjectID"].ToString();
}
//MessageBox.Show("mgs 3 " + projID);//////remove later
Ice.BO.QueryExecutionDataSet dsBAQ = yourbaq.GetQueryExecutionParameters (dsQuery);
dsBAQ.ExecutionParameter[0].ParameterID = "PROJECTID";
dsBAQ.ExecutionParameter[0].IsEmpty = false;
dsBAQ.ExecutionParameter[0].ParameterValue = projID.ToString();
dsBAQ.AcceptChanges();
yourbaq.Execute(dsQuery, dsBAQ);
if (yourbaq.QueryResults != null && yourbaq.QueryResults.Tables.Count > 0)
{
results = yourbaq.QueryResults.Tables["Results"];
epiUltraGridC1ShipID.DataSource = yourbaq.QueryResults.Tables["Results"];
MessageBox.Show("mgs 4 " + projID);//////remove later
}
else
{
results = new DataTable();
epiUltraGridC1ShipID.DataSource = null;
MessageBox.Show("mgs 5 " + projID);//////remove later
}
EpiDataView edv = (EpiDataView)oTrans.EpiDataViews["AAA_PJ_JOBS_DQA"];
if (!(edv != null))
{
edv = new EpiDataView();
oTrans.Add("AAA_PJ_JOBS_DQA", edv);
edv.dataView = results.DefaultView;
MessageBox.Show("mgs 6 " + projID);//////remove later
}
else
{
edv.dataView = results.DefaultView;
MessageBox.Show("mgs 7 " + projID);//////remove later
}
}
On the grid that loads there is one column that is editable and the idea is that the user can enter values into this column and then be able to save the changes on save
P.S. I live in Australia so thus the odd hours that I reply
Ok, I’ve found what I was doing wrong and now have it working…
I needed to declare the following in the Script area
DynamicQueryAdapter yourbaq;
DataTable results;
With that done, I could then use them outside of the private void (this may seem common sense to seasoned users but I’m still cutting my teeth on customizations)
I then was able to add the suggestion from @rosshughes
I got the idea to declare them from this post
Thank you @dhewi for the bones of the DQA
You can hook into the BeforeClose event and check then, but it can be quite tricky getting it 100% right whether anything has been changed.
I’ve always felt that EpiDataView must have a property to call on to check whether the data is “dirty”, but nothing has proved as reliable as I’ve wanted.
The best results I’ve had have been manually setting a Boolean variable when the user changes the data - you can toggle it each way on a DataColumnChange event and the save, then check it on form close.
Ok, thanks for the direction, I’ll let you know how I go!!
Hello @dhewi and anyone else that has worked with DQA’s and UBAQ’s
I’ve got my grid displaying correctly and if I manually update the editable grid cells and hit save the database updates as expected…
The problem I’m having now is, I’ve created a button that updates the editable cells in all highlighted (selected) rows to a value from another field
The grid cell values change to the expected values but when I click save (which runs yourbaq.Update(“uBAQName”,results.DataSet); ) the database doesn’t update and the grid rolls back to the previous values
I’ve studied the grid and noticed that the little pencil icon appears both when I manually update and when I update with the button click
Any hints/ideas would be much apreciated
Ok, I sorted my own problem, I had to set the RowMod to “U” for the particular row… I thought I had tried this before I posted, but I mustn’t have been holding my mouth the right way
1 Like
Glad you sorted it. It’s worth bearing in mind that it usually proves more reliable working with the EpiDataView and letting the changes show up in the grid than doing the reverse, wherever possible. And then you can use BeginEdit / EndEdit to sort the RowMod.
Thanks Daryl, I haven’t had much luck with the EpiDataView’s, to get my results to display I’ve had to use epiUltraGridC1ShipID.DataSource = results;
I know how to bind to the EDV in the grid properties but the results only show up if there is data present on the first query run, and then disappear if I rerun the query, I know there is data there on the rerun because if I hardwire the grid to the results the grid populates
To get a bit of the EpiMagic, I’ve used the properties to bind the EDV to the grid and I’ve also hard-wired it too, this lets me hide columns etc
Do you have any tips on getting refreshed results to display in grids via EDV’s???
I’ve leveraged DynamicQuery a lot by now, and pulled the repeatable bits out into a class I declare in the customisation code. Maybe I should just share that with you!
There are two ways to bind the query results to the grid - the native Infragistics way, which is via the DataSource, and the Epicor way, which is the EpiBinding. You don’t need to do both.
If you do the EpiBinding, you’ll get all the EpiMagic just as if it was all native, and updates etc will work as normal. Usually you can update the data and it will show, and there are notifications to trigger if needed.
The only reason to use the DataSource instead is if you want to manually format the grid, because once the EpiMagic is involved you can’t do that - Epicor just overwrites whatever you do. But in that case you need to also manually handle keeping the grid and the EpiDataView in sync with each other, which is obviously extra work.
2 Likes
Thank you for your time Daryl,
I’m interested in learning how to do the following
and
Manually formating grids gets a bit laborious at times especially if there are a lot of columns
I would be interested in this if you are willing/able to share the class. We have a number of UBAQs with input parameters and I’ve been able to wire into grids thanks to the great examples you have previously shared (thanks for those examples btw!)
@LBARKER, @tanner - I’m happy to share, but might be a day or two, because there’s too much extra complication in what I use and it would confuse things. I’ve been meaning to create a stripped-back version and this might be a useful spur.
1 Like
@LBARKER, @tanner - here you go. It might be a bit rough around the edges because I’ve rewritten this from scratch, leaving out a lot of the bits that I’ve included over time which are convenient for me but confuse the issue for anyone else. But I’ve tested the basics, and at a minimum it’ll give you the general idea.
There’s probably a lot of code in here that could be done better and more efficient ways. It would be nice to have feedback if so!
The main feature is that you can declare classes of your own before the Script part of the customisation code.
You need some extra “usings” and the DynamicQuery assemblies:
using Ice.Core;
using Infragistics.Win.UltraWinToolbars; // for manual button addition
using Infragistics.Win.UltraWinGrid;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
Then the class is:
class M8DynQuery
{
private string baqName;
private Ice.Adapters.DynamicQueryAdapter adptr;
private Ice.BO.DynamicQueryDataSet ds;
private Ice.BO.QueryExecutionDataSet dsBAQ;
private EpiDataView edv;
private DataTable results;
private EpiUltraGrid grid;
private Dictionary<string, string> baqParams;
private Dictionary<string, string> defParams;
private Dictionary<string, string> lastParams;
private bool changedparams;
private Dictionary<string, Control> paramControls;
private string[] keynames;
private string[] keys;
private EpiTransaction oTrans;
private Control maincontrol;
private bool gotBAQ;
public M8DynQuery (string baqname, EpiTransaction trans, Control sheet, EpiUltraGrid ultragrid, string[] paramnames, string[] paramdefaults)
{
baqName = baqname;
oTrans = trans;
adptr = new Ice.Adapters.DynamicQueryAdapter(oTrans);
adptr.BOConnect();
edv = new EpiDataView();
results = new DataTable();
edv.dataView = results.DefaultView;
oTrans.Add(baqName, edv);
changedparams = true;
maincontrol = sheet;
gotBAQ = false;
if (paramnames != null && paramdefaults != null && paramnames.Length == paramdefaults.Length)
{
baqParams = new Dictionary<string, string>();
defParams = new Dictionary<string, string>();
for (int i = 0; i < paramnames.Length; i++)
{
baqParams[paramnames[i]] = paramdefaults[i];
defParams[paramnames[i]] = paramdefaults[i];
}
GetParamControls();
}
if (ultragrid != null)
{
grid = ultragrid;
grid.EpiBinding = edv.ViewName;
grid.UpdateMode = UpdateMode.OnCellChange;
}
GetData(false);
MatchDropdowns();
}
public Ice.Adapters.DynamicQueryAdapter Adapter()
{
return adptr;
}
public EpiDataView EpiDataView()
{
return edv;
}
public DataTable DataTable()
{
return results;
}
public void Clear()
{
adptr.ClearDynamicQueryData();
}
public DataRowView CurrentDataRow()
{
if (edv == null || edv.Row < 0)
{
return null;
}
else
{
return edv.dataView[edv.Row];
}
}
public void GetData(bool getparams = true)
{
//MessageBox.Show("get " + baqName);
string ep = "Start";
try
{
oTrans.PushStatusText("Getting data for " + baqName + "...",true);
if (getparams) { ParamsFromControls(); }
ep = "1";
if (baqName != string.Empty)
{
if (!gotBAQ)
{
if (adptr.GetByID(baqName)) { gotBAQ = true; }
}
if (!gotBAQ)
{
return;
}
ep = "2";
if (!(ds != null)) { ds = adptr.DynamicQueryData; }
if (ds.DynamicQuery.Rows.Count == 0)
{
ep = "3";
Ice.BO.DynamicQueryDataSet dsQDesign = adptr.QueryDesignData;
DataRow targetRow;
foreach (DataTable table in ds.Tables)
{
foreach (DataRow sourceRow in dsQDesign.Tables[table.ToString()].Rows)
{
targetRow = table.NewRow();
targetRow.ItemArray = sourceRow.ItemArray;
table.Rows.Add(targetRow);
}
}
}
ep = "4";
if (!(dsBAQ != null)) { dsBAQ = adptr.GetQueryExecutionParameters(ds); }
if (baqParams != null)
{
ep = "5";
int i = 0;
foreach (KeyValuePair<string, string> p in baqParams)
{
bool empty = false;
string key = p.Key;
string val = p.Value;
dsBAQ.ExecutionParameter[i].ParameterID = key;
dsBAQ.ExecutionParameter[i].IsEmpty = empty;
dsBAQ.ExecutionParameter[i].ParameterValue = val;
i++;
}
dsBAQ.AcceptChanges();
ep = "6";
lastParams = new Dictionary<string,string>(baqParams);
}
ep = "execute";
adptr.ExecuteByID(baqName, dsBAQ);
ep = "7";
if (adptr.QueryResults != null && adptr.QueryResults.Tables.Count > 0)
{
results = adptr.QueryResults.Tables["Results"];
}
else
{
results = new DataTable();
}
ep = "8";
if (!(edv != null)) { edv = (EpiDataView)oTrans.EpiDataViews[baqName]; }
if (!(edv != null))
{
edv = new EpiDataView();
oTrans.Add(baqName, edv);
}
ep = "9";
if (edv.dataView != results.DefaultView) { edv.dataView = results.DefaultView; }
ep = "10";
if (grid != null && grid.DataSource != results) { grid.DataSource = results; }
//edv.EpiDataChanged();
//edv.Notify(new EpiNotifyArgs(oTrans,0,EpiTransaction.NotifyType.Initialize));
//if (grid != null) { grid.EpiBinding = edv.ViewName; grid.Refresh(); }
changedparams = false;
}
}
catch (Exception e)
{
MessageBox.Show("Data Error " + baqName + " (" + ep + "):" + System.Environment.NewLine + e.Message);
}
finally
{
oTrans.PopStatus();
}
}
public void RefreshData()
{
if (baqParams != null && lastParams != null)
{
bool matched = true;
foreach (KeyValuePair<string,string> kp in baqParams)
{
if (!kp.Value.Equals(lastParams[kp.Key]))
{
matched = false;
break;
}
}
if (!matched) { GetData(); }
}
else
{
GetData();
}
}
public bool Save()
{
bool ret = true;
try
{
oTrans.PushStatusText("Saving " + baqName + "...",true);
DataSet retds = adptr.Update(adptr.DynamicQueryData, results.DataSet, false);
if (retds != null && retds.Tables.Count > 0 && retds.Tables["Errors"] != null && retds.Tables["Errors"].Rows.Count > 0)
{
MessageBox.Show("Save Error " + baqName + System.Environment.NewLine + retds.Tables["Errors"].Rows[0]["ErrorText"].ToString());
ret = false;
}
else
{
GetData();
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Save Error " + baqName);
ret = false;
}
finally
{
oTrans.PopStatus();
}
return ret;
}
#region Parameters
public string[] ParamNames()
{
return baqParams.Keys.ToArray();
}
public Dictionary<string, string> Params()
{
return baqParams;
}
private void GetParamControls()
{
Control c = maincontrol;
while (c.Parent != null) { c = c.Parent; }
AddParamControl(c);
}
private void AddParamControl(Control parentcontrol)
{
foreach (Control c in parentcontrol.Controls)
{
if (c.HasChildren)
{
AddParamControl(c);
}
else
{
if (c.Name.StartsWith("param" + baqName))
{
string pname = c.Name.Substring(baqName.Length + 5,c.Name.Length - (baqName.Length + 5));
if (!(paramControls != null)) { paramControls = new Dictionary<string, Control>(); }
paramControls[pname] = c;
}
}
}
}
public bool ParamsChanged()
{
bool ret = true;
if (baqParams != null && lastParams != null)
{
bool matched = true;
foreach (KeyValuePair<string,string> kp in baqParams)
{
if (!kp.Value.Equals(lastParams[kp.Key]))
{
matched = false;
break;
}
}
ret = !matched;
}
return ret;
}
public void ParamsFromControls()
{
if (paramControls != null && maincontrol != null)
{
Control top = maincontrol;
while (top.Parent != null) { top = top.Parent; }
foreach (KeyValuePair<string, Control> p in paramControls)
{
Control c = p.Value;
string val = ControlValue(c);
if (baqParams[p.Key] != val)
{
UpdateParam(p.Key, val);
//MessageBox.Show(p.Key + " - " + val);
}
}
}
}
public void UpdateParam(string key, string newval)
{
if (baqParams.ContainsKey(key))
{
baqParams[key] = newval;
changedparams = true;
}
}
public void ResetParams()
{
baqParams = new Dictionary<string, string>(defParams);
changedparams = true;
}
private string ControlValue(Control c)
{
string val = string.Empty;
if (c is EpiTextBox)
{
val = ((EpiTextBox)c).Text ?? string.Empty;
}
else if (c is EpiCombo && ((EpiCombo)c).Value != null)
{
val = ((EpiCombo)c).Value.ToString();
}
else if (c is EpiCheckBox && ((EpiCheckBox)c).CheckState != CheckState.Indeterminate)
{
val = ((EpiCheckBox)c).Checked.ToString();
}
else if (c is BAQCombo && ((BAQCombo)c).Value != null)
{
val = ((BAQCombo)c).Value.ToString();
}
else if (c is EpiDateTimeEditor && ((EpiDateTimeEditor)c).Value != null)
{
val = ((DateTime)((EpiDateTimeEditor)c).Value).ToString("s");
}
else if (c is EpiTimeEditor)
{
}
else if (c is EpiNumericEditor && ((EpiNumericEditor)c).Value != null)
{
val = ((EpiNumericEditor)c).Value.ToString();
}
else if (c is EpiCurrencyEditor && (decimal?)((EpiCurrencyEditor)c).Value != null)
{
val = ((EpiCurrencyEditor)c).Value.ToString();
}
else if (c is EpiRetrieverCombo && ((EpiRetrieverCombo)c).Value != null)
{
val = ((EpiRetrieverCombo)c).Value.ToString();
}
return val;
}
#endregion
#region DropDowns
public void MatchDropdowns()
{
if (grid != null)
{
UltraGridBand listBand = grid.DisplayLayout.Bands[0];
for (int i = 0; i < listBand.Columns.Count; i++)
{
string caption = listBand.Columns[i].Header.Caption;
string key = listBand.Columns[i].Key;
Control top = grid;
while (top.Parent != null) { top = top.Parent; }
MatchDropdownControls(top, listBand, caption, key);
}
}
}
private bool MatchDropdownControls(Control parentcontrol, UltraGridBand listBand, string caption, string key)
{
bool donebind = false;
foreach (Control c in parentcontrol.Controls)
{
string ctype = c.GetType().ToString().Replace("Ice.Lib.Framework.", "");
if (ctype == "BAQCombo" || ctype == "EpiCombo")
{
if (ctype == "BAQCombo")
{
if (((BAQCombo)c).EpiBinding == baqName + "." + key)
{
listBand.Columns[key].ValueList = (BAQCombo)c;
listBand.Columns[key].Style = Infragistics.Win.UltraWinGrid.ColumnStyle.DropDownList;
((BAQCombo)c).ForceRefreshList();
donebind = true;
break;
}
}
else if (ctype == "EpiCombo")
{
if (((EpiCombo)c).EpiBinding == baqName + "." + key)
{
listBand.Columns[key].ValueList = (EpiCombo)c;
listBand.Columns[key].Style = Infragistics.Win.UltraWinGrid.ColumnStyle.DropDownList;
((EpiCombo)c).ForceRefreshList();
donebind = true;
break;
}
}
}
else if (c.HasChildren)
{
if (MatchDropdownControls(c, listBand, caption, key))
{
return true;
}
}
}
return donebind;
}
#endregion
protected virtual void Dispose(bool disposing)
{
}
public void Dispose()
{
paramControls = null;
defParams = null;
baqParams = null;
results = null;
edv = null;
dsBAQ = null;
ds = null;
adptr.Dispose();
adptr = null;
Dispose(true);
GC.SuppressFinalize(this);
}
}
To use it, you declare one variable per BAQ needed, at the Script level (after “// Add Custom Module Level Variables Here **”):
private M8DynQuery dqXXXX;
Declare and initialise those variables within InitializeCustomCode:
dqXXXX = new M8DynQuery(
"BAQNAME_HERE", // string, name of BAQ
oTrans, // always oTrans
csm.GetNativeControlReference("guid"), // use GUID of the panel where the controls are
grdXXXX, // grid for the data, or null if none
new string[] {"PARAM1","PARAM2"}, // string array of BAQ parameter names, or null if none
new string[] {"VALUE1","VALUE2"} // string array of initial parameter values, or null if no parameters
);
Dispose of the object in DestroyCustomCode, after “// Begin Custom Code Disposal”:
dqXXXX.Dispose();
Methods:
GetData() - redownloads the BAQ data.
RefreshData() - redownloads the BAQ data only if the parameters have been changed.
Save() - saves the updated BAQ data, assuming the BAQ is updateable.
UpdateParam(string paramName, string paramValue) - updates the named parameter.
ResetParams() - sets all parameters back to the initial defaults.
Clear() - resets all data and the Dynamic Query.
Properties:
Adapter() - returns the DynamicQueryAdapter for the M8DynQuery object.
EpiDataView() - returns the associated EpiDataView (which is also in the oTrans.EpiDataViews collection).
DataTable - returns the underlying DataTable (DynamicQuery results).
CurrentDataRow() - the DataRowView of the active EpiDataView row, or null if none is active.
ParamNames() - a List of the parameter names.
Params() - a Dictionary<string,string> of parameter names and current values.
There are two conveniences built in.
Automatic drop-downs within the grid - if drop-down controls are used within the screen and bound to fields in the EpiDataView used by the M8DynQuery object, those drop-downs will be pushed to the same fields within the grid so both behave in the same way.
Parameter input boxes - if a control is placed within the screen, unbound, and named according to the M8DynQuery convention, it will automatically be treated as the input field for the associated BAQ parameter. The naming convention for this is “param” + the BAQ name + the parameter name.
6 Likes