Need some help editing this looped script to better suit our needs...

[angelleye]

Quick overview of our system:

We have a FM file called Bids which holds of the products that we are receiving. Bids includes fields like PO Number, Item Number, QTY, Cost, etc.

Here is a look at our Bids file: http://www.dealexpress.com/products/bids.jpg

What you see here are 5 items on the same PO#, 2 of which have a cost of $20, 2 have a cost of $0, and 1 has a cost of $30. The green button that you see that has a 5 on it is a button we click to "check in" these items. What happens upon clicking it is that it adds up the QTY of the items on the same PO, with the same Item Number, and the same Cost (bid) and creates an inventory item in our system.

Here is a look at the script that does this: http://www.dealexpress.com/products/script.jpg

As you can see, it finds all records where Item Number, PO Number, and Bid are the same. It then loops through them and adds up a total QTY to use when adding this item to our inventory.

The problem is, it stops right there and does not continue on to do the same thing for the rest of the items on the PO. We need a separate inventory item for each value of bids with the correct QTY within a single PO.

Any information I can get on how to accomplish this would be greatly appreciated. Thanks!!

[angelleye]

Here's what I've come up with on my own: http://www.dealexpress.com/products/script2.jpg

Idea being that it will first do a find for all records with the same Purchase Orders::Purchase Order and Products::Item_Number. Let's say this returns a set of 5 records with 3 different costs. The script then sorts these records by Bid descending, so my found set should now be sorted from most expensive Bid to cheapest. Then it moves to the first record in the found set.

Then loop #1 starts and simply creates a variable to hold the current bid value ($currentBidValue). Then, the second loop begins and basically takes over with the original script we're already using except that it uses the $currentBidValue so that it only collects the items with the same Bid amount and runs the New Record from Bids script for those items, successfully adding all items with the same cost to the same PO in our inventory.

Then, my thinking is that from here it would move on to the next record of the first loop and go through everything again. $currentBidValue would be repopulated with the Bid value in the next record of the first Find so that this time the second Find will only include items with THAT new Bid amount. It would go through everything again, creating another PO record for all items with that new Bid amount, and then move on again.

What's happening, though, is that it's only adding the first set. For instance, if the first find which only uses Item_Number and Purchase Order returns 6 records, 3 of which have a Bid of 0 and the other 3 have a bid of 400. Well, it gets sorted so that the items with a Bid of 400 show up first in the found set within the script. It then creates a PO in our system and correctly adds up 3 at 400, but it doesn't move on to add another with 3 at Bid of 0 like I need it to.

Have I confused you enough yet? Do I make any sense? I feel like I'm close but I just don't know what to do from here.

[angelleye]

I think maybe it must has something to do with having 2 different Finds? After the second Find takes place within the second loop, the first found set is now gone, so when it tries to move to the next record of the first found set it no longer exists and simply exits out. Does that sound right? Is there any way to make this work how I'd like?

[mr_vodka]

Drew,

I think that it may better serve you to create a concatenated key and then have a calc field that sums up the qty via a self join relationship keying on the concat field.

Then you can run a loop script to add to your inventory. Do not forget to compensate for records with duplicate concat keys being that you only need one copy of the calc field to update your inventory, otherwise it may create a second similar record in the inventory if you do not handle it properly.

[angelleye]

You've pretty much lost me there...??

I've been able to get VERY close to what I need with the following: http://www.dealexpress.com/products/goodscript2.jpg.

It sets initial values and then moves to the next record to compare. If the Bids are NOT equal, it goes ahead and runs Perform Script step which creates our PO record for the current values. Then it moves on to the next one...although it's actually already on the next record.

That's the problem now. The way the script is setup it's actually a little bit ahead of itself throughout. When it gets into the If Bid <> zgnBid part it's actually still working with data from the previous record than it's currently on. So what happens is it sets the Added to POs field to Yes on the record it's on...which actually is NOT the record that is about to get added to PO's.

This isn't a crucial deal, because once this script is working all the records should have Added to POs set to yes anyway.

As it stands now, it gets through each record, except that when it's on the last record the If Bid <> zgn Bid check fails because at this point gnBid does equal Bid for the last record.

I also had a check for record number in there for when to end the script. If I added the Exit on last option added to the go to next record step, then it was leaving out the stuff after it...which was the final run-through for the last record. Or so I thought.

It might just be the fact that the Bid <> zgnBid is failing for the last record and in fact leaving the Exit on last option on the go to next record would work.

As it stands now it will do exactly what it needs to do for all records but the last one.

[angelleye]

and actually, in a case where the last 2 of 3 records had the same Bid value, it only did the 1st one and didn't do anything with the 2 matching ones...because of the same problem I'm guessing. Bid was 50, and then it matched the next record which was 50 so it looped around instead of running the Perform Script step. But then on that next go-round the check for Bid <> zgnBid fails and it doesn't ever run the Perform Script step.

[angelleye]

Ok. I got it working.

____________________________

* #This script will first find all records on the same PO for the item you click on. It then compares each and creates PO items with correct QTY and Bid prices for the entire found set of items on that PO.

* If [ IsEmpty(Products::Item_Number) ]

* Exit Script [ ]

* End If

* Set Field [ Bids::zgtPO; Purchase Orders::Purchase Order ]

* Set Field [ Bids::zgtItem; Products::Item_Number ]

* Enter Find Mode [ ]

* Set Field [ Products::Item_Number; Bids::zgtItem ]

* Set Field [ Purchase Orders::Purchase Order; Bids::zgtPO ]

* Perform Find [ ]

* Sort Records [ Specified Sort Order: Bids::Bid; descending ] [ Restore; No dialog ]

* Go to Record/Request/Page [ First ]

* Set Variable [ $NumberOfRecords; Value:Get(FoundCount) ]

* Loop

* Set Field [ Bids::zgnBid; Bids::Bid ]

* Set Field [ Bids::zgtPO; Purchase Orders::Purchase Order ]

* Set Field [ Bids::zgtItem; Products::Item_Number ]

* Set Variable [ $QTY; Value:$QTY + Bids::Quantity ]

* If [ Get ( RecordNumber ) = 1 ]

* Set Field [ Bids::Added to POs; "Yes" ]

* End If

* If [ Get(RecordNumber) = $NumberOfRecords ]

* Set Field [ Bids::Added to POs; "Yes" ]

* Set Field [ Bids::zgnQty; $QTY ]

* Perform Script [ “New Record from Bids with Qty” from file: “Purchase Order Items” ]

* Select Window [ Current Window ]

* Go to Layout [ original layout ]

* Exit Script [ ]

* End If

* Go to Record/Request/Page [ Next ]

* If [ Bids::Bid <> Bids::zgnBid ]

* Set Field [ Bids::Added to POs; "Yes" ]

* Set Field [ Bids::zgnQty; $QTY ]

* Perform Script [ “New Record from Bids with Qty” from file: “Purchase Order Items” ]

* Select Window [ Current Window ]

* Go to Layout [ original layout ]

* Set Variable [ $QTY; Value:0 ]

* Else

* Set Field [ Bids::Added to POs; "Yes" ]

* End If

* End Loop