0

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

null
    • Peter_Albrecht
    • 4 days ago
    • Reported - view

    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 did

    2 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 invitems

    That'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 :-)

    • bflack
    • 4 days ago
    • Reported - view

    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.

      • bflack
      • 4 days ago
      • Reported - view

       To clarify. There's one InvItem table, and I'm trying to copy two different fields at each line: WScur -> WSfix and RETLcur ->RETLfix.

      • bflack
      • 4 days ago
      • Reported - view

       Apologies, I misread your earlier suggestion/code.

      The unpacked version works on shorter tasks well, but still flips out when the effort is 100+. I'm unsure how to implement a "do as transaction" before the loops - to speed things up. 

      • bflack
      • 4 days ago
      • Reported - view

       I've fiddled with adding "do as transaction" ... without success; see below. Is there way of getting there more directly without looping?

      do as transaction
          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
      end

    • Fred
    • 3 days ago
    • Reported - view

    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.

    • Fred
    • 3 days ago
    • Reported - view

    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.

    • bflack
    • 3 days ago
    • Reported - view

    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;
        )
    end

    Many thanks for the help (again) !!

      • Fred
      • 3 days ago
      • Reported - view

       Does do as transaction make a difference since you are using the app with a local DB?

    • bflack
    • 3 days ago
    • Reported - view

    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