Skip to main content
Solved

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

K
Forum|alt.badge.img+2

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

Best answer by kaspar_bossers

awesomenist wrote:

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!')

 

View original
Did this topic help you find an answer to your question?

4 replies

kuovonne
Forum|alt.badge.img+27
  • kuovonne
  • Brainy
  • 6001 replies
  • June 1, 2021

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},
                        ]
                    }
                })
            }
K
Forum|alt.badge.img+2
kuovonne wrote:

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 =)

A
Forum|alt.badge.img
  • awesomenist
  • New Participant
  • 1 reply
  • December 19, 2022
kaspar_bossers wrote:

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 ✌️

K
Forum|alt.badge.img+2
  • kaspar_bossers
  • Author
  • New Participant
  • 2 replies
  • Answer
  • December 19, 2022
awesomenist wrote:

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!')

 

Reply


Most helpful members this week

Terms & Conditions