Add an updateable dashboard

Dang it!!! yeah, you are right. The first time I open up the dashboard, it’s on the right part, which is why I thought it was working. But if I have two parts checked out and I switch to the next part, the dashboard doesn’t follow along. It’s stuck on the first part.

Annoying…

I wanted to clarify something. While I thought the check boxes for the controls had something to do with the updateable nature of the dashboard, it does not. I just did something else wrong with the saving of the customization. Even if leave those unchecked, it’s now updateable.

But I still have the problem that if I make a change using the dashboard, it will no longer update as I change parts. It will follow along fine until I do that, but it’s kind of useless if I have to keep reloading engineering workbench just to see the correct information.

or " … not updateable." ?

nope. Those checkboxes had nothing to do with whether the dashboard came through as a working updateable panel or not. I thought they might have, because initially they were not updateable, and I re-added the dashboard with those check boxes checked and they changed to updateable, so I thought that’s what caused the change, but later in my dinking around, I re-added the dashboard with both of them unchecked, and it was still updateable. Which is the opposite of what I had stated earlier. So that temporary correlation did was not the causation that I had assumed.

1 Like

I have been told by Epicor support several times that you can not embed an updateable dashboard and I have never been able to get one to work.

It’s this kind of behaviour that means we use Dynamic Query for most of this now. It’s proved simpler for us overall.

Can the dynamic query be updateable? (is that a dumb question… :man_facepalming:)

Do you have any examples of making a dynamic query updateable?

Or I can just search before I ask dumb questions

Tomorrow I’ll dig something out - I’m not at my desk now. We use this a LOT - so much so we’ve packaged all the behaviour into a standard class we paste in. For all I know EpiMagic may do all the same things but it’s all so undiscoverable.

But yes, it certainly does work for updateable. And parameters. And some other nice things.

1 Like

Just curious … Why are your UD fields on the Part table instead of the PartRev table?

Had then been on Part Rev, you could have just put individual controls on that added sheet, with no need for a dashboard/uBAQ.

You could still add those fields to the PartRev table, and use BPMs to sync them between Part and Part Rev. Its a real hack - but gets the job done.

One of them is a UD field the part table, the other is an attribute field that already existed.

As to why I didn’t want them on Part Rev is that I didn’t want to have to check out the part to make the change. While, it’s usually the Manufacturing engineers that will see that a change needs to be made when we are doing the methods, it’s not always the ME’s that will make the change. I need them to be able to be made by the person who prints out the print packets, and the people painting parts because they know if something is wrong.

A BPM to map some fields might be an ok way to go though. Something where the part master updates the ECO table as the part is checked out, and the ECO table updates the part master as the part get’s checked in. Seems a little messy and kind of hammerish, but it could work.

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

Awesome! I’ll take this and try it out. I’m sure I’ll have some questions as I’m still learning the coding part. (but really, aren’t we all??)

From a cursory look, the things that need to be changed when this code is plugged into a customization is

  1. BAQ name
  2. parameter name
  3. wire up the parameter value (like current part number for example)

After that, I can bind this to a grid, (can I bind it to a drop down if I know I only get one row??)

Anything else simple that I am missing?

I’m sure you’ve got the gist!

We’ve packaged up the functionality we need and drop it into a customization as a class outside the main Script, which keeps things tidy. But short of going that far, to make it work you need to make sure of the following (apologies if any of it is too basic, but you never know who may later need the full ABC):

  • Add custom assemblies Ice.Contracts.BO.DynamicQuery / Ice.Adapters.DynamicQuery
  • Declare the DynamicQuery adapter in the Script itself, and also the DataTable used for the returned results
  • Initialize the adapter in InitializeCustomCode (or an additional method called from it)
  • Call the above main code in InitializeCustomCode with default parameters, ideally ones that return no initial data
  • Have a way of calling it again each time whatever the parameter is based on changes - EpiViewNotification or EpiRowChanged are the usual logical choices

You’re right about the BAQ name and parameter name (or parameter names, you can have as many as you need). They can be hard-coded. You just need to re-set the parameter value each time.

If you do set it up this way, then yes, once you’ve let it run once by closing and re-opening the screen with the applied customization, from then on the EpiDataView is usable in the same way for binding as any other view is, and you’ll recognise it by the BAQ name. That means you can bind it to a drop-down, a grid, or anything else you like. By default, if only one row is returned then that row is automatically selected so the drop-down alone should work, but for multiple rows you may need the grid for selecting the row before any other bound controls will show data.

4 Likes

No apology necessary. I’m excited to learn something new!

So I’m trying to figure this out, but I don’t think that I’m understanding some of the periphery pieces that need be put in place for this to work. I guess I’m not sure if I am going to be able to learn this just through forum posts, but I’m kind of stubborn, so here goes…

I have 1 and 2 in your list (I think).
I have your code in the initialize custom code just to populate the grid with hard coded values to start with. One thing I’m not understanding is, I don’t see anywhere that tells states what the actual BAQ is. From what I understand, yourbaq is an object that we created, dsQuery is a data set for the results, dsQDesign grabs the data to create the grid, dsBAQ is for the parameters. baqname has the name of the BAQ, but it’s only used in creating the dataview (as far as I can tell)

I don’t see where we are passing in the BAQ as a parameter anywhere to actually run the BAQ. Is there an assumption that I am missing somewhere?

OK, I don’t think you’re far off. You seem like you have at least as much experience and knowledge as me, and I got it working. The possible confusion is that I’ve given you the “do it once” code, which is what tends to be passed around.

The best approach, I’ve found, is to adapt it by pulling the adapter, the EpiDataView and the results DataTable out into script level variables, then packaging the remainder into a method of its own which you can pass the parameter to, and which only initialises the objects if it needs to. You can then call that method with the default parameter from InitializeCustomCode, and again whenever your parameter needs to change.

Does that make more sense?

I’d happily give you the whole of our code, but there’s quite a lot of it by now, most of which is useful to us but would be distracting to anyone else.

1 Like

Can you tell me where or how “termsAdapter.” is defined? I am getting an error on those lines.

I can see errors in that code. I altered it after copying and pasting from where we used it and obviously wasn’t consistent.

If you replace the references to “termsAdapter” with “yourbaq” then I think you’ll find it works.

seeing that im still a rookie at coding right now can you show me how you have it declared in the top part of your script editor. Thanks.