E10 UDtable dataview join with another

Epicor ERP 10
1

I have a customized UD form where UD list shows the UD data.
In that form I have a function which gets the result from a BAQ, put into a datatable.

The BAQ has the Keys of the UDTable and some dynamic calculated data. I want to join the two so my UD records can be editable (for ud fields) and show non-editable information from the BAQ.

What would be the best way to combine the two?

My first thought would be to add the necessary columns to the UD grid, and have a loop passing each BAQ records, flag the corresponding UD record (based on same key values) fill the extra columns of the grid with BAQ fields info) , in order to only have a subset of the UD records shown, via filtering the grid on that flag.

But I beleive this is not very efficient… about 800 records on form load to be updated.

The user must keep the ability to edit some fields from the UD record.

Is there a better way? like a Join between both lists ? if so How to do that?

PS: I tried to use an Updatable BAQ, but got errors trying to customize the Dashboard… so I am on plan B here…

Thanks
Pierre

2

You should be able to LINQ your datasets

(an example stolen from MSDN)

Just setting up the example structures (in your case it’s already done)

DataSet ds1 = new DataSet("ds1");

DataSet ds2 = new DataSet("ds1");

DataTable dt1 = new DataTable("Table1");

DataTable dt2 = new DataTable("Table2");

 

ds1.Tables.Add(dt1);

ds2.Tables.Add(dt2);

dt1.Columns.Add("id", Type.GetType("System.Int32"));

dt1.Columns.Add("FirstName", Type.GetType("System.String"));

dt1.Rows.Add(1, "Carl");

dt1.Rows.Add(2, "John");

 

dt2.Columns.Add("key", Type.GetType("System.Int32"));

dt2.Columns.Add("id", Type.GetType("System.Int32"));

dt2.Columns.Add("LastName", Type.GetType("System.String"));

dt2.Rows.Add(1, 1, "Perry");

dt2.Rows.Add(2, 2, "Piercy");

dt2.Rows.Add(3, 4, "Johnson");

<— THE TASTY STUFF —>

IEnumerable<DataRow> dv = from table1 in ds1.Tables[0].AsEnumerable()

from table2 in ds2.Tables[0].AsEnumerable()

where table1.Field<int>("id") == table2.Field<int>("id")

select table2;

//select new { id = table1.Field<int>("id"), firstname = table1.Field<string>("FirstName"), lastname = table2.Field<string>("LastName")};

dataGridView1.AutoGenerateColumns = true;

dataGridView1.DataSource = DataTableExtensions.CopyToDataTable<DataRow>(dv);

As far as the editing goes - Off the top of my head, I dont see a way around custom handling of the field changes from the linq’d table back to the original

3

Actually if you have two EpiDataViews you can relate them (parent child) and the “EpiMagic” will do the filtering for you and keep them editable.
So if you use a BAQDataView to run your BAQ and bring that in as a child of the original UDXX Data View then I believe it will do all you want.

string[] parentKeyFields = new string[2];
		string[] childKeyFields = new string[2];
		parentKeyFields[0] = "ABCCode";
		childKeyFields[0] = "Key1";
		parentKeyFields[1] = "CalcPcnt";
		childKeyFields[1] = "Key2";
		this._edvUD01.SetParentView(this._edvAbcCode, parentKeyFields, childKeyFields);
1 Like
4

Whoa! thanks Jose I have not thought of that at all!

I will give it a try and let you all know!!

Pierre

5

So i was able to make the connection using the following:

string[] parentKeyFields = new string[3];
string[] childKeyFields = new string[3];
parentKeyFields[0] = “Key1”;
childKeyFields[0] = “UD14_Key1”;
parentKeyFields[1] = “Key2”;
childKeyFields[1] = “UD14_Key2”;
parentKeyFields[2] = “Date01”;
childKeyFields[2] = “OrderRel_ReqDate”;
this.edvGrille.SetParentView(this.edvUD14, parentKeyFields, childKeyFields);
which made UD14 having edvGrille (the baq result) as a child.

But how can I make this child columns show in the grid of UD14? I know how to add another column :
GrilleUD14.DisplayLayout.Bands[0].Columns.Add(“BCClient”, “BC Client”);
but there is no connection to the edvGrille column… what is the syntax to add the child column ?

thanks

1 Like
6

sorry hit a button too soon…
here’s my complete reply:
So i was able to make the connection using the following:

string[] parentKeyFields = new string[3];
string[] childKeyFields = new string[3];
parentKeyFields[0] = “Key1”;
childKeyFields[0] = “UD14_Key1”;
parentKeyFields[1] = “Key2”;
childKeyFields[1] = “UD14_Key2”;
parentKeyFields[2] = “Date01”;
childKeyFields[2] = “OrderRel_ReqDate”;
this.edvGrille.SetParentView(this.edvUD14, parentKeyFields, childKeyFields);

which made UD14 having edvGrille (the baq result) as a child.

But how can I make this child fields show in the grid of UD14? I know how to add another column :
GrilleUD14.DisplayLayout.Bands[0].Columns.Add(“BCClient”, “BC Client”);
but there is no connection to the edvGrille column… what is the syntax to add the child field as a column ?

thanks

7

Hello Jose

Found an old post from you which I think should solve my issue using BAQDataView

//Using
using Ice.Lib.Broadcast;
//Class Level

