Forum Discussion
Search / Match formula
Hello everyone.
I have a field on the table which records several phone numbers to dial.
The phone numbers are composed by 10 digits, like XXFFFFFFFF, where XX range from 11 until 99. For every XX, there is a city represented, e.g., 11 = Sao Paulo, 21 = Rio de Janeiro.
I am trying to create another column where I can identify which city the number is from, e.g., if the number is 1133333333, than, the column would receive Sao Paulo.
I wrote the below formula, but it is not working. I keep getting a sintax error
Can someone give me a hand on this?
Thanks,
Column =
SWITCH (
TRUE (),
SEARCH ( "11", database[phone_number], 1, 0 ) = 1, "São Paulo",
SEARCH ( "12", database[phone_number], 1, 0 ) = 1, "São Paulo",
SEARCH ( "13", database[phone_number], 1, 0 ) = 1, "São Paulo",
SEARCH ( "14", database[phone_number], 1, 0 ) = 1, "São Paulo",
SEARCH ( "15", database[phone_number], 1, 0 ) = 1, "São Paulo",
SEARCH ( "16", database[phone_number], 1, 0 ) = 1, "São Paulo",
SEARCH ( "17", database[phone_number], 1, 0 ) = 1, "São Paulo",
SEARCH ( "18", database[phone_number], 1, 0 ) = 1, "São Paulo",
SEARCH ( "19", database[phone_number], 1, 0 ) = 1, "São Paulo",
SEARCH ( "21", database[phone_number], 1, 0 ) = 1, "Rio de Janeiro",
SEARCH ( "22", database[phone_number], 1, 0 ) = 1, "Rio de Janeiro",
SEARCH ( "24", database[phone_number], 1, 0 ) = 1, "Rio de Janeiro",
SEARCH ( "27", database[phone_number], 1, 0 ) = 1, "Espírito Santo",
SEARCH ( "28", database[phone_number], 1, 0 ) = 1, "Espírito Santo",
SEARCH ( "31", database[phone_number], 1, 0 ) = 1, "Minas Gerais",
SEARCH ( "32", database[phone_number], 1, 0 ) = 1, "Minas Gerais",
SEARCH ( "33", database[phone_number], 1, 0 ) = 1, "Minas Gerais",
SEARCH ( "34", database[phone_number], 1, 0 ) = 1, "Minas Gerais",
SEARCH ( "35", database[phone_number], 1, 0 ) = 1, "Minas Gerais",
SEARCH ( "37", database[phone_number], 1, 0 ) = 1, "Minas Gerais",
SEARCH ( "38", database[phone_number], 1, 0 ) = 1, "Minas Gerais",
SEARCH ( "41", database[phone_number], 1, 0 ) = 1, "Paraná",
SEARCH ( "42", database[phone_number], 1, 0 ) = 1, "Paraná",
SEARCH ( "43", database[phone_number], 1, 0 ) = 1, "Paraná",
SEARCH ( "44", database[phone_number], 1, 0 ) = 1, "Paraná",
SEARCH ( "45", database[phone_number], 1, 0 ) = 1, "Paraná",
SEARCH ( "46", database[phone_number], 1, 0 ) = 1, "Paraná",
SEARCH ( "47", database[phone_number], 1, 0 ) = 1, "Santa Catarina",
SEARCH ( "48", database[phone_number], 1, 0 ) = 1, "Santa Catarina",
SEARCH ( "49", database[phone_number], 1, 0 ) = 1, "Santa Catarina",
SEARCH ( "51", database[phone_number], 1, 0 ) = 1, "Rio Grande do Sul",
SEARCH ( "53", database[phone_number], 1, 0 ) = 1, "Rio Grande do Sul",
SEARCH ( "54", database[phone_number], 1, 0 ) = 1, "Rio Grande do Sul",
SEARCH ( "55", database[phone_number], 1, 0 ) = 1, "Rio Grande do Sul",
SEARCH ( "61", database[phone_number], 1, 0 ) = 1, "Distrito Federal/Goiás",
SEARCH ( "62", database[phone_number], 1, 0 ) = 1, "Goiás",
SEARCH ( "63", database[phone_number], 1, 0 ) = 1, "Tocantins",
SEARCH ( "64", database[phone_number], 1, 0 ) = 1, "Goiás",
SEARCH ( "65", database[phone_number], 1, 0 ) = 1, "Mato Grosso",
SEARCH ( "66", database[phone_number], 1, 0 ) = 1, "Mato Grosso",
SEARCH ( "67", database[phone_number], 1, 0 ) = 1, "Mato Grosso do Sul",
SEARCH ( "68", database[phone_number], 1, 0 ) = 1, "Acre",
SEARCH ( "69", database[phone_number], 1, 0 ) = 1, "Rondônia",
SEARCH ( "71", database[phone_number], 1, 0 ) = 1, "Bahia",
SEARCH ( "73", database[phone_number], 1, 0 ) = 1, "Bahia",
SEARCH ( "74", database[phone_number], 1, 0 ) = 1, "Bahia",
SEARCH ( "75", database[phone_number], 1, 0 ) = 1, "Bahia",
SEARCH ( "77", database[phone_number], 1, 0 ) = 1, "Bahia",
SEARCH ( "79", database[phone_number], 1, 0 ) = 1, "Sergipe",
SEARCH ( "81", database[phone_number], 1, 0 ) = 1, "Pernambuco",
SEARCH ( "82", database[phone_number], 1, 0 ) = 1, "Alagoas",
SEARCH ( "83", database[phone_number], 1, 0 ) = 1, "Paraíba",
SEARCH ( "84", database[phone_number], 1, 0 ) = 1, "Rio Grande do Norte",
SEARCH ( "85", database[phone_number], 1, 0 ) = 1, "Ceará",
SEARCH ( "86", database[phone_number], 1, 0 ) = 1, "Piauí",
SEARCH ( "87", database[phone_number], 1, 0 ) = 1, "Pernambuco",
SEARCH ( "88", database[phone_number], 1, 0 ) = 1, "Ceará",
SEARCH ( "89", database[phone_number], 1, 0 ) = 1, "Piauí",
SEARCH ( "91", database[phone_number], 1, 0 ) = 1, "Pará",
SEARCH ( "92", database[phone_number], 1, 0 ) = 1, "Amazonas",
SEARCH ( "93", database[phone_number], 1, 0 ) = 1, "Pará",
SEARCH ( "94", database[phone_number], 1, 0 ) = 1, "Pará",
SEARCH ( "95", database[phone_number], 1, 0 ) = 1, "Roraima",
SEARCH ( "96", database[phone_number], 1, 0 ) = 1, "Amapá",
SEARCH ( "97", database[phone_number], 1, 0 ) = 1, "Amazonas",
SEARCH ( "98", database[phone_number], 1, 0 ) = 1, "Maranhão",
SEARCH ( "99", database[phone_number], 1, 0 ) = 1, "Maranhão",
"UNKNOWN"
)
Hmm, I tried this formula out and it seemed to work perfectly. Only thing I can think of is that you don't have a table named database or that your database table does not have a column called phone_number or that you are creating this as a measure not a column. Oh, one other possibility is that your regional language settings are set such that you need to use semi-colons instead of commas?
3 Replies
Hmm, I tried this formula out and it seemed to work perfectly. Only thing I can think of is that you don't have a table named database or that your database table does not have a column called phone_number or that you are creating this as a measure not a column. Oh, one other possibility is that your regional language settings are set such that you need to use semi-colons instead of commas?
Right on!
Regional setting. I should have used ";" instead of ",".Tks!!
Awesome!
