Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Trying to split records with multiple attachments in a field into a new table with records with single attachments

Topic Labels: Scripting extentions
Solved
Jump to Solution
1007 4
cancel
Showing results for 
Search instead for 
Did you mean: 

and failing, to be honest. Please take note that I have no idea what I’m doing and this script is pieced together from snippets and posts I found on the forum.
This is what i have until now. I’ve tried multiple variations but I haven’t been able to write to a new attachment field correctly.

//script settings
let settings = input.config({
    title: 'Create individual records from multiple attachments in a single attachment field',
    description: 'Creates 1 individual record in another table for each attachment in a multiple attachment field in the source table, and links the new records back to the source record.',
    items: [
        // Source table select
        input.config.table('tableSource', {
            label: 'Table with existing attachments'
        }),
        // Source table: Attachment field
        input.config.field('attachField', {
            parentTable: 'tableSource',
            label: 'Attachment field with multiple attachments to split',
        }),
        // Source table: Selected View
        input.config.view('selectedView', {
            label: 'View from the selected table',
            parentTable: 'tableSource',
        }),
        // Destination table select
        input.config.table('tableDest', {
            label: 'Table to create new records in'
        }),
        // Destination table: Name or title field
        input.config.field('destinationField', {
            parentTable: 'tableDest',
            label: 'Deliverable name or title field in destination table',
        }),
    ]
});
async function splitAttachments() {
	let { tableSource, attachField, selectedView, tableDest, destinationField } = settings;
    if (attachField.type !== 'multipleAttachments') {
        output.text(`"${attachField.name}" is not an attachment field. Run the script again with an attachment field.`);
        return;
    }
    // Loads the records and fields from the selections above
    let attachQuery = await selectedView.selectRecordsAsync();
    console.log('query:', attachQuery)
    let  attachRecords = attachQuery.records;
    console.log('records', attachRecords)

    // Loops through qualified records and create new records in target table
    for (let i=0; i<attachRecords.length; i++){
        let attachments = attachRecords[i].getCellValue(attachField);
        console.log('attachments:', attachments)
        if(attachments !== null){
            // Array for records with attachments and their info
            let attachmentInfo = []
            for(let l=0; l<attachments.length; l++){
                attachmentInfo.push({
                    id: attachments[l].id,
                    fields: {
                        [destinationField]: [
                            {url: attachments[l].url},
                        ]
                    }
                })
            }
             console.log('attachmentInfo', attachmentInfo)
             while (attachmentInfo.length > 0){
                    await tableDest.createRecordsAsync(attachmentInfo.slice(0,50));
                    attachmentInfo = attachmentInfo.slice(50)
                }
        }
    }
}
await splitAttachments();

I’ve gotten various errors ranging from needing to define fields to incorrectly defining fields. The error I get with this iteration is:

ERROR
P: Invalid record format. Please define field mappings using a `fields` key for each record definition object
    at splitAttachments on line 62
    at async main on line 68

Anybody know how to properly structure an attachment field array? Any help is appreciated!
Thanks

2 Solutions

Accepted Solutions

Welcome to the Airtable community!

I haven’t read your code in detail, but this section jumped out at me.

This looks like you are mixing the format for updating a record with the format for creating a new record, with the attachment id taking the place of a record ID. In your field mapping, you are also using the field object as the key, and you need to use either the field id or the field name.

I highly recommend you look over the scripting documentation for createRecordsAsync, and make sure your attachmentInfo variable matches the format in the example.

You probably want something more like:

for(let l=0; l<attachments.length; l++){
                attachmentInfo.push({
                    fields: {
                        [destinationField.name]: [
                            {url: attachments[l].url},
                        ]
                    }
                })
            }

See Solution in Thread

Hi Awesomenist, I never really got a better understanding of the documentation and I don't pretend to be a developer, but Idid manage to piece something together in the end that works for me! It's requires a new table in the same base with a couple of predefined fields but it does the trick in splitting out attachments to individual records and linking them back to the original table. See the script I used here:

