How to create a FKV for a UD Table?

I was trying to create and FKV for a UD table. The Column Name drop down has no options:

Am I missing a step?

YOU could try the knowledgebook on EpicCare
“Add a User-Defined table as a FKV to a base UI Form” - KB0032693
It describes editing the ContextMenu.xml.

Sorry, I don’t have more details, I only vaguely remember having to do this in V8 once… a long time ago.

You need to add Extended Properties to the field that you are trying to Link and set the “Like” value to be that of the related table.
For example if you field is
Character01 and you want to pull CustomerCustID then the “LIKE” value needs to be set on that column to
“Customer.CustID” that will let Epicor know that Character01 is “LIKE” Customer.CustID and then it knows what Adapter to use to pull the data.

2 Likes

Ah, we don’t have access to the extended properties window (Still on MT, should be moving soon).

I can set some extended properties through code, but that wouldn’t help the designer know what to do… Maybe I should backup and state what I want to do…

I have a EpiDataView for UD37 bound to a grid. I’m storing relationships between different UD100 rows in UD37.

Key1 Stores the spec ID, Character01 stores an equivalent spec ID. I want to add columns that look up the description for these specs on UD100 by the ID’s in Key1 and Character01. The spec ID’s are not very human readable, but UD37 only has the record IDs so I need to get the descriptions elsewhere.

This would be easy with a BAQ but I need to be able to create new rows and edit existing rows.

You actually can set extended Properties right on the Customization.
image

Then it should show up in the FKV dropdown.

1 Like

I tried that, the Column Name drop down still has no choices.


You might need to add it save and re-open Customization (close form completely, reopen)

1 Like

I completely closed and reopened Epicor… still no luck.

Anyone have a few seconds to help me via the Windows 10 Quick Assist? Its probably something super simple…

Wait looking at your screenshot you have like set to UD37.Key1?
So you are trying to bring in another UDTable as a child ot a current UD table?

That’s not going to work because the primary key of UDTables is 5 keys in total. Not just one key like CustNum or OrderNum…
Your best bet here is to use a BAQDataView, which can subscribe to the published Character01 values.

I misunderstood I thought you wanted to bring UDXX table into a regular table via a 1 to 1 link like OrderNum or CustID etc…

One Option (and I haven’t tested this) is to Store the SysRowID of the UD37 Row in your UD100 table, then set the Like value to UD37.SysRowID that “MAY” work assuming that UD37 has a GetBySysRowID method.

I checked, that won’t work, so BAQDataView is your best bet.

So I’m probably just doing this in a stupid way…

We have machine specs, and these specs are used in various configurators, reports, etc. The main entry for the spec is UD100. We want to be able to have specs linked to other specs, so if you have one open you can see a table of specs marked as related in some manner.

I was trying to do this by storing the relations between various UD100 rows in UD37 in a Many to Many fashion. This was working OK but the identifier for each UD100 isn’t descriptive enough to be human readable.

Does BAQDataView support adding new items?

EDIT:

Looks like maybe I should embed an updatable dashboard?

Yeah, that was the way I read Evans origianl post - why I was thinking ContextMenu.XML route.
Justfor fun I tested it in E10 - still seems to enable adding the FMKV for a UD - ref screen shot below
but… I don’t have any data in the tables & have no idea if it would work for Evan.
After his last post, I suspect there are better ways to get what he want anyway.
image

Oh, I’ll have to find the KB article. I am only using Key1 on UD37, would this method still work?

Edit: I didn’t see you said you didn’t test it. Probably should think of a different approach anyway like you said.

I’m thinking a DynamicQueryAdapter with an updatable BAQ tied directly to an EpiDataView might be the best way but I am still looking.

Knowledgebook explainsthe details
But basically you always need to to define all keys for UD tables - even if you don’t really populate/join on all of them

I’m guessing that will end up being simpler… and there are lot’s of examples on this site.

Ive got most of the way there, I have a UltraGrid bound the results of a DynamicQueryAdapter but I can’t get the grid to refresh when I run the BAQ again… None of the examples seem to cover this. I’ve tried both the Notify on the EpiDataView and Refresh on the grid but it still shows the results from the first time the query ran…

