How about instead of conditional formatting, you store the RGB values in the Item table? And then in place of the Item Nr, create a calculated field like:
TextColor ( ItemNr ; RGB ( ItemNr::red ; ItemNr::green ; ItemNr::blue ) )
As for the sort -- is your price field a number field? A text field with numbers in it will sort differently than a number field.
I did not fully understood it, but it brought me to this idea
I just used a Text calculationfield in which I placed:
ItemNr = '"&ItemNr&"
' ";"";"") ≠ "" ;
TextColor ( ItemText ; RGB ( 100 + (ItemNr / 100) ; 50 ; 100) ) ;
So the Itemsnummer had allways 5 digits and this gives me some random color, good enough to recognise the grouping of the items. Maybe that a bit rough, but extentable.
And yes it was a Text Field instead of a Number Field, so i also used a Calculationfield to turn it for sorting into a Numberfield, since I cannot change the originalfield.
Thanx great help.
Now I only need a function to color the background instead of the TextColor.
Maybe MBS has this in his Plug-in.
Glad you found a solution. Be aware, using ExecuteSQL as a field calculation is likely to to become very slow if your record count gets big.
it will be not more then 120-150 or so, it works well so far.
Coloring the background would be nicer though, but i cant find a way.
Go back to conditional formatting, maybe? Would GetNthRecord help?
Or, go old-school and use a container field.