hilite text

[Regulae]

Good morning smash,

It’s great that you posted the script you are using. It will really help us “tuneâ€

[smash]

thank you regulae for your time, i really appreciate it.
i will do my best to explain what i am trying to achieve.
there "could be" upto 32 different percentages in a column,
they must all be given a rank of 1st to the last (for this example) 34th.
i only needed the first 3 highest percentages to be highlighted, the remainder percentages to be just be given a rank.
now in the case of = (equal percentages) it must "skip" a rank.
example 1
equal 2nd
so we have a first place, 2 second place and a fouth place

example 2
we have 3 equal firsts
so we have 3 first places, and then the next highest percent will be a 4th place.
so how ever many equals, is how many ranks are skipped (does this even make sense?)

now in my columns, i have a heading with just (%) above it, and only the numbers go into the column, so i dont actually put the (%) symbol into the column.

thank you so much for your time, it is just great at how you explain YOUR scripts.

[sturgis]

Heres another example that works pretty well. Mostly commented.

We crossposted so I didn't see that you didn't need more than the first 3 hilighted, but if you shorten the "theColors" variable to just 3 colors the rest willl fall through to black.

Code: Select all

global theData
on mouseUp
   sortData
   tagScores 
end mouseUp

on sortData
   put empty into theData
   set the itemdelimiter to tab -- sets the delimiter to the default table delimiter 
   
   repeat with i = 1 to the number of lines in field "scoresField" -- this iterates through the field
      put i & tab & line i of field "scoresField" & cr after theData -- this adds a tab delimited line number to the front of the data line, and cr after to keep things line based
      put empty into item 3 line i of field "scoresField" -- While iterating might as well clear out column 3
      set the forecolor of line i field "scoresField" to empty -- since we're iterating through the field, might as well set the colors back to empty
   end repeat
   --set the itemdelimiter to tab -- sets the delimiter to the default table delimiter 
   
   -- for my table, the scores are in item 3.  As in the other script if you have a % sign in your field, use replace to get rid of it so you can do a numeric search. 
   -- since this is only the data in the variable, theres no need to mess with the % in the table
   sort lines of theData descending numeric by item 3 of each
   put theData
end sortData


-- handler to handle tagging and colorization
on tagScores 
   set the itemdelimiter to tab -- set the delimiter to match the data we're working with
   put "1st" & tab & "2nd" & tab & "3rd" & tab & "4th" & tab & "5th" & tab & "6th" & tab & "7th" & tab & "8th" & tab & "9th" & tab & "10th"  into theRanks -- a list of the possible rankings
   put "Blue" & tab & "Red" & tab & "Green" & tab & "Orange" & tab & "cyan" & tab & "purple" & tab & "yellow" & tab & "Brown" & tab & "magenta" & tab & "blue" into theColors -- a list of the possible colors
   
   -- to do it this way I did, you need 2 rank counters one for actual, one for effective.  Actual will be handled by the repeat loop effective is handled by rankCounter
   put 1 into rankCounter 
   put 10 into numOfRanks -- define how many ranks
   
   -- adjust the table as desired in this loop
   repeat with i = 1 to the number of lines in theData -- loop through the number of lines in theData
      
      -- check to see if we're still within the range of assigned rankings, but not at the very first record.
      -- also checks to make sure this is not a duplicate score. If its not a dupe, adjust the corresponding field line to insert the actual rank level.  
      if i > 1 and i < numOfRanks + 1 and item 3 line i of theData < item 3 line i-1 of theData then
         put item i of theRanks into item 3 line (item 1 line i of theData) of field "scoresField"
         set the forecolor of item 3 of line (item 1 of line i of theData) of field "scoresField" to item i of theColors -- sets the color
         put i into rankCounter -- since this is NOT a duplicate score, set the effective and actual rank index to match
         
      else if i > 1 and i < numOfRanks + 1 and item 3 of line i of theData = item 3 line i-1 of theData then -- this handles duplicates
         put item rankCounter of theRanks into item 3 line (item 1 line i of theData) of field "scoresField" -- uses the effective rather than actual index to set color and rank in the field
         set the foreColor of item 3 of line (item 1 of line i of theData) of field "scoresField" to item rankCounter of theColors
         -- handles the very first line whcih will always be first and red
      else if i = 1 then
         put item i of theRanks into item 3 line (item 1 line i of theData) of field "scoresField"
         set the foreColor of item 3 of line (item 1 of line i of theData) of field "scoresField" to item i of theColors
         -- this handles the special case where 5th position IS or HAS 1 or more duplicates.  Does a direct comparison to the 4th line of theData
      else if i > numOfRanks and item 3 of line i of theData = item 3 of line 4 of theData then 
         put item rankCounter of theRanks into item 3 line (item 1 line i of theData) of field "scoresField"
         set the foreColor of item 3 of line (item 1 of line i of theData) of field "scoresField" to item rankCounter of theColors
      else
         -- if none of the above match, no reason to finish cycling through theData
         exit repeat
      end if 
   end repeat
