0

Delete Files added to paper clip

I have an archive that I want to BULK delete various images attached to records. I want to keep the records but no longer need the images that are attached to them.

Where the files have been in a custom Images in records. It has been easy to overwrite to null from a button script. 

ie 

let mySure := dialog("Sure?", "Are you Sure?", ["Yes", "No"]);
if mySure = "Yes" then
    for a in select JobDockets where Status = 4 or Status = 7 do
        a.(SupplierQuote := null);
        a.(SupplierConfirmation := null);
       alert("Records have been deleted from the system")
    end
else
    alert("Okay - Nothing has been deleted")
end

All images for the last year or so have been stored this way as it gives me control to delete them in bulk.

However my issues is that for the first few years files were attached the the paperclip as back then I did not realise that it would not be easy to delete them in bulk. There is bulk edit facility to get at the paperclip and the above script work for these. (of actually I have not found a way to access the paperclip). I have about 3500 images I want to delete over some 2000 records and this could take me forever by hand.

So I am hoping that someone has successfully produced a script for deleting paperclip attachments? If so, could you possibly consider sharing how you achieved this ? 

21 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 6 mths ago
    • Reported - view

    After the deletion of your image fields with Image := null. Did you have a look at the attachments tab?

      • Mel_Charles
      • 6 mths ago
      • Reported - view

       yes they are okay.

      there is an issue if you delete an image file on main forms to " " as a file then is created in attachments

      • Ninox partner
      • RoSoft_Steven.1
      • 6 mths ago
      • Reported - view

      I mean, if you use a script to empty the Imagefields (Imagefield := null), aren't the files not still present in the attachment (paperclip) tab? In my case they do... 🤔

    • Ninox partner
    • RoSoft_Steven.1
    • 6 mths ago
    • Reported - view

    File deletion in attachments tab can be done with the Ninox API 

      • Mel_Charles
      • 6 mths ago
      • Reported - view

       

      use the API is a little over my head

      can you explain how I would use it ?

      • Ninox partner
      • RoSoft_Steven.1
      • 6 mths ago
      • Reported - view

      Mel CharlesPutting a sample database here, only place a api-key in the settings table:

      You can make a iteration (for i in .... do) for all the records then...

      Delete attachments.ninox
      • Rafael Sanchis
      • Rafael_Sanchis
      • 6 mths ago
      • Reported - view

      RoSoft_Steven Hi Steven 

      let method := "DELETE";
let teamid := DatabaseTeam;
let dbid := DatabaseID;
let tid := TableID;
let cont := "application/json";
let bear := "Bearer " + first(select Settings).'API-Key';
for i in select Attachments do
    let file := urlEncode(text(Filename));
    let url := "https://api.ninox.com/v1/teams/{teamid}/databases/{dbid}/tables/{tid}/records/{rid}/files/{file}";
    let response := do as server
            http(method, url, {
                Authorization: bear,
                'Content-Type': cont
            }, {})
        end;
    if response.error then
        alert(text(response.error))
    end
end

      I try to use the for i in ... do  but don't work, maybe place on wrong site.

    • Rafael Sanchis
    • Rafael_Sanchis
    • 6 mths ago
    • Reported - view

    Works perfect. First time I use API for something.

    • Mel_Charles
    • 6 mths ago
    • Reported - view

     Thats great thank you, your sample makes sense to me.

    I have not touched anything relating to API before so very much learning this as of now.

    so i have keying into my live database your coding and setups the fields as per your example.

    Before I run this - I want to take several backups when other users are out.

    re your question about flushing other image fields to null - I was aware that it would populate back into the clipboard attachments . hence the reason the find a way to clear these. Interestingly in my few tests and a a dozen or so sample images - not all appear go back to the clipboards so i wander if some are left orphaned on Ninox server

    Your script will delete all attachments and that is fine for me as onece gone I will turn off the attachments as far as users are concerned and then any image files set to Null from a script will populate in the background clip and I can then run your script from time to time.

    what is "bearer " significance it appears to be text ?

    and is the api key specific to just each database - so if i restored a copy of database that database would have the same key in it. If I ran your script code in that copy would it impact on database A in error?

    whole new area for me to get to grips with...

      • Ninox partner
      • RoSoft_Steven.1
      • 6 mths ago
      • Reported - view

       

      Bearer is the type of authorization method Ninox is using. Other examples are API key, Basic Auth,OAuth 1.0, ....

      The Ninox API key is for your entire cloud subscription. This for all Databases and even all Teams (workspaces).

      Don't worry about mixing the databases as the team and the database, table,record and filename (of the first attachment) are "called" on the fly in the formula-fields below the button.

      Welcome to the world of API...👋

      • Mel_Charles
      • 6 mths ago
      • Reported - view

      Out of interest, is this script intended to delete all attachments to all records.

      as far as i can see it it deleting a single attachment

      ie if there are 3 shown on the paperclip then it requires 3 key presses to get rid of them all. doesn't seem to be looping.

      Ideally i would like to clear all of them globally across the given table.

      However still a lot quicker that me selecting each image in turn and then manually selecting delete.

      • Ninox partner
      • RoSoft_Steven.1
      • 6 mths ago
      • Reported - view

      Script is for the current record only.

      Following code is for the entire table and all the attachments in the paperclip-tab.

      let method := "DELETE";
let teamid := DatabaseTeam;
let dbid := DatabaseID;
let tid := TableID;
let cont := "application/json";
let bear := "Bearer " + first(select Settings).'API-Key';
for i in select Attachments do
    let rid := text(i);
    while cnt(files(i)) != 0 do
        let file := urlEncode(text(i.Filename));
        let url := ---
 https://api.ninox.com/v1/teams/{ teamid }/databases/{ dbid }/tables/{ tid }/records/{ rid }/files/{ file }
            ---;
        let response := do as server
                http(method, url, {
                    Authorization: bear,
                    'Content-Type': cont
                }, {})
            end;
        if response.error then
            alert(text(response.error))
        end
    end
