My database has a script that searches for a list of merchandise that needs to be ordered for a photography project. My find function operates perfectly: Find everything that needs to be ordered. The next step is to eliminate items that are duplicates if they are the same SKU and color and in a different photo. I use variables during the search, but they change during the If/Else part.

For instance: 2 green chairs that appear in Shot 1 means that I need to order 2 green chairs. A green chair in Shot 2 means I do not need to order a third chair, since I can use one of the chairs from Shot 1. The script functions well until I encounter similar chair colors in different shots. Then, records get skipped and the "need to order" amount and "what colors to order" gets thrown off.

Earlier in the script, I sort by a combined field: Item Number_Color_Shot Name

Then this loop, which is creating the problem:

Go to Record/Request/Page [First]

Set Variable [$$ID; Value:Merchandise::mShot Name]

Go to Record/Request/Page [Next]

Loop

If [Merchandise::mShot Name = $$ID]

Go to Go to Record/Request/Page [Next; Exit after last]

Else

Go to Record/Request/Page [Previous]

Set Variable [$$ID; Value:Merchandise::mItem Number_Color]

Go to Record/Request/Page [Next]

If [Merchandise::mItem Number_Color = $$ID]

Set Field [Merchandise::mSample Omit; "do not order"]

Set Variable [$$ID; Value:Merchandise::mShot Name]

Go to Record/Request/Page [Next]

Else

Go to Go to Record/Request/Page [Next; Exit after last]

End If

End If

End Loop

Sort Records

Here's a list of my results using this script:

1. 302 white Shot 1

2. 302 white Shot 1

3. 302 white Shot 2 do not order

4. 302 green Shot 1

5. 302 green Shot 1 do not order*

6. 302 green Shot 2 do not order

7. 302 green Shot 3 do not order

8. 302 green Shot 4 do not order

9. 302 green Shot 4**

10. 302 green Shot 4**

11. 302 red Shot 4

*This should be ordered

**This should not be ordered

Confused? Me too. Any help would be greatly appreciated.

I find it difficult to follow a script that bounces around up and down records in a loop. Why not try a more mathematical approach whereby you count (in a calc field) the quantity of items for each shot and simply subtract from that the quantity of those items from the previous shot.

Jim