Hi,
I have one dropdown field of “Office”. It displays the value as text and DB stores the value integer. How it’s possible to sort on DB value instead of display value.
DB Value ::: Display Value
7 ::: Alliance
43 ::: CANNABIS
42 ::: CAAS
35 ::: CHERRY HILL
2 ::: FINANCE
Thanks
Asif
Offshore Evolution Pvt Ltd
Is your Kreporter a full version? If yes, you should have the query analyzer under tab integration.
Turn it on, save, then you will see the queryanalyzer button in “tools”.
Please trigger and copy/paste the query built here. I’d like to see it.
Is the office column an integer?
Hi @maretval
Yes, we are using Kreporter a full version.
Query
SELECT MIN(chytfbdznnc.id) as sugarRecordId, ‘Accounts’ as “sugarRecordModule” , aebpthxehmp.id as “aebpthxehmpid”, ‘root:Accounts::relate:Accounts:client_partner_c’ as “aebpthxehmppath”, jbszyrknxpa.id as “jbszyrknxpaid”, ‘root:Accounts:
Accounts:assigned_user_link’ as “jbszyrknxpapath”, mqjafbmqryr.id as “mqjafbmqryrid”, ‘root:Accounts:
FROM accounts chytfbdznnc
LEFT JOIN accounts_cstm as hpfykjaryxe ON chytfbdznnc.id = hpfykjaryxe.id_c
LEFT JOIN users AS aebpthxehmp ON aebpthxehmp.id=hpfykjaryxe.user_id1_c
LEFT JOIN users_cstm as bqsxesmmrmp ON aebpthxehmp.id = bqsxesmmrmp.id_c
LEFT JOIN users jbszyrknxpa ON chytfbdznnc.assigned_user_id=jbszyrknxpa.id AND jbszyrknxpa.deleted=0
LEFT JOIN users_cstm as gzdryejcrsn ON jbszyrknxpa.id = gzdryejcrsn.id_c
INNER JOIN accounts_opportunities terkgrqwkbt ON chytfbdznnc.id=terkgrqwkbt.account_id AND terkgrqwkbt.deleted=0
INNER JOIN opportunities mqjafbmqryr ON mqjafbmqryr.id=terkgrqwkbt.opportunity_id AND mqjafbmqryr.deleted=0
LEFT JOIN opportunities_cstm as erxabmpzjgb ON mqjafbmqryr.id = erxabmpzjgb.id_c
WHERE chytfbdznnc.deleted = ‘0’
GROUP BY hpfykjaryxe.client_id_c
ORDER BY MIN(hpfykjaryxe.office_c) ASC
“Office” is the Account of the custom “DropDown” field. It is DB data type is varchar.
DropDown Option List
‘7’ => ‘Alliance’,
‘43’ => ‘Cannabis’,
‘42’ => ‘CAAS’,
‘35’ => ‘Cherry Hill’,
‘2’ => ‘Finanace’,
‘10’ => ‘NAPLES’,
‘39’ => ‘WEST PALM BEACH’
Report of Graph sorting on DB value of varchar 10(NAPPLES), 35(Cherry Hill), 39(West Palm Beach), 42(CAAS), 43(Cannabis), 7(Alliance)
Data is displayed on DropDown Display Value
I want the toggle option to search by DropDown display value or search by DB value.
if you convert you string values to integers using a customSQL
CAST({t}.{f} AS UNSIGNED)
for office_c you should get sorting as wished but you will loose the label in display.
Only solution I see for now would be to have native integers for your values in the dropdown
I am using the CAST in the office_c field but it is lost the display value and again sorting issues 0, 10, 35, 39, 42, 43, and 7.
Then you’ll have to make your office_c an integer field. Not string.
But in the chart sorting as string 0, 7, 10, 35, 39, 42, and 43 but in below data as soring string 0, 35, 39, 42, 43, and 7.
Looks like presentation grouped view will consider the value as a string.
What if you go back to string values for your enum but using leading zeros?
000
007
010
035 and so on?
