https://netsuiteprofessionals.com logo
Join Slack
Powered by
we use sequential numbers for our item name/number...
# general
p
we use sequential numbers for our item name/numbers and I've always found it annoying that if I sort by that field, it doesn't sort in numeric order but all the 1s or 9s first. In some of our searches I've converted to number but then it adds the thousandths comma, and I've tried to trim that comma too but can't quite get it. anyone experienced this or a solution? I've tried replace too. basically trying something like REPLACE(TO_NUMBER({name}), ',','') or TRIM(both ',' from TO_NUMBER{name}))
b
I'd be interested to hear if anyone has a solution to this as well. We usually recommend sorting by internal ID...if your items were setup in chronological order then that should, in theory, match the item number order also.
p
unfortunately we didn't realize that until it was too late and there was no way to change it
k
TO_NUMBER({field},'9999')
in a text field
you might add leading zeroes
which you could do by doing "length" tests and then adding to your format mask leading 0s
i.e. if you have 5 digits and you need 6 - '099999'
decode(length({field}), 5, to_number({field},'099999'), 6, to_number({field},'999999'))
@pmendola educated guess on item number length - if it's less you can adjust. You'd just need to do
Copy code
Decode(length({field}), Test 1, result 1, test 2, result 2, test 3, result 3, test 4, result 4)
p
Untitled
Untitled
interesting, but im having no luck
its missing a single quote but still invalid expression when i added it
k
I'd try smaller chunks and see if you can get something working
Also, is your field type text?
p
its the item name/number field so i believe its text yesa
i tried smaller chunks as well, will keep exploring it
k
Do you have any item names that contain text other than numbers?
One bad result could throw that error when running the search.
p
ahh
probalbly
good call
or actual maybe not, with the given criteria in the search
k
You'll have to manually exclude them or do a machining to remove the non number text
I'd try doing the length test first and see what numbers you get
Then I'd try doing a separate column for the results and see what you get
also try updating your statement to be more like this
Copy code
decode ( length({name}), number1, result1, number 2, result2, nomatching result)
Copy code
decode(length({name}), 5, to_number({name},'9999'), 4, to_number({name},'09999'), 3, to_number({name},'00999'), 'Error')
@pmendola any luck?
p
no luck, still invalid expression, used the exact last code snippet you posted. also checked and all the names in the search are numbers only and only 3 to 5 digits
tried {itemid} too
k
What happens when you just add columns with pieces of the code (i.e. try adding a results column
Copy code
length({name})
or
Copy code
to_number({name},'00999')
maybe the test needs to be
Copy code
decode(length({name}), '5', to_number({name},'9999'), '4', to_number({name},'09999'), '3',to_number({name},'00999'), 'Error')
since it's text?
p
if i just "to number" {name} the column is a number with the thousandth separater. length({name}) shows intergers 3 to 5
everything else ive tried shows invalid expression
it still needs to resolve to formula numeric, or it wont sort right, but i tried formula text too and still nothing
if I use trim or replace, it renders the formula but doesnt remove the comma, just shows it as it was before
k
well if you resolve to formula text with leading zeroes it will sort right.
p
oh i see what you mean
k
So then the column is just there for sorting purposes - and you display the actual column you want people looking at in another.
2 Views
Open in Slack
PreviousNext
Powered by