end tagScores

[sturgis]

Heres a better version. It tags all scores with a place, and tags the first 3 with colors.

Code: Select all

global theData,theSuffix
on mouseUp
   sortData
   tagScores 
end mouseUp

on sortData
   put empty into theData
   set the itemdelimiter to tab -- sets the delimiter to the default table delimiter 
   
   repeat with i = 1 to the number of lines in field "scoresField" -- this iterates through the field
      put i & tab & line i of field "scoresField" & cr after theData -- this adds a tab delimited line number to the front of the data line, and cr after to keep things line based
      put empty into item 3 line i of field "scoresField" -- While iterating might as well clear out column 3 which contains 1st, 2nd etc
      set the forecolor of line i field "scoresField" to empty -- since we're iterating through the field, might as well set the colors back to empty
   end repeat
   --set the itemdelimiter to tab -- sets the delimiter to the default table delimiter 
   
   -- for my table, the scores are in item 2, item 3 in theData.  As in the other script if you have a % sign in your field, use replace to get rid of it so you can do a numeric sort. 
   sort lines of theData descending numeric by item 3 of each
   put theData
end sortData


-- handler to handle tagging and colorization
on tagScores 
   set the itemdelimiter to tab -- set the delimiter to match the data we're working with
   put "Blue" & tab & "Red" & tab & "Green" into theColors
   
   -- to do it this way I did, you need 2 rank counters one for actual, one for effective.  Actual will be handled by the repeat loop effective is handled by rankCounter
   put 1 into rankCounter 
   
   -- adjust the table as desired in this loop
   repeat with i = 1 to the number of lines in theData -- loop through the number of lines in theData
      
      -- check to see if we're still within the range of assigned rankings, but not at the very first record.
      -- also checks to make sure this is not a duplicate score. If its not a dupe, adjust the corresponding field line to insert the actual rank level.  
      if i > 1 and  item 3 line i of theData < item 3 line i-1 of theData then
         ordinate i
         put theSuffix into item 3 line (item 1 line i of theData) of field "scoresField"
         set the forecolor of item 3 of line (item 1 of line i of theData) of field "scoresField" to item i of theColors -- sets the color
         put i into rankCounter -- since this is NOT a duplicate score, set the effective and actual rank index to match
         
      else if i > 1 and item 3 of line i of theData = item 3 line i-1 of theData then -- this handles duplicates
         ordinate rankCounter
         put theSuffix into item 3 line (item 1 line i of theData) of field "scoresField" -- uses the effective rather than actual index to set color and rank in the field
         set the foreColor of item 3 of line (item 1 of line i of theData) of field "scoresField" to item rankCounter of theColors
         
         -- handles the very first line whcih will always be first and red
      else if i = 1 then
         put "1st" into item 3 line (item 1 line i of theData) of field "scoresField"
         set the foreColor of item 3 of line (item 1 of line i of theData) of field "scoresField" to item i of theColors
         
      end if 
   end repeat
end tagScores

The ordinate handler is placed in the stack script. The code for this was snitched from this thread. http://forums.runrev.com/phpBB2/viewtop ... highlight=

Code: Select all

global theSuffix
on ordinate pNum
   switch
      case pNum is among the items of "11,12,13"
         put pNum & "th " into theSuffix
         exit switch
      case the last char of pNum is "1"
         put pNum & "st " into theSuffix
         exit switch
      case the last char of pNum is "2"
         put pNum & "nd " into theSuffix
         exit switch
      case the last char of pNum is "3"
         put pNum & "rd " into theSuffix
         exit switch
      default
         put pNum & "th " into theSuffix
   end switch 
end ordinate

[smash]

hi sturgis,
i keep getting a message error for line 38 ??
now, i will confess, that your script has me a touch lost (well very lost)
it will only put up 1st place, and not skip for equal placings.

now i liked that the "placings" where just highlighted, so i added that to this script.
my script is now

