Using IF Formulas with different worksheets

[Timothy_Grocott]

Hello,

I’m rather new to Airtable and need help with a Formula.

I have [worksheet 1] & [worksheet 2]

Worksheet 2 has two columns, {A} & {B}.

{A} has words or short phrases. {B} has a point value associated with the text in {A}.

In worksheet 1, I have a column that those words will populate in. I’d like to use the column right next to it to show the point value of text that appears in that column.

How can I make this happen. I’m thinking using a formula with IF. But I can’t figure out how to type it up.

Thanks!

[Justin_Barrett]

Welcome to the community, Timothy! :grinning_face_with_big_eyes: To begin with, Airtable formulas don’t currently work across tables (what you called worksheets). They only work within a single table. To share data between tables requires at least one field that links records between the relevant tables, along with some combination of either rollup and/or lookup fields.

Before going further, I have a question about your desired setup. Say in [Table 1] you’ve got “apple” assigned a point value of 5, and “pear” a value of 10. In [Table 2] you enter “apple” into the entry field, and I’m assuming you want the value 5 to appear in the field next to it. But what happens if the entry field contains “apple pear”? Should the value in the adjacent field be 15, the sum of the point value for both words?

I ask because finding a single match against a reference table, and connecting that to a single point value, can be done with a little work (I’ve done it). However, finding all possible matches, and adding all relevant point values, is a whole different story. I can’t think of a way to do it using a reference table like you describe. Even within a single table, Airtable doesn’t have the means in a single formula to perform multiple matches and connect each match with a unique value, then total all the found values. The only solution that comes to mind involves a host of individual formula fields—one to check for each word/phrase and return the resulting point value (or zero if not found)—and a final formula field to add all values, at which point the reference table is useless.

[Timothy_Grocott]

Justin, thank you for your response! The situation you described, “apple pear” would never happen. It would only be one exact match each time.

Thanks again!

[Justin_Barrett]

Actually, I think I jumped the gun a bit, and I apologize for getting your hopes up with my earlier comment.

The setup I built matches an entire word/phrase against a reference table of words/phrases. What it sounds like you want is to find one of several possible matching words/phrases inside a larger phrase; e.g. which fruit (if any) is named in the sentence, “I like this apple,” and if found, what value is assigned to that matching fruit. That is much more complicated because it requires some way to iterate through a collection of items (your reference list) and search for each item in the larger phrase one by one, returning the one that matches (if any). Airtable doesn’t currently have such tools in its arsenal I’m afraid.

[Timothy_Grocott]

I don’t think you jumped the gun. I think what you said earlier, will work perfectly.

The reference table words/phrases WILL match.

Let me explain a little better what I’m trying to do.

I am creating a tracking system for a point system for tracking Team Member Infractions.

This is the main Table:

This is the reference table:

The reference table will be mostly static. The list will be longer, this is just for an example until I know it works.

On the main Table [Incoming Points], we will use a form to populate it. the list of options is already linked to the {Infraction}. I just want to make the {Point Value} also carry over into the column right next to it.

I hope this makes better sense now.

[Justin_Barrett]

That makes much more sense. Because your infractions aren’t just text, they’re links, you can simply use a lookup to pull in the associated point value. Here’s a simplified recreation of your setup, using the lookup field on the [Incoming Points] table to pull relevant points from [Infractions]:

[Timothy_Grocott]

Wow. that’s exactly what I needed! thank you so much! I really appreciate your help!

I do have another question if you don’t mind.

Is there any way to automatically delete an entry after X number of days?

I’d like the points to go away after 4 months, and was hoping to make it automatic, rather than a manual process.

Thanks again!

[Justin_Barrett]

You could use an integration service like Zapier or Integromat to check the records periodically, and delete records with dates older than four months. I’d lean toward Integromat because I already do something similar with some of my own stuff, plus there isn’t the pesky two-step limit that Zapier imposes for free accounts (Integromat’s free accounts still have their own limits; they’re just not based on a number of steps).

[Timothy_Grocott]

Sounds good! I’ll look into it! Thanks!

[Timothy_Grocott]

Justin, I was wondering if you could help me out with another portion of this same Base I’m working on. Here is the link to the post I just created: Automatically send emails to specific people based on what the info is