Loop Through Database Results

[NathanB]

Business Case:

I need to send an email to the Approver of Purchase Orders whenever a PO needing their approval is generated.

Problem:

Each Approver can have one or many Authorized Users.

How do I get my code to loop through all the possible Authorized User records for a PO Approver? I am currently using the syntax that I know which give me only the First and Last records for Authorized Users but I am sure there must be an array or 'do loop' function that will ensure that I get every Authoized User of the specified Approver.

Current Code:

for each ttPOHeader no-lock , each PurAgent where (ttPOHeader.Company = PurAgent.Company and ttPOHeader.BuyerID = PurAgent.BuyerID) no-lock.

Define variable vPurAuth as character no-undo.

Find First PurAuth where (PurAgent.Company = PurAuth.Company and PurAgent.ApprovalPerson = PurAuth.BuyerID) no-lock .

If available PurAuth then do:

Assign vPurAuth = PurAuth.DcdUserID + "@totalplastics.com; ".

End.

Find Last PurAuth where (PurAgent.Company = PurAuth.Company and PurAgent.ApprovalPerson = PurAuth.BuyerID) no-lock .

If available PurAuth then do:

Assign vPurAuth = vPurAuth + PurAuth.DcdUserID + "@totalplastics.com; ".

End.

{lib\PublishInfoMsg.i &InfoMsg = "vPurAuth"}.
/*Info message is for testing purposes only and will be removed prior to deployment to production*/

define variable vFrom as character no-undo.
define variable vTo as character no-undo.
define variable vCC as character no-undo.
define variable vSubject as character no-undo.
define variable vBody as character no-undo.
define variable hEmailEx as handle no-undo.
run Bpm/BpmEmail.p persistent set hEmailEx.
assign vFrom = 'vantage@totalplastics.com'.
assign vTo = vPurAuth.
assign vCC = vCC + string( ttPOHeader.EntryPerson + '@totalplastics.com').
assign vSubject = vSubject + 'PO Num, ' + String(ttPOHeader.PONum) + ' is pending your approval'.
assign vBody = vBody + String(ttPOHeader.PONum) + ' test'.
run SendEmail in hEmailEx (
false,
CUR-COMP,
vFrom,
vTo,
vCC,
vSubject,
vBody,
String(ttPOHeader.PONum)
).
if valid-handle(hEmailEx) then delete procedure hEmailEx.
leave.
end.

 

[TomBascom]

Why wouldn't you do something like:

for each ttPOHeader no-lock:

  for each PurAgent no-lock where ( ttPOHeader.Company = PurAgent.Company and ttPOHeader.BuyerID = PurAgent.BuyerID ):

    pAgent = "".

    for each PurAuth no-lock where ( PurAgent.Company = PurAuth.Company and PurAgent.ApprovalPerson = PurAuth.BuyerID ):

      pAgent = pAgent + PurAuth.DcdUserID + "@totalplastics.com; ".

    end.

    /* code to send out e-mail to pAgent list... */

  end.

end.

 

[NathanB]

D'oh! Of course your code works fine... Your the best, Tom!

I had tried something like:

"Find Each...

If available then do:..."

But that wouldn't pass validation.

Your solution works great.

Thanks again,

NB