Looping and stuff - I'm stuck!

[chood]

I'm having a hard time figuring out how to build a relatively complicated function that will take an invoice record, loop through the line items and create Purchase Orders for multiple Distributors. If someone might be able to point me in the right direction I would be eternally grateful!

Here's the setup summarized:

Invoice_records.fp3 (The line-item file that holds the invoice order detail)

Peek at the Invoice_records.fp3 file:

Record ID DistributorID Item#

-------------------------------------------

1234 9000 ABCDEF

1234 9000 1234556

1234 8000 GGGGG

To make a long story longer... I need to loop thorough each of these records, and create 2 PO's, 1 for Distributor 9000 containing Item#'s ABCDEF and 123456 and the other for Distributor 8000 contain Item# GGGGG.

Thanks!

Craig Hood

Allegro Medical

America's discount source for medical supplies.

http://www.AllegroMedical.com (800) 861-3211ext. 100

[Kurt Knippel]

Start by writing out your procedure in English. Then translate as much of that into Pseudo Code as possible. Then if you still cannot figure out how the loop should work, post both the English and PseudoCode here and I am sure that we can work it out.

[tlsparker]

One solution would be to create a related file having one record per distributor (you may already have a distributor contacts file). Create the script in this one-record-per-distributor file. Then, when you loop through the found set, you don't have to worry about how to deal with duplicates records for a distributor (which is probably what was driving you nuts in the first place). Now you can use the Go To Related Records Script step to find the appropriate records for each distributor in your other file and create and print your invoices one at a time.

[chood]

Ok, I've worked through this for the last 2 days and started w/ and English version of the script that I want to create and developed some pseudocode. I'm getting farther but can't seem to solve one problem.

File Example (Simplified)

Name: File.fp3

RecordID# DIST.ID# ITEM# gGlobal1 gGlobal2

---------------------------------------------------------

1123 9000 ZZZZZ

1123 9000 11111

1123 7000 AAAA

1123 7000 BBBB

1123 5000 12345

---------------------------------------------------------

English:

Locate all the "1123" records and determine if the DIST.ID# is the same for all records. If it is the same create a Purchase Order to that DIST.ID# for ITEM#'s in that RecordID#. If more than one DIST.ID# is present in the found set, creat Purchase Orders for each DIST.ID# individually with the associated ITEM#(s).

Pseudocode:

Find record 1123

Go to the first record

Set gGlobal1 to DIST.ID#

Perform an external script:

Create a new PO record in the PO.fp3 file

Set the PurchasedFrom field in PO.fp3 to gGlobal1

Start a loop

If the DIST.ID# = gGlobal1 add the ITEM# to the PO

If the DIST.ID# <> gGlobal1 then set gGlobal2 to DIST.ID#

And,

Perform an external script:

Create a new PO record in the PO.fp3 file

Set the PurchasedFrom field in PO.fp3 to gGlobal2

If the DIST.ID# = gGlobal2 add the ITEM# to the PO

Right about here my brain leeks out me ear and I think I'm going about this wrong...

Help!

Thanks,

Craig

[chood]

Thanks Tom,

I think I'm getting closer. If I sort the found set by the DIST.ID# first I don't need to use a bunch of global fields to keep the loop working. I was having a hard time figuring out how to handle the creating of new PO's when I had a situation like this:

DIST.ID#

----------

9000

9000

8000

9000

[Thom]

The INVOICES, LINEITEMS, and PURCHASEORDERS files should all have invoiceID fields. LINEITEMS and PURCHASEORDERS should each have a calc field:

purchaseOrderID(calc,text) = invoiceID & " " & distID

The INVOICES file should have a LINEITEMS portal that is sorted by distID. It should also have a relationship from INVOICES:invoiceID to PURCHASEORDERS:invoiceID. Create a PURCHASEORDERS portal in the INVOICES file. Write a script in INVOICES that creates a new row in the PURCHASEORDERS portal for each unique distID in the LINEITEMS portal. Go to a new row and Set Field[PURCHASEORDERS::distID,distID from LINEITEMS]. You'll need a couple of globals for this script.

Finally, create a relationship from PURCHASEORDERS:purchaseOrderID to LINEITEMS:purchaseOrderID. Make a portal with this relationship. The line items used for the invoice become the line items in the purchase orders.