BPMs and ttTables

Yahoo Archive
1
Now I understand, you are using the query in the Condition formula. I
thought you were using the 4GL code of the Action.
Have you tried setting the condition
the ttPart.SNPrefix field has been changed from "" to any
and number of rows in the 'test' query is not less than 1
'test' being something like.
for each ttpart where ttpart.RowMod = 'U' or ttpart.RowMod = 'A'
first part where part.Company = ttpart.Company and part.SNPrefix =
ttpart.SNPrefix

That should limit it to when someone changes or creates an new the
record (save) and only when they have changed the SNPrefix, and then
find the first part with a matching prefix and trigger your Action.
I don't use SN here, but let me know the results.
Best of luck,
Aaron


Mark Wonsil wrote:
>
> > Below is the code that I call from within the ReportQty.ReportQty Method
> > PreProcess...
> > Condition: the ReportQty.CurrentQty field of the changed row is not
> > equal to the 0 value
> > Action: synchronously execute 4GL /* Define the Variables */ ... record
> > nothing
>
> <cool_stuff_snipped/>
>
> > It's not pretty, but I assure you this code works every time. I don't
> > start with a For... where are you creating your code that it is
> > requiring it?
>
> Excellent example Aaron but maybe I've been a little dense in explaining
> what I'm trying to do. So here's a little background. Our serial number
> system is straight forward: a serial number must be unique for each
> product
> sent out the door. As you know, in Vantage, one serial number can be
> assigned to different parts. That's fine. I'm sure that provides essential
> functionality to others. In order to make serial numbers unique, we want a
> business rule that says "Each serialized part must have a unique prefix".
> That is the only way that we know how we can ensure that no two parts will
> share a serial number. (Open to ideas if there's another way around this.)
> Sure as heck, the users have been reusing prefixes on different parts and
> two different products now have the same serial number. I think to myself,
> "Hey, wouldn't a BPM be a great way to catch these errors? There are
> several
> areas where one can change the prefix of a serial number on a part and
> a BPM
> would catch them all."
>
> So I start out with the knowledge that the Serial Number Prefix is
> stored on
> the Part. I create a Pre-Processing Method Directive for the Part.Update
> method. I looked through the available conditions and think that
> "number of
> rows in the designed query is more than 0" fits the best if the designed
> query is "show me all of the parts (not including the current part) that
> match the SNPrefix of the part I am maintaining". If I find one record
> that
> matches this condition, I want to display a message and cancel the update.
>
> In the condition "number of rows in the designed query is more than 0", I
> click on the word 'designed' and a Compose Query window appears. It is
> here
> that Vantage requires that the query begin with a FOR [EACH | FIRST |
> LAST].
> The Compose Query window is equipped with a data dictionary and will
> auto-complete field names for those record/buffers in the FOR clause. One
> can also insert BAQ constants. Very cool really. So I enter my query
> (and I
> think my lack of Progress is hurting me here) which is:
>
> FOR EACH ttPart, FIRST Part WHERE ttPart.Company = Part.Company AND
> ttPart.PartNum <> Part.PartNum AND ttPart.SNPrefix = Part.SNPrefix
>
> I *think* this query should retrieve the first Part record that
> matches the
> SNPrefix but not for the Part that I'm currently maintaining. If so, the
> user is trying to enter a duplicate SNPrefix that exists for another
> part.
>
> If that condition holds, my action is to "raise exception based on the
> designed template", which should prevent the operation from continuing.
> (Right?)
>
> Now I'm fairly sure there's an error in my Query because my BPM Action
> sometimes fires even though there is no matching record and sometimes
> doesn't fire when there is a matching record. (I have a BAQ that searches
> the Part table to verify existence of a particular SNPrefix.)
>
> I appreciate any thoughts this august gang might have.
>
> Thanks,
>
> Mark W.
>
>


