Use outcome in formula field in another formula field

[Onur]

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

[Onur]

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:

[Bill_French]

Onur:

none of these can be solved by “normal customers” at the moment.

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.

Onur:

So I assume, the way to do the lookup I described above, should be a valid workaround at the moment, right?

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

Onur:

Anyone any idea why this above does not work?

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

[Onur]

Bill.French:

output.inspect(rangeRecord.getCellValue(“MonthName”) === lookupValue);

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

=> a false statement all the time

[Bill_French]

Onur:

=> 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.

[Bill_French]

@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).

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 -

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

[Onur]

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.

[Bill_French]

Good to hear,

Onur:

Where & how would you add a fallback value (in case nothing matched)?

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

[Onur]

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! :slightly_smiling_face:

[Onur]

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?

[Bill_French · Accepted solution]

Onur:

Sry, but I can’t get the correct line to have this fallback line pasted in (I nearly tried them all).

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