Hi
I’m wanting to use a form epiButton to fetch results from a BAQ and use this result to populate a form field on the MouseDown event. Specifically, the BAQ runs on the UD02 table and returns the top 1 result for Key1 (which happens to be numeric), I want to fetch this result, add 1 to it and populate ReceiptRcvDtl.LotNum with this number. How would I go about doing this?
I’ve created a button and specified the MouseDown Event for the epiButton, and now need to enter some custom code (?), but have no idea where to go from here…
Question: Why do you need a BAQ if you just want the Top 1 result?
You can use GetRows and put Key1 = XXX in the whereClauseUD02
Also for button, I normally use the Event Click rather than MouseDown Event, as the user has a chance to move away the button and cancel the button click.
I could give you close to the exact code for this if you want, but it does sound a bit like overkill for the purpose.
Is the BAQ handling whether a particular key is yet associated with any records? Or are you updating it somewhere else to reflect that numbers have been used? What does the BAQ do that UD02 doesn’t do by itself?
I would also second the use of Click rather than MouseDown.
Hi Toby,
thanks for your tip. I’m not sure what you mean by “use GetRows and put Key1 = XXX in the whereClauseUD02”.
I used the MouseDown event as that is what it suggests in the Epicor Manual - I had started with Click as well - I’ll use that instead 
Hi Daryl,
Thanks for your reply. I’m all for simplicity. The UD02 table is updated via a different mechanism, but you have got me thinking… if we have a receipt with multiple lines (quite common), then we will want to have different lots for each line. if the user doesn’t update the UD02 table between receipts, then whatever we do here will return the same number (that is not what we want). Oh dear… not as simple s I thought.
This all stems from our need to scan in rolls on our shop floor MES. The rolls all need to have unique identifiers for trace-back. That’s why we use the UD02 table to hold the roll record details. It all works well for locally produced items where we use the job number as Key1. The problem comes where we purchase roll stock that then has to be processed in the shop floor MES. Epicor CSG have written a virticalisation (I think that’s what they call it) that translates GS1 compliant bar codes into the MES MaterialEntry record. To do this it needs to have a matching record in the UD02 table - no problem as long a we can put that record in there, and to do that we need to have a unique number for Key1. That’s why I posted this question. But now I have to work out how to increment the UD02 table with a “dummy” record that just has Key1 and so that when the user clicks the button to fetch the next lot number it also increments the UD02 table …
How about a timestamp as the lot code. Any need for the lot code to be continuous? Like 1, 2, 3, 4. Or can it have gaps like 20181213224533, 20181213224535, 20181213224602?
And you if you truly need it to be unique, Go with serial numbers instead of lot
It’s difficult to know exactly what’s best without getting involved with the whole set-up. It’s definitely worth looking at @ckrusen’s recommendations if you can.
If, as I suspect from what you’ve said, you need to stick with the bones of a system that’s already in place, then three possible approaches occur to me:
Use the BAQ you have, and make it updateable. From the form, add a line with the new number and use it.
Get the latest number each time from UD02 and use it for the receipt. Have a BPM which adds a line to UD02 triggered by its appearance in the receipt.
Modify the BAQ to include all receipts and use the higher number of UD02 or receipt lot.
Any code recommended from this point would depend which way you take this …
(Added) I’m assuming with the above that the BAQ itself remains necessary. Options one and two could be more elegantly done with the UD02 adapter directly as @TobyLai says, otherwise.
Good idea Calvin. The lots do not need to be continuous, just unique, but I’ll have to test it out as the [GS1 compliant] barcode is currently like this 01{GTIN}3110{Qty}10x.10.y where x is the lot number and y is the roll number (e.g. 0199329939015737311000114310001256.10.104). If x gets too big (like 20181213224533) then the bar code gets very long and I start running into printing problem on the shop floor label printers.
Hi Daryl,
Yes, I think this approach might work best as, you are right, we are trying to make this fit in with something that is already in place and is working. I think writing a BPM to create a new record in the UD02 Table on Update of the RecDtl table would be the go (I think that I can do this bit). I am still left with the issues of how to get my Receiving form button to populate the Lot number field by fetching the last record from the UD02 table…
Some generic comments. On your UD02, you’ll need some keys, unique identifiers. You have to figure out what from your source record is going to make a unique record in your UD. You have a combo of Key1-5 (which are all strings by the way). Also, I havent grasped exactly what you are trying to do - but from my limited understanding, I dont know if a blind creation of UD02 record upon RcvDtl save is going to be the ticket. Everytime you modify a Reciept, surely you dont want a new record do you? What about only on new RcvDtls?
Can you explain what you are trying to do? From my perspective of just glancing at this thread, it almost seems like you are wanting to pull the latest lot for a particular part if it exists - is this the case? If so, you dont even need UD’s. You can pull the latest PartLot record for a given part number by making a BAQ on Part, parameter of PartNum, top 1, sort ascending by LastUsed or Created date (whichever makes more sense). Then if you have no result, you can also create a new lot automatically too.
Did you make a typo in your example? The roll number ended with …256.10.104 So y = 4 and x = null in that example?
So x is the lot and must be unique over what scope? For the part? For example, is the following table of P/N’s and “roll ID’s” acceptable? ( I added dashes to the Roll ID, just to show the different fields)
| PartNum | GTIN | Qty | Lot# | Roll# | RollID |
|---|---|---|---|---|---|
| MT-101 | 99329939015737 | 1000 | 34 | 1 | 01-99329939015737-3110-1000-1034.10.1 |
| MT-101 | 99329939015737 | 980 | 34 | 2 | 01-99329939015737-3110-980-1034.10.2 |
| MT-101 | 99329939015737 | 980 | 35 | 1 | 01-99329939015737-3110-980-1035.10.1 |
| MT-102 | 99329939015737 | 1000 | 34 | 1 | 01-99329939015737-3110-1000-1034.10.1 |
| MT-102 | 99329939015737 | 1123 | 35 | 1 | 01-99329939015737-3110-1123-1035.10.1 |
Note that row 1 and 4 generate the same Roll ID for two different Part Numbers. Is this allowed? Or does the GTIN have some relationship to the P/N?
Are the fields for Qty ( I assume the Qty of material in FT or LB), Lot# and Roll# variable length?
Is the “10” the precedes the Lot, and the Roll, a fixed set of characters that is always there?
If the Lot needs to always be unique (regardless of Part number), then your UD table needs two use two keys, Key1 for the Lot number, and Key 2 for the Roll number.
OK, I for one am going to assume at this point that the solution already in place is working for you and that what you want is almost exactly what you originally asked for!
Does your BAQ take any parameters? And does it always return a single row?
Assuming one parameter and that one correct row IS returned, your customization code would be something like this:
private void btn_Click(object sender, System.EventArgs args)
{
// ** Place Event Handling Code Here **
EpiDataView edvRcvDtl = (EpiDataView)oTrans.EpiDataViews["RcvDtl"];
if (edvRcvDtl != null && edvRcvDtl.Row > -1)
{
DataRowView rcvRow = edvRcvDtl.dataView[edvRcvDtl.Row];
string param = rcvRow["where your parameter comes from"].ToString();
DynamicQueryAdapter dqadptr = new DynamicQueryAdapter(oTrans);
dqadptr.BOConnect();
string baqname = "Your UD02 BAQ";
Ice.BO.QueryExecutionDataSet dsBAQ = dqbaq.GetQueryExecutionParametersByID(baqname);
dsBAQ.ExecutionParameter[0].ParameterID = "Your Parameter";
dsBAQ.ExecutionParameter[0].IsEmpty = false;
dsBAQ.ExecutionParameter[0].ParameterValue = param;
dsBAQ.AcceptChanges();
dqadptr.ExecuteByID(baqname, dsBAQ);
DataTable results = dqadptr.QueryResults.Tables[0];
if (results != null && results.Rows.Count > 0)
{
string maxkey = results.Rows[0]["Key1"].ToString();
// some code to arrive at your next lot number - round trip to int with Int32.TryParse and back to string?
rcvRow.BeginEdit();
rcvRow["LotNum"] = "your new value";
rcvRow.EndEdit();
}
results = null;
dqadptr.Dispose();
}
}
You need Ice.Adapters.DynamicQuery and Ice.Contracts.BO.DynamicQuery as assemblies to make this work. (And I’ve typed it out from memory, so E&OE and let me know if it needs any tweaking that isn’t obvious.)
As @Chris_Conn says, obviously you don’t want a UD02 record created every time you save, so the matching BPM needs sensible construction. My inclination would be a condition that checks for the matching UD02 record on RcvDtl Update, and creates one if and only if one isn’t there already.
Hi Calvin,
Thanks for your input on this - very much appreciated
To answer your questions:
No typo: in this example the Lot is 001256. The 10 (".10." in the Barcode) represents the Operation Sequence - it’s always 10 for roll stock. 4 is the roll number.
The lot is unique over the scope of the company. The lot can be any length but must be numeric (but like to keep it less than 6 digits long for printing reasons).
We do indeed use Roll number as Key3 in the UD02 table (Key2 is the operation sequence which is always 10 for roll stock)
The quantity field is in Metres (the 3110 prefix indicates this) and is always 6 characters long with leading zeros.
Hi Chris,
Thanks for your tips. What we are trying to do is create an electronic record of roll stock and finished goods that can allow us to trace back faults. Our base materials are roll stock, either manufactured by us or Purchased. we use:
Key1 = Lot (This is the job number if manufactured but not sure how to handle Purchase items yet - that’s what this post is partly about)
Key2 = Operation sequence (10 for roll stock and we make it 10 for purchased items even though there really is no sequence number of course)
Key3 = Roll number (or carton number for Bags)
This gives us a unique code that is easy to sort though as long as you know the Lot. In theory, we could use the same Key1 for various receipts but I know that will cause great confusion in the factory and we want to keep it as simple and easy to understand as possible.
You are right, we don’t want to create a new UD02 record every time someone goes into a receipt so that’s why I want to attach that action to the button in the Receipt Details form.
That’s Brilliant Daryl. Thanks Heaps. I’ll give it a go and let you know if that does the business…
Yep, That did the business alright!
I had to make a slight modification to your script:
private static void btnNewLot_Click(object sender, System.EventArgs args)
{
EpiDataView edvRcvDtl = (EpiDataView)oTrans.EpiDataViews[“RcvDtl”];
if (edvRcvDtl != null && edvRcvDtl.Row > -1)
{
DataRowView rcvRow = edvRcvDtl.dataView[edvRcvDtl.Row];
DynamicQueryAdapter dqadptr = new DynamicQueryAdapter(oTrans);
dqadptr.BOConnect();
string baqname = “FindLastUD02Lot”;
Ice.BO.QueryExecutionDataSet dsBAQ = dqadptr.GetQueryExecutionParametersByID(baqname);
dqadptr.ExecuteByID(baqname, dsBAQ);
DataTable results = dqadptr.QueryResults.Tables[0];
if (results != null && results.Rows.Count > 0)
{
string maxkey = results.Rows[0][“UD02_Key1”].ToString();
// some code to arrive at your next lot number - round trip to int with Int32.TryParse and back to string?
rcvRow.BeginEdit();
rcvRow[“LotNum”] = (int.Parse(maxkey) + 1).ToString();
rcvRow.EndEdit();
oTrans.Update();
}
results = null;
dqadptr.Dispose();
}
}
I then created a PostAction BPM on the Receipt.UpdateMaster BO to create the new UD02 record.
Sweet!
1 Like