Use outcome in formula field in another formula field

Hi,
maybe I its to obvious and I don’t get it - but how can I work with the outcome of a formula field?
Every time I build formulas with which I want to “work further” with the outcome of another formula field there is no output (blank).
Any idea how to make that possible?
Thanks in advance
Onur

Any formula field can be referenced in any other formula field (in the same table) by adding the desired formula field name to formulas in other fields. For example, if your first formula field is named {First Formula} and outputs a string, a later formula can use it like this:

"My first formula's output is " & {First Formula}

Can you post a screenshot of a situation where this isn’t working for you?

Hi Justin,
I have in column {Dateraugh} with value “Date May 7-8, 2020”

In order to get rid of the “Date” I use a formula field in the next column {extracted} with:
SUBSTITUTE({Dateraugh},“Date”,"")
=> “May 7-8, 2020” => works = fine

Now I want to go on with this output and extract the month (first word) - in the next column with a formula:
LEFT(extracted,SEARCH(" ",extracted)-1)
=> expected outcome would be: “May”
=> what I have is a blank field

Does this explain?

Yes, that definitely clears things up. Let me explain what’s happening based on your existing formulas.

In your {extracted} field, you remove the word “Date” from the string. However, you did not remove the space between “Date” and “May”, so the string returned by that formula is " May 7-8, 2020" (notice the space before “May”). When the next formula tries to extract the next piece based off the position of the first space it finds, it find that space at the very start of the string, which is position 1. You then subtract 1 from that position to get 0, and then return the leftmost 0 characters from the string.

This can be fixed most easily by modifying your first formula to remove "Date " (including the space after “Date”), which should make the next formula return “May” correctly.

Hi Justin,
you are so right - and lost so much time;) Thanks to your right hint - this DAU Problem is solved.
I have another break when I try to use the solution for a lookup.
Let me explain:
I have this 2 tables:

In T1 I already have my StartDateMonth via formulas. Now I want to use the value in fields of {StartDateMonth} to been looked up in the “MonthLookUp” Table:

Therefore I use this script in a script block:
2020-05-30 16_42_35-Work2_ MonthLookUp - Airtable

No matter if I use the formula field {StartDateMonth} or the values in {DateTypedIn} (that I just manually typed in randomly to check if it was the field type that mattered)
=> effect is nothing!
=> while expected behavior would be: that I have the corresponding value like 5 (for May) or 7 (for Jul) in T1>{StartDateMonNr}.

Any idea what I am missing here?
Help is highly appreciated.

Reference:

Btw: VLOOKUP as one of the core features of “good” excel spread sheets - should be a “must have” in the default airtable formulas - don’t you think?

  • for the common user there are many usecases & I am sure using formulas to solve problems needs a bit of a way as an airtable user - but to go further and build every vlookup as scripts is simply considered as a missing core feature - something that airtable doesn’t deserve;)

Recommendation -

Above line 11, add a debugging line that compares the cell value of MonthName with lookupValue.

output.inspect(rangeRecord.getCellValue("MonthName") === lookupValue);

If nothing resolves to True, you are on your way to understanding the nature of this bug. If many resolve to True, the issue is neither your logic nor the approach and instead simply the data type of the returnValue. Are you certain it is a string (for example)?

Also, consider evaluating with “==” just for giggles.

2 Likes

Not necessarily. While the default grid view looks a lot like a spreadsheet, it’s important to remember that Airtable is not a spreadsheet. It’s a database. Database design is inherently very different from spreadsheet design. A lot of people come to Airtable expecting it to have spreadsheet-like behavior (especially VLOOKUP), but I feel it’s important to understand what Airtable is and work within that structure, rather than try to turn it into something that it isn’t designed to be.

Hm, for most users (reflected in the template library) - Airtable is a super useful tool/product and a lot more than a “database”.
(Otherwise all the product moves with blocks & sdks wouldn’t make sense.)
& yes, luckily it moved far away from being in the simply-online-spreadsheet-league.

