merge ifs

[aminsaleh]

Hi, 

is there a way to merge many ifs in one line code

IFS(
[City] ="Mississauga","West",
[City] ="Oakville","West",
[City] ="Milton","West",
[City] ="Brampton","West",
[City] ="Etobicoke", "East",
[City] ="Vaughan", "East",
[City] ="Markham","East",
[City] ="Scarborough","East",
[City] ="Toronto","East",
[City] ="North York","East",

)

[Ridwan12 · Accepted solution]

IFS(
OR(
[City] = "Mississauga",
[City] = "Oakville",
[City] = "Milton",
[City] = "Brampton"
),
"West",
OR(
[City] = "Etobicoke",
[City] = "Vaughan",
[City] = "Markham",
[City] = "Scarborough",
[City] = "Toronto",
[City] = "North York"
),
"East"
)

maybe you can try this one

[aminsaleh]

For me I will go with this

Thanks all indeed

IFS(
OR(
[City] ="Mississauga",
[City] ="Oakville",
[City] ="Milton",
[City] ="Brampton",
[City] ="Burlington",
[City] ="Hamilton",
[City] ="Kitchener",
[City] ="Guelph",
[City] ="Cambridge",
[City] ="Cambridge",
[City] ="Halton"
),
"West",
OR(
[City] ="Etobicoke",
[City] ="Vaughan",
[City] ="Markham",
[City] ="Scarborough",
[City] ="Oshawa",
[City] ="North York",
[City] ="Toronto",
[City] ="North York",
[City] ="East York",
[City] ="Richmond Hill",
[City] ="Oshawa",
[City] ="Ottawa",
[City] ="Ajax"
),
"East"
)

[Suvrutt_Gurjar]

Please try 

IFS (IN([City], LIST("Mississauga" , "Brampton" , "Oakville" , "Milton")), "West",

          ISNOTBLANK([City]), "East"

     )

If [City] column can never be blank, meaning it is a "required" column field, then the expression could be further simplified as 

IF(IN([City], LIST("Mississauga" , "Brampton" , "Oakville" , "Milton")), "West",

          "East"

       )

Another ,could be 

IF( IN(LEFT([City],2), LIST("Mi",  "Oa",  "Br")), "West",

     "East"

     )

     With last one especially , one would need to be careful as it depends on just first 2 letters of the city name. If in future a city having the same first two letters is added to the "East" category, the expression will fail.

Essentially, the simplification will need to be with proper awareness of long term impact.🙂           

Please test well all sugegstions.

[aminsaleh]

@Suvrutt_Gurjar 

Thank you very much

This solution is very good

IFS (IN([City], LIST("Mississauga" , "Brampton" , "Oakville" , "Milton")), "West",

          ISNOTBLANK([City]), "East"

)

------

But the problem if a customer enters a new city the app will directly set it in east and that may be true and may be wrong as well. 

any new city can be east or west.

Can I make two lines like this

IFS (IN([City], LIST("Mississauga" , "Brampton" , "Oakville" , "Milton")), "West",

IFS (IN([City], LIST("aaa" , "bbb" , "ccc" , "ddd")), "east",

@Ridwan12 

[Suvrutt_Gurjar]

Hi ,

The solution was for the given requirement of simplifying the given expression.

Adjusting the expression dynamically for a new city added is a new requirement that you did not mention earlier.🙂

It may not be possible to very intuitively adjust the new city in either of the buckets. You may need to add a new city in the expression at an appropriate place. If a new city belongs to the  "west" , you will need to add it in the list of "west" cities, LIST("Mississauga" , "Brampton" , "Oakville" , "Milton") in the expression. If the city belongs to the "East", you can leave the expression as it is.

Alternatively you can write a logic to let customer define the "East" and "West" during the data entry when the customer enteres a new city and use that information suitably in the app logic.

[aminsaleh]

You are true, I could have explained it better.

thanks for your help