Skip to main content

Hey all,


I’ve tested a simple script that takes two Rich Text fields and copies them into a third field, and excitingly the Markdown format keeps as I’d hoped. :partying_face:


My next step is to now have two tables in a base, a primary table that has field lookups to the secondary table. And then any lookups added to the primary table record - to have their single Rich Text fields (in the Secondary Table) combined into the Primary table Rich Text Field. I’m basically substituting the Airtable RollUp formula due to it lacking support for Rich Text, as although the Rollup works unfortunately it drops all formatting (and I’ve since written a support request to address this).


Here’s my work so far, keep in mind I’m still learning JS so this is all new to me. I understand some of the basics, but am struggling to understand the syntax of how to place the Lookup Name values into a For Loop, and then assign each of their Rich Text contents to a variable that I then combine into the Primary Table record field, hopefully I’ve explained well enough for people to understand.


I’m assuming I need a loop in a loop, but… just can’t get the syntax correctly understood.


let primary_table = base.getTable('Markdown Test');

let primary_view = primary_table.getView('Grid view');

let primary_query = await primary_view.selectRecordsAsync();



let secondary_table = base.getTable('Markdown Apps');

let secondary_view = secondary_table.getView('Grid view');

let secondary_query = await secondary_view.selectRecordsAsync();



console.log(primary_query)



for (let my_record of primary_query.records) {



    let _name = my_record.getCellValue("Name");

    let _notes_a = my_record.getCellValue("Name") ? my_record.getCellValue("Notes_A") : ''

    let _notes_b = my_record.getCellValue("Name") ? my_record.getCellValue("Notes_B") : ''



  //  console.log(String(primary_query.records.length) + ' records found.');

  //  console.log(_name)

  //  console.log(_notes_a)



     primary_table.updateRecordAsync(my_record, {

            "A_and_B": _notes_a + _notes_b

        })        



    for (let _record of primary_query.records) {

        let _lookup = _record.getCellValue("Name") ? _record.getCellValue("Lookup") : ''

        console.log(_lookup);



        for (let _record of secondary_query.records) {

            let _name = _record.getCellValue("App_Name") ? _record.getCellValue("App_Name") : ''

            let _notes = _record.getCellValue("App_Name") ? _record.getCellValue("App_Notes") : ''

             console.log(_notes);

        }

    }

}



I’ll spend the next couple of hours reading through Airtable script examples, as I’m sure this has probably been done a million times already - appreciate any help or direction that anyone can give.

Your code has multiple nested loops, and it isn’t clear what you are trying to accomplish with them since their only output is logging to the console. From my understanding of the problem you would not need any nested loops.


It isn’t clear why you are using the value of one field to determine if you are going to use the value of a different field versus an empty string.


Also keep in mind that lookup fields are the most tricky to deal with. The read value of lookup fields is not well documented—it is simply an array. This means that you cannot count on the structure being stable.

Hi,

great explanation and respect for your attempts.

i’d like to clarify some details.




Rich text in formulas, lookups and rollups


Formulas, lookups, and rollups of long text fields with rich text enabled will use the field’s plain text plus the characters automatically added for lists. We do not currently support rich text formatting in formula, lookup, and rollup field.




  • if you consider updating muptiple records, you should use updateRecordsAsync, not updateRecordAsync, otherwise script stops after 15-20 update actions in a loop. updateRecordsAsync limited by size (max 50 records).


So, you can’t use your method, because lookup just won’t return formatting.

Instead, you may create your own ‘lookup function’:

Get cell value from a link field (which you called ‘lookup’, and it’s a kind of a little misunderstanding from your side. “Lookup” is another field, which rely on “link field” settings and contetns. And “Rollup” is like advanced version of “Lookup”)


Cell value of link field is array of objects {id:“rec…” , name:“text in link”}

You should get each array element, read id, find record by id in second table, read value 1, value 2 and add them.

That must be done for each element, and then the total of all must be written to

“Rollup” field


i would also notice that

(x)? x : ’ ’

(y)? y : ’ ’

x+y

may be changed to (x&&y)? x+y : x||y||’ ’

In your case test is not mandatory as empty text field returns empty string. of course, they may be needed for code reuse


i’m totally agree that you may not need so many loops depending on your goal. updading result according to update linked field value usually made by automation

(step1= trigger by record update “linked field” or “text fields”) - step 2 (gather additional data) - step3 (update result field)


about programming,

started from very simple programming background (basic, pascal, some t-sql), i understood that using ‘array helpers’ is cruical for effective working with airtables data. map, filter, using functions, converting object<>arrays, that’s it. Also, “Set” and “Map” data types for their specific tasks.


i prefer using const for table and fields for better readable code. some of my syntax may be considered as not recommended to use in real development, but i just want to show data flow logic.

your task can be done in such way (with correct const variables):



const PRI_TABLE = base.getTable(“Test”);

