How to Add Fields in DataView

daisygonzales · September 2, 2021, 10:24pm

Hello there,
We are trying to add the following fields: OrderHed.ShortChar02 and OrderDtl.DocExtPriceDtl in Customer Tracker > Orders > Open list view but it’s not available in the dataview OrderOpen. Is there a way to add those fields?

Thank you and stay safe,
Daisy

wcouch · September 9, 2021, 4:57pm

Hey there Daisy,

Hope this finds you well. I’m not on Kinetics so take everything I say with a grain of salt.

Since these fields aren’t available on the OrderOpen Dataset, you’ll need to add them to the grid. Here’s an example which helped me with a similar problem a while back:

You can try using this thread to add the needed fields to the datagrid when the form loads.

That’s only half the battle. We need to actually populate these fields with data, and since they aren’t on the Dataset, you’ll need to add them.

Add an EpiUltraGrid InitializeRow event to the OrdersOpen grid. Since it’s not a custom control you can’t do this through the wizard, but you can use the wizard as a guide and grab the native control using the csm.GetNativeControlReference(“EpiGuidHere”) method.

Then in the InitializeRow method, you can call GetList or another method to pull in the sales order information for each row and populate the added fields ShortChar02 and DocExtPriceDtl.

WARNING
This will slow down customer tracker when you click the “Retrieve” button. Depending on how many open orders exist for the customer, the performance hit could be significant.

Let me know if this makes sense or if I need to clarify anything.

daisygonzales · September 13, 2021, 1:16am

Hi Wesley,

Thank you for your response. Our system was upgraded to Kinetic version as we using cloud-based platform, but we are still using the original UI version.

I’ve tried your codes in ResourceSchedForm to check if it will work on our end, unfortunately I am getting this error:

I’m not sure if it’s because we’re on cloud-based platform and they have it restricted. Please advise. Thank you.

wcouch · September 14, 2021, 9:02pm

Hey Daisy,

Sure thing. So for starters we need to add the two fields to the grid. Since you’re using the classic forms I believe you should be golden.

The first step is to add the custom columns to the grid.

Code for adding columns to the Grid

Here all I’m doing is adding the column headers to the grid. At this stage, this is basically meaningless as the columns won’t be populated. However, you should see 2 new columns in Customer Tracker > Order. You shouldn’t need to add any using statements at this stage - just grab the EpiUltraGrid and add the two columns. We’ll fill these in later.

// **************************************************
// Custom code for CustomerTrackerForm
// Created: 9/9/2021 11:59:38 AM
// **************************************************

extern alias Erp_Contracts_BO_Customer;
extern alias Erp_Contracts_BO_CreditManager;
extern alias Erp_Contracts_BO_Company;
extern alias Erp_Contracts_BO_ARInvoice;
extern alias Erp_Contracts_BO_Quote;
extern alias Erp_Contracts_BO_CustCnt;
extern alias Erp_Contracts_BO_ARLOC;
extern alias Erp_Contracts_BO_CashDtlSearch;
extern alias Erp_Contracts_BO_ServiceContract;
extern alias Erp_Contracts_BO_CustShip;
extern alias Erp_Contracts_BO_CashRecSearch;
extern alias Erp_Contracts_BO_ARPromissoryNotes;
extern alias Erp_Contracts_BO_SalesOrdHedDtl;
extern alias Erp_Contracts_BO_OrderDtlSearch;
extern alias Erp_Contracts_BO_ShipDtlSearch;
extern alias Erp_Contracts_BO_ARAgingTracker;

using System;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
using Erp.Adapters;
using Erp.UI;
using Ice.Lib;
using Ice.Adapters;
using Ice.Lib.Customization;
using Ice.Lib.ExtendedProps;
using Ice.Lib.Framework;
using Ice.Lib.Searches;
using Ice.UI.FormFunctions;

public class Script
{
	// ** Wizard Insert Location - Do Not Remove 'Begin/End Wizard Added Module Level Variables' Comments! **
	// Begin Wizard Added Module Level Variables **

