Adding a simple Calculated field to a form

Epicor ERP 10
1

I hope someone out there has a simple solution to this. It doesn’t seem like it should be complicated.
I’m trying to add a numeric box to the Time Phase Inquiry that will simply show the total amount of the selected part that has been over-issued to currently open jobs.
In SQL, the query would look like:

select sum(IssuedQty-RequiredQty) OverIssuedQty
from erp.JobMtl
inner join erp.JobHead
on JobHead.JobNum = JobMtl.JobNum
where JobHead.JobClosed = 0
and IssuedQty>RequiredQty
and JobMtl.PartNum = @PartNum

Is the best way with these to add a custom field to one of the tables that will be retrieved for dataview (Part, for example) and then have a BPM that calculates the field when the data is retrieved? I think this could work, but it seems like a very long way around for something very simple.

2

Is this a good use case for Epicor Functions? I haven’t used them before.
If so, how do you integrate them into a form customization? Or are they only really available in the BPM’s?

3

For others out there looking for the solution to this problem, I ended up solving it through the new Epicor Functions feature.

I made a function that takes in PartNum and returns OverIssuedQty as follows:

this.OverIssuedQty = 0;

var OverIssuedMaterials = 
  from Materials in this.Db.JobMtl
  join Jobs in this.Db.JobHead
    on Materials.JobNum equals Jobs.JobNum
  where Materials.IssuedQty > Materials.RequiredQty
    && Materials.PartNum.Equals(this.PartNum)
    select new
    {
      OverIssuedQty = Materials.IssuedQty - Materials.RequiredQty
    };
    
foreach (var OverIssuedMaterial in OverIssuedMaterials)
{
  this.OverIssuedQty += OverIssuedMaterial.OverIssuedQty;
}

This was used on the Time Phased Inquiry by adding the reference for RestClient and the following:

using Ice.Common;
using Ice.BO;
using Ice.Lib.RestClient;

The following was used to call the function and populate the field

private const string ApiKey = “YourAPIKey”;
private const string LibraryName = “YourLibraryName”;

private void Misc_AfterRowChange(EpiRowChangedArgs args)
{

  if (this.Misc_Row.dataView.Count == 0 ) return;
  
  var restClient = new RestClientBuilder()
  .SetDefaultApiKey(ApiKey)
  .UseSession(this.oTrans.CoreSession)
  .Build();
  using (restClient)
  {
  	try
  	{
  		var OverIssuedQtyContent = new RestContent(new { PartNum = this.Misc_Row.dataView[0]["PartNum"].ToString()});
  		var OverIssuedQtyResponse = restClient.Function.Post(
  			"ANXCustomFunctions",
  			"GetOverIssuedQty",
  			OverIssuedQtyContent,
  			published: true);
  		var OverIssuedQtyResult = OverIssuedQtyResponse.GetAnonymousResult( new { OverIssuedQty = 0.0 });
  		if ( OverIssuedQtyResult.OverIssuedQty>0 ) MessageBox.Show("Over-Issued Qty: " + OverIssuedQtyResult.OverIssuedQty);
  		this.nbOverIssuedWIP.Value = OverIssuedQtyResult.OverIssuedQty;
  	}
  	catch (RecordNotFoundException ex)
  	{
  		this.nbOverIssuedWIP.Value = 0.0;
  	}
  	catch (Exception ex)
  	{
  		MessageBox.Show(ex.ToString());
  	}
  }

}

A couple of things to note:

  • I had to install the ssl certificate onto the client machine for the rest call to work
  • If you’re following, in ‘Epicor ICE 3.2 Customization User Guide’ version 10.2.600, the example ‘Calling Function from Client Customization’ then you will see the call to restClient.Function.Post is passing in the custom library (TipLib) and custom function (NextTipNum) names as variables without declaring them. They are just the strings with library and function name.

I hope this helps others trying to achieve what seems like very basic functionality. The functions actually seem like a fantastic addition to Epicor. Reusable code FTW!

… Now if they can only have the ability to layer BAQs, one referencing another like you can do with SQL views.