Use outcome in formula field in another formula field

Onur · ‎May 29, 2020

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

Bill_French · ‎May 30, 2020

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

Here’s a new version that works:

/*

   ***********************************************************
   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
    })
}

See Solution in Thread

Justin_Barrett · ‎May 29, 2020

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?

Onur · ‎May 29, 2020

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?

Justin_Barrett · ‎May 29, 2020

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.

Onur · ‎May 30, 2020

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:

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:

Onur · ‎May 30, 2020

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;)

Bill_French · ‎May 30, 2020

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.

Justin_Barrett · ‎May 30, 2020

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.

Onur · ‎May 30, 2020

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)

Bill_French · ‎May 30, 2020

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. :winking_face:

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

Make it possible for formulas to exist as attributes of an actual (real) field.
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.
Build an optimized Script Block VLookup() that only fires when the field it is configured to populate is empty.