[Non-text portions of this message have been removed]
2
I'm trying to write a BPM that prevents duplication of serial number
prefixes across parts. So on the Part.Update method, I want to do a query
that looks for serialized parts that has an SNPrefix that matches the one in
the ttPart table. My query looks like:

FOR LAST Part WHERE part.SNPrefix = ttPart.SNPrefix

When I check the query, I receive this message:

ttPart SNPRefix must be a quoted constant or an unabbrevidate, unambiguous
buffer/field reference or buffers known to query.(7328)

Is there a trick to using the values in the temporary tables in queries?

Thanks

Mark W.
3
I don't directly know the answer to your question but I do know that when I
get that message when creating a BAQ it is because I am trying to draw a
relationship to a field that does not exist in one of the tables.



Ross



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Mark Wonsil
Sent: Wednesday, July 30, 2008 10:37 AM
To: vantage@yahoogroups.com
Subject: [Vantage] BPMs and ttTables



I'm trying to write a BPM that prevents duplication of serial number
prefixes across parts. So on the Part.Update method, I want to do a query
that looks for serialized parts that has an SNPrefix that matches the one in
the ttPart table. My query looks like:

FOR LAST Part WHERE part.SNPrefix = ttPart.SNPrefix

When I check the query, I receive this message:

ttPart SNPRefix must be a quoted constant or an unabbrevidate, unambiguous
buffer/field reference or buffers known to query.(7328)

Is there a trick to using the values in the temporary tables in queries?

Thanks

Mark W.





[Non-text portions of this message have been removed]
4
> I don't directly know the answer to your question but I do know that when
> get that message when creating a BAQ it is because I am trying to draw a
> relationship to a field that does not exist in one of the tables.

Thanks Ross. In the BPM interface, there is a Query Designer that auto-fills
the field names from drop-down boxes. Pretty cool actually. I think that the
key that you mentioned here is relationships.

I tried this:

FOR FIRST ttPart, each Part where ttPart.SNPrefix = Part.SNPrefix

And that doesn't give me an error. That's great except I think that this is
looking in the part table where the part number's match the ttPart number. I
want to search ALL part numbers to find a duplicate SNPrefix.

Game on...

Mark W.
5
Mark,
the trick to using the tt tables is that you need to define what tt
record you are using. Most of the time there is only one tt record, but
in your code you still must define the record. You do it below with the
" FOR FIRST ttPart" and that is the difference from your first attempt
that specifies the tt record. Now you can link to any table using any
defined link you like. I would start with Company unless you really
want a multi company search. So my code would look much like yours but
I would have reversed the link...

Find First ttPart.
For Each Part where Part.Company = ttPart.Company and Part.SNPrefix =
ttPart.SNPrefix no-lock.

From my experience that will return all Part records in your company
that have the same SNPrefix as the one you are committing to the DB.
If you are just testing to see if there is at least one (prevent
duplicate) you might want to change to a little less intensive search
such as changing the For Each to Find First Part and then add...

If Available Part
Then...

That way you only return one record and it stops looking after it finds one.
Best of luck,
Aaron Hoyt
Vantage Plastics

Mark Wonsil wrote:
>
> > I don't directly know the answer to your question but I do know that
> when
> > get that message when creating a BAQ it is because I am trying to draw a
> > relationship to a field that does not exist in one of the tables.
>
> Thanks Ross. In the BPM interface, there is a Query Designer that
> auto-fills
> the field names from drop-down boxes. Pretty cool actually. I think
> that the
> key that you mentioned here is relationships.
>
> I tried this:
>
> FOR FIRST ttPart, each Part where ttPart.SNPrefix = Part.SNPrefix
>
> And that doesn't give me an error. That's great except I think that
> this is
> looking in the part table where the part number's match the ttPart
> number. I
> want to search ALL part numbers to find a duplicate SNPrefix.
>
> Game on...
>
> Mark W.
>
>


