I have a complex saved search formula that calculates the number of days elapsed since an event as part of a text field. I need this to be displayed with leading zeros so that I can group the column by 'minimum' and order the numbers correctly.
The part of the formula for the date calculation is:
TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY')
Is there a simple function to set the number function to 3 digits (i.e. 001, 012, 123 etc)
I tired to build nested Case When statements to check if it was < 10 and add '00' before the result, and then when <100 add '0' before the result, but the resulting formula was so huge and complex I can't now troubleshoot why it is erroring.
This is the initial formula (that works):
CASE WHEN {activity.status}='Completed'THEN (CASE WHEN {activity.type}='Event' THEN TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY')||' days ago '||{activity.assigned}||' '||{activity.title}||' '||'<a href=https://3817341.app.netsuite.com/app/crm/calendar/event.nl?id='||{activity.internalid}||' target="_blank"> Open EVENT</a>' WHEN {activity.type}='Phone Call' THEN TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY')||' days ago '||{activity.assigned}||' '||{activity.title}||' '||'<a href=https://3817341.app.netsuite.com/app/crm/calendar/call.nl?id='||{activity.internalid}||'&e=T'||' target="_blank"> Open CALL</a>' WHEN {activity.type}='Task' THEN TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY')||' days ago '||{activity.assigned}||' '||{activity.title}||' '||'<a href=https://3817341.app.netsuite.com/app/crm/calendar/task.nl?id='||{activity.internalid}||'&e=T'||' target="_blank"> Open TASK</a>' END)WHEN {activity.status}='Canceled' THEN TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY')||' days ago '||{activity.assigned}||' '||'Cancelled'||'<a href=https://3817341.app.netsuite.com/app/crm/calendar/event.nl?id='||{activity.internalid}||' target="_blank"> Open EVENT</a>'END
This is the monster formula I ended up with (that doesn't work) is:
CASE WHEN {activity.status}='Completed'THEN (CASE WHEN {activity.type}='Event' THEN (CASE WHEN TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') < 10 THEN '00'||TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') WHEN TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') < 100 THEN '0'||TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') ELSE TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') END) ||' days ago '||{activity.assigned}||' '||{activity.title}||' '||'<a href=https://3817341.app.netsuite.com/app/crm/calendar/event.nl?id='||{activity.internalid}||' target="_blank"> Open EVENT</a>' WHEN {activity.type}='Phone Call' THEN (CASE WHEN TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') < 10 THEN '00'||TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') WHEN TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') < 100 THEN '0'||TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') ELSE TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') END) ||' days ago '||{activity.assigned}||' '||{activity.title}||' '||'<a href=https://3817341.app.netsuite.com/app/crm/calendar/call.nl?id='||{activity.internalid}||'&e=T'||' target="_blank"> Open CALL</a>' WHEN {activity.type}='Task' THEN (CASE WHEN TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') < 10 THEN '00'||TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') WHEN TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') < 100 THEN '0'||TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') ELSE TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') END) ||' days ago '||{activity.assigned}||' '||{activity.title}||' '||'<a href=https://3817341.app.netsuite.com/app/crm/calendar/task.nl?id='||{activity.internalid}||'&e=T'||' target="_blank"> Open TASK</a>' END)WHEN {activity.status}='Canceled' THEN (CASE WHEN TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') < 10 THEN '00'||TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') WHEN TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') < 100 THEN '0'||TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') ELSE TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') END) ||' days ago '||{activity.assigned}||' '||'Cancelled'||'<a href=https://3817341.app.netsuite.com/app/crm/calendar/event.nl?id='||{activity.internalid}||' target="_blank"> Open EVENT</a>'END
Essentially the original formula has this (in a number of places):
TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY')
And I tried to replace it with:
(CASE WHEN TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') < 10 THEN '00'||TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') WHEN TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') < 100 THEN '0'||TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') ELSE TO_DATE(sysdate, 'dd/mm/yyyy')- TO_DATE({activity.date},'DD/MM/YYYY') END)
Does that look like the correct format for nested Case/When statements??
Any ideas on how to unpick this mess???