BPM Linq query with UD Field

jmarenghi · February 12, 2019, 2:32pm

Trying to use a UD Field in the where clause of Linq statement in a BPM.
I have searched and found some good information here, but still cannot get the results I am looking for.

In our case we use Character02 on the Customer table to hold an additional SalesRepCode to link to the SalesRep table. I have BPM (Pre) on Update for Customer. I can get the results I am looking for when I link Compnay & SalesRepCode from the ttCustomer table to the SalesRep table. But when I try to use Company & Character02 to link to Company & SalesRepCode, the syntax check works but I get a Ling error from the UI.

BPM runtime caught an unexpected exception of ‘NotSupportedException’ type.
See more info in the Inner Exception section of Exception Details.

LINQ to Entities does not recognize the method ‘System.Object get_Item(System.String)’ method, and this method cannot be translated into a store expression.

Erp.Tables.SalesRep SalesRep = null;

var ttCustomer_xRow = (from ttCustomer_Row in ttCustomer
where ttCustomer_Row.RowMod == “U”
select ttCustomer_Row).FirstOrDefault();

if (ttCustomer_xRow != null)
{
SalesRep = (from SalesRep_Row in Db.SalesRep
where SalesRep_Row.Company == Session.CompanyID
//&& SalesRep_Row.SalesRepCode == ttCustomer_xRow.SalesRepCode (This Line Wokrs)
//&& SalesRep_Row.SalesRepCode == ttCustomer_xRow[“ShortChar02”]
//&& string.Compare((string)SalesRep_Row.SalesRepCode,(string)ttCustomer_xRow[“ShortChar02”] ,true)==0
select SalesRep_Row).FirstOrDefault();

  if (SalesRep != null)

    {
    callContextBpmData.ShortChar03 = SalesRep.EMailAddress;
    }
  else
    {
    callContextBpmData.ShortChar03 = "NoRecordsSalesRep";
    }

}

dhewi · February 12, 2019, 2:40pm

Linq to Entities depends on which version of Epicor you’re on, but either way UD fields can be tricky. They even sometimes behave differently in different types of directive.

You could save yourself the effort by creating a string variable first, since there’s only a single line, and using that in the Linq instead.

jmarenghi · February 12, 2019, 3:09pm

Thanks Daryl,

We are on 10.2.300.3.

I did try to set a variable (usgin the gui) and pass it to the where clause, but it still choked on me.
Do I simple just declare the string in the C# compiler and assigned it after I the code checks the ttCustomer table? Far from a pro here… appreciate the help.

Thanks,
JM

dhewi · February 12, 2019, 3:14pm

That’s what I’d do in the circumstances, yes.

string src = ttCustomer_xRow["ShortChar02"].ToString();
...
&& SalesRep_Row.SalesRepCode == src
...

In recent versions of Epicor you can use ToString() directly within LINQ too, which never used to work.

A.Baeisa · February 12, 2019, 3:17pm

please correct me if i’m wrong, i can not see Character02 in your code? you are using ShortChar02

jmarenghi · February 12, 2019, 3:57pm

Not getting any Linq errors… this is a plus.
But I still can’t seem to return the data I am looking for.
I even passed the value of src to a popup… so i know it is getting data.
I can query the table in sql to verify that there should be a record from the Linq query,
But my second query must be coming back null for some reason.

Erp.Tables.SalesRep SalesRep = null;

var ttCustomer_xRow = (from ttCustomer_Row in ttCustomer
where ttCustomer_Row.RowMod == “U”
select ttCustomer_Row).FirstOrDefault();

if (ttCustomer_xRow != null)
{
string src = ttCustomer_xRow[“ShortChar02”].ToString();
callContextBpmData.ShortChar02 = src;

SalesRep = (from SalesRep_Row in Db.SalesRep
    where SalesRep_Row.Company == Session.CompanyID
    //&& SalesRep_Row.SalesRepCode == ttCustomer_xRow.SalesRepCode
    && SalesRep_Row.SalesRepCode == src
    select SalesRep_Row).FirstOrDefault();

  if (SalesRep != null)

    {
    callContextBpmData.ShortChar03 = SalesRep.EMailAddress;
    }
  else
    {
    callContextBpmData.ShortChar03 = "NoRecordsSalesRep";
    }

}

My result message:

ResultPopUp

There should be a record:

Query

dhewi · February 12, 2019, 4:01pm

Try leaving out the declaration of SalesRep and just using “var SalesRep” in the line where you’re getting the data?

jmarenghi · February 12, 2019, 4:41pm

Just can’t seem to get this one over the goal line.
Appreciate the help… it feels close, but I guess I am just missing something or don’t have the full breadth of skill to find out where this is,

Erp.Tables.SalesRep SalesRep = null;

var ttCustomer_xRow = (from ttCustomer_Row in ttCustomer
where ttCustomer_Row.RowMod == “U”
select ttCustomer_Row).FirstOrDefault();