Here is my code so far:

public void SetupRelatedSpecs(string currentSpec)
	{
		MessageBox.Show(currentSpec);
		DynamicQueryAdapter yourbaq = new DynamicQueryAdapter(this.oTrans);
		DataTable results;
		yourbaq.BOConnect();
		string baqName = "RelatedSpecs";

		yourbaq.GetByID(baqName);
		yourbaq.ClearDynamicQueryData();
		Ice.BO.DynamicQueryDataSet dsQuery = yourbaq.DynamicQueryData;
		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);
				}
			}
		}

		Ice.BO.QueryExecutionDataSet dsBAQ = yourbaq.GetQueryExecutionParameters(dsQuery);
		dsBAQ.ExecutionParameter[0].ParameterID = "CurrentSpec";
		dsBAQ.ExecutionParameter[0].IsEmpty = false;
		dsBAQ.ExecutionParameter[0].ParameterValue = currentSpec;
		dsBAQ.AcceptChanges();
		yourbaq.Execute(dsQuery, dsBAQ);
		if (yourbaq.QueryResults != null && yourbaq.QueryResults.Tables.Count > 0)
		{
			results = yourbaq.QueryResults.Tables["Results"];
		}
		else
		{
			results = new DataTable();
		}
		RelatedSpecs_edv = (EpiDataView)oTrans.EpiDataViews[baqName];
		if (RelatedSpecs_edv == null)
		{
			RelatedSpecs_edv = new EpiDataView();
			oTrans.Add(baqName, RelatedSpecs_edv);
		}
		RelatedSpecs_edv.dataView = results.DefaultView;	
		RelatedSpecs_edv.Notify(new EpiNotifyArgs(this.oTrans, -1, EpiTransaction.NotifyType.Initialize));
		this.RelatedSpecs_edv.Notify(new EpiNotifyArgs(this.oTrans, this.RelatedSpecs_edv.Row, this.RelatedSpecs_edv.Column));
		gridRelatedSpecs.Refresh();
	}

OK I’m still a little foggy on the details for your project, up until now I was only thinking in very general terms, how to link UD tables.

Maybe now it would help us all if you can start over and expand a little on how your raw data is stored, the joins and how it should finally be presented in your grid? Maybe some screen shots would be easier/faster?

And… if you can be more specific about what you meant by “run the BAQ again”
-right now I’m assuming that the grid populates correctly at some point (s) in the UD37 entry form. e.g. after loading your records?
-but the grid isn’t refreshing after some action(s) are taken in that entry form?

I’m using UD37 as part of a larger form I created for UD100. When looking at various UD100 records, I want a grid to show UD37 records where UD37.Key1 = UD100.Character10 OR UD37.Character01 = UD100.Character10.

I have the BAQ that gets these UD37 records, and I was able to display them in a grid with the code I posted above. But when I change UD100 records I need to run the BAQ again to to update the grid for UD37. That is the part I am stuck on, it seems to stay with the first set of results.

How were you calling SetupRelatedSpecs(string currentSpec)?

Also, in the meantime… if you can find it, there is a GREAT thread by Jose about how to add field to existing grids which describes publishing and subscribing.
— I’m not sure (yet) if his post will apply directly to your current task
---- but… it is definitely worth looking at regardless.
------ sorry, I don’t have a direct link handy but it’s pretty popular, I think you will be able to find it without too much trouble. and I will at it later if I remember.

Once at InitializeComponents and then on the Initialize EpiNotification for the UD100 table. I know its running, my message boxes appear with valid values. But the binding does not update.

I’ll look for the thread…

Yeah, I think your calling is a problem
– some tracing might help but not always
— when I’m in doubt (and not using VS) I can use the wizard(s) to add all the events I can think of to my form customization, and then add messageboxes to events so I can see what is happening, when as I navigate in the form. Kind of tedious to set up (if you’re not OCD) but I have found them to be enlightening.

Finally… I think this is the correct link to that thread:
Efficiently Adding Column to DataGrid