Wonder how/if I can verify that a “Key” value for a new record in a UD table
matches some other table.value?
Tried using a query condition on a Method Directive for a UD Table update but…
That didn’t like my Table Relations - objected to linking a Key with a System.Int32 field.
Ref screen shots
I also tried some custom code, similar results, failing on Key == System.Int32
Try using code to return the record that you’re looking for.
See:
https://www.epiusers.help/t/simple-one-liner-c-to-retrieve-data-from-a-table/44627/2
I think that thread is for UD fields, where I’m having trouble with a UD table?
I have tried a few variations of joins in custom code
Save OK but error out when BPM is triggered.
e.g.
InvcHead = (from InvcHead_Row in Db.InvcHead
where InvcHead_Row.Company == ttUD40Row.Company
// fail, error ----- && InvcHead_Row.InvoiceNum.ToString() == ttUD40Row.Key2
// fail, error ---- && InvcHead_Row.InvoiceNum.ToString() == ttUD40Row.Key2
// fail, error — && InvcHead_Row.InvoiceNum == Convert.ToInt32(ttUD40Row.Key2)
// fail, error ---- && InvcHead_Row.InvoiceNum == Int32.Parse(ttUD40Row.Key2)
select InvcHead_Row).FirstOrDefault();
Also tried setting a variable
int intKey = Convert.ToInt32(ttUD40Row.Key2);
then… where InvcHead_Row.Company == ttUD40Row.Company && InvcHead_Row.InvoiceNum == intKey
I remember UD tables being be kind of funky anyway.
Putting aside to today… maybe tomorrow, fresh eys I will be able to spot where I’ve strayed.
P.S.
Setting variable intKey and using it to join actually did work…
Wouldn’t be surprised if there is a better way but for now…
Ref
Erp.Tables.InvcHead InvcHead;
/// this.callContextBpmData.ShortChar01 = “Start”;
foreach(var ttUD40_xRow in ttUD40)
{
var ttUD40Row=ttUD40_xRow;
if(ttUD40_xRow != null)
{
this.callContextBpmData.ShortChar01 = “UD40 OK”;
int intKey = Convert.ToInt32(ttUD40Row.Key2);
//
InvcHead = (from InvcHead_Row in Db.InvcHead
where InvcHead_Row.Company == ttUD40Row.Company && InvcHead_Row.InvoiceNum == intKey select InvcHead_Row).FirstOrDefault();
//
if(InvcHead != null)
{
// zzzzzz
this.callContextBpmData.ShortChar02 = “InvcHead_Row OK”;
this.callContextBpmData.Number01 = intKey;
}
else
{
// zzzzz
}
}
}
I’m just guessing at what you’re end goal is… But here’s a MD that prevents a UD40 record from being created if Key1 <> an existing InvcHead.InvoiceNum
It uses two variables
intKey1 is Key1 in Integer formInvcNum is the InvoiceNum when an invoice number matching Key1 is found. If no invoice exists, InvcNum is set to -1The first widget converts the Key1 to an integer and stores it in intKey1
The second widget finds an invoice with InvoiceNum = Key1. If no invoice found, it sets InvcNum to -1
The Show Message was just fro my debugging and testing.
The condition widget:
Just tests the variable InvNum to make sure it is not -1.
Then I raise an exception to prevent the record from updating.
You might have only needed to add a variable and the Set Variable widget, to your original BPM.
I was able to do it with just the new variable and a “Number of rows in qry is not less than 1” condition:
The table Relations are just Company == Compnay`
Note: Using the Convert.ToInt32(ttUD40Row.Key1) will throw an error if Key1 can’t be converted to an int. Either add to the function to trap for non-convertable values of Key1, or set the mask in UD40 form to only allow numbers. I think the Parse() function has some better functionality for converting to Int.
Yes that was the intent.
At least now I have a couple working examples and can start playing around, see what breaks.
Thanks for your input.
I often forget that nothing is ever as simple as I first imagine anyway.
and then there are UD table key fields.
In the table relations you can make the int col be: cast(MyIntColName as nvarchar)
Note that if the col name is ambiguous (exists in more than one table in query) make sure you prefix tablename: cast(MyTable.MyIntColName as nvarchar)
I think the query designer in the condition widget only allows yo to specify the column of each table (see the image in the original post). No expresions, and can’t even specify an operator. It is fixed at =. Also, the join type is fixed at INNER.
oh, thats tough
Erp.ErpEFFunctions.ConvertToInt(s.Key1)
Epicor extension method will do the conversion for you.
What do non-conevertable values yeild?
Erp.ErpEFFunctions.ConvertToInt("123") -> 123
Erp.ErpEFFunctions.ConvertToInt("10h") -> ?? (10, 16, other?)
Erp.ErpEFFunctions.ConvertToInt("x10") -> ??
Erp.ErpEFFunctions.ConvertToInt("0x10") -> ??
Erp.ErpEFFunctions.ConvertToInt("123.45") -> ??
Erp.ErpEFFunctions.ConvertToInt("hello") -> ??
Erp.ErpEFFunctions.ConvertToInt(" 123 ") -> ?? (note the leading and trailing spaces)
Erp.ErpEFFunctions.ConvertToInt("123-") -> ?? (-123 ??)
0 if I read their TryParse correctly (in above post) it all depends I think C# TryParse is smart to trim spaces and some other things.