on mouseUp
/* Identify the table field */
put "Entries" into tableFieldName
put card field tableFieldName into tableStore
/* Identify the column number you want to colour */
put 4 into chosenColumn
-- extract the desired column, adding the line numbers, into variable
set the itemDelimiter to tab
repeat with rowNumber = 1 to the number of lines in tableStore
put (item chosenColumn of line rowNumber of tableStore)&tab&rowNumber&return after extractedColumn
-- clear previous highlights in column
set the foregroundcolor of item chosenColumn of line rowNumber of card field tableFieldName to empty
put empty into item (chosenColumn + 1) of line rowNumber of card field tableFieldName
end repeat
replace "%" with empty in extractedColumn
-- sort the variable
sort numeric descending extractedColumn
/* Currently, we are colouring the top three percentages */
put 12 into rankLevels
put 1 into currentRank
put item 1 of line 1 of extractedColumn into currentTop
-- cycle through our list
set the lockScreen to true
repeat with rowNumber = 1 to the number of lines in extractedColumn
-- identify 1st, 2nd, 3rd, 4th rank- stop when we reach 34th rank
if item 1 of line rowNumber of extractedColumn < currentTop then
put item 1 of line rowNumber of extractedColumn into currentTop
put currentRank + 1 into currentRank
-- once we have coloured the top three ranks, we can stop
if currentRank > rankLevels then
exit repeat
end if
end if
-- using the line numbers of the top three ranks, we can colour them
put (item 2 of line rowNumber of extractedColumn) into lineToColor
if currentRank = 1 then
put "1st" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
set the foregroundcolor of item (chosenColumn +1) of line lineToColor of card field tableFieldName to blue
end if
if currentRank = 2 then
put "2nd" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
set the foregroundcolor of item (chosenColumn +1) of line lineToColor of card field tableFieldName to red
end if
if currentRank = 3 then
put "3rd" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
set the foregroundcolor of item (chosenColumn +1) of line lineToColor of card field tableFieldName to green
end if
if currentRank = 4 then
put "4th" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
end if
if currentRank = 5 then
put "5th" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
end if
if currentRank = 6 then
put "6th" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
end if
if currentRank = 7 then
put "7th" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
end if
if currentRank = 8 then
put "8th" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
end if
if currentRank = 9 then
put "9th" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
end if
if currentRank = 10 then
put "10th" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
end if
if currentRank = 11 then
put "11th" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
end if
if currentRank = 12 then
put "12th" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
end if
end repeat
end mouseUp

which works brilliantly, i am just trying to work out if how to skip a placing/placings if there are equal percentages ???
thank you for ideas sturgis, every little bit helps
cheers

[sturgis]

Think I may have messed up my copy past. Also, I doubt my table is setup exactly like yours, but if you want to look at it, go to http://bonkersonline.info/stacks/
and theres a sortHilite.rev stack in there. Can right click and save.

I changed it slightly to put in random data with each button click.

Out of curiosity, what was the error for line 38?

There is 1 handler in the stack script, and the rest is in the single button.

Give it a look and let me know what I can clear up.

hi sturgis,
i keep getting a message error for line 38 ??
now, i will confess, that your script has me a touch lost (well very lost)
it will only put up 1st place, and not skip for equal placings.

now i liked that the "placings" where just highlighted, so i added that to this script.
my script is now

