Skip to main content

I have 2 switch formulas that work off different fields and they are combined by a 3rd if statement that just says if this one isn’t blank return this one, if it is return the other. is it possible to put them in one field? if so how might i go about parsing it?


REGISTRATION STATUS FORMULA:


SWITCH({SERVICE STATUS}, ‘INQUIRE’, “NEEDS ATTENTION”, ‘UNKNOWN’, “NEEDS ATTENTION”, ‘OUT OF SERVICE’, “OUT OF SERVICE”, IF(AND(MONTH(TODAY())= MONTH({REG MO}),DATETIME_DIFF({LAST INSP},TODAY(),‘DAYS’)<-150),“DUE FOR INSPECTION”, IF(MONTH(TODAY())!= MONTH({REG MO}), “NOT DUE”,“READY TO RENEW”)))


REGISTRATION MOVE FORMULA:


SWITCH({REG PIPELINE}, ‘CANT BE INSPECTED’, “CANT BE INSPECTED”, ‘SENT TO TAX OFFICE’, CONCATENATE(“TO TAX OFFICE” & " " & DATETIME_FORMAT({PIPELINE MOVE}, ‘MM/DD/YYYY’)), ‘BACK FROM TAX OFFICE’, CONCATENATE(“RETURNED” & " " & DATETIME_FORMAT({PIPELINE MOVE}, ‘MM/DD/YYYY’)), ‘MAILED TO MANAGER’, CONCATENATE(“MAILED” & " " & DATETIME_FORMAT({PIPELINE MOVE}, ‘MM/DD/YYYY’)))


REGISTRATION PIPELINE FORMULA:


IF({REG MOVE}!= “”, {REG MOVE}, {REG. STATUS})


happy to give more context if needed

Hi Rebecca!

Switch statements allow you to enter a default value at the end, which will be output if none of the other values match. At the end of the first Switch statement, make the default value ‘Blank’. Then you can write an IF statement that says if the first Switch returns ‘Blank’, run the second Switch, otherwise run the first. I cannot be totally sure that below is correct without the original base to test, but it would look like this:


IF(

SWITCH({SERVICE STATUS}, ‘INQUIRE’, “NEEDS ATTENTION”, ‘UNKNOWN’, “NEEDS ATTENTION”, ‘OUT OF SERVICE’, “OUT OF SERVICE”, IF(AND(MONTH(TODAY())= MONTH({REG MO}),DATETIME_DIFF({LAST INSP},TODAY(),‘DAYS’)<-150),“DUE FOR INSPECTION”, IF(MONTH(TODAY())!= MONTH({REG MO}), “NOT DUE”,“READY TO RENEW”,'Blank')))='Blank',

SWITCH({REG PIPELINE}, ‘CANT BE INSPECTED’, “CANT BE INSPECTED”, ‘SENT TO TAX OFFICE’, CONCATENATE(“TO TAX OFFICE” & " " & DATETIME_FORMAT({PIPELINE MOVE}, ‘MM/DD/YYYY’)), ‘BACK FROM TAX OFFICE’, CONCATENATE(“RETURNED” & " " & DATETIME_FORMAT({PIPELINE MOVE}, ‘MM/DD/YYYY’)), ‘MAILED TO MANAGER’, CONCATENATE(“MAILED” & " " & DATETIME_FORMAT({PIPELINE MOVE}, ‘MM/DD/YYYY’))),

SWITCH({SERVICE STATUS}, ‘INQUIRE’, “NEEDS ATTENTION”, ‘UNKNOWN’, “NEEDS ATTENTION”, ‘OUT OF SERVICE’, “OUT OF SERVICE”, IF(AND(MONTH(TODAY())= MONTH({REG MO}),DATETIME_DIFF({LAST INSP},TODAY(),‘DAYS’)<-150),“DUE FOR INSPECTION”, IF(MONTH(TODAY())!= MONTH({REG MO}), “NOT DUE”,“READY TO RENEW”))))

Reply


Terms & Conditions