if (ttCustomer_xRow != null)
{
string src = ttCustomer_xRow[“ShortChar02”].ToString();
callContextBpmData.ShortChar02 = src;

var SalesRep_xRow = (from SalesRep_Row in Db.SalesRep
    where SalesRep_Row.Company == Session.CompanyID
    //&& SalesRep_Row.SalesRepCode == ttCustomer_xRow.SalesRepCode
    && SalesRep_Row.SalesRepCode == src
    select SalesRep_Row).FirstOrDefault();

  if (SalesRep_xRow != null)

    {
    callContextBpmData.ShortChar03 = SalesRep_xRow.EMailAddress;
    }
  else
    {
    callContextBpmData.ShortChar03 = "NoRecordsSalesRep";
    }

}

dhewi · February 12, 2019, 4:59pm

It certainly looks like it should work. Spaces in the UD field (although the message doesn’t look like it)? The SQL query doesn’t include the check for Company? Otherwise I’m afraid there’s something there I’m not seeing.

Aaron_Moreng · February 12, 2019, 5:07pm

If you hard code “NG” instead of using the variable src, does it still set ShortChar03 to “NoRecSalesRep”?

jmarenghi · February 12, 2019, 5:12pm

Hi Aaron… Thanks for the input…

Hard coding still returns “NoRecSalesRep”.
So I guess the code is structured right.
Just can’t get the query to return data that I know is there.

Aaron_Moreng · February 12, 2019, 5:17pm

Well, it could be that or something simple.
Walking through it…

Are you sure that the customer you are running the BPM against has a ShortChar02 value of “NG”?

Have you cleared the form you’re using to trigger this code? Call Context BPM variable data is kind of icky to work with. Sometimes it clears, sometimes it doesn’t. There’s an article on this forum about when the callcontextBPMdata persists vs. clears…

You could also try instead of using call context variables to create your own BPM variables and explicitly clear them and set them in your code. That would help to simplify I think

jmarenghi · February 12, 2019, 5:27pm

Hi Arron…

So I closed Customer Maintenance, Cleared Client Cache, logged out and back in.
Same result:
I will try to ditch the callContextBPMDate variables… can’t hurt to learn how to skirt them in the future.

Customer table does have NG in ShortChar02 for the CustID i’m testing.

NG%20Query

Aaron_Moreng · February 12, 2019, 5:29pm

Two other silly things to check. Are you in the right company? Are you in the right environment?

jmarenghi · February 12, 2019, 5:54pm

Checked my company and environment… we are solid on that front.
I dumped the callContextBPMData and found your post on pushing our a message in a BPM (thanks).
I swapped the commented line to use the SalesRepCode to in the where clause and ran it successfully.
I toggle back to try use the src variable in the where clause and came up with "NoRecordsSalesRep’ Again

   Erp.Tables.SalesRep SalesRep = null;
   string RepEMail = "";

var ttCustomer_xRow = (from ttCustomer_Row in ttCustomer
    where ttCustomer_Row.RowMod == "U"
    select ttCustomer_Row).FirstOrDefault();

if (ttCustomer_xRow != null)
{

    //string src = ttCustomer_xRow["ShortChar02"].ToString();
    string src = "NG";
    callContextBpmData.ShortChar02 = src;

    var SalesRep_xRow = (from SalesRep_Row in Db.SalesRep
        where SalesRep_Row.Company == Session.CompanyID
        && SalesRep_Row.SalesRepCode == ttCustomer_xRow.SalesRepCode
        //&& SalesRep_Row.SalesRepCode == src
        select SalesRep_Row).FirstOrDefault();
  
     if (SalesRep_xRow != null)

        {
        RepEMail = SalesRep_xRow.EMailAddress;
        this.PublishInfoMessage(RepEMail, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "FirstVar","SecondVar");
        }
      else
       {
        RepEMail = "NoRecordsSalesRep";
        this.PublishInfoMessage(RepEMail, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "FirstVar","SecondVar");
        //RepEMail = SalesRep_xRow.EMailAddress;
        }
  }

SalesRepEMail

Aaron_Moreng · February 12, 2019, 6:02pm

What happens if you use

(string)SalesRep_Row.SalesRepCode == src

or
SalesRep_Row.SalesRepCode.ToString() == src

jmarenghi · February 12, 2019, 6:23pm

Boom! got records.

Many thanks…

Drink on me if our path’s cross at Insights.

-JM

Topic		Replies	Views
BPM for SalesRep Push Epicor ERP 10	9	1099	February 21, 2020
Help with syntax in BPM Custom Code Epicor ERP 10 e10 , bpm	4	654	December 5, 2023
Linq Queries Epicor ERP 10 bpm	2	598	February 25, 2021
Simple linq statement throws error Epicor ERP 10 bpm	6	1074	July 8, 2020
UD Table BPM error NotSupportedException Epicor ERP 10 bpm	14	2213	December 29, 2021

BPM Linq query with UD Field

Related topics