on mouseUp
/* Identify the table field */
put "Entries" into tableFieldName
put card field tableFieldName into tableStore
/* Identify the column number you want to colour */
put 4 into chosenColumn
-- extract the desired column, adding the line numbers, into variable
set the itemDelimiter to tab
repeat with rowNumber = 1 to the number of lines in tableStore
put (item chosenColumn of line rowNumber of tableStore)&tab&rowNumber&return after extractedColumn
-- clear previous highlights in column
set the foregroundcolor of item chosenColumn of line rowNumber of card field tableFieldName to empty
put empty into item (chosenColumn + 1) of line rowNumber of card field tableFieldName
end repeat
replace "%" with empty in extractedColumn
-- sort the variable
sort numeric descending extractedColumn
/* Currently, we are colouring the top three percentages */
put 12 into rankLevels
put 1 into currentRank
put item 1 of line 1 of extractedColumn into currentTop
-- cycle through our list
set the lockScreen to true
repeat with rowNumber = 1 to the number of lines in extractedColumn
-- identify 1st, 2nd, 3rd, 4th rank- stop when we reach 34th rank
if item 1 of line rowNumber of extractedColumn < currentTop then
put item 1 of line rowNumber of extractedColumn into currentTop
put currentRank + 1 into currentRank
-- once we have coloured the top three ranks, we can stop
if currentRank > rankLevels then
exit repeat
end if
end if
-- using the line numbers of the top three ranks, we can colour them
put (item 2 of line rowNumber of extractedColumn) into lineToColor
if currentRank = 1 then
put "1st" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
set the foregroundcolor of item (chosenColumn +1) of line lineToColor of card field tableFieldName to blue
end if
if currentRank = 2 then
put "2nd" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
set the foregroundcolor of item (chosenColumn +1) of line lineToColor of card field tableFieldName to red
end if
if currentRank = 3 then
put "3rd" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
set the foregroundcolor of item (chosenColumn +1) of line lineToColor of card field tableFieldName to green
end if
if currentRank = 4 then
put "4th" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
end if
if currentRank = 5 then
put "5th" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
end if
if currentRank = 6 then
put "6th" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
end if
if currentRank = 7 then
put "7th" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
end if
if currentRank = 8 then
put "8th" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
end if
if currentRank = 9 then
put "9th" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
end if
if currentRank = 10 then
put "10th" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
end if
if currentRank = 11 then
put "11th" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
end if
if currentRank = 12 then
put "12th" into item (chosenColumn + 1) of line lineToColor of card field tableFieldName
end if
end repeat
end mouseUp

which works brilliantly, i am just trying to work out if how to skip a placing/placings if there are equal percentages ???
thank you for ideas sturgis, every little bit helps
cheers

[Regulae]

Hi smash,

What you posted helped clarify the aim, which is not always so easy to explain in writing. The following highights the top three percentages. The rankings in the column on the right are a separate matter, with repeats “skippedâ€

[sturgis]

Go ahead and grab the stack I posted, it has the most cleaned up and functional set of scripts. Think the postings above are a bit too muddled.

And thanks for the compliment.

Some of the comments in the stack are still there from prior verions and don't apply anymore, i'll clean up and re-comment again tomorrow once i'm perky again.

[smash]

hi regulae, that works perfectly.
exactly what i wanted, thank you so much for all your time and effort.
hi sturgis, you have some great ideas and scripts.
i am in there having a look now, and trying to absorb as much info as i can.
you both have just been fantastic, and i thank you both very much.
cheers

[sturgis]

Edit: posted this updated version to http://bonkersonline.info/stacks/

One last post, cleaned up with better documentation. Also, the ordinate handler has been changed to allow for any size numbers rather than only sub 100 numbers.

For your code, remove the portion that fills the table of course, I was just generating random test data.

Code: Select all

global theData,theSuffix
on mouseUp
   -- fill the table with random data
   
   put empty into field "scoresField"
   repeat with i = 1 to 114
      put "Name" & i & tab & random(100) into line i of field "scoresField"
   end repeat
   
   sortData
   tagScores 
end mouseUp

-- fill var theData with data from the table plus line numbers
on sortData
   put empty into theData
   set the itemDelimiter to tab -- sets the delimiter to the default table delimiter 
   
   repeat with i = 1 to the number of lines in field "scoresField" -- this iterates through the field
      put i & tab & line i of field "scoresField" & cr after theData -- this adds a tab delimited line number to the front of the data line, and cr after to keep things line based
      put empty into item 3 line i of field "scoresField" -- While iterating might as well clear out column 3
      set the foreColor of line i field "scoresField" to empty -- since we're iterating through the field, might as well set the colors back to empty
   end repeat
    
   -- for my table, the scores are in item 2, but item 3 in theData.  
   -- sort theData variable based on item 3, which contains the scores. Line 1 of theData now contains 1st place. 
   sort lines of theData descending numeric by item 3 of each
end sortData