Nevertheless - the “Vlookup” is not a “spreadsheet” thing.
It reflects (&solves elegantly) a common use case when dealing&working with structured data.
So having this on your fingertips (just like other formulas) would bring a lot of value (time, ux)

Indeed. It’s simply a type of implementation to find “x” as a function of “y”. In any data-centric environment, this is extremely common. What you call it (or where you make it callable) is not at issue; it is fundamental to relationships.

I see this sentiment a lot in the community and I think it has become a pat answer for an unapologetic retort to anything that doesn’t work in a sensible fashion. We can debate the essence (Is it a database? Or is it a spreadsheet) in the context of historical definitions that really have little to do with the user’s requirement - which in this case is clear. Given “x”, what is the corresponding “y” record in another table. As @Onur makes clear, this is usual and customary in both spreadsheets and databases; the bottom line - it’s usual and customary in all structured data solutions. As such, 51% of me believes that Airtable has a duty to make VLookup-like processes simple and effortless.

This, I fully agree with and @Justin_Barrett’s comment does hold water; a VLookup() function implemented in the current architecture of Airtable seems unlikely and perhaps a bad idea. Here’s why…

Unlike a Script Block approach - the likes of which is provided in this thread - a VLookUp() function would have to fire against every cell in a column and repeatedly as the formula engine decides to recompute the world. I believe this inefficiency is precisely why Array Functions were created by Google and Microsoft to scale formulas as they apply to large and growing data sets. But - and it’s a big BUT - they create a performance hit.

Airtable is facing a similar challenge - it is indeed more like a database but its formula system seems to be – in many regards – designed more like a spreadsheet. We can’t blame Airtable for this balancing act; users want the best of both worlds and they are simply trying to make a better mousetrap.

I’ll bet a nice turkey sandwich that Airtable has experimented a number of times with a VLookup() formula and tabled it because of the performance issues which are far more common in a tool that casts itself as a “database”, not a “spreadsheet”.

Back to the Script Block as a VLookup Proxy

This is extremely fast – UNTIL – you update the database, the likely issue Airtable would face if implementing VLookup as a formula function that blindly recomputes lookups all the time and for results that have already been predetermined. If you don’t have blistering fast record/field update performance, VLookup() might actually make matters worse.

What’s the Remedy?

All of the above is opinion and subject to a compass-heading correction by Airtable themselves, but since I’m blathering on like the old “Doc” in Doc Hollywood, why stop now. :wink:

In my view - and given the assertion that Airtable is both a database and a spreadsheet - the right approach is:

  1. Make it possible for formulas to exist as attributes of an actual (real) field.
  2. Make script blocks definable as internal functions; this makes it possible for anyone to create their own VLookup() formula addition to the existing functions and call it from any location where formulas are supported.
  3. Build an optimized Script Block VLookup() that only fires when the field it is configured to populate is empty.
1 Like

Looking at the remedies stated:
none of these can be solved by “normal customers” at the moment.
So I assume, the way to do the lookup I described above, should be a valid workaround at the moment, right?
Anyone any idea why this above does not work?

I see it coming: :thinking: Seems like I will skip the idea of using airtable for parsing and use phyton scripts offside & push then the outcome to airtable.
Sad…, I thought I can do at least the same or even more in airtable then compared to excel - guess sometimes the “old” things are not that bad :face_with_hand_over_mouth:

That is correct - even customers with superpowers could not address the VLookup() request in a workaround. But… step #3 in the [ideal] remedy is precisely what should work today once you debug your script.

Yes, I use this approach in Script Blocks all the time.

Yes. Did you follow my recommendations for debugging, and what was the outcome?

Hi,
sry - I did & that is what I get:


=> a false statement all the time

Right - and so we’ve proven that nothing should happen because none of the tests you perform through the lookup process evaluate to True.

This means that the data coming from rangeRecord.getCellValue("MonthName") is not equal to (===) lookupValue. Ergo, something is obviously wrong with how you establish either (or both) of these values.

@Onur,

Once you solve your comparison issue, you’ll have to fix where you update the table - you cannot specify “record”; it must be the record ID, (i.e., record.id).

image