	// End Wizard Added Module Level Variables **

	// Add Custom Module Level Variables Here **
	private EpiUltraGrid _custOrdGrid;

	public void InitializeCustomCode()
	{
		// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
		// Begin Wizard Added Variable Initialization

		// End Wizard Added Variable Initialization

		// Begin Wizard Added Custom Method Calls

		// End Wizard Added Custom Method Calls
		_custOrdGrid = (EpiUltraGrid)csm.GetNativeControlReference("741857dd-a984-45d3-ad73-94903e9abcd2"); 
		_custOrdGrid.DisplayLayout.Bands[0].Columns.Add("UserChar1","User Char 1");
		_custOrdGrid.DisplayLayout.Bands[0].Columns.Add("DocExtPriceDtl","Ext Price Detail");
	}

	public void DestroyCustomCode()
	{
		// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
		// Begin Wizard Added Object Disposal

		// End Wizard Added Object Disposal

		// Begin Custom Code Disposal

		// End Custom Code Disposal
		
	}
}

Now that we have added two columns, we can fill them with data as the grid populates.
This more directly deals with the error message you sent over.

Populating the Grid Part 1

First, we need to add an assembly reference to the “Ice.Contracts.Lib.BOReader” dll

Now that we’ve added our assembly reference, let’s define the BOReader object we’ll use to populate the added columns

Declare a new private property:

public class Script
{
	// ** Wizard Insert Location - Do Not Remove 'Begin/End Wizard Added Module Level Variables' Comments! **
	// Begin Wizard Added Module Level Variables **

	// End Wizard Added Module Level Variables **

	// Add Custom Module Level Variables Here **
	private EpiUltraGrid _custOrdGrid;
	private BOReaderImpl _boRdr;

Then add _boRdr = WCFServiceSupport line to the InitializeCustomCode method:

	public void InitializeCustomCode()
	{
		// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
		// Begin Wizard Added Variable Initialization

		// End Wizard Added Variable Initialization

		// Begin Wizard Added Custom Method Calls

		// End Wizard Added Custom Method Calls
		_custOrdGrid = (EpiUltraGrid)csm.GetNativeControlReference("741857dd-a984-45d3-ad73-94903e9abcd2");
		_custOrdGrid.DisplayLayout.Bands[0].Columns.Add("UserChar1","User Char 1");
		_custOrdGrid.DisplayLayout.Bands[0].Columns.Add("DocExtPriceDtl","Ext Price Detail");

		_boRdr = WCFServiceSupport.CreateImpl<BOReaderImpl>((Ice.Core.Session)oTrans.Session, Epicor.ServiceModel.Channels.ImplBase<Ice.Contracts.BOReaderSvcContract>.UriPath);
	}

Now it’s time to add our event which we will use to trigger a BO Method to populate the data on the grid. Add an InitializeRow event to our EpiUltraGrid. At this point, the custom script should look similar to this

// **************************************************
// Custom code for CustomerTrackerForm
// Created: 9/9/2021 11:59:38 AM
// **************************************************

extern alias Erp_Contracts_BO_Customer;
extern alias Erp_Contracts_BO_CreditManager;
extern alias Erp_Contracts_BO_Company;
extern alias Erp_Contracts_BO_ARInvoice;
extern alias Erp_Contracts_BO_Quote;
extern alias Erp_Contracts_BO_CustCnt;
extern alias Erp_Contracts_BO_ARLOC;
extern alias Erp_Contracts_BO_CashDtlSearch;
extern alias Erp_Contracts_BO_ServiceContract;
extern alias Erp_Contracts_BO_CustShip;
extern alias Erp_Contracts_BO_CashRecSearch;
extern alias Erp_Contracts_BO_ARPromissoryNotes;
extern alias Erp_Contracts_BO_SalesOrdHedDtl;
extern alias Erp_Contracts_BO_OrderDtlSearch;
extern alias Erp_Contracts_BO_ShipDtlSearch;
extern alias Erp_Contracts_BO_ARAgingTracker;
extern alias Ice_Contracts_Lib_BOReader;

using System;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
using Erp.Adapters;
using Erp.UI;
using Ice.Lib;
using Ice.Adapters;
using Ice.Lib.Customization;
using Ice.Lib.ExtendedProps;
using Ice.Lib.Framework;
using Ice.Lib.Searches;
using Ice.Proxy.Lib;
using Ice.UI.FormFunctions;

public class Script
{
	// ** Wizard Insert Location - Do Not Remove 'Begin/End Wizard Added Module Level Variables' Comments! **
	// Begin Wizard Added Module Level Variables **