const LINK = PRI_TABLE.getField(‘link’);

const SOURCE=(‘Name’,‘Notes’];

const TARGET=‘rollup’;


const SEC_TABLE=base.getTable(LINK.options?.linkedTableId);

let primary_query = await PRI_TABLE.selectRecordsAsync({fields:(LINK]});

let sec_query = await SEC_TABLE.selectRecordsAsync({fields:SOURCE});


function addText(cell){return SOURCE.map(fld=>cell.getCellValue(fld)).join()};

function getText(rec){return rec.getCellValue(LINK).map(r=>

sec_query.getRecord(r.id)).map(addText).join()};


function setValue(rec){return {‘id’:rec.id,‘fields’:{eTARGET]:getText(rec)}}};

let updates=primary_query.records.map(setValue);

while (updates.length) await PRI_TABLE.updateRecordsAsync(updates.splice(0, 50));


Thanks for having a look @kuovonne - my code is more or less just me learning/debugging and I was reserved about posting it cause of its gaping holes, but decided it was needed for the context of my ability (at time of writing). The pictures are probably best to go off, and my ultimate goal being that I’m just trying to create a manual script “Rollup” that will push Rich Text Lookup fields into a summary. Rollup will do this with ordinary text but unfortunately it doesn’t support Rich Text markdown formatting - and I figured this is the perfect opportunity for me to learn JavaScript in depth.


@Alexey_Gusev - parsing the lookup array is exactly what I’m needing to do, and then pull the Rich Text field for each of those array entries, concatenating them into a main Rich Text field. The things you’ve pointed out are invaluable to know, thanks! I’ve just signed up to JS training at W3Schools and figure I’ll get JS basics fully understood, and then I’ll come back here once I have a working prototype.


It’s frustrating cause I can see everything working in my head (and have solved more-or-less identical problems in other languages that I’ve coded in over the years), but I know that I’ll get there. I will hopefully report back in a week or two with an update.

Thanks for having a look @kuovonne - my code is more or less just me learning/debugging and I was reserved about posting it cause of its gaping holes, but decided it was needed for the context of my ability (at time of writing). The pictures are probably best to go off, and my ultimate goal being that I’m just trying to create a manual script “Rollup” that will push Rich Text Lookup fields into a summary. Rollup will do this with ordinary text but unfortunately it doesn’t support Rich Text markdown formatting - and I figured this is the perfect opportunity for me to learn JavaScript in depth.


@Alexey_Gusev - parsing the lookup array is exactly what I’m needing to do, and then pull the Rich Text field for each of those array entries, concatenating them into a main Rich Text field. The things you’ve pointed out are invaluable to know, thanks! I’ve just signed up to JS training at W3Schools and figure I’ll get JS basics fully understood, and then I’ll come back here once I have a working prototype.


It’s frustrating cause I can see everything working in my head (and have solved more-or-less identical problems in other languages that I’ve coded in over the years), but I know that I’ll get there. I will hopefully report back in a week or two with an update.


Congrats on working on your coding journey! Don’t worry about the holes in your code, as long as you know that they exist. Just keep in mind that sometimes it is difficult to troubleshoot a specific issue in code when there are multiple issues going on. After you get some more JavaScript concepts under your belt and a better understanding of the Airtable API, things will be easier.


I recently wrote a script for a client that does what you want (creates a mock rollup of rich text fields, along with a lot of other things). Because the read format of lookup fields is not documented (and thus subject to change) I choose to identify the linked records through the original linked record field and then get the actual cell values from the linked table. This way, if Airtable decides to change the read format of a lookup field, everything will still work.

Thanks for having a look @kuovonne - my code is more or less just me learning/debugging and I was reserved about posting it cause of its gaping holes, but decided it was needed for the context of my ability (at time of writing). The pictures are probably best to go off, and my ultimate goal being that I’m just trying to create a manual script “Rollup” that will push Rich Text Lookup fields into a summary. Rollup will do this with ordinary text but unfortunately it doesn’t support Rich Text markdown formatting - and I figured this is the perfect opportunity for me to learn JavaScript in depth.


@Alexey_Gusev - parsing the lookup array is exactly what I’m needing to do, and then pull the Rich Text field for each of those array entries, concatenating them into a main Rich Text field. The things you’ve pointed out are invaluable to know, thanks! I’ve just signed up to JS training at W3Schools and figure I’ll get JS basics fully understood, and then I’ll come back here once I have a working prototype.


It’s frustrating cause I can see everything working in my head (and have solved more-or-less identical problems in other languages that I’ve coded in over the years), but I know that I’ll get there. I will hopefully report back in a week or two with an update.


Yes, yhat’s exactly what’s the script doing.




You can also apply filtering setting .filter() before each join, or substitute .join by you own function


I would recommend to experiment for better understanding, and that’s also good for debug


Reply


Terms & Conditions