BAQDataView baqViewLate;
public void CreateLateBAQView()
{
	baqViewLate = new BAQDataView("LateOpenPos");
	oTrans.Add("LateOpenPOBAQ",baqViewLate);

	string pubBinding = "POTotal.BuyerID";
	IPublisher pub = oTrans.GetPublisher(pubBinding);
	if(pub==null)
	{
		oTrans.PublishColumnChange(pubBinding, "MyCustomPublish");
		pub = oTrans.GetPublisher(pubBinding);
	}

	if(pub !=null)
		baqViewLate.SubscribeToPublisher(pub.PublishName, "POHeader_BuyerID");
}

I have four fields from my BAQ which are “keys” to uniquely identify the row so that
UD14.Key1 = baq_Key1 //the ordernum
UD14.Key2 = baq_Key2 //the plant
UD14.Date01 = baq_OrderRel_ReqDate
UD14.ShortChar01 = baq_OrderRel_ShipToCode

What would be the syntax to publish the 4 fields?
Would it be comma seperated? string pubBinding = “UD14.Key1”, “UD14.Key2”… ; ?
then to subscribe the same, in similar order…?
baqViewLate.SubscribeToPublisher(pub.PublishName, “baq_Key1”, “baq_Key2” … ); ?

Now looking at it, I am not sure it is the good way…

Thanks…

8

Back to my UD14 custom form. I added a unique row id in Number20 field. In order to only have one field to reference.

note: before testing this I was filling the grid with UD14 data with columns to edit by the user. I had a BAQ gathering sales order info which changes often , as readonly columns, so that the results was UD14 rows (400) with editable columns collated with my BAQ columns. The way I did it is not working well and wanted to pursue your method using “EpiMagic” :wink:

I have implemented the BAQDataView following your post…using a fresh UD14 base form.

`public void CreateUD14BAQView()
	{
		baqViewUD14 = new BAQDataView("REQ_TranspUD14");
		oTrans.Add("TranspBAQ",baqViewUD14);
                //The TranspBAQ is a query combining UD14 columns and sales order data...
		string pubBinding = "UD14.Number20";//the form column to publish
		IPublisher pub = oTrans.GetPublisher(pubBinding);
		if(pub==null)
		{
			oTrans.PublishColumnChange(pubBinding, "MyCustomPublish");
			pub = oTrans.GetPublisher(pubBinding);
		}

		if(pub !=null)
			baqViewUD14.SubscribeToPublisher(pub.PublishName, "UD14_Number20");//the ud14 query column

		GetUD14Data();//fill the grid with UD14 data about 800 records on load of the form.
	}

And have changed the Binding to the grid  to TranspBAQ dataView.

The results: no compile errors!

On form load, I see the tree pane with all UD14 key1 , and the grid only showing columns from my BAQ REQ_TranspUD14.  no data showing.
But clicking on one of the key1 values (which is a sales order number) I see the corresponding data being shown in the grid !.

That is not what I want....   I want all the rows showing in the grid !  But with the editable UD14 columns !

So I feel I am close but not there yet....  Missing something somewhere....

Here is my get data I use
private void GetUD14Data()
	{//copied from a wizard....
		
			// Build where clause for search.
			string date = String.Format("{0:yyyy/MM/dd}", DateTime.Today.AddDays(-15)); 
			
			string whereClause  = "Date01 >= \'" + date + "\' AND ShortChar01 <> \'TRANSFERT\'" ;
			
			System.Collections.Hashtable whereClauses = new System.Collections.Hashtable(1);
			whereClauses.Add("UD14", whereClause);

			// Call the adapter search.
			SearchOptions searchOptions = SearchOptions.CreateRuntimeSearch(whereClauses, DataSetMode.RowsDataSet);
			this._UD14Adapter.InvokeSearch(searchOptions);

			if ((this._UD14Adapter.UD14Data.UD14.Rows.Count > 0))
			{
				this.edvUD14.Row = 0;
				
			} else
			{
				this.edvUD14.Row = -1;
			}

			// Notify that data was updated.
			this.edvUD14.Notify(new EpiNotifyArgs(this.oTrans, this.edvUD14.Row, this.edvUD14.Column));
			
			
			
		}
		
	}

I would need some assistance on this as I felt it was a promising way to meet my goal!

Thanks in advance!

PS:  I have tried the Updatable BAQ and it was working until I had to customize the dashboard and  all went south!   the objects not keeping positions with labels etc....   so went through the UD form route instead....more stable...

Pierre
9

Easiest way to do this (with read only data) is to link your stuff in a BAQ and use a BAQDataView

1 Like
10

Well that is what I mentionned in my post (sorry my comments got mixed up with code section… )
I did use it, but the results were not what I was expecting… the grid no longer shows UD14 columns, and now only shows the baq columns…in readonly, but also no rows are populated in the grid. But the Key1 value is shown in the left tree pane, so selecting one , will make that row (from the BAq) show.

I need to make the grid show all the desired rows with the UD14 columns (editable) combined with the baq columns (readonly)

Like I said… I feel I am close but I am missing something somewhere…

Pierre

11

to format your code section use

```CS at the beginning
and ``` at then end of your section. The CS is supposed to format for C#. You can leave that off too.

example

code here

Actual text in the message.
```cs
code here
```

1 Like
12

I’ve never tried it, but I wonder if you couldnt just make your BAQ updatable for the fields you need.

13

Thanks I never remember that! would be nice if just a highlight of the code and then a selection of an icon at the top of the edit window , representing adding code, would do the same!!!