13 Replies Latest reply on Oct 22, 2015 7:41 PM by DavidJondreau

    Need help - Seeking numerical order

    Stigge

      Hi

       

      I have run in to a wall. =) am stuck, and checking now if anyone have any easy suggestion for me.

       

      My Table:

      ID = a number from 0001 to 9999 (as text, this is the last 4 digits on a PC name register)

      Status = If the ID is "free" or "in use".

       

      Now I need to build a function that when am purchasing new computers, lets say 10. I then need to find 10 numbers in a numerical order that are free, always the lowest numbers available that are in a 10 scoop.

      Am guessing I need to use a global variable that I use to type in the number of ID`s I need. But now I need some sort of function that search these numbers in a numerical order. :/ that's where am stuck..

       

      Next step is to use these ID`s to deploy them to model, purchase order numbers etc. but that the next problem I think =D

       

      Anyone have any good ides ?

        • 1. Re: Need help - Seeking numerical order
          bigtom

          The idea that comes to mind is a script with increment variable for the number of IDs you need. Pretty rough script example but you will get the point.

           

          Find for only Free status.

          Sort by ID ( if not the original table sort)

          Set variable $number to script parameter (number of IDs you need)

          set variable $record to MyTable::ID

          set variable $increment to 1.

          Go to next record

          Loop

          If MyTable::ID = $record +1 // if next record is in order

               set variable $increment to $increment + 1

               If $increment = $number // if the current total equals the number you need

                    (Do something with $record and $number to display or find your set

                    or do some other script that is useful for your needs)

                    exit

               End if

          Else If MyTable::ID ≠ $record +1 // next record is not in order

               Set variable $record to MyTable::ID //reset the starting ID

               set variable $increment to 1 //start over counting

          End If

          Got toNext Record-Exit after last

          End Loop

          • 2. Re: Need help - Seeking numerical order
            bigtom

            Made a few quick edits.

             

            Basically you end up with the first ID in a set of free IDs that equal the number you need and they are the lowest available ID numbers that fit.

            • 3. Re: Need help - Seeking numerical order
              Stigge

              I made it work =D

              Was only 1 correction in the code you typed..

              Set variable $record to $record + 1

              was missing.. after adding that it worked good =) now I just need to use this information..

               

              Thank you for the help !! =)

              • 4. Re: Need help - Seeking numerical order
                bigtom

                I am not really sure where you added that but it should not be necessary unless you want to get the last record and count backwards.

                 

                I guess if you do that you don't even need $record variable at all.

                • 5. Re: Need help - Seeking numerical order
                  bigtom

                  I see it now. $record needs to be set once each loop but +1 might be the wrong value. Set it to the current ID before the next loop.

                  • 6. Re: Need help - Seeking numerical order
                    Stigge

                    I noticed I didn't get any hits at all when I tried it.

                    So I added the line, and it now works awesome. check bold to know where

                     

                     

                    Find for only Free status.

                    Sort by ID ( if not the original table sort)

                    Set variable $number to script parameter (number of IDs you need)

                    set variable $record to MyTable::ID

                    set variable $increment to 1.

                    Go to next record

                    Loop

                    If MyTable::ID = $record +1 // if next record is in order

                         set variable $increment to $increment + 1

                         set variable $record to $record + 1  //reason for that it doesn't count ahead otherwise.

                         If $increment = $number // if the current total equals the number you need

                              (Do something with $record and $number to display or find your set

                              or do some other script that is useful for your needs)

                              exit

                         End if

                    Else If MyTable::ID ≠ $record +1 // next record is not in order

                         Set variable $record to MyTable::ID //reset the starting ID

                         set variable $increment to 1 //start over counting

                    End If

                    Got toNext Record-Exit after last

                    End Loop

                    • 7. Re: Need help - Seeking numerical order
                      Stigge

                      yaa that might do it aswell. =)

                      • 8. Re: Need help - Seeking numerical order
                        bigtom

                        You're right. At that location +1 and the current ID are the same thing.

                        • 9. Re: Need help - Seeking numerical order
                          erolst

                          A different implementation of the same idea:

                           

                          Set Variable [ $desiredBlockSize; Value:5 ]

                          # [ for production, implement with field or custom dialog ]

                          Set Variable [ $listOfFreeIDs; Value:ExecuteSQL ( " SELECT id

                          FROM FreeBlocks WHERE Status = ? ORDER BY id " ; "" ; "" ; "Free" ) ]

                          Set Variable [ $IDcount; Value:ValueCount ( $listOfFreeIDs) ]

                          Set Variable [ $i; Value:1 ]
                          Loop

                            Set Variable [ $currentID; Value:GetValue ( $listOfFreeIDs ; $i ) ]
                           
                          Set Variable [ $nextID; Value:GetValue ( $listOfFreeIDs ; $i + $desiredBlockSize - 1 ) ]
                           
                          Set Variable [ $startOfFreeBlock; Value:Case ( $nextID = $currentID + $desiredBlockSize - 1 ; $currentID ) ]

                            Exit Loop If [ $i + $desiredBlockSize >= $IDcount - $desiredBlockSize + 2 or $startOfFreeBlock ]
                           
                          Set Variable [ $i; Value:$i + $desiredBlockSize - 1 ]

                          End Loop

                          If [ not IsEmpty ( $startOfFreeBlock ) ]

                            Show Custom Dialog [ Title: "Found it!"; Message: "Free contiguous block of size " & $desiredBlockSize & " starts at ID " & $startOfFreeBlock ]

                          Else

                            Show Custom Dialog [ Title: "Not found!"; Message: "No contiguous free block of size " & $desiredBlockSize & " was found." ]

                          End If

                           

                          • 10. Re: Need help - Seeking numerical order
                            bigtom

                            The benefit of using ExecuteSQL and variables only is you can get what you need on any layout or in any script without jumping around layouts.  Very useful.

                            • 11. Re: Need help - Seeking numerical order
                              DavidJondreau

                              For speed you can do this with two summary fields and a single calculation. No script required if you start from a "Show All" state.

                               

                              1) Make a List summary field of Status and a List Summary field of the IDs.

                              2) Show All records.

                              3) The calc would be:

                               

                              Let( [

                              status.set = table::statusSummary ;

                              id.set = table::idSummary  ;

                              set.size = 10 ;

                              status = "free" ;

                               

                              free.list = status & Substitute ( 10^ ( set.size-1) ; [ 1 ; "¶" ] ; [ "0" ;  status ] ) ;

                              start.segment = position ( status.set ; free.list ; 1 ; 1 ) ;

                              start.value.position = PatternCount ( left ( status.set ; start.segment ) ; "¶" )  + 1 ;

                               

                              //this is our result

                              values = MiddleValues ( id.set ; start.value.position ;  set.size) ;

                               

                              //let's do some light error checking

                              error = case (

                                        set.size > 100 or set.size < 1 ; "ERROR: set size must be between 1 and 100" ;

                                        not isEmpty ( filter ( status ; "10" ) ) ; "ERROR: status contains a 1 or 0" ;

                                        start.segment = 0 ; "ERROR: set not found" ) ;


                              result = Case ( isEmpty ( error ) ; values ; error )

                              ];

                              result

                              )

                              • 12. Re: Need help - Seeking numerical order
                                Stigge

                                That's a nice solution.. don't think it will work for me though this time.. since the number constantly change, we purchase different amount of PC´s every time, more or less.

                                • 13. Re: Need help - Seeking numerical order
                                  DavidJondreau

                                  You can make set.size a local variable or a field or whatever to account for the number of PCs purchased.

                                   

                                  Also, thinking it over, it would probably be best done with ExecuteSQL() in which case you wouldn't need the Summary fields or a script at all.