	// End Wizard Added Module Level Variables **

	// Add Custom Module Level Variables Here **
	private EpiUltraGrid _custOrdGrid;
	private BOReaderImpl _boRdr;

	public void InitializeCustomCode()
	{
		// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
		// Begin Wizard Added Variable Initialization

		// End Wizard Added Variable Initialization

		// Begin Wizard Added Custom Method Calls

		// End Wizard Added Custom Method Calls
		_custOrdGrid = (EpiUltraGrid)csm.GetNativeControlReference("741857dd-a984-45d3-ad73-94903e9abcd2");
		_custOrdGrid.DisplayLayout.Bands[0].Columns.Add("UserChar1","User Char 1");
		_custOrdGrid.DisplayLayout.Bands[0].Columns.Add("DocExtPriceDtl","Ext Price Detail");

		_boRdr = WCFServiceSupport.CreateImpl<BOReaderImpl>((Ice.Core.Session)oTrans.Session, Epicor.ServiceModel.Channels.ImplBase<Ice.Contracts.BOReaderSvcContract>.UriPath);

		_custOrdGrid.InitializeRow += new Infragistics.Win.UltraWinGrid.InitializeRowEventHandler(_custOrdGrid_InitializeRow);
	}

	public void DestroyCustomCode()
	{
		// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
		// Begin Wizard Added Object Disposal

		// End Wizard Added Object Disposal

		// Begin Custom Code Disposal

		// End Custom Code Disposal
		_custOrdGrid.InitializeRow -= new Infragistics.Win.UltraWinGrid.InitializeRowEventHandler(_custOrdGrid_InitializeRow);
		
	}

	private void _custOrdGrid_InitializeRow(object sender, Infragistics.Win.UltraWinGrid.InitializeRowEventArgs args)
	{
		// this is where we will actually populate the datagrid
	}
}

Now to tie it all together and populate the 2 columns we’ve added to the grid.

Populating the Grid Part 2

We’ll be using the “OrderNum” field to retrieve the UserChar1 and DocExtPriceDtl columns for each order.

private void _custOrdGrid_InitializeRow(object sender, Infragistics.Win.UltraWinGrid.InitializeRowEventArgs args)
{
    // this is where we will actually populate the datagrid
    if(!string.IsNullOrEmpty(args.Row.Cells["OrderNum"].Value.ToString()))
    {
        DataSet orderHedDS = _boRdr.GetRows("Erp:BO:SalesOrder","OrderNum = \'" + args.Row.Cells["OrderNum"].Value.ToString() + "\'", "UserChar1,DocExtPriceDtl");
        args.Row.Cells["UserChar1"].Value = orderHedDS.Tables[0].Rows[0]["UserChar1"].ToString();
        args.Row.Cells["DocExtPriceDtl"].Value = orderHedDS.Tables[3].Rows[0]["DocExtPriceDtl"].ToString();
    }
}

The final custom script should resemble the below code block:

// **************************************************
// Custom code for CustomerTrackerForm
// Created: 9/9/2021 11:59:38 AM
// **************************************************

extern alias Erp_Contracts_BO_Customer;
extern alias Erp_Contracts_BO_CreditManager;
extern alias Erp_Contracts_BO_Company;
extern alias Erp_Contracts_BO_ARInvoice;
extern alias Erp_Contracts_BO_Quote;
extern alias Erp_Contracts_BO_CustCnt;
extern alias Erp_Contracts_BO_ARLOC;
extern alias Erp_Contracts_BO_CashDtlSearch;
extern alias Erp_Contracts_BO_ServiceContract;
extern alias Erp_Contracts_BO_CustShip;
extern alias Erp_Contracts_BO_CashRecSearch;
extern alias Erp_Contracts_BO_ARPromissoryNotes;
extern alias Erp_Contracts_BO_SalesOrdHedDtl;
extern alias Erp_Contracts_BO_OrderDtlSearch;
extern alias Erp_Contracts_BO_ShipDtlSearch;
extern alias Erp_Contracts_BO_ARAgingTracker;
extern alias Ice_Contracts_Lib_BOReader;

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
using Erp.Adapters;
using Erp.UI;
using Ice.Lib;
using Ice.Adapters;
using Ice.Lib.Customization;
using Ice.Lib.ExtendedProps;
using Ice.Lib.Framework;
using Ice.Lib.Searches;
using Ice.Proxy.Lib;
using Ice.UI.FormFunctions;

public class Script
{
	// ** Wizard Insert Location - Do Not Remove 'Begin/End Wizard Added Module Level Variables' Comments! **
	// Begin Wizard Added Module Level Variables **

