You could do it with a custom function (that you'd probably have to write), but let's start with just a script. To go from 1,3,6... to 1¶3¶6... you use Substitute; to step through each value you use a loop with an incrementing variable; to recognize the 10-12 as a value to be manipulated you use PatternCount and then Position to determine where exactly the hyphen is in the value; then GetAsNumber the portion before the hyphen and again for the portion after the hyphen, and count by 1 (another loop) between them. The hard part is figuring out all the possible errors: what if the person inputs 1 , 3, 6 , 10 ... 12, or there's a 6.3 or a -3 in there? Use Trim and Int to solve some of those problems; the negative number's a bit trickier but again can be solved with PatternCount.
After you've written a script to do this and trapped for errors, you can try converting this to a custom function, but unless you're going to use it for other tasks or need to increase the speed, it might be easier to just leave it as a script. If you are going to use it for other tasks, writing this script as a subscript (passing the value list to and from it as a ScriptParameter and ScriptResult) helps re-use your work efficiently.
I assume a custom function would work best here
You are processing that list in a script anyway, so rather than a complicated recursive function, I'd put this into the script, along the lines of:
# [ input is "1,3,6,10-12" ]
Set Variable [ $userInput ; Substitute ( YourTable::userSelection ; "," ; ¶ ) ]
Exit Loop if [ Let ( $i = $i +1 ; $i > ValueCount ( $userInput ) ) ]
Set Variable [ $curLine ; Trim ( GetValue ( $userInput ; $i ) ) ]
If [ PatternCount ( $curLine ; "-" ) ]
Set Variable [ $dummy ; Let ( [ $curList = Substitute ( $curLine ; "-" ; ¶ ) ; $curStart = GetValue ( $curList ; 1 ) ; $curStop = GetValue ( $curList ; 2 ) ] ; "" ) ]
Set Variable [ $cleanedList ; List ( $cleanedList ; $curStart ) ]
Exit Loop if [ Let ( $curStart = $curStart + 1 ; $curStart > $curStop ) ]
Set Variable [ $cleanedList ; List ( $cleanedList ; $curLine ) ]
# [ now $cleanedList contains "1¶3¶6¶10¶11¶12" ; go and process it ]
As was mentioned, you need to perform some amount of error trapping (e.g. what about "1,3,6,12-10" …?)
and on second thought, why not present the user with a list of classes to select from, maintain that list in a $$var and process that?
By using the Substitute function I'm able to create a working list: 1¶3¶6¶10-12. The single values are not a problem. I am trying to use the CustomList custom function (briandunning.com) to fill in the missing values. I can get the ~First value and the ~Last value and a ~Count of the difference +1. But the function doesn't like my parameters. CustomList ( 1; ~Count; "~First + [n]") I have tried it with just the [n] and all works fine. [n] + 1 also works, but when I try to add the ~First value, it gives a bad parameter message. Maybe I'll just do it all in a script and be done with it. I tend to try to come up with an elegant solution when down-and-dirty may be better. Thanks for the feedback.
"and on second thought, why not present the user with a list of classes to select from, maintain that list in a $$var and process that?"
+1. Allowing users to enter free text and expecting it to comply with a predictable format is a recipe for disaster.
Yup - using the script is what I'm going to do. I thought about having the user select classes from a list, but due to space requirements and trying to keep with keyboard entry I decided to do the single field entry. Most people are used to that from entry when printing various pages from a Word document.
All of this input is from within a "New Request" PopOver. On the Classes Affected, the user has the radio button options of All or Selected. It would be nice if the "Selected" option could be another PopOver where I could have room to display check boxes of the classes. But you can't have a PopOver inside a PopOver. I may try to make room for a dynamic checkbox value list on the original PopOver where the user could click on the individual classes. I just want this to be as easy as possible from the user's viewpoint.
Well, if you want to try a CF:
Signature: ListFromRange ( input )
e.g. ListFromRange ( "1,3,6,10..12, 18…25, 42-44" ) =
Let ( [
input = Substitute ( input ; "," ; ¶ ) ;
curLine = Trim ( GetValue ( input ; 1 ) ) ;
remainder = MiddleValues ( input ; 2 ; ValueCount ( input ) -1 ) ;
curList = Substitute ( curLine ; [ "-" ; ¶ ] ; [ ".." ; ¶ ] ; [ "…" ; ¶ ] ) ;
startValue = Trim ( GetValue ( curList ; 1 ) ) ;
stopValue = Trim ( GetValue ( curList ; 2 ) ) ;
penUltimate = stopValue - startValue = 1 ;
Case ( stopValue ; Case ( not penUltimate ; startValue + 1 & "-" & stopValue ; stopValue ) ) ;
start & Case ( remainder ; ¶ & ListFromRange ( remainder ) )
(Custom function editor doesn't accept it as is)
"start" in last line should be "startValue"
Right; thanks Bruce.
Changed the var names after it already worked (shouldn't do that without re-testing in the Calc Engine)…
As an aside:
Case ( remainder ; …)
only works because an implicit GetAsBoolean(remainder) interprets the numeric values; better would be to use
Case ( not IsEmpty ( remainder ) ; … )
It seems to me you are asking the wrong question. As long as it's a text input, you don't know what you are going to get; maybe commas, maybe semi-colons, maybe the word "through," or "All." Consider making it simpler by presenting them with checkboxes of the classes they want to change. It looks like you are dealing with a fairly limited set of options (1 - 12, 1 through 12, …). This is what the checkbox option was designed for.
Wow - you're awesome! I will save that CF for some future use. I can not seem to get my geezer brain wrapped around recursion. I took everyone's advice to heart and changed the text entry to a checkbox of class numbers and topic descriptions. That way I don't have to try to anticipate what other's finger-fumbles will create. Thanks all for your help.
I took everyone's advice to heart and changed the text entry to a checkbox of class numbers and topic descriptions. That way I don't have to try to anticipate what other's finger-fumbles will create.
Good idea. In the meantime you can regard the script and the CF as study objects.
I can not seem to get my geezer brain wrapped around recursion.
It's not that bad, but in most cases a script should do just fine and is easier to grasp.