Creating blank records by copying formulated array from one table to another tables non-primrary field

carloschristian · ‎Jul 28, 2020

Hi, everyone -

I could seriously use some assistance with a script I’m trying to figure out. Please note, I always thought of myself as efficient with computes and I like to think I’m pretty good with Airtable formulas, but the scripting stuff is so above me it’s crazy… Any assistance would be greatly appreciated. Here’s what I’m trying to do.

Table 1

In table 1 I have a formulated field that auto populates with a bunch of comma separated text strings. Any given cell can look something like this.

Auto-populated Field

TEST_S1_12345_Commercial_15s_[Pre/Post]Master_RecordTest_16x9_XXXX_MV_en-US,TEST_S1_12345_Commercial_15s_Video2_RecordTest_16x9_XXXX_MV_en-US,TEST_S1_12345_Commercial_15s_EDL_RecordTest_XXXX_MV_en-US,TEST_S1_12345_Commercial_15s_Music_RecordTest_XXXX_MV_en-US,TEST_S1_12345_Commercial_15s_GraphicToolkit_RecordTest_XXXX_MV_en-US,TEST_S1_12345_Commercial_15s[Pre/Post]Wide_RecordTest_16x9_XXXX_MV_en-US,TEST_S1_12345_Commercial_15s[Pre/Post]Vertical_RecordTest_9x16_XXXX_MV_en-US,TEST_S1_12345_Commercial_15s[Pre/Post]OrganicSquare_RecordTest_1x1_XXXX_MV_en-US,TEST_S1_12345_Commercial_15s_SquareVideo2_RecordTest_1x1_XXXX_MV_en-US,TEST_S1_12345_Commercial_15s_DialogueList_RecordTest_XXXX_MV_en-US,TEST_S1_12345_Commercial_15s[Pre/Post]SRT_RecordTest_XXXX_MV_en-US,TEST_S1_12345_Commercial_15s[Pre/Post]Video_RecordTest_16x9_XXXX_MV_en-US,TEST_S1_12345_Commercial_15s[Pre/Post]PaidSquare_RecordTest_1x1_XXXX_MV_en-US,TEST_S1_12345_Commercial_15s_VerticalVideo2_RecordTest_9x16_XXXX_MV_en-US,TEST_S1_12345_Commercial_15s[Pre/Post]_SRT_RecordTest_XXXX_MV_en-US - [FF],TEST_S1_12345_Commercial_15s_CueSheet_RecordTest_XXXX_MV_en-US,TEST_S1_12345_Commercial_15s_FF_RecordTest_XXXX_MV_en-US.pdf, TEST_S1_12345_Commercial_15s_FF_RecordTest_XXXX_MV_en-US.zip

Table 1 Screenshot

Each line represents the same commercial, but in a different language. The strings I am looking to copy are the specific deliverables for each language. Records related to the same commercial are grouped together by the commercials generic name that is linked via a linked record in a Table 0.
There are 10 lines for this commercial the fields highlighted in red can include several comma separated strings. The en-US line can have 12 strings, the de-DE line can have 4, etc.
If each line has 5 strings, the result of this script would be 50 records in table 2.

Table 2

Preferably I would like to click a button in a custom block that asks for the commercial’s generic name per Table 0 and gathers all the strings in Table 1 associated with that generic name so that it can create new blank records in Table 2 by filling in a non-primary, single line text field. The result would look something like this:
If each line has 5 strings, the result of this script would be 50 records in table 2.

Is something like this possible? If so, any help would be greatly appreciated.

Also, if anyone is down to assist me with some general base design help please DM me. I created an overly complicated Base for work that I truly believe in and want to make work, but it’s not scalable and very messy :confused:

Kamille_Parks · ‎Jul 28, 2020

In Table 0 you could create a button field associated with a Scripting Block. Then you could have a script that does the following:

let table0 = base.getTable("Table 0")
let table1 = base.getTable("Table 1")
let table2 = base.getTable("Table 2")

let commercialGeneric = await input.recordAsync("Select a commercial", table0)

let linkedCommercialVersions = commercialGeneric.getCellValue("Name of field linking to Table1").map(x => x.id)

let table1Query = await table1.selectRecordsAsync()
let table1Records = table1Query.records

let filteredTable1 = linkedCommercialVersions.filter(x => {return table1Records.map(y=> y.id).includes(x)})

