I have a feeling this one is going to be tricky, so stay with me. My background is in hardware design so my software/coding chops are a little lacking. I'm working on my code skills, but the learning curve is pretty steep. Little background for context purposes
In electronics we have to deal with often very large and very small numbers at the same time. In order to not use values like "0.000001F" we use prefixes that scales a value to a friendlier "1uF" (u is for "micro"). "10,000,000ohms" would be written "10Mohms" (M is for "mega"). The prefixes step in 1,000 increments: 1,00,000 is M (mega), 1,000 is k (kilo), 0.001 is m (milli), 0.000,001 is u (micro).
I have a table which has sets of value and scale for a number of parameters. When adding or editing a record, the user types in PartsValue1 and selects a PartsScale1 from a lookup table. Both are decimal format. For example PartsValue1=560, PartsScale1=0.001 PartsValue2=120, PartsScale2=1,000. Multiply the two and you get a decimal value in the root units.
I've successfully used concat in my MySQL query to take the two fields and combine them to a combined term for the list and view pages. The problem I'm running into is how to allow the user to sort these parameters with the list header click function by their calculated root decimal value instead of their displayed prefix'ed value. For example "560u" should be sorted as smaller than "120k".
One option that sounds plausible is to concat the decimal value as a 33 character value along with the predix'ed value but somehow strip the first 33 characters when the actual values are displayed in the list. For this to work however, the header click to sort function would have to sort on the full concat'ed value, but only display some of the characters. For example:
PartsValue1=560, PartsScale1=0.001
DecimaleValue1(Calculated) into 0000000000000000.5600000000000000
Concat'ed into 0000000000000000.5600000000000000560u
Sorted with header click by 0000000000000000.5600000000000000560u
But displayed in list with the first 33 characters removed as 560u
I'm not sure if this is even possible with PHPrunner, or of there is a more elegant way to go about this. Any advice would be greatly appreciated.
-EM
