Link Tables Limit record selection to a view by existing relationship?

2 Tables - Constituents and Constituent Types.

Constituents holds contact information.
First Name, Last Name, Email, Phone, etc.

Constituent Type holds the taxonomy (so it has a hierarchy) to organize the Constituent data

a. Individual
a. Organizations
b. Foundation
b. Business
c. Healthcare
c. Retail
c. Personal Activities-Interests
b. Government
c. Local
c. Regional
c. Federal
b. Nonprofit
b. Religious-Educational
b. Club-Membership-Association

I have three fields in Constituent to link to the Constituent Type

Constituent Type
Links to Constituent Types with a Limit record selection to a view only a.
Works great because there are only 2 options.

Constituent Organization Type
Links to Constituent Types with a Limit record selection to a view only b.
Works great because currently, only Organizations have multi-levels.

Constituent Organization Area.
This is where I want to link to “Constituent Types” based on the “Constituent Organization Type” selected in the “Constituents” table.
Creating a view for only c, doesn’t work because it will not limit the user to only the terms related to the “Organization Type” selected in the “Constituents” table.

Any ideas???

Hi @Cathy_Anderson,

What you’re requesting is known as a “conditional value list” when linking your records.

Unfortunately, this is not natively possible in Airtable, but there are 2 known workarounds:

  1. You could write your own custom JavaScript in the scripting block, which would guide the user through the data entry process by limiting their options based upon previous options. They would do their data entry from the scripting block, which would be running your JavaScript.

  2. Sticking with the Airtable interface (without needing any JavaScript), one of our community leaders came up with a workaround. You can read more about how to implement his workaround here:

Thanks for the feedback (and the correct terminology!) I will explore both those recommendations and report back.

Decided to give scripting a try.
I don’t need any more tables-but my solution right now is far from perfect.

A button is running the script on the ‘constituents’ table.
A filtered view for all of the 2 levels was created on the ‘taxonomy’ table.
I would prefer the script to filter but I could not figure that out.
I can’t set the final field—‘category’ with the selected text.

let name = base.getTable('Constituents');

let parent = await input.recordAsync(‘The constituent’, name);
if (parent) {
output.text(${parent.getCellValueAsString('Community')});
}

let taxonomy = base.getTable(‘Taxonomy’);
let view = taxonomy.getView(${parent.getCellValueAsString('Community')});

// pick a category
let category = await input.recordAsync(‘Pick a category’, view);
// if there is a category
if (category) {
const writeValue = output.text(${name.getCellValueAsString('Category')});
await table.updateRecordAsync(name, {[Category]: writeValue});
return writeValue;

}

It’s possible that @JonathanBowen or @Justin_Barrett or @kuovonne might be able to help you with your script.

Thanks for the tag, @ScottWorld.

@Cathy_Anderson I think there are some formatting issues due to how the code got pasted into the forum. I believe this is how it actually looks on your end:

let name = base.getTable('Constituents');
let parent = await input.recordAsync('The constituent', name);
if (parent) {
    output.text(`${parent.getCellValueAsString('Community')}`);
}

let taxonomy = base.getTable('Taxonomy');
let view = taxonomy.getView(`${parent.getCellValueAsString('Community')}`);

// pick a category
let category = await input.recordAsync(‘Pick a category’, view);
// if there is a category
if (category) {
    const writeValue = output.text(`${name.getCellValueAsString('Category')}`);
    await table.updateRecordAsync(name, {[Category]: writeValue});
    return writeValue;
}

As a heads-up for the future, you can format long blocks of code by wrapping it inside pairs of graves triplets, like this:

```
code in here
```

Which becomes:

code in here

First off, when all that you’re outputting—or inserting into another function or method call—is the value from a variable (or another method call), you don’t need to wrap the string in graves, like you do here:

output.text(`${parent.getCellValueAsString('Community')}`);

You can do it like this:

output.text(parent.getCellValueAsString('Community'));

Only when you want to insert that value into some larger text string that you’re outputting would you need to do the more complex insertion method:

output.text(`The value I received is ${parent.getCellValueAsString('Community')}, and I'm not sure what to do with it.`);

That lets us simplify the start of the script to this:

let name = base.getTable('Constituents');
let parent = await input.recordAsync('The constituent', name);
if (parent) {
    output.text(parent.getCellValueAsString('Community'));
}

let taxonomy = base.getTable('Taxonomy');
let view = taxonomy.getView(parent.getCellValueAsString('Community'));

The “pick a category” line is fine, so I’ll skip that. The biggest hiccups are in the latter end, and I’ll tackle them one line at a time.

    const writeValue = output.text(`${name.getCellValueAsString('Category')}`);

