7 Replies Latest reply on Aug 11, 2014 5:42 PM by ch0c0halic

    Bad Scripting or Bug in FMPA 13

    MEC

      I currently have a script with problems that causes me to think there “may” be a bug in FileMaker 13 Advanced.

       

      Client is a retailer with multiple shipping/warehouse locations. A Customer Order(CO) can be built by the sales staff with items shipping from multiple locations on a single CO. The Purpose of the script is to first check if the CO includes lines from multi locations, and if so, build new CO’s with single location. i.e. A single CO has different locations on several lines.

      Line 1 is location 2

      Line 1 is location 1

      Line 1 is location 5

      Line 1 is location 1

      Line 1 is location 3

      Line 1 is location 2

      Line 1 is location 1

       

      3 additional CO’s need to be created, for a total of 4 CO’s

      Order # 12345 (location 1…. 3 line items)

      Order # 12345A (location 2…. 2 line items)

      Order # 12345B (location 3…. 1 line item)

      Order # 12345C (location 5…. 1 line item)

       

      Note: Primary Key in CO is UUID ….. auto enter text field, get (UUID)

       

      The Bug: In the part Noted below, some of the Line Items, CO UUID field don’t get end up empty (they were originally populated with the original CO UUID), and some get two UUID’s in the same field with the last character of the first ID missing.

       

      Example:

      I have the following CO UUID’s

      Location Parent UUID

      Location 1 95D6DB75-4CD3-4EBA-A325-FE0F526ED3D0

      Location 2 7A7C9876-4D81-4396-8458-0BFCA1C2205C

      Location 6 B8A3F46B-F2A2-4A69-92A4-DB5EAAF7AC36

      Location 8 29C835E6-0229-4DE4-B595-423F6AD223CB

       

      What I end up with

      Location Parent UUID

      Location 1 95D6DB75-4CD3-4EBA-A325-FE0F526ED3D0

      Location 2

      Location 6 B8A3F46B-F2A2-4A69-92A4-DB5EAAF7AC37A7C9876-4D81-4396-8458-0BFCA1C2205C

      Location 8 29C835E6-0229-4DE4-B595-423F6AD223CB

       

      I’ve found, If I use Flush Cache to Disk at the beginning of the script, it works fine. I don’t feel good about having to use Flush Cache. Does anyone have thoughts as to what may be causing this?


      MacBookPro i7 processor

      16 gigs of RAM

      OWC SSD

      512 MB of RAM allocated to FMP Advanced 13

       

       

      Method used:

      1. ) UUID of original CO is placed in global variable $$OriginalUUID
      2. ) Set a variable to the Count of unique locations $$LocationCount
      3. ) Let the user select a location to start Pulling (gathering the items for shipping at a single location) the order. User selects their location (a global text field with a number/location ID in it) g_location.
      4. ) Perform a find for the original CO using $$OriginalUUID
      5. ) Set variable $Count to 1
      6. f) Start a loop
        • (1) Duplicate the original CO and setting some some fields (Order Number, Shipping status, etc)
        • (2) If $Count = 1
          1. Set a variable, $COIDs to the CO’s UUID
          2. (3) Else
            1. Set the Variable, $COIDs to $COIDs and the CO’s UUID ( $COIDs ends up with a carriage return list of all the new duplicated CO’s
            2. ii) End If
              • (1) Increment $Count to $Count + 1
              • (2) Exit Loop if $Count = $LocationCount
      7. g) End Loop
      8. ) Perform another find to find the Original CO
      9. ) Go To Related Records (line items)
      10. ) Go to first record
      11. Start Loop (to omit the line items with the location the user chose to pull the items from)
        • (1) If user selected location to pull items from, g_location = line item location
          1. Omit record
          2. End If
          3. (2) Go to next record
      12. l) End Loop
      13. Set the list of new CO UUID’s, $COIDs to $COIDs & ¶
        • (1) Note: I’ve tried the script with this line disabled as well
      14. ) Set the location list variable to remove the location the items that will be pulled from, Set Variable $$LocationList ; Substitute ( $$LocationList ; g_location & ¶ ; “” )
      • o) Reset the $Count variable to 1
      1. ) Replace OR walk the lines to set the new CO UUID’s in the CO Lines with the new CO UUID’s, thus re assigning the lines to the newly duplicated CO’s. NOTE: I’ve tried both Replace and walking the lines with the same results
      2. ) Find the Original CO
      3. ) Go To Related Records (CO Line items)
      4. ) Go to first record
      5. ) Omit all of the pulled location items from the found set
        1. i) Loop
          • (a) If g_Location (user selected location to start pulling items) = CO line location
            1. Omit Record
            2. (b) End If
            3. (c) Go to Next Record, exit after last
            4. End Loop
            5. ) Reset Variables to remove the User selected location they chose (to pull items)
              1. ) Set Variable [$COIDs ; Value:Substitute ( $COIDs ; g_location & ¶ ; “” )]
              2. ) Set Variable [ $$LocationList ; Value:Substitute ( $$LocationList ; g_location & ¶ ; “” )]
              3. ) Set Variable $Count back to 1
              4. ) Find line items from original CO and specified location in variable location list and set the child ID to the new associated (duplicated) CO UUID
                1. i) Loop
                  • (a) Enter find mode
                  • (b) Set child ID field to $$OriginalUUID
                  • (c) Set location to to selected location from Location list GetValue ( $$LocationList ; $Count )
                  • (d) Perform Find
                  • (e) Go to first record
                  • (f) Loop
                    1. Set Field line item Parent ID to selected value from list of UUID’s in variable GetValue ( $COIDs ; $Count )
                    2. Go to next record, exit after last
                    3. (g) End Loop
                    4. Exit Loop [( $Count + 1 ≥ $$LocationCount )
                    5. Set Variable [ $Count ; Value:$Count + 1]
                    6. 5) End Loop
                    7. ) Finally, clean up, null the Global Variables.
        • 1. Re: Bad Scripting or Bug in FMPA 13
          erolst

          It's not a bug …

           

          See the attached example to see your goal can be achieved with much less code.

           

          The script can easily be adapted for a higher-level CustomerOrders table, where the first LocationOrder record needs to be created, too; ATM it's the context for line item collecting and its existence implicitly assumed in the script (and nothing happens if all items are from the same location).

           

          PS: next time please do without the fancy formatting, and give us the original script code (quite easy on OS X with Print > Open in Preview, copy PDF text), or a sample file.

          • 2. Re: Bad Scripting or Bug in FMPA 13
            MEC

            erolst, thank you for the file and the time taken to help. I'm going to study this a bit more, and find out what's really going on in your very clean, succinct version.

             

            BTW, what does "ATM" mean as you wrote in your reply?

             

            I'm looking forward to seeing how your script works in my scenario with a current record count of 122K CO's and half a million lines.

             

            Were you able to spot my errors? If so, what are they?

             

            Again, thanks

            • 3. Re: Bad Scripting or Bug in FMPA 13
              erolst

              MEC wrote:

              what's really going on in your very clean, succinct version.

              Thanks; now let's hope it's error-free, too, which is the most important requirement.

              MEC wrote:

              BTW, what does "ATM" mean as you wrote in your reply?

              IIRC, it means 'At the Moment' (as any major dude Google will tell you … )

              MEC wrote:

              Were you able to spot my errors? If so, what are they?

               

              Not sure – I didn't bother with re-writing your script, but started from scratch. Try using List() to compile lists, and if you think you need to whittle down lists within loops, write a custom function where you can be sure it works every time; the error is probably hidden in code like

               

              Set Variable [ $$someList ; Substitute ( $$someList ; someItem & ¶ ; "" ) ]

               

              which better should read

               

              Set Variable [ $someList ; Let ( ~l = Substitute ( $someList & ¶ ; someItem & ¶ ; "" ) ; Left ( ~l ; Length ( ~l ) - 1 ) ) ]

               

              to 1. cater for the possibility of the item being located at the end of the list, 2. remove the temporary ¶ (which otherwise would remain if the item wasn't at the end), and 3. not use $$vars unnecessarily …

               

              – On the other hand, why bother with list-thinning? Just process the list from beginning to end.

               

              MEC wrote:

              I'm looking forward to seeing how your script works in my scenario with a current record count of 122K CO's and half a million lines.

              Do you want to apply this logic retroactively to a massively sized table? Maybe try with a smaller sets first (which is the scenario I had in mind, anyway), until you've ironed out any wrinkles.

               

              Here's what I hinted at in the other post: if you create COs as child records of a higher level table, the script could even be simpler (and doesn't have to rely on sort order, as in this version).

               

              The basic structure would look like:

               

              Customer --< CustomerOrders --< LocationOrders (the current CustomerOrders) --< LineItems >-- Items >-- Locations

               

              Say you're compiling the items for a customer's order in a CustomerOrders (CO) table, pulling items from all over the place(s) into the line items table, where they receive a CO FK. For that you need:

               

              CustomerOrders --< LineItems_forCO >-- Items_forCO >-- Locations_forCO

               

              When you're done, create a list of LocationOrders (LO) records, by reading in a list of unique locations (as per the structure above) and creating an LO for each location. Each LO has a CO FK and a Location FK.

               

              Return to the CO layout, and use Go to Related Record (related only) to go from the CO record to its related line items in LineItems_forCO; then use another relationship

               

              LineItems_forCO::CO_FK = LocationOrders_lookupByCOandLoc::CO_FK

              LineItems_forCO::cLocationID* = LocationOrders_lookupByCOandLoc::locationFK

               

              and the Replace Field Contents step** for the LineItems::LO_FK field of the found set with the expression

               

              LocationOrders_lookupByCOandLoc::LO_PK

               

              So you have (in pseudo-code)

               

              [ start from CO with existing line items ]

              List ( unique locationIDs for related line items )

              Go to LO table

              Loop ( to create one LO per location in the list )

              Go back to COs layout

              GTRR ( to related line items )

              Replace Field Contents

              Go to CO layout and check list of related LOs and their line items

               

              * or instead of calculating this field, add a location_FK as auto-enter to the line items table, to preserve a history of an item's locations

              ** be aware that there is no undo, and potential record-locking conflicts in multi-user scenarios

              • 4. Re: Bad Scripting or Bug in FMPA 13
                gdurniak

                Very odd.  Flush Cache to Disk should not matter

                 

                but Flush Cached Join Results can make a difference

                 

                we would need to see the script ...

                 

                greg

                 

                 

                > I’ve found, If I use Flush Cache to Disk at the beginning of the script, it works fine. I don’t feel good about having to use Flush Cache. Does anyone have thoughts as to what may be causing this?

                • 5. Re: Bad Scripting or Bug in FMPA 13
                  gdurniak

                  Just be careful

                   

                  In addition to finding a better way,  it sometimes pays to see why the less efficient method failed

                   

                  greg

                   

                  > Not sure – I didn't bother with re-writing your script, but started from scratch

                  • 6. Re: Bad Scripting or Bug in FMPA 13
                    erolst

                    gdurniak wrote:

                     

                    In addition to finding a better way,  it sometimes pays to see why the less efficient method failed

                     

                    Sure; feel free to implement and analyze the PO's script, as given in the first post.

                    • 7. Re: Bad Scripting or Bug in FMPA 13
                      ch0c0halic

                      One problem is the way you do the Omits.

                       

                           • Start Loop (to omit the line items with the location the user chose to pull the items from)

                                • (1) If user selected location to pull items from, g_location = line item location

                                     • Omit record

                                     • End If

                                     • (2) Go to next record

                           • l) End Loop

                       

                      Starting at record 1

                      Omit

                      now you are on record 2

                      go to next record

                       

                      Oops, just skipped record number 2!