Further, I typically don’t create loops inside of loops just to perform a lookup. This tends to impact performance quite a bit. Rather, I create an index hash (JSON object) that allows me to discretely address the keys in the lookup process.

Example … this produces a JSON object whose key is “record.name” from the

// create index hash object for the lookup table
let oLookupData = {};
for (let record of lookupRangeRecords.records)
{
  oLookupData[record.getCellValue("Month Name")] = record.name;
}
// output.inspect(oLookupData);

Why is this useful?

  1. It requires only a single pass across the lookup table.
  2. It supports direct addressability in the JSON object which is the equivalent of variables in memory for every lookup you need to process.
  3. Since there are likely multiple lookups for the same values, this streamlines memory and processing making it ultra fast even in large data sets and large lookup tables.

Lastly, this code works -

image

/*
***********************************************************
Airdrop - Fast Lookup
Copyright © 2020 by Global Technologies Corporation
ALL RIGHTS RESERVED
***********************************************************
*/

// display app title
output.markdown('# Perform Fast Lookup');

// get the main table data
let mainTable = base.getTable("T1");

// get the data set for the main table
let mainTableRecords = await mainTable.selectRecordsAsync();

// get the lookup table data
let lookupTable = base.getTable("MonthLookUp");

// get the data set for the lookup table
let lookupRangeRecords = await lookupTable.selectRecordsAsync();

// create index hash object for the lookup table
let oLookupData = {};
for (let record of lookupRangeRecords.records)
{
    oLookupData[record.getCellValue("Month Name")] = record.name;
}
// output.inspect(oLookupData);

// update the main table based on the lookups
for (let record of mainTableRecords.records)
{
    // get the lookup key
    let lookupValue = record.getCellValue("DateTypedIn");
    // get the lookup values
    let returnValue = oLookupData[lookupValue];
    output.inspect(returnValue);
    // update the main table
    mainTable.updateRecordAsync(record.id, {
        "StartDateMonthNr" : returnValue
    })
}
1 Like

Bill - you are amazing!
This fine-tuned-one worked as expected :partying_face: Thanks a lot!

Where & how would you add a fallback value (in case nothing matched)?
With this last addon - this would be it.

Good to hear,

I believe the returnValue would contain null if nothing matching in the hash index. As such, I think this might work…

// get the lookup values
let returnValue = (oLookupData[lookupValue] == null) ? "Nothing Found" : oLookupData[lookupValue];

And to add where I think my problem came from => the missing trims before!
Learning: Given not edited/cleared input data, then trim em like it’s hot!:slight_smile:

Sry, but I can’t get the correct line to have this fallback line pasted in (I nearly tried them all). Could you tell me in which line this will bring the expected result?

It’s a replacement for the let returnValue = line…

Here’s a new version that works:

image
/*

   ***********************************************************
   Airdrop - Fast Lookup
   Copyright (c) 2020 by Global Technologies Corporation
   ALL RIGHTS RESERVED
   ***********************************************************
   
*/

// display app title
output.markdown('# Perform Fast Lookup');

// get the main table data
let mainTable = base.getTable("T1");

// get the data set for the main table
let mainTableRecords = await mainTable.selectRecordsAsync();

// get the lookup table data
let lookupTable = base.getTable("MonthLookUp");

// get the data set for the lookup table
let lookupRangeRecords = await lookupTable.selectRecordsAsync();

// create index hash object for the lookup table
let oLookupData = {};
for (let record of lookupRangeRecords.records)
{
    oLookupData[record.getCellValue("Month Name")] = record.name;
}
// output.inspect(oLookupData);

// update the main table based on the lookups
for (let record of mainTableRecords.records)
{

    // get the lookup key
    let lookupValue = record.getCellValue("DateTypedIn");

    // get the lookup values
    let returnValue = (oLookupData[lookupValue] == null) ? "Nothing Found" : oLookupData[lookupValue];
    // output.inspect(returnValue);

    // update the main table
    mainTable.updateRecordAsync(record.id, {
        "StartDateMonthNr" : returnValue
    })
}
2 Likes