for (let version of filteredTable1) {
    let deliverables = version.getCellValueAsString("Name of auto-populated field").split(",")
    deliverables.map(x => {
        table2.createRecordsAsync({
            "From Table 1": x
        })
    })
}

Let me know if that works for you.

carloschristian · ‎Jul 28, 2020

Kamille, thank you so much for your quick response!. I was so excited to see that I had gotten a response back - yay!

I gave your code a shot and ran into an error that I’m hoping we can trouble shoot?

New Code

let table0 = base.getTable(“OV”)
let table1 = base.getTable(“Localization”)
let table2 = base.getTable(“Deliverables”)

let commercialGeneric = await input.recordAsync(“Select a commercial”, table0)

let linkedCommercialVersions = commercialGeneric.getCellValue(“PASTE - Localization Records”).map(x => x.id)

let table1Query = await table1.selectRecordsAsync()
let table1Records = table1Query.records

let filteredTable1 = linkedCommercialVersions.filter(x => {return table1Records.map(y=> y.id).includes(x)})

for (let version of filteredTable1) {
let deliverables = version.getCellValueAsObject(“COPY - Deliverables (Final)”).split(",")
deliverables.map(x => {
table2.createRecordsAsync({
“From Table 1”: x
})
})
}

ERROR

TypeError: version.getCellValueAsString is not a function
at main on line 15

Thanks, again!

Kamille_Parks · ‎Jul 28, 2020

Try this replacement

let filteredTable1 = table1Records.filter(x => {return linkedCommercialVersions.includes(x.id)})

carloschristian · ‎Jul 28, 2020

Thanks, Kamille -

I replaced this line in the v1 code:

let filteredTable1 = linkedCommercialVersions.filter(x => {return table1Records.map(y=> y.id).includes(x)})

with the following:

let filteredTable1 = table1Records.filter(x => {return linkedCommercialVersions.includes(x.id)})

And unfortunately, I am getting the exact same error. Again, appreciate the assistance with this. If there is anything else I can share please let me know.

Justin_Barrett · ‎Jul 28, 2020

Try this:

let filteredTable1 = table1Records.filter(x => linkedCommercialVersions.includes(x.id))

carloschristian · ‎Jul 28, 2020

Hi, Justin -

I tried the line of code you suggested and no-dice :confused: I’m getting the same exact error on line 15.

I’ve spent the last 30 minutes try to create a paired down version of the Base that I feel comfortable sharing, there’s just way too much and if I touch one thing it breaks another.

let table0 = base.getTable("OV")

let table1 = base.getTable("Localization")

let table2 = base.getTable("Deliverables")

let commercialGeneric = await input.recordAsync("Select a commercial", table0)

let linkedCommercialVersions = commercialGeneric.getCellValue("PASTE - Localization Records").map(x => x.id)

let table1Query = await table1.selectRecordsAsync()

let table1Records = table1Query.records

let filteredTable1 = table1Records.filter(x => linkedCommercialVersions.includes(x.id))

for (let version of filteredTable1) {

let deliverables = version.getCellValueAsString("COPY - Deliverables (Final)").split(",")

deliverables.map(x => {

table2.createRecordsAsync({

"From Table 1": x

})

})

}

Kamille_Parks · ‎Jul 28, 2020

Would you be comfortable sharing the base with the same structure and no data in it?

Kamille_Parks · ‎Jul 28, 2020

I just went ahead and made a rough approximation of how I’m assuming the base is set up. The script provided below works.

let table0 = base.getTable("Table0")
let table1 = base.getTable("Table1")
let table2 = base.getTable("Table2")

let commercialGeneric = await input.recordAsync("Select a commercial", table0)

let linkedCommercialVersions = commercialGeneric.getCellValue("Table1").map(x => x.id)

let table1Query = await table1.selectRecordsAsync()

let table1Records = table1Query.records

let filteredTable1 = table1Records.filter(x => linkedCommercialVersions.includes(x.id))

for (let version of filteredTable1) {
    let deliverables = version.getCellValueAsString("Deliverables String").split(",")
    let records = deliverables.map(x => ({
        fields: {
            "Table1": [{id: version.id}],
            "From Table1": x
        }
    }))
    table2.createRecordsAsync(records)
}