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

# Need help - Seeking numerical order

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

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

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

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

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

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

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

yaa that might do it aswell. =)

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

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

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

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

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

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" ;

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

];

result

)

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

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

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.