https://netsuiteprofessionals.com logo
Join Slack
Powered by
why is this returning 0? It is monday right? case...
# general
j
why is this returning 0? It is monday right? case when to_char({today}, 'Day') = 'Monday' then 1 else 0 end
c
I think 
TO_CHAR()
returns the day name as uppercase
k
plus, I'd rather use 
case when to_char({today},'D') = '1' then 1 else 0 end
or whatever the right number is for Monday. While it's less clear what it's doing it takes less characters.
and less likely to run into silliness like capitals being an issue.
c
What Kevin said
Today I learned the case of Day/day/DAY determines the TO_CHAR case output in Oracle https://livesql.oracle.com/apex/livesql/file/content_GCEY1DN2CN5HZCUQFHVUYQD3G.html
k
wait, seriously?
j
i dont think the capitals are the issue, in the search the formatting follows what i enter ('DAY' = 'MONDAY' 'Day' = 'Monday' 'day' = 'monday') i'll give it a chance with numbers, but the end goal is to use a list field on customer (scheduled pickup - days of the week as string list) to set the value to the next available day of the week.
k
Also - should probably note the number may or may not need to be in single quotes. I can't ever remember until I'm actively doing it - and if this is going to be part of a larger formula, that's more weight for my approach.
j
using the number value is working. only issue is now i dont know how to compare to the list on the customer record. I guess i could use an integer field, but i wanted it to be user friendly so other users could set the day of the week
i think this gets me close enough that i can figure out what works best for us
thanks
k
Make a custom record to use as the list
and then add a field that is the "translation"
So Record name is "Monday" - with custom field that is a 1 or whatever. "Tuesday" with a 2 or whatever.
j
yeah that totally works.
k
Then when you do the comparison it's {custfieldfromcustomer.translationfield}
Should be noted - if this is being referenced on the sales order itself - maybe I push that field from the customer onto a field on the sales order - so that I'm within the 1 hop rule on saved searches.
j
yeah i think thats the best way to handle it
g
Fun fact: To_Char adds trailing spaces...
1st line with trailing spaces is the result of 
to_char({today},'Day')
2nd line without them is the result of 
replace(to_char({today},'Day'),' ','')
2 Views
Open in Slack
PreviousNext
Powered by