Testing a single-select field where the options have the same name ?

Hey ​@Yves56,

Would you mind sharing some further context, or maybe even screenshots? I’m not sure I am following.
Not sure if this helps, but if you’d like to show the value from {Field A} in Field B, then formula for Field B should just be:

{Field A}

By just referencing the original field name, that will show its value.

Is this what you are looking for?

Mike, Consultant @ Automatic Nation 
YouTube Channel

The color picked for a single select isn’t attached to the string value, so outside of the actual field Airtable is going to read everything as blank (this is also honestly bad for data integrity). 

You’re going to need to add something to the single select for this to work, but depending on how many options you’re utilizing, there may be a useful workaround. What about using colored circle emojis to fill the string value? You need to add a Switch function to translate them (or I suppose you could work directly with them in your formula) but I just tested this and it worked 🤷‍♂️

Hi ​@Yves56,

I agree with ​@DisraeliGears01, using empty or even spaces for the single select bubbles is not an idea solution, but I will assume that you have a very good reason for keeping that set up so I will entertain this approach. 

My recommendation is to use a script to get the color and or the option ID of the single select. You can use these these values to create a mapping formula that will do something based on the actual selected single select bubble. 

Here is a script you can use:

const TABLE_NAME = "Table 1";                        // your table name
const SINGLE_SELECT_FIELD_NAME = "Select";           // your single-select field
const DEST_OPTION_ID_FIELD_NAME = "Select ID";       // single line text field
const DEST_COLOR_FIELD_NAME = "Select Color";        // single line text field
const WRITE_RESULTS = true;                          // set false to only console.log
const RECORD_ID = null;                              // e.g. "recXXXXXXXXXXXXXX" (or leave null to process all)


const table = base.getTable(TABLE_NAME);
const selectField = table.getField(SINGLE_SELECT_FIELD_NAME);

// Basic guardrails
if (selectField.type !== "singleSelect") {
  throw new Error(`Field "${SINGLE_SELECT_FIELD_NAME}" is not a single-select.`);
}

// Build map
const choices = selectField.options?.choices ?? [];
const choiceById = new Map(choices.map(c => [c.id, { name: c.name, color: c.color }]));

// Figure out which fields to load
const destIdField = DEST_OPTION_ID_FIELD_NAME ? table.getField(DEST_OPTION_ID_FIELD_NAME) : null;
const destColorField = DEST_COLOR_FIELD_NAME ? table.getField(DEST_COLOR_FIELD_NAME) : null;

const fieldsToLoad = [selectField];
if (destIdField) fieldsToLoad.push(destIdField);
if (destColorField) fieldsToLoad.push(destColorField);

// Query records
const query = await table.selectRecordsAsync({ fields: fieldsToLoad });
const records = RECORD_ID
  ? query.records.filter(r => r.id === RECORD_ID)
  : query.records;

// Prepare updates
const updates = [];
for (const record of records) {
  const val = record.getCellValue(selectField); // {id, name} or null
  if (!val) {
    if (WRITE_RESULTS && (destIdField || destColorField)) {
      updates.push({
        id: record.id,
        fields: {
          ...(destIdField ? { [destIdField.id]: null } : {}),
          ...(destColorField ? { [destColorField.id]: null } : {}),
        },
      });
    }
    console.log(`[${record.id}] No selection`);
    continue;
  }

  const optionId = val.id; //the single-select option ID
  const meta = choiceById.get(optionId) || {};
  const color = meta.color ?? null; // e.g., "greenBright", "blueLight2", etc.

  console.log(
    `[${record.id}] ${SINGLE_SELECT_FIELD_NAME}: name="${val.name}", optionId="${optionId}", color="${color}"`
  );

  if (WRITE_RESULTS && (destIdField || destColorField)) {
    updates.push({
      id: record.id,
      fields: {
        ...(destIdField ? { [destIdField.id]: optionId } : {}),
        ...(destColorField ? { [destColorField.id]: color } : {}),
      },
    });
  }
}

// Batch update in chunks of 50
if (WRITE_RESULTS && updates.length) {
  while (updates.length) {
    await table.updateRecordsAsync(updates.splice(0, 50));
  }
  console.log(`Updated ${updates.length} records.`);
} else {
  console.log("No updates written (WRITE_RESULTS is false or nothing to change).");
}

This is how it looks on the table:

You can make the script as an automation script so that it will run anytime your single select is updated. Hope this helps! Also if you want to share more about your use case we might be able to recommend other options that won’t need this workaround.

Thanks a lot for these two excellent tips !