Help

Re: Creating new entries from a long text field

Solved
Jump to Solution
3504 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Hidro01
6 - Interface Innovator
6 - Interface Innovator

Continuing the discussion from Creating new row entries from a long text:

Since this thread was closed and I’m facing a similar problem I was wondering if someone could help out.

I have a Long text field in my table, lets call it “Task Submission”, that I would like to create entries in another table from each new line of text, as shown in the image below.

image

Is there a Way to script this to run manually or automatically. I have an enterprise plan.

I need this because i need users to submit new tasks that need to be followed in another table.

thanks

1 Solution

Accepted Solutions
Alexey_Gusev
12 - Earth
12 - Earth

Hi,
change table1,2 and pri2,lnk2 names

const table=base.getTable('totals1')
const table2=base.getTable('totals2')
const query=await table.selectRecordsAsync({fields:['Task Submission']})
const arr=text=>text? text.split('"').join('').split(`\n`):[]
const newrow=r=>arr(r.getCellValue('Task Submission')).map(ln=>({fields:{'pri2':ln,'lnk2':[{id:r.id}]}}))
const crt=query.records.flatMap(newrow)
while (crt.length) await table2.createRecordsAsync(crt.splice(0,50))

See Solution in Thread

14 Replies 14

Are the task names known? Is there a maximum amount of tasks that can be entered?

It’s definitely possible to have some javascript code parse over the text field and transcribe returns into a linked field - the tricky part will be knowing what triggers words and numbers within the text field to be a task, and what separates one task from the next.

Hidro01
6 - Interface Innovator
6 - Interface Innovator

The tasks are coming via a form submission, so they can be anything .
There is no Maximum but I don-t expect anything above 10.

Ideally, the script would be triggered via form submission or to run every day.

I’m sure this would be an easy task for someone with java script knowledge :slightly_smiling_face:

Hidro01
6 - Interface Innovator
6 - Interface Innovator

I think What I would like to do is similar to this:

Linking Existing Records Using Automations | Airtable Support

Except I would like to create the new records (tasks) from an existing field. So each task is unique for each project

Alexey_Gusev
12 - Earth
12 - Earth

Hi,
change table1,2 and pri2,lnk2 names

const table=base.getTable('totals1')
const table2=base.getTable('totals2')
const query=await table.selectRecordsAsync({fields:['Task Submission']})
const arr=text=>text? text.split('"').join('').split(`\n`):[]
const newrow=r=>arr(r.getCellValue('Task Submission')).map(ln=>({fields:{'pri2':ln,'lnk2':[{id:r.id}]}}))
const crt=query.records.flatMap(newrow)
while (crt.length) await table2.createRecordsAsync(crt.splice(0,50))
Hidro01
6 - Interface Innovator
6 - Interface Innovator

Many thanks I will try it. just to be sure I got it…

I am guessing

“totals1” is the originator table with the “Task Submission” field from which lines will be converted to entries in the “totals2” table?

So I guess one of the “pri2” or the “link2” is the field in the destination table “totals2”, right? So what is the other one?

Maybe you copied this from another example and i am just a bit confused…
Thanks for clarifying
Thanks

Hi,
nope, I’ve created it, just forget to rename tables. Pri and Lnk are primary and link fields of destination table.

Hidro01
6 - Interface Innovator
6 - Interface Innovator

Thanks
FYI I found a work around that requires the use of automation and regex formula field. I will post it later

Hey, can you post the workaround that you utilized? I have a similar problem to address.

Hidro01
6 - Interface Innovator
6 - Interface Innovator

Yes, let me post the complete workaround in a while. But I can tell you it involves the Regex formula and an Automation

Kenneth_Raghuna
7 - App Architect
7 - App Architect

I was actually able to figure it out using a script.

Hidro01
6 - Interface Innovator
6 - Interface Innovator

Here is a different approach to generating multiple entries in a new field  (this field is in a new table) from a single field.
Table setup:

