Forum Discussion
Post PatronIF Column is blank, then use another IF statement in created column
Just in case your client type is either "" or blank,
New Ministry =
IF(OR(ISBLANK([CLIENT TYPE]),[CLIENT TYPE]=""),IF([SOURCE_SYSTEM_CODE]="SAMS","MCCSS",""),SWITCH([CLIENT TYPE],"MCCSS NOT REFERRED","MCCSS","MCCSS REFERRED","MCCSS","MLTSD NOT REFERRED","MLTSD","NO USER","NO MINISTRY","UNHAPPY PATH","MCCSS"))
What this will do is it will search Client Type for either Blank or "". If it is, then it will look for Source System Code. So if Client Type = Blank or "" and if Source System Code = "SAMS", then it will give you "MCCSS". If it is not blank or "", it will look at Client Type, where if Client Type ="MCCSS NOT REFERRED", then it will give you "MCCSS" and so on. SWITCH works similarly as IF function.
Post Prodigy
Just in case your client type is either "" or blank,
New Ministry =
IF(OR(ISBLANK([CLIENT TYPE]),[CLIENT TYPE]=""),IF([SOURCE_SYSTEM_CODE]="SAMS","MCCSS",""),
SWITCH([CLIENT TYPE],"MCCSS NOT REFERRED","MCCSS","MCCSS REFERRED","MCCSS","MLTSD NOT REFERRED","MLTSD","NO USER","NO MINISTRY","UNHAPPY PATH","MCCSS"))
What this will do is it will search Client Type for either Blank or "". If it is, then it will look for Source System Code. So if Client Type = Blank or "" and if Source System Code = "SAMS", then it will give you "MCCSS". If it is not blank or "", it will look at Client Type, where if Client Type ="MCCSS NOT REFERRED", then it will give you "MCCSS" and so on. SWITCH works similarly as IF function.
Post PatronThis is great! So can I do this?:
New Ministry = IF(ISBLANK([MINISTRY ROLE]),
IF(OR(ISBLANK([CLIENT TYPE]),[CLIENT TYPE]=""),IF([SOURCE_SYSTEM_CODE]="SAMS","MCCSS",""),
SWITCH([CLIENT TYPE],"MCCSS NOT REFERRED","MCCSS","MCCSS REFERRED","MCCSS","MLTSD NOT REFERRED","MLTSD","NO USER","NO MINISTRY","UNHAPPY PATH","MCCSS")), [MINISTRY ROLE])
Since my primary driver is the original [MINISTRY ROLE] column, if this column is blank, then it does the above function that you have written?
- darentengmfs
- OPS-MLTSD
it worked, thank you, really appreciated your help on this!