Help

Re: Upload files in correct sequence to attachment

Solved
Jump to Solution
2571 0
cancel
Showing results for 
Search instead for 
Did you mean: 
John-Paul_Kerno
7 - App Architect
7 - App Architect

I am trying to upload several jpgs to one record. I use ‘Gallery’ and drop files in bulk to the plus sign bottom right. They are pages from a book and therefore in a sequence. The files are numbered as jpgs in a numerical sequence.

Why can I not have the viewed output remain in the correct sequence?
Why is the output always reversed with the last item first?
How can I fix this?

Many thanks if anybody can help!

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

And here’s the script that will sort the attachments in alphabetical order for all records in a view.

Note: Make sure that all uploads are done before running the script or you could get unexpected results.


let table = await input.tableAsync("Select a table");
let view = await input.viewAsync("Select a view", table);
let field = await input.fieldAsync("Pick an attachment field", table);

let queryResult = await view.selectRecordsAsync();
let records = queryResult.records;

for (let record of records) {
    let attachmentData = record.getCellValue(field.name);
    if (attachmentData && attachmentData.length > 1) {
        let newAttachmentData = sortArray(attachmentData);
        await table.updateRecordAsync(record.id, {
            [field.name]: newAttachmentData,
        });
    }
}


function sortArray (attachmentArray) {
    return attachmentArray.sort((a,b) => {
        return a.filename.localeCompare(b.filename);
    })
}


If this answers your question, please mark this post as the solution. Otherwise, could you please give a bit more details and a screen capture?

See Solution in Thread

40 Replies 40

Okay, let’s unpack this carefully.

I’m not aware of any sort order that Airtable claims or guarantees with respect to collections of objects as they are uploaded and unlike many other apps (like Google Drive), uploads are processed from bottom to top. Ideally, they should process in a first-in-first-uploaded method.

If you batch the upload collections from bottom-to-top, does this cause them to be uploaded in the order you expect?

Unfortunately it seems to be pretty random. Some come out in sequence and many don’t but it’s an awful chore having to rearrange manually especially as the Airtable window will not resize in order to be able to see more than a few attachments in one go. There seems to be no rhyme or reason in the sequence when batch uploaded. I think there ought to be.

I am talking about loading many attachments in one record for clarity.

Here’s info about attachment ordering that may shed some light on the situation:

I don’t know for certain, but I’ll bet the order is influenced by the size (indirectly). Here’s my hypothesis -

  • You drop a big batch of files into the funnel and the process begins from the top or bottom (which is unclear but really doesn’t matter), and as each URL in the collection is requested by Airtable, they finish uploading at seemingly random intervals, but they’re not random at all - they’re in order of upload completion time and small files will be faster than larger ones. Whatever the case, there will be subtle differences in upload completion.
  • I also think there could be another seeming randomization that’s occurring nested within the first point of my hypothesis; the upload process probably has a limit to the number of simultaneous web-workers that are allowed to perform uploads. Imagine it is set to say - 5 - this would cause an additional flow of confusing order logic because a set of 15 documents would start uploading the first (or last) 5 and then as soon as the first one finished, the sixth would get it’s own web worker thread, etc - rinse-repeat. You might imagine the randomization this could cause over a batch of 15 files.

This would not help @John-Paul_Kernot for the reasons noted; he’s getting a seeming randomized ordering and no amount of reversal logic could overcome this batch upload behaviour.

Remedy

One way to avoid this is to upload everything into a temporary staged field, and then use the Script Block feature to read the attachments and use the file names to rebuild the order in a final field. I don’t like this much because it requires two fields.

A better workaround is to create an API process that uploads with a pace suitable for discrete ordering. Something like a blocking javascript (server-side) system that uses Google Drive as the dropzone would be able to upload with precision and in any order prescribed by the script logic.

You should be able to use Scripting block to re-arrange the attachments without using a second field. The script can simply re-arrange the items in the attachment array based on filename, then write the new array back to the same field.

I thought about this approach but dismissed it on the fear that Airtable itself might assume the same URLs it initially created would influence how the order would be reestablished, perhaps not even making new image copies and leaving no change whatsoever. I was also unsure if Airtable would accept a URL from its own CDN as a new attachment value. If that proves to be an issue, then the customer would be required to store the original URLs for each image, and that seems like a bad idea as well.

And script block would need to process the re-copying with asynchronous blocking, and I have a hunch even that would not be enough breathing room for the upload process of one document to fully complete before moving on to the next. Just because the await has completed, it doesn’t mean the upload and copy steps have completed - it only means that Airtable has received the request and satisfying all aspects of the upload process are yet other asynchronous processes that cannot be controlled from the Script Block.