	// End Wizard Added Module Level Variables **

	// Add Custom Module Level Variables Here **
	private EpiUltraGrid _custOrdGrid;
	private BOReaderImpl _boRdr;

	public void InitializeCustomCode()
	{
		// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
		// Begin Wizard Added Variable Initialization

		// End Wizard Added Variable Initialization

		// Begin Wizard Added Custom Method Calls

		// End Wizard Added Custom Method Calls
		_custOrdGrid = (EpiUltraGrid)csm.GetNativeControlReference("741857dd-a984-45d3-ad73-94903e9abcd2");
		_custOrdGrid.DisplayLayout.Bands[0].Columns.Add("UserChar1","User Char 1");
		_custOrdGrid.DisplayLayout.Bands[0].Columns.Add("DocExtPriceDtl","Ext Price Detail");

		_boRdr = WCFServiceSupport.CreateImpl<BOReaderImpl>((Ice.Core.Session)oTrans.Session, Epicor.ServiceModel.Channels.ImplBase<Ice.Contracts.BOReaderSvcContract>.UriPath);

		_custOrdGrid.InitializeRow += new Infragistics.Win.UltraWinGrid.InitializeRowEventHandler(_custOrdGrid_InitializeRow);
	}

	public void DestroyCustomCode()
	{
		// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
		// Begin Wizard Added Object Disposal


		// End Wizard Added Object Disposal

		// Begin Custom Code Disposal

		// End Custom Code Disposal
		_custOrdGrid.InitializeRow -= new Infragistics.Win.UltraWinGrid.InitializeRowEventHandler(_custOrdGrid_InitializeRow);
		_boRdr.Dispose();
	}

