Complex (for me) script help needed

[LaRetta]

I've attached a script. LineItems lists a product, then the sample is listed as 'sample' on the next line. As you will see, it doesn't show product the sample is for ... which is my job tonight. Business has no idea which products are being 'sampled' out!! If a sample was given, it's dollar and quantity was added to stock quantity and price instead and then sample line deducts the dollars back out. NetTransAmount is currently static field. Once conversion is complete and invoicing begins in new system, it will be extend using auto-enter (Replace).

I need to isolate each invoice - convert any samples, damaged etc. to 'real products' and then minus the stock line accordingly. The samples will usually be right below the product they represent. At times, a sample was added at the bottom and I realize they'll have a few to hand clean up.

The quantity and prices must accordingly be adjusted, ie, deduct the sample quantity (which depends upon whether it's divisible into the stock) from stock quantity; deduct the sample dollars (according to the quanitity determined) from the NetTransAmount in stock.

The attached breaks in three places:

1) It never exits the loop although it addresses all records. I'm missing something obvious but can't see it.

2) The second Stock/Sample (starting on 5th record) doesn't convert correctly, ie, Stock Unit Price $1.00 NetTransAmount $3.00 and Quanity 3 should change to NetTransAmount $1.00 Quanitity 1. It's sample changes properly.

3) Third example has two 'giveaways' for the one stock item. It breaks also because the Damaged line doesn't adjust.

I can see no pattern on what is wrong with this script. Any ideas would be most appreciated.

LaRetta

testproduct.zip

[Vaughan]

I don't have FMP 7 at the moment, so I can only give general advice...

"1) It never exits the loop although it addresses all records."

Two ways to exit a loop: with the "Go to Record [exit after last, next]" script step, and "Exit Loop If [ Get( CurrentRecordNumber ) = Get( CurrentFoundCount ) ]". The Get function names might not be quite correct, but the gist is there.

The other thing could be that the script is returning to the first (or perhaps an earlier) record, stopping it from ever reaching the end.

If you can, as a test, increment a counter in a global field so you can see how many times the script is actually looping. It could be also possible that it's extremely slow. Zero the field before the loop, then add 1 to it each time through. I sometimes use Beep steps so I can hear each time it cycles the loop, or occasionally a Show Custom Dialog step, though pressing Return gets very boring and slow after a while.

Finally, with FMP 7 "context" is everything. Each script uses the current layout to determine which table and records it processes; from a developer perspective this means we have to be constantly ensuring that the right layout is selected to ensure that the process will complete correctly. To ensure robust and reliable behaviour this could mean peppering "Go to Layout []" steps throughout scripts to explicitly ensure that processes happen in the right table and on the right set of records. (I use "Freeze Window" steps to avoid all those layout changes causing unnecessary screen movement.)

Sorry I cannot be more specific, but I hope it helps.

[Ender]

Hi Laretta,

I hope your conversion went well.

Your script looks pretty crazy with all the jumping forward and backwards through the records. By using the script debugger, I can see that when it gets to the last record, it ends up going back to the previous 'Stock' record, and then back to the last record, in an infinite loop.

I think I understand what you are doing. I would approach this problem by first looping through the records and setting the Product ID on any that are missing, to be the last Product ID (you said the Samples are usually right after the Stock lines.)

Then use one or two self-join relationships to access the related LineItems by Invoice#, ProductID, and ItemType (showing related Samples and Damaged.) Use a second script to find the 'Stock' items and loop through them once, adjusting the quantities based on something like abs(div(sum(Sample::NetTransAmount); LineItems::UnitPrice)).

Well, I hope that was clear enough to give you another way to approach this.

[LaRetta]

Hi Vaughan! Yes, your ideas have certainly helped and I'll incorporate them.

Jumping around? Mike, surely you jest. In my own defense, there are many types of lineitems not listed - this is a basic example. I had originally tried relationships but couldn't isolate one Sample from the other, since several sample lines might be listed - even with same price, etc. And multiple 'giveaway's per Stock item.

Debugger? Umm, I used it except when I added the second 'giveaway' item. Getting tired, I guess. If there is only one giveaway it exits the script properly. I deleted the last giveaway and it works. Then the only break is the second example.

Well, my mind only seems to see this way to do it - I can't quite envision how it would work the way you're suggesting but I have no doubt you are correct. I'll set the script aside and try to wrap my brain around your ideas. But, if I can identify how to address multiple giveaways, I might be closer to stay with this process.

Can anyone tell why the third stock conversion breaks? The others work but that one doesn't?? Not logical. Stock should produce Quantity 1 and NetTransAmount of 1.00. Okay, well maybe I'll try taking a break, chugging five or six latte' quads and coming back to it, with my mind key'd on using relationships instead. Thank you both so much for the ideas!

LaRetta

[LaRetta]

Hi Mike,

Well, my Set field [] was replacing the value instead of subtracting. Good Lord. I ended up using the script instead of a relationship because I was just too tired to think through alternatives - still am. If there were two 'giveaways' I omitted one then ran the script; then showed the stock again and ran the script on the second (and third) giveaways. It was actually very fast on 400,000 lineitems - I was quite surprised.

When I have time to again play, I will apply your suggestions at which time, you watch, it'll make perfect sense and will come together quickly for me. Exhaustion numbs the brain.

LaRetta