end
    • Mel_Charles
    • 6 mths ago
    • Reported - view

    That is fantastic my friend !!!

    I can make great use of both the per record basis and global for the table - !!

    re the question of per record.

    just checking did you design it like this example

    There are 3 attachments on paperclip - I press the buttton and the first is deleted and the other 2 shuffle forward - I press the button again and the now new first file is deleted etc i do this untill all files are deleted for that one record!.

    Do you think it is possible to modify the script so at all attachments for that one records are deleted on the one button press? 

      • Ninox partner
      • RoSoft_Steven.1
      • 6 mths ago
      • Reported - view

       

      Sure, here's the code:

      let method := "DELETE";
let teamid := DatabaseTeam;
let dbid := DatabaseID;
let tid := TableID;
let cont := "application/json";
let bear := "Bearer " + first(select Settings).'API-Key';
let rid := text(this);
while cnt(files(this)) != 0 do
    let file := urlEncode(text(Filename));
    let url := ---
 https://api.ninox.com/v1/teams/{ teamid }/databases/{ dbid }/tables/{ tid }/records/{ rid }/files/{ file }
        ---;
    let response := do as server
            http(method, url, {
                Authorization: bear,
                'Content-Type': cont
            }, {})
        end;
    if response.error then
        alert(text(response.error))
    end
end
      • Mel_Charles
      • 6 mths ago
      • Reported - view

       Thats great thnk you. I have trieed the all 3 versions

      • Deleting ONE image at a time in ONE record - this script Works and files are deleted
      • Deleing ALL images in One record - Runs to completion but does nothing and no files are deleted and there is no error. ?
      • Deleting ALL images in ALL records - also runs but does nothing (simply ignored)

      I have tried this on a table with some 370 images on. The same table that test one works on.

      after running step 2 and 3 above the count of images is still 370 ?

      any ideas why it might run but do nothing? I ensured I setup all fields and info exactly as your test database

      • Mel_Charles
      • 6 mths ago
      • Reported - view

      RoSoft_Steven 

      Thank you

      I have tried all 3 version on a test table the contains 370 records with either 1 ,2 or 3 images (paperclip attachments) per record

      1. Delete ONE attachment image at a time In ONE record. Thsi script works as expected and for every button press one of the files are deleted until they are all gone. :-)

      2. Delete All attachment images at the same time in ONE record. This also runs to completion without error but appears be ignored - no images are deleted.

      3. Delete All attachment Images in ALL records. This also runs and again appears be ignored - no images are deleted.

      For both 2 and 3 the initial count was 370 and after running the count was 370. opening many records at random still show the attachments.

      I did ensure that I set this up exactly as per your test DB using same field/Formula names names.

      Test 1 was run on exactly the same table and worked?

      Any ideas as to why it would be ignored?

      • Ninox partner
      • RoSoft_Steven.1
      • 6 mths ago
      • Reported - view

      Weird.

      Did you try on my example database?

      In table view, the images are still there?  And when you refresh ⭮ the table?

      • Mel_Charles
      • 6 mths ago
      • Reported - view

        Okay so all of the scripts above were copy pasted into both your database and mine.

      I ran the version this morning (delete one file at a time) in both yours and mine and they both worked!

      Then making sure that a record had more that 1 image in it (thus I added an extra 2 into one for your records) I ran the delete ALL files in ONE record script

      This appear to delete just one image and then stop I pressed the button again and and this was ignored. then I ran the same in your DB and this was ignored (just sits there spinning circle).

      if i exit out then go back into your and try the delete ONE image in ONE record (known to be working properly) this again then sits there spinning (as if the server has gone on strike!)

      I am wondering if there is a memory server issue or something

      so I will leave this for a day to ensure the server cache etc is clear and do more tests

      • Mel_Charles
      • 6 mths ago
      • Reported - view

       I have had comms with support.

      They have stopped the server session and restarted it.

      I have now run all 3 scripts in YOUR database and they now work.

      I will try mine tonight o see how this fares! :-)

    • Mel_Charles
    • 6 mths ago
    • Reported - view

     I couldn't wait until tonight

    so in the copy of my live DB with the test table

    There we 370 with either 1, 2 or 3 or images in them. Total image count was around 580 - however the table records itself number some 9700 records with 9120 records with NO images in.

    Thus the report to clear all images in all records still has to check those records with 0 images in - in order to discount them. I thinks the call to do this either was hanging due to memory issue or making the server at Ninox end hang/fall over OR maybe was simply gonna take hours even at the server end! 

    Done further test in sofar as deleting all but some 300 records and all 3 scripts do indeed work in my play copy. !

    So I guess - I might need to look at running this on a batch of records at a time.

    I will have a go a compiling a header part of your script to select a batch of records in order say 400 at a time. I'm thinking of something along the lines of bolting something like this in.

    let a := (select JobDockets where 'Status = 7 );
    for myVar in slice(a, 0, 400) do

    but not thought it through yet ....

    • Mel_Charles
    • 6 mths ago
    • Reported - view

    RoSoft_Steven Just I case I didn't make it clear or forgot to say so. Steven you are a complete star! The solutions issued by you is breathtakingly superb! - This will make my life so much easier - Once I get around to sorting in batches but thats my problem.

    I have quite a bit of interaction with Ninox support over images and I will out my findings on a separate topic.

Login to reply

Content aside

  • 6 mths agoLast active
  • 21Replies
  • 212Views
  • 3 Following