One thing is certain about this approach - there’s a fair bit of uncertainty about how Airtable will respond to the idea of the snake eating its own tale.

Lastly, updating the array in the field will likely trigger new upload/copies, right? Ergo, the randomization happens again.

Good hypotheses. But, if I upload different files sizes (say only 4 images), the order is not equivalent to those sizes. Furthermore, uploading in small batches seems to have no effect either. Hmm…

John-Paul_Kerno
7 - App Architect
7 - App Architect

I can obviously order the files if one attachment is in one record but not with multi attachments in one record.

Well, we don’t know [exactly] how many threads are allowed either. Imagine it is two, or three, and further imagine that the first thread gets an HTTP route that traverses servers and routers that are slightly slower than the others of the same size. It’s a cesspool of seemingly random outcomes, and they might was well be randomly ordered if any of these ideas are actual truths.

Have you confirmed that one file at a time sustains the order you expect?

If your new array contains only the url of the attachment, then there is a new upload. However, if you keep the full object information for the existing attachment (which includes far more info than just the url), there is no new upload.

This little script will sort attachments for a single record in alphabetical order by filename. It could be easily adapted to work on all the records in a table/view. Note that this is a strictly alphabetical sort so a file named “10.jpg” would sort before “2.jpg”. A numeric sort based on filenames, would depend on the exact file name structure.


let table = await input.tableAsync("Select a table");
let field = await input.fieldAsync("Pick an attachment field", table);
let record = await input.recordAsync("Pick a record with attachments", table)
let recordId = record.id;

let attachmentData = record.getCellValue(field.name);
output.inspect(attachmentData);

if (attachmentData && attachmentData.length > 1) {
    let newAttachmentData = sortArray(attachmentData);
    await table.updateRecordAsync(recordId, {
        [field.name]: newAttachmentData,
    });
    let queryResult = await table.selectRecordsAsync();
    let updatedRecord = queryResult.getRecord(recordId);
    let updatedAttachmentData = updatedRecord.getCellValue(field.name);
    output.inspect(updatedAttachmentData);
}

function sortArray (attachmentArray) {
    return attachmentArray.sort((a,b) => {
        return a.filename.localeCompare(b.filename);
    })
}



kuovonne
18 - Pluto
18 - Pluto

And here’s the script that will sort the attachments in alphabetical order for all records in a view.

Note: Make sure that all uploads are done before running the script or you could get unexpected results.


let table = await input.tableAsync("Select a table");
let view = await input.viewAsync("Select a view", table);
let field = await input.fieldAsync("Pick an attachment field", table);

let queryResult = await view.selectRecordsAsync();
let records = queryResult.records;

for (let record of records) {
    let attachmentData = record.getCellValue(field.name);
    if (attachmentData && attachmentData.length > 1) {
        let newAttachmentData = sortArray(attachmentData);
        await table.updateRecordAsync(record.id, {
            [field.name]: newAttachmentData,
        });
    }
}


function sortArray (attachmentArray) {
    return attachmentArray.sort((a,b) => {
        return a.filename.localeCompare(b.filename);
    })
}


If this answers your question, please mark this post as the solution. Otherwise, could you please give a bit more details and a screen capture?

One at a time definitely does.

This is amazing … but frightens me as I don’t know how to run it! Sorry … any help appreciated.

@John-Paul_Kernot The script that I wrote goes into a Scripting Block.
You need to install Scripting Block, and then paste in the code into the Scripting Block editor. Then press the Run button.

I always recommend testing out a new script on a copy of your base.

Awesome!

I tried this out buy it won’t run. I tried the first script “sort attachments for a single record in alphabetical order by filename”.
I created a new base
I created a new gallery
I uploaded 51 attachments to one field all sequenced from “IMG-0214-APER-SM.JPG”, “IMG-0215-APER-SM” … etc
I ran the script.

There is a code error I can’t fix. Something to do with the Field “attachment”.

FYI- I notice that the filed is automatically created by Airtable as “Attachments” but I have tried that to no success. Looking at renaming the field in the Airtable rename field dropdown I notice that although it is called “Attachments” in the table header, it is referred to there as “Attachment”.

What to do?

Screen Shot 2020-06-17 at 12.11.07

Correct - the script is apparently looking for field named “attachment” (field names are case sensitive). Rename the “Attachment” field to “attachment” and run the script again.