11 Replies Latest reply on Jun 6, 2013 9:38 AM by philmodjunk

    Script or another way to automate

    BCarruth

      Title

      Script or another way to automate

      Post

           Hi all,

           Using the following data, I need to know how to insert a character "###" after each set of matching records.

           The "Route Number" Field has several addresses with the same route number assigned.

           There are many "Route Numbers" in the database of records. 

           I would like to place the three ### after each set of the same route numbers to act as a break in software we are using. Here is example of what I need. The data is all there except for the "Bundle Break" field.

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                          Load Code                     Route Number                     Address                     City                     State                     Zip                     Bundle Break                      
                          GO1                     1                     XXXXXXX                     XXXXX                     OR                     XXXXX                                            
                          GO1                     1                     XXXXXXX                     xxxxxx                     OR                     

                               XXXXX

                     
                                                 
                          GO2                     1                     XXXXXXX                     xxxxxx                     OR                     XXXXX                     ###                      
                          GO2                     2                     XXXXXXX                     XXXXX                     OR                     XXXXX                                            
                          GO2                     2                     XXXXXXX                     XXXXX                     OR                     XXXXX                     ###                      
                          GO3                     3                     XXXXXX                     XXXXX                     TX                     XXXXX                                            
                          GO3                     3                     XXXXXXX                     XXXXX                     TX                     XXXXX                     ###                      

           File has 6282 records.

           I don't do this every day so I am not even sure that I even know where to start.

        • 1. Re: Script or another way to automate
          schamblee

               Here is a sample with a couple of different reports. https://www.sugarsync.com/pf/D9559058_78149901_681672

          • 2. Re: Script or another way to automate
            BCarruth

                 Hi S Chamblee,

                 I see what you have done, but it is only putting in the ### as text. I need it to actually be a field so that when exported it shows up as another field.

                 Is there a way to count how many route numbers are "1" as in the sample above, and then look at the last field and set the ### in the last record bundlebreak field?

                 Bob 

            • 3. Re: Script or another way to automate
              schamblee

                   I only have 12.

              • 4. Re: Script or another way to automate
                BCarruth

                     Hi S Chamblee,

                     I downloaded trial of FIlemaker to see what you created and I see what you have done, but it is only putting in the ### as text. I need it to actually be a field on the last record of each sub summary so that when exported it shows up as another field.

                     Is there a way to count how many route numbers are "1" as in the sample above, and then look at the last field and set the ### in the last record bundlebreak field and then repeat for each sub summary?

                     Bob 

                • 5. Re: Script or another way to automate
                  philmodjunk

                       Take a look at the GetNthRecord funciton. You can use it to compare the value in the route number field in the current record to the value in the next record in your found set. If they are different return the text you want to mark the "break" in your groups of data.

                  • 6. Re: Script or another way to automate
                    BCarruth

                         I am not very familiar with this function but I have played with it for over two hours and not getting too far.

                         I can get the GetnthRecord for a record but how do I compare to the next record? 

                         I was trying:

                         If ((GetNthRecord (Route Number;1) > GetNthRecord ( Route Number ; 1)+1), "", "XXX")

                         But all I get is empty field in all the fields. 

                         I can's seem to get it to look at the next record.

                         Can you write a simple calculation to help me get started? Thank you in advance.

                    • 7. Re: Script or another way to automate
                      philmodjunk

                           In both cases, your use of GetNthRecord is returning the value of the first record in your found set as both refer to record #1.

                           Try this syntax

                           If ( Get ( RecordNumber ) ≠ Get ( foundCount ) And Route Number ≠ GetNthRecord ( Route Number ; Get ( RecordNumber ) + 1 ) ; "###" )

                           And make sure to use storage options to set this up as an unstored calculation field.

                      • 8. Re: Script or another way to automate
                        BCarruth

                             Thank you so very much for the help. I really do appreciate all of your experience and your willingness to share.

                             One followup question:

                             Now that we have the breaks put in for each carrier, can I modify the formula to put a break after each 20 records and then one at the end like you have it now?

                             I would also like to create a field that lets me set the amount of records for each break.

                              

                             For example: I will set up a field to put in the maximum records for each break. The XXX would be after each group of 20 records. and then after whatever the balance is at the end of the Route number group.

                              

                        • 9. Re: Script or another way to automate
                          philmodjunk

                               Use a global field or variable to set the maximum number. I'll call it Globals::gMaxRec in the following calculation, but you can define a field with global storage in any table in your database.

                               Case ( Mod ( Get ( RecordNumber ) ; Globals::gMaxRec ) = 0 ; "###" ;
                                           Get ( RecordNumber ) ≠ Get ( foundCount ) And Route Number ≠ GetNthRecord ( Route Number ; Get ( RecordNumber ) + 1 ) ; "###"
                                        )

                          • 10. Re: Script or another way to automate
                            BCarruth

                                 It works when I set the the gMaxRec value to 20, but it is counting every 20 instead of between the ###'s made by the first statement we used. Also, just an FYI, I use a single # for the gMaxRec return value for easier identification. 

                                 Is there a way for it to look at just the records between each ### and put #.

                                 Also, my application in addition to seeing the max records between breaks, now I have been told the min number as well. The machines we are using the output for do not like to break closer than 6 records. So I would need to say First Bundle is gMaxRec Value and gMinRec  <6 then back up bundle break from end so that no bundle has less than 6 records between breaks. 

                                 Very "mind knotting" for me as it is out of my brain power so I appreciate your guidance.

                                  

                                  

                            • 11. Re: Script or another way to automate
                              philmodjunk

                                   That could be done, but I think you'll need to use a script to set values in a text file instead of a calculation field. The script would loop through the records setting values in the "break" field each time either the MaxRec count is reached or the group changes to a new group. And then a counter for determining when to set a "MaxRec" break can be reset each time either type of break is encountered while the script loops through the records.