Working from the inside out, you have name.getCellValueAsString('Category'). However, name was originally defined at the top of your script as a table, and tables don’t have a getCellValueAsString method. Only records have that. You already have the category that the user picked in the “Pick a category” query, so in the end, this whole line is unnecessary. There are some other issues with it (the output.text() method doesn’t return anything, so the writeValue variable probably ended up as null or maybe void), but those aren’t relevant to what you’re trying to achieve, so I’ll just remove this line and move to the next one.

    await table.updateRecordAsync(name, {[Category]: writeValue});

This has several things amiss:

  • The updateRecordAsync method is supposed to be called on the table instance that was returned when you called base.getTable() elsewhere in your code. In your case, that instance is in the name variable.
  • The first thing you pass it should be the record ID of the record you wish to update, which is currently in the parent variable in your case.
  • Because you want to assign the category value to the {Category} field, you need “Category”, not [Category].
  • We’re no longer using writeValue because the category value is in the category variable

With those changes, this line should be:

    await name.updateRecordAsync(parent, {"Category": category});

Now for the last line in that block:

    return writeValue;

Only functions return values. Because we’re not in a function, this line can be omitted. That leaves us with this as the modified version of the script:

let name = base.getTable('Constituents');
let parent = await input.recordAsync('The constituent', name);
if (parent) {
    output.text(parent.getCellValueAsString('Community'));
}

let taxonomy = base.getTable('Taxonomy');
let view = taxonomy.getView(parent.getCellValueAsString('Community'));

// pick a category
let category = await input.recordAsync('Pick a category', view);
// if there is a category
if (category)
    await name.updateRecordAsync(parent, {"Category": category});

Let me know if that works.

Thanks Justin! I appreciate your help. This is a lot for me to wrap my head around—will see where I get with this.

Justin, this is so close. thank you! But the last line is throwing an error.
What did I overlook?

—— Can’t set cell values: invalid cell value for field ‘test’.
Cell value has invalid format: must be a string.

let name = base.getTable('Constituents');
let parent = await input.recordAsync('The constituent', name);
if (parent) {
    output.text(parent.getCellValueAsString('Organization Type'));
}

let taxonomy = base.getTable('Taxonomy');
let orgtype = parent.getCellValueAsString('Organization Type');

let view;
if (orgtype === 'Business') {
    view = taxonomy.getView('Affiliations--Business');
} else {
    view = taxonomy.getView('Affiliations');
}
// pick a category
let category = await input.recordAsync('Pick affiliation', view);

// if there is a category
if (category) {
await name.updateRecordAsync(parent, {'test': category});
}

When you collect the category here:

let category = await input.recordAsync('Pick affiliation', view);

What comes back is an instance of a record. What the “test” field is expecting to get, based on the error message you received, is a string. I’m guessing what you want is the name tied to that record, which is a string that can be retrieved using the name attribute on that record instance:

await name.updateRecordAsync(parent, {'test': category.name});

In case you’re wondering, Airtable (or, more accurately, JavaScript) won’t confuse the name attribute on the category record instance with the name variable that you created earlier. It knows internally that the two are different things.

Thanks Justin for so much insight.

This works: but only when 'test" is a text field.
await name.updateRecordAsync(parent, {‘test’: category.name});

The ultimate goal was to link it to the ‘taxonomy’ table.
but that throws

N: Can't set cell values: invalid cell value for field 'test'.
Cell value has invalid format: <root> must be an array.
Linked records field value must be an array of objects with property 'id' corresponding to linked record id.

I wish the Link to another field allowed for filters. The script button works, but the usability is questionable and adds an unnecessary column.

I feel like I’m making this much harder than it need to be.

Correct. Each field requires a specific type of data. Because you need a link, you’ll need to get the ID of the record that you’re linking to. If that’s the record that you chose for category, then you’re in luck, because the ID is also one of the available attributes.

Also, as the error message indicated, you need to pass an array of objects to create links. Here’s how to restructure that line to do this:

await name.updateRecordAsync(parent, {'test': [{id: category.id}]});

And with that, I express my gratitude. Thanks so much! Hopefully soon javascripting will sink in for me.

let name = base.getTable('Constituents');
let parent = await input.recordAsync('The constituent', name);
if (parent) {
    output.text(parent.getCellValueAsString('Organization Type'));
}

let taxonomy = base.getTable('Taxonomy');
let orgtype = parent.getCellValueAsString('Organization Type');

let view;
if (orgtype === 'Business') {
    view = taxonomy.getView('Affiliations--Business');
} else {
    view = taxonomy.getView('Affiliations');
}
// pick a category
let category = await input.recordAsync('Pick affiliation', view);

// if there is a category
if (category) {
await name.updateRecordAsync(parent, {'Affiliation': [{id: category.id}]});
}
1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.