Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 07, 2020 01:25 PM
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???
Solved! Go to Solution.
Aug 12, 2020 02:51 PM
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}]});
}
Aug 07, 2020 02:33 PM
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:
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.
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:
Aug 07, 2020 02:47 PM
Thanks for the feedback (and the correct terminology!) I will explore both those recommendations and report back.
Aug 09, 2020 04:31 AM
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;
}
Aug 09, 2020 07:19 AM
It’s possible that @JonathanBowen or @Justin_Barrett or @kuovonne might be able to help you with your script.
Aug 10, 2020 12:33 PM
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:
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.parent
variable in your case.{Category}
field, you need “Category”, not [Category].writeValue
because the category value is in the category
variableWith 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.
Aug 12, 2020 05:43 AM
Thanks Justin! I appreciate your help. This is a lot for me to wrap my head around—will see where I get with this.
Aug 12, 2020 10:53 AM
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});
}
Aug 12, 2020 12:08 PM
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.
Aug 12, 2020 01:16 PM
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.