Table 1

  • Primary Field
  • Entry_Field (Fieldtype Longtext)
  • Regex_Field (Formula = REGEX_REPLACE({Task Input},"\n",",")
  • New_Field (Linked field in table 2)

Hidro01_0-1671551749619.png

as you can see each new line in the "Entry" field will be a new entry on table 2

Table 2

  • Primary field
  •  Name (Linked field in table 1)

Hidro01_1-1671551777254.png

In this setup for each new entry in table 1 each new line in the "Entry_field" will generate a new entry in table 2 via the Regex formula fiedl and an automation.


The automation is set up as follows:

  • trigger: when a new record is created (or any other thing suitable to your implementation)
  • action:
    • Find records: based on the trigger find the new record
    • Update record: in table 1, create a list of entries fromt he regex field as seen bellow:

Hidro01_2-1671551926448.png

 

There are some requirements for this to work:

  • in the "Entry" field each new line in the entry field will generate an entry in the new linked field
  • you cannot use commas, unless you somehow include in the formula a way to remove them

Maybe its not so clear but but happy to give feedback

Feel free to share!

To come back to this if I want to just do this for a record identified in an automation step, I would need to invoke the input config

How would I include that in this script example?

Hidro01_0-1674224013190.png

Many Thanks

Here is the full script I ended up using (in case someone else needs it), put together by a few helpful examples across the community board.

 

 

let inputData = input.config();
let musicianData = inputData.musicianData;
let presenterData = inputData.presenterData;
let installationData = inputData.installationData;
let subRecordId = inputData.subRecordId;
let parseMP = "";


const mpTable = base.getTable("Musicians/Presenters");
const installTable = base.getTable("Installation Artists");

//checks to see which data we're using
if (presenterData) {
    parseMP = presenterData
}
else if (musicianData) {
    parseMP = musicianData
}
else {}

if(parseMP){
//split data in separate arrays/lines for each record
    console.log(`Processing musician/presenter data...`)
    let getRecordCount = parseMP.split("\n").length -1;
    for (let i = 0; i < getRecordCount; i++) {
        let array = parseMP.split("\n");
        let line = array[0+i];
        let [perfName, legName, email, startTime, endTime, fee] = line.split(',');

        console.log(`
            Performer: ${perfName}
            Legal Name: ${legName}
            Email: ${email}
            Start Time: ${startTime}
            End Time: ${endTime}
            Fee: ${fee}`)
        
        //create record
        let artistRecord = await mpTable.createRecordAsync({
            "Performance": perfName,
            "Legal Name": legName,
            "Email": email,
            "Start Time": startTime,
            "End Time": endTime,
            "text_Fee": fee,
            "Linked Submission": [{id:subRecordId}],
            "Registration Status": {name: "Unregistered"}
        })
        console.log(`Records created`)
    }
}

if(installationData){
    console.log(`Processing installation artist data... `)
    let getRecordCount = installationData.split("\n").length -1;
    for (let i = 0; i < getRecordCount; i++) {
        let array = installationData.split("\n");
        let line = array[0+i];
        let [installName, legName, email, fee] = line.split(',');

        console.log(`
            Install: ${installName}
            Legal Name: ${legName}
            Email: ${email}
            Fee: ${fee}`)
        
        //create record
        let artistRecord = await installTable.createRecordAsync({
            "Installation": installName,
            "Legal Name": legName,
            "Email": email,
            "text_Fee": fee,
            "Linked Submission": [{id:subRecordId}],
            "Registration Status": {name: "Unregistered"}
        })
        console.log(`Record created`)
    }
}

 

 

For context, this takes a booking submission for Musicians, Workshop Presenters, and Installation Artists from a Jotform submission, and turns it into records in the relevant tables. I can post screenshots of the database setup if anyone needs it.

 
@Hidro01:This piece is where I invoke my variables from input config.

 

let inputData = input.config();
let musicianData = inputData.musicianData;
let presenterData = inputData.presenterData;
let installationData = inputData.installationData;
let subRecordId = inputData.subRecordId;

 

 

In your example, I would change the variable name to selectedRecords (remove the space, use camel case if you want multiple words in one variable assignment).

Note that you can only call input.config() once. If you only have this one variable in your code, you can get away with doing something like

 

let selectedRecords = input.config().selectedRecords

 

but if you anticipate using more than one variable from earlier in the automation, you'll need to assign input.config() to it's own variable, then assign each new variable using that. This is what I did in my script, input.config() became inputData, and I brought each variable into the script by doing "xyz = inputData.xyz"

Please note: I am not that experienced with Javascript. I may not be using best practices in my script. If anyone else would like to chime in with any suggestions, edits, or conventions they'd like to share, by all means.