//script settings
let settings = input.config({
    title: 'Create individual records from multiple attachments in a single attachment field',
    description: 'Creates 1 individual record in another table for each attachment in a multiple attachment field in the source table, and links the new records back to the source record.',
    items: [
        // Source table select
        input.config.table('tableSource', {
            label: 'Table with existing attachments'
        }),
        // Source table: Selected View
        input.config.view('selectedView', {
            label: 'View from the selected table',
            parentTable: 'tableSource',
        }),
        // Source table: Attachment field
        input.config.field('attachField', {
            parentTable: 'tableSource',
            label: 'Attachment field with multiple attachments to split into new table',
        }),
        // Destination table select
        input.config.table('tableDest', {
            label: 'Table to create new records in'
        }),
        // Destination table: Output field
        input.config.field('destinationField', {
            parentTable: 'tableDest',
            label: 'Attachment field in destination table',
         }),
        // Destination table: Linked record field (back to the Source table record)
        input.config.field('linkField', {
            parentTable: 'tableDest',
            label: 'Linked record field, links back to source table with the source record',
        }),
    ]
});
async function splitAttachments() {
	let { tableSource, attachField, selectedView, tableDest, destinationField, linkField } = settings;
    // Check selected fields
    if (attachField.type !== 'multipleAttachments') {
        output.text(`"${attachField.name}" is not an attachment field. Run the script again and select an attachment field.`);
        return;
    }
    if (linkField.type !== 'multipleRecordLinks') {
        output.text(`"${linkField.name}" is not an linked field. Run the script again and select a linked field.`);
        return;
    }
    // Loads the records and fields from the selections above
    let attachQuery = await selectedView.selectRecordsAsync();
    let  attachRecords = attachQuery.records;

    // Loops through qualified records and create new records in target table
    for (let i=0; i<attachRecords.length; i++){
        let attachments = attachRecords[i].getCellValue(attachField);
        if(attachments !== null){
            // Array for records with attachments and their info
            let attachmentInfo = []
            for(let l=0; l<attachments.length; l++){
                attachmentInfo.push({
                    fields: {
                        "filename": attachments[l].filename,
                        [destinationField.name]: [{url: attachments[l].url}],
                        [linkField.id]: [{id: attachRecords[i].id}]
                    }
                })
            }

             //Splits the action up in batches of 50
             while (attachmentInfo.length > 0){
                    await tableDest.createRecordsAsync(attachmentInfo.slice(0,50));
                    attachmentInfo = attachmentInfo.slice(50)
                }
        }
    }
}
await splitAttachments();
console.log('all done!')

 

See Solution in Thread

4 Replies 4

Welcome to the Airtable community!

I haven’t read your code in detail, but this section jumped out at me.

This looks like you are mixing the format for updating a record with the format for creating a new record, with the attachment id taking the place of a record ID. In your field mapping, you are also using the field object as the key, and you need to use either the field id or the field name.

I highly recommend you look over the scripting documentation for createRecordsAsync, and make sure your attachmentInfo variable matches the format in the example.

You probably want something more like:

for(let l=0; l<attachments.length; l++){
                attachmentInfo.push({
                    fields: {
                        [destinationField.name]: [
                            {url: attachments[l].url},
                        ]
                    }
                })
            }

Thank you so much for the input kuovonne! You are absolutely correct with your observation that i’ve mixed the update and create record formatting. Honestly I didn’t quite know how to interpret the syntax in the documentation. Your proposed solution works and makes it a lot clearer how these should be structured.
Cheers 😃

Hi @kaspar_bossers,

Did you find a solution? Maybe a code snippet you can share, so others can get around this problem as well.

All the best ✌🏼

Hi Awesomenist, I never really got a better understanding of the documentation and I don't pretend to be a developer, but Idid manage to piece something together in the end that works for me! It's requires a new table in the same base with a couple of predefined fields but it does the trick in splitting out attachments to individual records and linking them back to the original table. See the script I used here:

//script settings
let settings = input.config({
    title: 'Create individual records from multiple attachments in a single attachment field',
    description: 'Creates 1 individual record in another table for each attachment in a multiple attachment field in the source table, and links the new records back to the source record.',
    items: [
        // Source table select
        input.config.table('tableSource', {
            label: 'Table with existing attachments'
        }),
        // Source table: Selected View
        input.config.view('selectedView', {
            label: 'View from the selected table',
            parentTable: 'tableSource',
        }),
        // Source table: Attachment field
        input.config.field('attachField', {
            parentTable: 'tableSource',
            label: 'Attachment field with multiple attachments to split into new table',
        }),
        // Destination table select
        input.config.table('tableDest', {
            label: 'Table to create new records in'
        }),
        // Destination table: Output field
        input.config.field('destinationField', {
            parentTable: 'tableDest',
            label: 'Attachment field in destination table',
         }),
        // Destination table: Linked record field (back to the Source table record)
        input.config.field('linkField', {
            parentTable: 'tableDest',
            label: 'Linked record field, links back to source table with the source record',
        }),
    ]
});
async function splitAttachments() {
	let { tableSource, attachField, selectedView, tableDest, destinationField, linkField } = settings;
    // Check selected fields
    if (attachField.type !== 'multipleAttachments') {
        output.text(`"${attachField.name}" is not an attachment field. Run the script again and select an attachment field.`);
        return;
    }
    if (linkField.type !== 'multipleRecordLinks') {
        output.text(`"${linkField.name}" is not an linked field. Run the script again and select a linked field.`);
        return;
    }
    // Loads the records and fields from the selections above
    let attachQuery = await selectedView.selectRecordsAsync();
    let  attachRecords = attachQuery.records;

    // Loops through qualified records and create new records in target table
    for (let i=0; i<attachRecords.length; i++){
        let attachments = attachRecords[i].getCellValue(attachField);
        if(attachments !== null){
            // Array for records with attachments and their info
            let attachmentInfo = []
            for(let l=0; l<attachments.length; l++){
                attachmentInfo.push({
                    fields: {
                        "filename": attachments[l].filename,
                        [destinationField.name]: [{url: attachments[l].url}],
                        [linkField.id]: [{id: attachRecords[i].id}]
                    }
                })
            }

             //Splits the action up in batches of 50
             while (attachmentInfo.length > 0){
                    await tableDest.createRecordsAsync(attachmentInfo.slice(0,50));
                    attachmentInfo = attachmentInfo.slice(50)
                }
        }
    }
}
await splitAttachments();
console.log('all done!')