Need help with a loop, do script

I made a database for listing jewelry items and forming "linesheets" and invoices that have specs, a photo of items, the wholesale and retail prices. Items for linesheets are selected from the "Articles" table and assembled in the "InvItems" subtable, then printed to various templates.
The "Articles" table has pricing data for each item, "WScur" - Wholesale Current Price & "RETLcur" Retail Current Price --both of which are updated as prices fluctuate. However, I need to "fix" these prices for issued Invoices and Linesheets by copying the "current" dynamic prices into "fixed" number fields "WSfix" and "RETLfix" ... so issued documents remain as issued, unless I choose manually overide with the updated price.
I made a button script that works well with a modest number of items (under 20), but it gets completely hammered (hangs for over an hour) when the list is 100-200 items (a full product listing) ...
let Zcnt := 1;
for Zql in InvItems[Article.itemCatg = 1 and WSfix = null] do
InvItems.(WSfix := Article.WScur);
for Zql in InvItems[Article.itemCatg = 1 and RETLfix = null] do
InvItems.(RETLfix := Article.RETLcur)
end;
Zcnt := Zcnt + 1
end
The modifiers "Article.itemCatg = 1" limits the script to "products" and "WSfix = null" prevents overwriting the prior Wholesale Price (if there was one).
My Question: how can I do this function either with (a) a more efficient looping script (maybe there's an error here), or (b) avoid the loop construct altogether? I prefer a button activated script as opposed to a "triggered" script, largely because I can't figure out where the trigger script should live (beyond my experience).
I've attached a screenshot of the "InvItems" subtable with some notes.
Many thanks in advance for thoughts/advice.
Bob
10 replies
-
Hi Bob,
I'm curious whether you actually need the nested loop and why you need the zcnt.
In my opinion this should work for you:
for zql in InvItems[Article.itemCatg = 1 and WSfix = null] do InvItems.(WSfix := Article.WScur); end; for Zql in InvItems[Article.itemCatg = 1 and RETLfix = null] do InvItems.(RETLfix := Article.RETLcur) end;
What you did right now was
per item you wanted you have updated invitems.retlfix several times
so you had a loop which did2 changes if you had 1 invitem.
1+2+1+2=6 changes if you have 2 invitems
1+3+1+3+1+3=12 changes if you have 3 invitemsThat's why ninox crashed with 200 items.
If you remove this nested loop then it will be a lot faster.
if you write "do as transaction" before the loops - it will become even faster :-) -
Thanks. I could use a little help actually translating that code correction. Doesn't surprise me my attempt is off. Not that familiar with this loop syntax.
-
I am with , do you need to put the 2nd loop inside the first one?
Also, I may be wrong, but I think you need to change:
do as server for Zql in InvItems[Article.itemCatg = 1 and WSfix = null] do Zql.(WSfix := Article.WScur); end; for Zql in InvItems[Article.itemCatg = 1 and RETLfix = null] do Zql.(RETLfix := Article.RETLcur) end; end
Lines 3 and 6, remove the reference InvItems and put in the loop variable. Why? Well if you leave InvItems then you are modifying all related records in InvItems. What if you had records in InvItems that all ready have data, then now you will over write the fixed. By switching to the loop variable you are only touching the record that matches your filter criteria.
Or you can try:
do as server for Zql in InvItems[Article.itemCatg = 1 and WSfix = null or RETLfix = null] do Zql.( if WSfix = null then WSfix := Article.WScur end; if RETLfix = null then RETLfix := Article.RETLcur end ); end; end
Line 2, filters for all InvItem record with Article.itemCatg = 1 and either WSfix = null or RETLfix = null.
Lines 4 and 5, should only copy data when the fields are empty.
-
Another way you can simplify:
do as server InvItems.( if WSfix = null then WSfix := Article.WScur end; if RETLfix = null then RETLfix := Article.RETcur end; ) end
With the power of relationships, this tells Ninox to just go through all related records and do whatever you want.
Though you can probably skip the do as server as this should be pretty fast even with 200 records.
-
Fred: all three suggestions work wonderfully. I'm using the desktop app, so I changed to "do as transaction". Your last suggestion had a small typo, when corrected, works like a charm!
do as transaction
InvItems.(
if WSfix = null then WSfix := Article.WScur end;
if RETLfix = null then RETLfix := Article.RETLcur end;
)
endMany thanks for the help (again) !!
-
Guess I didn't understand the 'do transaction' function is a network function. Overall, your corrected script is so much better I didn't notice. Removing 'do transaction' speeds up the cycle by 1 sec for a 180 line list (from 7.5 sec with and 6.5 sec without). So I've removed 'do transaction' and am taking a well desired rest with the newly acquired spare time.
Again, thanks for the help!
Content aside
- Status Answered
- 3 days agoLast active
- 10Replies
- 39Views
-
3
Following