1 2 Previous Next 21 Replies Latest reply on Jul 2, 2013 10:07 AM by philmodjunk

    Repost: Script help needed to determine shipping boxes needed

    JasonO'Berry

      Title

      Repost: Script help needed to determine shipping boxes needed

      Post

      Hi,
            
      I am in need of some help crafting, what is to me, a complicated calculation. My company ships Art prints in different sized tubes. Depending on the size of the print and the quantity, I need a calc that can determine which box to use, and the final weight. 

                     Each of our tubes can hold up to 3 prints. So an easy example, if a customer orders 1, 2, or 3 small prints, the calc should determine that 1 small tube can be used. But if they order 4 then 2 small tubes are required. 

                     But if someone orders 1 extra large print and 2 small prints, it should determine that 1 extra large tube can be used.

                     It get more complicated if say a customer orders 1 extra large, 1 large, and 3 small prints. In that case I always want to group the larger prints into the largest tube. So the X-large, Large and 1 small print should use 1 extra large tube, and the remaining 2 small prints should use 1 small tube.

                     Is this an example of where some recursive function should be used or can this be achieved with standard operations.

                     I enter all products in on a line item table and that is where the size field is also. I've also created a boxes table to hold the tube sizes, dimensions, and max quantity.

                     Thanks in advance.

                      

                     UPDATE: I forgot to mention that I have a quantity field that needed to be figured into the script as well.

                      

                     @Ninja & PhilModJunk

                      

                     "I've been trying to get your previous examples to work for me but so far unsuccessfully. Mostly due to the quantity field and using a larger tube if it still has space available. I guess I'm not very experienced with looping and placing commands in the right place. I can get the number of the sizes of prints into variable, but I can't figure out how to correctly step through the variables to get the needed tubes with a 3 print maximum. I've put in the work, I just can't get the results I need. Your help is appreciated.maybe a more detailed walk through is what I need. Thanks."

                      

      "This is getting more complicated by the second. I thought I had a working loop but it would not take into account if the max had not been reached for a larger tube, when it started working on a smaller tube. It would just create another tube and start using the smallest size tube that would accomodate that print, leaving the previous tube with remaining space.

                     Please Help. I would post what I came up with, but i think it's was too complicated and probably not a good starting point for help. So back to the chalkboard."

            

        • 1. Re: Repost: Script help needed to determine shipping boxes needed
          ninja

               Hi Jason,

               You've got the variables set correctly...well done.

               Loop1: Xtra large prints.  Loop through the XL prints until they're all used up, adding +1 to XLtubes as needed.  when 4XL prints are gone, you've got 2XL tubes...one with 3prints and one with one print.  The Variable $XLTubes = 2 and The variable $PrintsInCurrentTube = 1

               Loop2: Large Tubes:  First step is to put two of them into the "currentTube"...If ($PrintsInCurrentTube<3) then $PrintsInCurrentTube gets a +1 and LargePrints get a -1. 

               If ($PrintsInCurrentTube<3) is false, add 1 to LargeTubes (not XL), set $PrintsInCurrentTube=1 and keep on going.

               Loop3: Medium Prints...still using $PrintsInCurrentTube

               Loop4: Small Prints

               If the $PrintsInCurrentTube variable the one you are missing?  That is the variable that continues from the XL loop into the L loop so you don't grab a new tube when you still have room in the XL tube from the previous loop

          • 2. Re: Repost: Script help needed to determine shipping boxes needed
            philmodjunk

                 This one IS complex in a number of ways.

                 See if this demo file works for you: https://dl.dropboxusercontent.com/u/78737945/ShippingTubes.fmp12

                 This one is simplified, you just select the size and qty for line items to get enough test data to see that the script works.

                 What's key is that the size field is one that can be used to sort the line items in descending order by size--which is why I am using a number field for the size.

            • 3. Re: Repost: Script help needed to determine shipping boxes needed
              JasonO'Berry

                   I went with your suggestion Ninja because I had already started it that way. Here's a screenshot of the main parts. My screen isn't big enough to get the whole thing but it shows how the script fully processes two sizes. Do you see any way to improve this or make it more efficient. Thank you both for your help. Trying things in multiple ways helps me grasp things better.

              • 4. Re: Repost: Script help needed to determine shipping boxes needed
                ninja

                     I'm not sure you need mested loops, just sequential loops:

                     Loop

                        If ($XLPrints > 0 )

                          If ($PrintsInTube < 3)

                            SetVariable ( $XLPrints; Value: $XLPrints - 1 )

                            SetVariable ( $PrintsInTube ; Value: $PrintsInTube +1)

                          EndIf

                          If ($PrintsInTube = 3)

                            SetVariable ($XLTubes ; Value: $XLTubes + 1)

                            SetVariable ($PrintsInTube ; Value: 0 )

                          EndIf

                        EndIf

                        Exit Loop If ($XLPrints = 0)

                     End Loop

                      

                     Then follow this structure for the large prints, medium and so forth, carrying the $PrintsInTube value on into the next loop.

                • 5. Re: Repost: Script help needed to determine shipping boxes needed
                  philmodjunk

                       That seems overly complex when compared to:

                  #Script should always be performed from context of Invoices
                       Freeze Window
                       If [ LineItems::_fkInvoiceID // there are line items to pack ]
                           If [ PackagingList::_fkInvoiceID // Packaging records exist ]
                               #Clear any existing packaging records.
                               Go to Related Record [ From table: “PackagingList”; Using layout: “PackagingList” (PackagingList) ] [ Show only related records ]
                               Delete All Records [ No dialog ]
                               Go to Layout [ original layout ]
                           End If
                           #Pull up list of Line Items records sorted by Size in descending order.
                           Go to Related Record [ From table: “LineItems”; Using layout: “LineItems” (LineItems) ] [ Show only related records ]
                           Sort Records [ Keep records in sorted order; Specified Sort Order: LineItems::Size; descending ] [ Restore; No dialog ]
                           Go to Record/Request/Page [ First ]
                           Loop
                               Set Variable [ $Qty; Value:0 ]
                               Set Field [ Invoices::gSelectedSize; LineItems::Size ]
                               Set Field [ PackagingList|BySize::Qty; PackagingList|BySize::Qty + 1 ]
                               Loop
                                   If [ $Remainder // There are unpacked prints that didn't fit in previous tube. ]
                                       Set Variable [ $willFit; Value:Min ( 3 - $Qty ; $Remainder ) ]
                                       Set Variable [ $Qty; Value:$Qty + $WillFit ]
                                       Set Variable [ $Remainder; Value:$Remainder - $WillFit ]
                                   Else
                                       Set Variable [ $willFit; Value:Min ( 3 - $Qty ; LineItems::Qty ) ]
                                       Set Variable [ $Qty; Value:$Qty + $WillFit ]
                                       Set Variable [ $Remainder; Value:LineItems::Qty - $WillFit ]
                                   End If
                                   If [ not $Remainder and //No remaining prints to pack from this line item Get ( RecordNumber ) = Get ( FoundCount ) // there are no more prints to pack ]
                                       Set Variable [ $ExitFlag; Value:True ]
                                       Exit Loop If [ True // exit the inner loop ]
                                   Else If [ not $Remainder //No remaining prints to pack from this line item ]
                                       Go to Record/Request/Page [ Next; Exit after last ]
                                   End If
                                   Exit Loop If [ $Qty = 3 ]
                               End Loop
                               Exit Loop If [ $ExitFlag //No more prints to pack ]
                           End Loop
                           Go to Layout [ original layout ]
                       End If
                        

                  • 6. Re: Repost: Script help needed to determine shipping boxes needed
                    JasonO'Berry

                         Thanks for both of your replies. I am trying to work with PhilModJunk's at the moment as I think I can use it easier to create records in the Packaging table to accumulate the weight of the individual prints as well to get an accurate shipping cost. I'll update with my progress.

                    • 7. Re: Repost: Script help needed to determine shipping boxes needed
                      JasonO'Berry

                           @PhilModJunk

                           Thanks, I've got your script working nicely now. In addition, I'd like to modify it a bit to get a cumulative weight for the tube and the prints that it will hold.  If I add a weight field to the Packaging table do you think it would be possible that, as the script executes, it can grab the weight (possibly from line items record) and add it to the weight field in Packaging as it executes?

                      • 8. Re: Repost: Script help needed to determine shipping boxes needed
                        ninja

                             ...guess complexity is subjective. ;)

                             Either way works fine...I would write it with sequential loops as a favor to other developers following after me...the nested loops work great, but they are much harder (for me and likely for others) to dissect after the fact.  Sequential loops break the process down into bite sized pieces.  But that is more in the realm of preference and multi-developer sustainability, not actual function.

                             The end result is that it works, and that is great.

                        • 9. Re: Repost: Script help needed to determine shipping boxes needed
                          philmodjunk

                               Do you need the total shipping weight of all prints + all tubes or do you need the weight of each packed tube? The total overall shipping weight won't require any modification of the script. Computing the weight of each tube + the prints contained in it would require a small modification of the script.

                               @Ninja, nested loops are such a "fact of life" in programming that it would never occur to me to see sequential loops as easier to understand. wink

                          • 10. Re: Repost: Script help needed to determine shipping boxes needed
                            JasonO'Berry

                                 I would need to compute the weight of each tube and the prints contained. Thanks for asking, after re-reading my request, it sounded like I needed the overall weight when in fact I need individual. I almost have a working script that will do it, but I learn alot after trying to tackle it, and then seeing how an experienced programmer would handle the situation. I feel more like a hacker when I try to modify the scripts that you've come up with, and then see how elegant they "CAN" be when you've worked through it. Thanks so very much to both of you.

                            • 11. Re: Repost: Script help needed to determine shipping boxes needed
                              JasonO'Berry

                                   Scratch that. The solution I was working on, I just can't get it to work. I changed the relationship from INVOICE to PACKAGING2 to be based on InvoiceID and tubeNumber. So when i Set Field Invoice::g_tubeNumber, I expected a new record to be created in PACKAGING2 when I Set Field in the PACKAGING2 tabel, but instead it just updates the record it already created, even though I though that record would no longer be related, due to the relationship of tubeNumber. I even threw a flush cache join thinking that might be the culprit. Can anyone explain this behavior so that I may learn from it? Thanks.

                                   Update: I needed a commit step to get the results. But I still haven't got it working just yet. Still at it.

                              • 12. Re: Repost: Script help needed to determine shipping boxes needed
                                JasonO'Berry

                                     OK, here's what I came up with to get my desired results. I'm open for criticism.

                                • 13. Re: Repost: Script help needed to determine shipping boxes needed
                                  ninja
                                  @Phil: And I love that they are so common in a "rapid deployment Dbase" program.  It makes folks come to me with money to untangle them.  Any Dbase programmer is used to them, but when they get opened up to the world that {thinks they} process things in straight lines they cause no end of troubleshooting opportunities. But I'm way off topic from the OP so I'll shut up now.  Nice job to the both of you.  Best of luck on your art shipments!
                                  • 14. Re: Repost: Script help needed to determine shipping boxes needed
                                    JasonO'Berry

                                         Thanks philMod and Ninja. Both methods were great, I was able to learn a lot from both of your suggestions. It's gotten a little more complicated as it's gone on. Now it needs to be conditional on the type of print, as to how many the packaging can contain. So a framed print gets its own box. I'm going to try and work this out as far as I can. Thanks again.

                                    1 2 Previous Next