-- handler to handle tagging and colorization
on tagScores
   set the itemDelimiter to tab -- set the delimiter to match the data we're working with
   put "Blue" & tab & "Red" & tab & "Green" into theColors
    
    
   -- to do it this way I did, you need 2 rank counters one for actual, one for effective.  
   --Actual will be handled by the repeat loop effective is handled by rankCounter
   put 1 into rankCounter 
   
   -- adjust the table as desired in this loop
   repeat with i = 1 to the number of lines in theData -- loop through the number of lines in theData
      
      -- This if structure mathes 3 possible conditions.  
      --Is it the first line of theData?
      -- If its not the first line of the data, do the current data line and the preceeding data line match = true?
      --If its not th efirst line of data, do the current data line and the preceeding data line match = false?
      
      -- this is the "not the first, and not a match" conditional. 
      --Since there is no match, the counter i indicates the placement position, so we send it to the ordinate handler to set the suffix.
      -- We then shove the ordinated index i into the proper item and line number of the table.
      -- the table line number in this case is determined by item 1 line i of theData.
      --We set the forecolor of the suffix to rankCounter.  If i is more than 3 the forecolor is set to empty clearing any prev color
      -- since this is a non-matching conditiona, it's necessary to set rankCounter to i so that effective and actual counters match      
      if i > 1 and  item 3 line i of theData < item 3 line i-1 of theData then
         ordinate i
         put theSuffix into item 3 line (item 1 line i of theData) of field "scoresField"
         set the foreColor of item 3 of line (item 1 of line i of theData) of field "scoresField" to item i of theColors -- sets the color
         put i into rankCounter -- since this is NOT a duplicate score, set the effective and actual rank index to match
         
         
         -- this is the "Not the first, and IS a match" conditional.
         -- as above, ordinate is used to generate the correct place suffix, but since its a matching rank, we use rankCounter rather than i to pass to ordinate. 
         --  put the suffix into the correct item and line of the table as above .
         -- set the forcolor using rankCounter rather than i so that it is the same as the previous color 
         -- Don't modify rankCounter so that on next loop if there is another duplicate, rankcounter still contains the correct value.
      else if i > 1 and item 3 of line i of theData = item 3 line i-1 of theData then -- this handles duplicates
         ordinate rankCounter
         put theSuffix into item 3 line (item 1 line i of theData) of field "scoresField" -- uses the effective rather than actual index to set color and rank in the field
         set the foreColor of item 3 of line (item 1 of line i of theData) of field "scoresField" to item rankCounter of theColors
         
         -- handles the very first line which will always be first and red
         -- don't do anything with rankCounter here, both rankCounter and i are the same at this point.
      else if i = 1 then
         ordinate i
         put theSuffix into item 3 line (item 1 line i of theData) of field "scoresField"
         set the foreColor of item 3 of line (item 1 of line i of theData) of field "scoresField" to item i of theColors
         
      end if 
   end repeat
end tagScores

Heres the Ordinate handler, borrowed and modified from cleverRev, and if I recall, they borrowed it from elsewhere.

Code: Select all

global theSuffix
on ordinate pNum
   put empty into theSuffix
   put "st,nd,rd" into theTags
   put the length of pNum into numDigits
   repeat with theNumber= 1 to 3
      if (char numDigits of pNum = theNumber) and   (char numDigits - 1 of pNum <> "1") then
         put pNum & item theNumber of theTags into theSuffix
         exit repeat
      end if
   end repeat
   if theSuffix is empty  then put pNum &  "th" into theSuffix
end ordinate
   

[smash]

thank you sturgis,
i will have a good look at this tonight when i get home
thank you very much

[Regulae]

Hi smash,

I’ve been away from the computer for a while, and see that sturgis has things well in hand, but I’ll post what I’ve got in case there is anything of interest. I hope the colourisation/numbering skips repeats as required, e.g.:
100 1st
100 1st
100 1st
90 4th
90 4th
70 6th
70 6th
60 8th
... (blue/red/green colour scheme) and so on.
Sturgis’s solution is highly instructive- for one thing, the way I was rendering 1st, 2nd , 3rd ... was sorely lacking- mine would give you 21th, 22th, 23th... I really hadn’t thought it through. The script that follows simply uses the (advanced) “ordinateâ€

[smash]

ahh regulae, that is perfect.
i was just trying combine sturgis script with yours.
but yours works a TREAT !!!!!!!

if either of you two are up for a challenge, i have one problem that i have been trying to solve, and just can not get a handle on it ( i blew a fuse )
i am not too sure if it can be done, even ???
if you are interested, just email me on
performancepintos@gmail.com
thanks again, sturgis and regulae, you have both been fantastic.

[sturgis]

Mind posting what you need here?

Reg: Nice catch on the select empty, didn't even occur to me that might be a problem. Both our solutions are actually pretty similar, gonna go through your code a few more times to pick up what I can.

Also was thinking, if ordinate is changed to a function, it can be used inline and eliminate theSuffix. I also should have just manually handled the first line like you did to avoid one more if check. Think that could have saved 2 lines of code, the rest of the loop should work fine as long as you start the repeat at 2 like you did.

[smash]

no problems sturgis, as i have posted this many times, with no solution.
but i will start a new thread, as this is too good of a thread to complicate.
cheers