[Non-text portions of this message have been removed]
6
> the trick to using the tt tables is that you need to define what tt
> record you are using. Most of the time there is only one tt record, but
> in your code you still must define the record. You do it below with the
> " FOR FIRST ttPart" and that is the difference from your first attempt
> that specifies the tt record. Now you can link to any table using any
> defined link you like. I would start with Company unless you really
> want a multi company search. So my code would look much like yours but
> I would have reversed the link...

Point well taken. I always do that in my BAQs.

> Find First ttPart.
> For Each Part where Part.Company = ttPart.Company and Part.SNPrefix =
> ttPart.SNPrefix no-lock.

Unfortunately, BPM queries MUST begin with a FOR. Your example makes perfect
sense, but Vantage will not allow that syntax here. :-(

> That way you only return one record and it stops looking after it finds
> one.

Again, a very good point.

While I thought I had it, while testing it the query is finding records that
match while a separate BAQ shows that no records exists. <sigh>

Thanks,

Mark w.
7
> FOR FIRST ttPart, each Part where ttPart.SNPrefix = Part.SNPrefix

Quick question. I assume that if you select more than one part in Part
Maintenance that the ttPart table will have more than one part in it. How
the #$%! do you know which ttPart record is being sent to the BPM?

Curiouser and curiouser...

Mark W.
8
Mark,
Below is the code that I call from within the ReportQty.ReportQty Method
PreProcess...
Condition: the ReportQty.CurrentQty field of the changed row is not
equal to the 0 value
Action: synchronously execute 4GL /* Define the Variables */ ... record
nothing
4GL code:
/* Define the Variables */
DEFINE VARIABLE stackid AS CHARACTER INITIAL "" NO-UNDO.
DEFINE VARIABLE job AS CHARACTER INITIAL "" NO-UNDO.
DEFINE VARIABLE date AS DATE INITIAL Today NO-UNDO.
DEFINE VARIABLE wcs AS CHARACTER INITIAL
"MACH1,MACH2,MACH3,MACH4,MACH5,MACH6,MACH7,MACH8,MACH9,MACHJ,ASM,INSP"
NO-UNDO.
DEFINE VARIABLE sup AS CHARACTER INITIAL "2,218,135,133,4,138" NO-UNDO.
DEFINE VARIABLE grnd AS CHARACTER INITIAL "SHRD1,SHRD2,CMPT" NO-UNDO.
DEFINE VARIABLE shift AS CHARACTER INITIAL "" NO-UNDO.
DEFINE VARIABLE cust AS CHARACTER INITIAL "" NO-UNDO.
DEFINE VARIABLE stackcount AS CHARACTER INITIAL "" NO-UNDO.
DEFINE VARIABLE material AS CHARACTER INITIAL "" NO-UNDO.
DEFINE VARIABLE issued AS CHARACTER INITIAL "" NO-UNDO.
DEFINE VARIABLE scrap AS INTEGER NO-UNDO.
DEFINE VARIABLE goodparts AS INTEGER NO-UNDO.
DEFINE VARIABLE rcvparts AS INTEGER NO-UNDO.
DEFINE VARIABLE wipmtl AS INTEGER NO-UNDO.
DEFINE VARIABLE remsheet AS CHARACTER INITIAL "" NO-UNDO.
DEFINE VARIABLE operator AS CHARACTER INITIAL "" NO-UNDO.
DEFINE VARIABLE remparts AS CHARACTER INITIAL "" NO-UNDO.
DEFINE VARIABLE n AS INTEGER NO-UNDO.
DEFINE BUFFER LaborDtl2 FOR labordtl.
DEFINE VARIABLE earned AS DECIMAL NO-UNDO.
DEFINE VARIABLE worked AS DECIMAL NO-UNDO.
DEFINE VARIABLE qty AS DECIMAL NO-UNDO.

/* Initialize Integers */
qty = 0.
scrap = 0.
goodparts = 0.
n = 0.
rcvparts = 0.
earned = 0.
worked = 0.

/* If workcenter is right Procede */
Find First LaborDtl WHERE (LaborDtl.Company = "VPC") and
(LaborDtl.EmployeeNum =
ttReportQty.EmpID) and
(LaborDtl.ActiveTrans = True) and
(LaborDtl.JobNum =
ttReportQty.JobNum) and
(LaborDtl.AssemblySeq =
ttReportQty.AssemblySeq) and
(LaborDtl.OprSeq =
ttReportQty.OprSeq) No-LOCK No-ERROR.

IF LOOKUP (ttReportQty.ResourceGrpID, wcs) > 0 AND
(LaborDtl.LaborType) <> "I"
THEN DO:

/*Add up the scrap values from this and other labor detail records and
add receipts up*/


/* Look up Information from Job, EmpBasic, Order */
FIND JobHead WHERE (JobHead.Company = LaborDtl.Company) AND
(JobHead.JobNum = LaborDtl.JobNum)
NO-LOCK No-ERROR.

FIND JobOper WHERE (JobOper.Company = LaborDtl.Company) AND
(JobOper.JobNum = LaborDtl.JobNum) AND
(JobOper.AssemblySeq = LaborDtl.AssemblySeq) AND
(JobOper.OprSeq = LaborDtl.OprSeq)
NO-LOCK No-ERROR.

FIND JobAsmbl WHERE (JobAsmbl.Company = LaborDtl.Company) AND
(JobAsmbl.JobNum = LaborDtl.JobNum) AND
(JobAsmbl.AssemblySeq = LaborDtl.AssemblySeq)
NO-LOCK No-ERROR.

FOR EACH PartWip WHERE (PartWip.Company = LaborDtl.Company) AND
(PartWip.JobNum = LaborDtl.JobNum) AND
(PartWip.WareHouseCode = "FG"):

rcvparts = rcvparts + PartWip.Quantity.

END.


/*Add up the scrap values from this and other labor detail records*/
FOR EACH LaborDtl2 WHERE (LaborDtl2.Company = LaborDtl.Company) AND
(LaborDtl2.JobNum = LaborDtl.JobNum) AND
(LaborDtl2.LaborType <> "I") AND
(LaborDtl2.AssemblySeq = LaborDtl.AssemblySeq) AND
(LaborDtl2.OprSeq = LaborDtl.OprSeq):
scrap = scrap + LaborDtl2.ScrapQty.
earned = earned + LaborDtl2.EarnedHrs.
worked = worked + LaborDtl2.LaborHrs.
goodparts = goodparts + LaborDtl2.LaborQty.
END.

FIND FIRST JobProd WHERE (JobProd.Company = JobHead.Company) AND
(JobProd.JobNum = JobHead.JobNum)
NO-LOCK No-ERROR.

FIND LaborHed WHERE (LaborHed.Company = LaborDtl.Company) AND
(LaborHed.LaborHedSeq = LaborDtl.LaborHedSeq)
NO-LOCK No-ERROR.

IF JobProd.OrderNum > 0
THEN DO:

FIND OrderHed WHERE (OrderHed.Company = JobProd.Company) AND
(OrderHed.OrderNum = JobProd.OrderNum)
NO-LOCK No-ERROR.

FIND Customer WHERE (Customer.Company = OrderHed.Company) AND
(Customer.CustNum = OrderHed.CustNum)
NO-LOCK No-ERROR.

FIND FIRST OrderRel Where (OrderRel.Company = JobProd.Company) AND
(OrderRel.OrderNum = JobProd.OrderNum) and
(OrderRel.OrderLine = JobProd.OrderLine) No-LOCK
NO-ERROR.
/* Set the Variables */
IF Customer.CustNum = 219
THEN DO:
cust= "".
END.
ELSE DO:
cust = string(Customer.Name).
END.
END.
ELSE DO:
cust = "WIP".
END.

qty = ttReportQty.CurrentQty.

stackcount = "____".
stackid = string(LaborDtl.LaborDtlSeq) + FILL("0", (5 -
LENGTH(string(Time)))) + string(Time).
job = CAPS(string(ttReportQty.JobNum)).
date = TODAY.
shift = string(LaborHed.Shift).
goodparts = (JobOper.QtyCompleted + ttReportQty.CurrentQty).
operator = ttReportQty.EmpID.
remparts = string(JobOper.RunQty - goodparts).


/* Now create the acutal export file */

/* Start the output to data file */
output to "\\Vpserver3\epicor\mfgsysdata\Label\RptQty.CSV".

/* Set up Data Identifiers */
export delimiter ","
"StackID"
"Part"
"Job"
"Qty"
"Date"
"Customer"
"Shift"
"StackCount"
"Material"
"MatIssued"
"Scrap"
"GoodParts"
"RemainingSheet"
"Operator"
"Assm"
"OperSeq"
"RemainingParts"
"N"
"RecQty"
"QtyComplete"
"WipMaterial"
"Shipped".

/* Begin Export of data to CSV format */
FOR EACH JobMtl WHERE (JobMtl.Company = LaborDtl.Company) AND
(JobMtl.JobNum = LaborDtl.JobNum) AND
(JobMtl.AssemblySeq = LaborDtl.AssemblySeq) AND
(JobMtl.RelatedOperation = LaborDtl.OprSeq) AND
(JobMtl.WarehouseCode = "RAW"):
issued = string(JobMtl.IssuedQty).
material = string(JobMtl.PartNum).
remsheet = string(JobMtl.IssuedQty - (((JobOper.QtyCompleted +
scrap) * JobMtl.QtyPer) / JobOper.QtyPer)).
n = n + 1.
wipmtl = 0.
FOR EACH PartWip WHERE (PartWip.Company = JobMtl.Company) AND
(PartWip.PartNum = JobMtl.PartNum) AND
(PartWip.JobNum = JobMtl.JobNum) AND
(PartWip.AssemblySeq = JobMtl.AssemblySeq) AND
(PartWip.OprSeq = JobMtl.RelatedOperation):
wipmtl = wipmtl + PartWip.Quantity.

END.

export delimiter ","
stackid
string(ttReportQty.TagPart)
job
qty
date
cust
shift
stackcount
material
issued
scrap
goodparts
remsheet
operator
string(ttReportQty.AssemblySeq)
string(ttReportQty.OprSeq)
remparts
n
rcvparts
JobHead.QtyCompleted
wipmtl
string(JobProd.ShippedQty).
END.

IF n = 0 THEN DO:
export delimiter ","
stackid
string(ttReportQty.TagPart)
job
qty
date
cust
shift
stackcount
string("No Material")
string(0)
scrap
goodparts
string(0)
operator
string(ttReportQty.AssemblySeq)
string(ttReportQty.OprSeq)
string(0)
1
rcvparts
JobHead.QtyCompleted
wipmtl
string(JobProd.ShippedQty).
END.
ELSE DO:
END.
/* Finsh Export and close output */
output close.

/* end of export program *//* end of export program */
END.

It's not pretty, but I assure you this code works every time. I don't
start with a For... where are you creating your code that it is
requiring it?
Aaron Hoyt
Vantage Plastics


Mark Wonsil wrote:
>
> > the trick to using the tt tables is that you need to define what tt
> > record you are using. Most of the time there is only one tt record, but
> > in your code you still must define the record. You do it below with the
> > " FOR FIRST ttPart" and that is the difference from your first attempt
> > that specifies the tt record. Now you can link to any table using any
> > defined link you like. I would start with Company unless you really
> > want a multi company search. So my code would look much like yours but
> > I would have reversed the link...
>
> Point well taken. I always do that in my BAQs.
>
> > Find First ttPart.
> > For Each Part where Part.Company = ttPart.Company and Part.SNPrefix =
> > ttPart.SNPrefix no-lock.
>
> Unfortunately, BPM queries MUST begin with a FOR. Your example makes
> perfect
> sense, but Vantage will not allow that syntax here. :-(
>
> > That way you only return one record and it stops looking after it finds
> > one.
>
> Again, a very good point.
>
> While I thought I had it, while testing it the query is finding
> records that
> match while a separate BAQ shows that no records exists. <sigh>
>
> Thanks,
>
> Mark w.
>
>


[Non-text portions of this message have been removed]
9
Mark,
Try a Condition to limit one record to being sent...
for instance...
the IssueReturn.RowMod field of the updated row is equal to the "U" value
or
the IssueReturn.RowMod field of the updated row is equal to the "U" value

As you get further into this you might want to turn on the "Enable Trace
Logging" in Options, Tracing Options. If you choose to "Write Full
DataSet" you will learn exactly what is going to what BPM Method.

Aaron

Mark Wonsil wrote:
>
> > FOR FIRST ttPart, each Part where ttPart.SNPrefix = Part.SNPrefix
>
> Quick question. I assume that if you select more than one part in Part
> Maintenance that the ttPart table will have more than one part in it. How
> the #$%! do you know which ttPart record is being sent to the BPM?
>
> Curiouser and curiouser...
>
> Mark W.
>
>


[Non-text portions of this message have been removed]
10
> Below is the code that I call from within the ReportQty.ReportQty Method
> PreProcess...
> Condition: the ReportQty.CurrentQty field of the changed row is not
> equal to the 0 value
> Action: synchronously execute 4GL /* Define the Variables */ ... record
> nothing

<cool_stuff_snipped/>

> It's not pretty, but I assure you this code works every time. I don't
> start with a For... where are you creating your code that it is
> requiring it?

Excellent example Aaron but maybe I've been a little dense in explaining
what I'm trying to do. So here's a little background. Our serial number
system is straight forward: a serial number must be unique for each product
sent out the door. As you know, in Vantage, one serial number can be
assigned to different parts. That's fine. I'm sure that provides essential
functionality to others. In order to make serial numbers unique, we want a
business rule that says "Each serialized part must have a unique prefix".
That is the only way that we know how we can ensure that no two parts will
share a serial number. (Open to ideas if there's another way around this.)
Sure as heck, the users have been reusing prefixes on different parts and
two different products now have the same serial number. I think to myself,
"Hey, wouldn't a BPM be a great way to catch these errors? There are several
areas where one can change the prefix of a serial number on a part and a BPM
would catch them all."

So I start out with the knowledge that the Serial Number Prefix is stored on
the Part. I create a Pre-Processing Method Directive for the Part.Update
method. I looked through the available conditions and think that "number of
rows in the designed query is more than 0" fits the best if the designed
query is "show me all of the parts (not including the current part) that
match the SNPrefix of the part I am maintaining". If I find one record that
matches this condition, I want to display a message and cancel the update.

In the condition "number of rows in the designed query is more than 0", I
click on the word 'designed' and a Compose Query window appears. It is here
that Vantage requires that the query begin with a FOR [EACH | FIRST | LAST].
The Compose Query window is equipped with a data dictionary and will
auto-complete field names for those record/buffers in the FOR clause. One
can also insert BAQ constants. Very cool really. So I enter my query (and I
think my lack of Progress is hurting me here) which is:

FOR EACH ttPart, FIRST Part WHERE ttPart.Company = Part.Company AND
ttPart.PartNum <> Part.PartNum AND ttPart.SNPrefix = Part.SNPrefix

I *think* this query should retrieve the first Part record that matches the
SNPrefix but not for the Part that I'm currently maintaining. If so, the
user is trying to enter a duplicate SNPrefix that exists for another part.

If that condition holds, my action is to "raise exception based on the
designed template", which should prevent the operation from continuing.
(Right?)

Now I'm fairly sure there's an error in my Query because my BPM Action
sometimes fires even though there is no matching record and sometimes
doesn't fire when there is a matching record. (I have a BAQ that searches
the Part table to verify existence of a particular SNPrefix.)

I appreciate any thoughts this august gang might have.

Thanks,

Mark W.