Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 06, 2019 06:42 PM
I have a table “Ethnicity”. For the purpose of this post, I’ll use Asian ethnicities as an example, but my Ethnicity table will also include ethnicities of other racial groups.
On this table are entries like “Asian Indian”, “Bangladeshi”, “Bhutanese”, “Taiwanese”, “Chinese”, etc.
I have another table “Contacts” which contains a column that links to Ethnicity. Each record in Contacts can be linked to multiple ethnicities.
I now want a column that marks whether a record’s ethnicities fall under larger racial groups.
For example, if the record is linked to “Taiwanese” or “Chinese” or both, I want the record to be marked “Yes” under the column, “Asian”
What’s the best way to accomplish this?
Solved! Go to Solution.
Sep 07, 2019 02:29 AM
Hi @NYCEDU_Admin - you could do this with an IF()
statement or a SWITCH()
statement, e.g.:
IF(
{Ethnicity} = 'Taiwanese',
'Yes',
IF(
{Ethnicity} = 'Chinese',
'Yes',
IF(...)
)
)
but I think this is going to get pretty complicated pretty quickly.
As Ethnicity is a linked field, a better way is to add the racial grouping to the Ethnicity table and then lookup this value back into the contacts table:
As you can see on Person 4, if you select two ethnicities, you will get two occurrences of the ethnic grouping.
If you then want a column that has a Y/N on, for example, “Asian” you could then add a formula field which works this out:
using:
IF(FIND('Asian', {Ethnic Grouping}), 'Y', 'N')
JB
Sep 07, 2019 02:29 AM
Hi @NYCEDU_Admin - you could do this with an IF()
statement or a SWITCH()
statement, e.g.:
IF(
{Ethnicity} = 'Taiwanese',
'Yes',
IF(
{Ethnicity} = 'Chinese',
'Yes',
IF(...)
)
)
but I think this is going to get pretty complicated pretty quickly.
As Ethnicity is a linked field, a better way is to add the racial grouping to the Ethnicity table and then lookup this value back into the contacts table:
As you can see on Person 4, if you select two ethnicities, you will get two occurrences of the ethnic grouping.
If you then want a column that has a Y/N on, for example, “Asian” you could then add a formula field which works this out:
using:
IF(FIND('Asian', {Ethnic Grouping}), 'Y', 'N')
JB
Sep 07, 2019 06:40 PM
This was exactly what I needed, thank you!!!