	private void _custOrdGrid_InitializeRow(object sender, Infragistics.Win.UltraWinGrid.InitializeRowEventArgs args)
	{
		// this is where we will actually populate the datagrid
		if(!string.IsNullOrEmpty(args.Row.Cells["OrderNum"].Value.ToString()))
		{
			DataSet orderHedDS = _boRdr.GetRows("Erp:BO:SalesOrder","OrderNum = \'" + args.Row.Cells["OrderNum"].Value.ToString() + "\'", "UserChar1,DocExtPriceDtl");
			args.Row.Cells["UserChar1"].Value = orderHedDS.Tables[0].Rows[0]["UserChar1"].ToString();
			args.Row.Cells["DocExtPriceDtl"].Value = orderHedDS.Tables[3].Rows[0]["DocExtPriceDtl"].ToString();
		}
	}
}

DISCLAIMER
I would highly recommend coming up with a better solution than this in the long run. As it stands, the GetRows method will run for every row in the grid. Ideally, you should populate whereClause in the InitializeRow event and then run _boRdr.GetRows once and loop through the grid to populate each column with the returned dataset.

Hopefully this will help in getting you started. Let me know if you have questions. I will respond as I am able

daisygonzales · September 27, 2021, 9:01am

Hi Wesley,

My apologies for the late response. Thank you for the solution, I was able to follow the steps provided but the new columns are still empty. Maybe I am missing something.

Regards,
Daisy

wcouch · September 30, 2021, 1:17pm

Hey Daisy! Thank you for your patience. Would you be able to provide the customization code that isn’t working?

daisygonzales · October 11, 2021, 2:04am

Hi Wesley,

Below are the codes, I just put 1 column (OrderHed.ShortChar02) as I wanna see if it will work.

// **************************************************
// Custom code for CustomerTrackerForm
// Created: 27/09/2021 5:23:56 AM
// **************************************************

extern alias Erp_Contracts_BO_Customer;
extern alias Erp_Contracts_BO_CreditManager;
extern alias Erp_Contracts_BO_Company;
extern alias Erp_Contracts_BO_ARInvoice;
extern alias Erp_Contracts_BO_Quote;
extern alias Erp_Contracts_BO_CustCnt;
extern alias Erp_Contracts_BO_ARLOC;
extern alias Erp_Contracts_BO_CashDtlSearch;
extern alias Erp_Contracts_BO_ServiceContract;
extern alias Erp_Contracts_BO_CustShip;
extern alias Erp_Contracts_BO_CashRecSearch;
extern alias Erp_Contracts_BO_ARPromissoryNotes;
extern alias Erp_Contracts_BO_SalesOrdHedDtl;
extern alias Erp_Contracts_BO_OrderDtlSearch;
extern alias Erp_Contracts_BO_ShipDtlSearch;
extern alias Erp_Contracts_BO_ARAgingTracker;
extern alias Ice_Contracts_Lib_BOReader;

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
using Erp.Adapters;
using Erp.UI;
using Ice.Lib;
using Ice.Adapters;
using Ice.Lib.Customization;
using Ice.Lib.ExtendedProps;
using Ice.Lib.Framework;
using Ice.Lib.Searches;
using Ice.Proxy.Lib;
using Ice.UI.FormFunctions;

public class Script
{
// ** Wizard Insert Location - Do Not Remove ‘Begin/End Wizard Added Module Level Variables’ Comments! **
// Begin Wizard Added Module Level Variables **

// End Wizard Added Module Level Variables **

// Add Custom Module Level Variables Here **
private EpiUltraGrid _custOrdGrid;
private BOReaderImpl _boRdr;

public void InitializeCustomCode()
{
	// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
	// Begin Wizard Added Variable Initialization

	// End Wizard Added Variable Initialization

	// Begin Wizard Added Custom Method Calls

	// End Wizard Added Custom Method Calls
	_custOrdGrid = (EpiUltraGrid)csm.GetNativeControlReference("741857dd-a984-45d3-ad73-94903e9abcd2"); 
	_custOrdGrid.DisplayLayout.Bands[0].Columns.Add("ShortChar02","ShortChar02");

	_boRdr = WCFServiceSupport.CreateImpl<BOReaderImpl>((Ice.Core.Session)oTrans.Session, Epicor.ServiceModel.Channels.ImplBase<Ice.Contracts.BOReaderSvcContract>.UriPath);

	_custOrdGrid.InitializeRow += new Infragistics.Win.UltraWinGrid.InitializeRowEventHandler(_custOrdGrid_InitializeRow);

	
}

public void DestroyCustomCode()
{
	// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
	// Begin Wizard Added Object Disposal

	// End Wizard Added Object Disposal

	// Begin Custom Code Disposal

	// End Custom Code Disposal
	_custOrdGrid.InitializeRow -= new Infragistics.Win.UltraWinGrid.InitializeRowEventHandler(_custOrdGrid_InitializeRow);
	_boRdr.Dispose();
}

private void _custOrdGrid_InitializeRow(object sender, Infragistics.Win.UltraWinGrid.InitializeRowEventArgs args)
{
	// this is where we will actually populate the datagrid
	
	if(!string.IsNullOrEmpty(args.Row.Cells["OrderNum"].Value.ToString()))
	{
    	DataSet orderHedDS = _boRdr.GetRows("Erp:BO:SalesOrder","OrderNum = \'" + args.Row.Cells["OrderNum"].Value.ToString() + "\'", "ShortChar02");
    	args.Row.Cells["ShortChar02"].Value = orderHedDS.Tables[0].Rows[0]["ShortChar02"].ToString();

	}
}

}

I’m not sure if I’m calling the correct dataset. Please help. Thank you.

Regards,
Daisy

wcouch · October 29, 2021, 3:11pm

Hey Daisy - I am not ignoring you I just haven’t had any time to circle back to this. My deepest apologies I’ll dive in as soon as I can!

daisygonzales · November 3, 2021, 6:41am

Hi Wesley,

No worries, take your time. Thanks for the update…

Regards and stay safe,
Daisy

wcouch · November 12, 2021, 4:45pm

OK! Finally able to circle back around - thank you for being so patient. I can’t seem to recreate this on my end. It may be helpful to debug using Visual Studio if you have it. I can walk you through setting it up if you don’t already have it installed.

daisygonzales · November 18, 2021, 9:45pm

Hi Wesley, I do not have Visual Studio on my laptop. It’s fine, I think I can manage to set it up. Thanks.
Regards,
Daisy

Shizar115 · November 19, 2021, 7:53am

Hi @daisygonzales ,

Funny thing is that I did this today myself.
Here is the post with full code of it.
You would need to do some coding though:

wcouch · December 1, 2021, 1:43pm

I wonder if it’s something in Kinetics? I can’t imagine how since she’s using the classic forms, but I couldn’t recreate her error on my end. Regardless, thanks for sharing Shizar!

daisygonzales · January 31, 2022, 6:08am

My apologies for a very long overdue response. I tried both of the codes and it’s working if I use different fields. I just realized that OrderHed.ShortChar02 is not part in BO:SalesOrder, that’s why I keep on getting blank under that column.

@wcouch you’re right, doing this customization will make the customer tracker slow down. I think I will just make my dashboard and create a new Sheet.

Thanks Shizar and Wesley for your efforts. I really appreciate it. Cheers!

A.Baeisa · January 31, 2022, 9:15am

Hi @tkoch,
another example of using BOReader that affect the screen/grid performance

tkoch · January 31, 2022, 2:09pm

According to the test I ran BOReader should be faster than DynamicQuery. I think the problem lies in how it is probably rendering (initializing) each row while querying the db each time. I do wish there was a built-in way to do this.

A.Baeisa · January 31, 2022, 2:13pm

agree 100%

Mark_Wonsil · January 31, 2022, 2:18pm

The best place is probably at the server, don’t you think? Take that logic out of the client!

shobdy · April 22, 2022, 3:51pm

Hello,

I followed thia and got it populating the data I want.

My problem is that the column is white and looks like you can make changes in it (have not tested that yet).

How do I lock the column down to Read Only?

Topic		Replies	Views
Add Fields to Epicor Custom Grid Epicor ERP 10	48	18961	January 25, 2022
Adding multiple columns to existing gridviews Epicor ERP 10 epiultragrid	2	2330	November 19, 2021
BAQ EpiDataView Kinetic ERP customization , baq , baq-dataview	13	1890	December 3, 2022
Epicor Grid Column Epicor ERP 10 customization	11	5400	September 20, 2019
Add UD fields from other BO in grid Epicor ERP 10 e10 , customization , ud , grid	22	11888	June 9, 2022

How to Add Fields in DataView

Related Topics