Hello,
Our ERP system, Epicor Vantage is using a Progress OpenEdge database. I am building a rule to show a message box (this will be handled by Vantage, however, I need to write a Progress statement to flag any records when the total booked in i.e. sub total of Part Tran\Tran Qty by Job No is greater than the Job Head\Job Prod.
I have tried the following to no avail:
for each ttPartTran BREAK BY ttPartTran.JobNum where (ttPartTran.RowMod = 'A' or ttPartTran.RowMod = 'U' and ttPartTran.TranType = 'MFG-STK' and ttPartTran.WareHouseCode = 'GHD') , each jobhead where jobhead.Company = ttPartTran.Company and jobhead.JobNum = ttPartTran.JobNum and ttPartTran.TranQty > jobhead.ProdQty (TOTAL BY ttPartTran.JobNum) no-lock
It seems the BREAK BY and\or TOTAL BY syntax is incorrect - can anybody help?
Thanks,
Michael
I don't believe that you can do the BREAK BY and TOTAL BY within the FOR EACH WHERE. You need to restructure this as a loop which will iterate through the records and on the LAST OF for each break group, test the TOTAL against the header.
Something like:
for each ttPartTran BREAK BY jobnum where (ttPartTran.RowMod = 'A' or ttPartTran.RowMod = 'U') , each jobhead where jobhead.Company = ttPartTran.Company and jobhead.JobNum = ttPartTran.JobNum ACCUMULATE ttPartTran.TranQty (SUB-TOTAL BY ttPartTran.JobNum)
IF LAST-OF(ttPartTran.JobNum) then ttPartTran.TranQty > jobhead.ProdQty no-lock
This still doesn't work though. Sorry, I'm new to Progress.
The issue is you are trying to apply SQL syntax to the ABL.. The statement needs to be broken into multiple pieces (the ABL breaks syntax into 'data retrieval' and data manipulation' parts):
That last line will need some fixing to turn it into an action statement, but yes, that is exactly what I meant. E.g., if you want to display only those which meet the criteria, the it is something like
Thank you all for your help, this has helped.
However, I don't actually need it to display any info, I just need it to basically say:
'Where the sum of the PartTran.Tranqty grouped by the PartTran.Job Num is greater than the JobHead.Prod Qty.'
The erp system splits it into two. 1. the condition, which is the 4gl code, then 2. an action, which I select 'Show message' etc. However, this Show message bit is all handled by the erp system, so I only need it to meet the conidtion.
I'm not sure I understand you. At the place I wrote "display stuff" you could run a program supplying appropriate parameters or you could add a record in a temp-table which was then used as input to something else or fed to a report or you could set some flag in one of the records involved ... but you do have to do something in order to cause any impact.
I've been told I need to break it down into chunks, defining a local var for the total, totalling it up in a loop. Then if the total is bigger than the Job call an exception message. Do u define variables similair to VB?
It is similar..
DEFINE VARIABLE NO-UNDO.
The NO-UNDO is optional, but recommended unless you need to track changes to the variable in a transaction.
I would recommend reading some of the documentation, which can be found here:
http://www.psdn.com/library/kbcategory.jspa?categoryID=129
There is one that deals with the ABL (http://www.psdn.com/library/entry.jspa?externalID=6189&categoryID=2139) and a datatype specific one (http://www.psdn.com/library/entry.jspa?externalID=2117&categoryID=2139)
Thanks for the links, I will investigate...
Ok, this is becoming frustating now. I still can't get this to work:
DEFINE VARIABLE Total Like ttPartTran.TranQty
for each ttPartTran where (ttPartTran.RowMod = 'A' or ttPartTran.RowMod = 'U' AND ttPartTran.Trantype = 'MFG-STK' AND ttPartTran.WareHouseCode = 'GHD') ,
each jobhead BREAK BY jobhead.JobNum no-lock where jobhead.Company = ttPartTran.Company and jobhead.JobNum = ttPartTran.JobNum:
SUB-TOTAL BY Total
IF Total > jobhead.ProdQty Then
MESSAGE "The received Qty is greater than the Total Job Qty"
Try this (note that the BREAK-BY must be last in the FOR statement):
The code validates ok but I get no message when I book more TranQty than ProdQty in.
I think its a problem with out ERP system.
Thank you for your help though.
Shouldn't you SUB-TOTAL BY jobhead.jobnum?
Yes, you need SUB-TOTAL to get it on the break. Look back at my original example. No need for another variable either.
Shouldn't you SUB-TOTAL BY jobhead.jobnum?
That's what I get for coding in the PSDN text editor..
To wrap things up...
Oops, made a typo...
Message was edited by:
Simon de Kraa