I have multiple case statements, that on their own i can get to work but i am stuck on how to combine them. Essentially i need to end up working how many of the criteria the line meets but as a number in the results.
Individually i can get them to work to return a 1 if the criteria fits, but i have no idea how to combine them or even when to start.
These are the 5 separate statements that i have working.
CASE WHEN {custcol_sd} = {custcol_en.custrecord_e_startdate} THEN '0' ELSE '1' END
CASE WHEN {custcol_ed} = {custcol_en.custrecord_e_enddate} THEN '0' ELSE '1' END
CASE WHEN {custcol_pc} = {custcol_en.custrecord_e_pc} THEN '0' ELSE '1' END
CASE WHEN {closed} = 'F' AND {custcol_en.isinactive} = 'T' THEN '1' ELSE '0' END
CASE WHEN {closed} = 'T' AND {custcol_en.isinactive} = 'F' THEN '1' ELSE '0' END
Essentially what i want to know if how many of the criteria has met, so each li9ne in the search will be allocated a number from 1 to 5 (they have to meet 1 of these criteria to appear on the search to begin with) Once i have the number, i will use these in the highlighting to apply a highlight rule based on what number they are allocated.
I am not sure actually where to start to get them combined as in adding the number's up and returning a result.
In excel I can produce what I need with something like this: (except the cell names would refer to cells instead)
If i was writing this in excel, it may look something like this. 🙁IF(custcol_sd=custcol_en.custrecord_e_startdate,0,1)+ IF(custcol_ed=custcol_en.custrecord_e_enddate,0,1)+IF(custcol_pc}={custcol_en.custrecord_e_pc,0,1)+IF(AND({closed}="F",{custcol_en.isinactive}="T"),1,0)+IF(AND({closed}="T",{custcol_en.isinactive}="F"),1,0)
and then this would result in either a number thats 1 2 3 4 or 5.
Essentially what i want to know if how many of the criteria has met, so each line in the search will be allocated a number from 1 to 5 (they have to meet 1 of these criteria to appear on the search to begin with) Once i have the number, i will use these in the highlighting to apply a highlight rule based on what number they are allocated.
You've pretty much given yourself the answer with your Excel formula. Following this pattern, but substituting SQL CASE for Excel IF, you get:
CASE WHEN {custcol_sd} = {custcol_en.custrecord_e_startdate} THEN '0' ELSE '1' END +
CASE WHEN {custcol_ed} = {custcol_en.custrecord_e_enddate} THEN '0' ELSE '1' END +
CASE WHEN {custcol_pc} = {custcol_en.custrecord_e_pc} THEN '0' ELSE '1' END +
CASE WHEN {closed} = 'F' AND {custcol_en.isinactive} = 'T' THEN '1' ELSE '0' END +
CASE WHEN {closed} = 'T' AND {custcol_en.isinactive} = 'F' THEN '1' ELSE '0' END
However, doing it this way involves a lot of implicit type conversions, so it would be better to use Formula (Numeric) and remove the single quotes around the ones and zeros:
CASE WHEN {custcol_sd} = {custcol_en.custrecord_e_startdate} THEN 0 ELSE 1 END +
CASE WHEN {custcol_ed} = {custcol_en.custrecord_e_enddate} THEN 0 ELSE 1 END +
CASE WHEN {custcol_pc} = {custcol_en.custrecord_e_pc} THEN 0 ELSE 1 END +
CASE WHEN {closed} = 'F' AND {custcol_en.isinactive} = 'T' THEN 1 ELSE 0 END +
CASE WHEN {closed} = 'T' AND {custcol_en.isinactive} = 'F' THEN 1 ELSE 0 END