Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 28, 2020 06:48 AM
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
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
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:
Jul 28, 2020 09:26 AM
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.
Jul 28, 2020 12:04 PM
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?
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!
Jul 28, 2020 01:11 PM
Try this replacement
let filteredTable1 = table1Records.filter(x => {return linkedCommercialVersions.includes(x.id)})
Jul 28, 2020 02:03 PM
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.
Jul 28, 2020 03:01 PM
Try this:
let filteredTable1 = table1Records.filter(x => linkedCommercialVersions.includes(x.id))
Jul 28, 2020 04:26 PM
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
})
})
}
Jul 28, 2020 04:36 PM
Would you be comfortable sharing the base with the same structure and no data in it?
Jul 28, 2020 05:58 PM
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)
}