Best way to replace strings in a table or add a 'friendly name' to a value?

Queries and Resources
1

Hi all,

I have a table that lists all inbound calls to a group of ten telephone numbers. We want the table to show a friendly name for each number so it's easier for the user to tell who each call belonged to. For example in the 'to_number' field if the value is +441111111 we want it to display 'Reception'. If the value was +44222222 we would want it to display 'Henry'.

What's the most efficient place to achieve this? e.g. in a transformer, SQL, JSQuery or Mapper?
What would be the best method? Switch Statement, Array comparison etc.?

Thanks in advance,

Jimmy

2

Hi @farthingcomms, I would suggest two options, and you can use either of these according to your convenience.

  • SQL Case statement
  • Mapper

I always prefer case statement and, if I am feeling lazier, then Mapper options.

1 Like
3

Welcome to the forum @farthingcomms.

Another option would be to use the Lookup Table pattern. Have another table with 2 columns, "friendly_name" and "number". Then use a join on your query to extract the name and use that in your table. Hide the number column in the table properties.

SELECT *, fn.friendly_name
FROM inbound_calls ic
JOIN friendly_names fn ON fn.number=ic.number

If you just have a few phones lines that never change this might be overkill, use the Case or the Mapper. But for 10 numbers I would go with the linked table route. If you get a new phone line, just add a record to the friendly_names table, no code needs to change.

2 Likes
4

Awesome, thanks for the reccomendations. I think @bradlymathews solution will work the best for my use case. Thanks again,

5