0

Purging Job History

I want to delete all main JobDockets records and its child tables) if these exists before a given date (6 years approx)

based on a date keyed into a delete for this date fieild (DeleteDate!)

I have the following which does ask the qestion are you sure you want to delete YES/NO - This works but it bypasses

the condition that tests to see if the date is less that 6 years ie <= today() - 2190 and gos straight onto the deletion

the actual deletion part isself is doing its job re date input and works

Where have i gone wrong?

 

I have no systax error

 

let myDel := dialog("Delete Multiple jobs from History File", "Delete Multiple Jobs from the History File - Note Once Deleted They are Really Gone!?", ["Yes", "No"]);
let myDate := 'DeleteDate!';
if myDel = "Yes" then
if myDate <= today() - 2190 then
alert("Records cannot be deleted less than 6 years for HMRC compliancy");
'DeleteDate!' := null
else
delete (select PurchaseOrders where 'Order Date' < myDate);
delete (select DeliveryNotes where 'Delivery Date' < myDate);
delete (select JobDockets where 'Order Date' < myDate);
'DeleteDate!' := null;
alert("Records have been deleted from the system")
end
else
alert("Okay - Nothing has been deleted")
end

5 replies

null
    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi Mel

     

    I can't see anything wrong but maybe swap the code round a bit

     

    let myDate := 'DeleteDate!';
    if myDate <= today() - 2190 then
       alert("Records cannot be deleted less than 6 years for HMRC compliancy");
       'DeleteDate!' := null
    else
       let myDel := dialog("Delete Multiple jobs from History File", "Delete Multiple Jobs from the History File - Note Once Deleted They are Really Gone!?", ["Yes", "No"]);
       if myDel = "Yes" then
          delete (select PurchaseOrders where 'Order Date' < myDate);
          delete (select DeliveryNotes where 'Delivery Date' < myDate);
          delete (select JobDockets where 'Order Date' < myDate);
          'DeleteDate!' := null;
          alert("Records have been deleted from the system")

    else
          alert("Okay - Nothing has been deleted")
       end
    end

     

    Regards John

    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    Maybe: if myDate > today() - 2190 then

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    Hi John

    Tried your version and got the same result

    Alaine - yes it was the date syntax that was being ignored

    all working now !!!

    But  in thinking about it I don't need really an input date as i can just make the button bypass all the use test date and alert function and sinply have dleete anything over 6 years before todayas date.

    Ideally I would like to do this from using the 1st of April as the start test date for each year. ie so if i was in say May/June etc- then the button press would work out that I need to delete 6 years from April first just passed - But I don't know how to do this

    anyway here is the code that works (with the date input)

    let myDate := 'DeleteDate!';
    if myDate = null then
    alert("No Date Entered");
    'DeleteDate!' := null
    else
    if myDate > today() - 30 then
    alert("Records cannot be deleted less than 6 years for HMRC compliancy");
    'DeleteDate!' := null
    else
    let myDel := dialog("Delete Multiple jobs from History File", "Delete Multiple Jobs from the History File - Note Once Deleted They are Really Gone!?", ["Yes", "No"]);
    if myDel = "Yes" then
    let mySure := dialog("Sure?", "Are you Sure?", ["Yes", "No"]);
    if mySure = "Yes" then
    delete (select PurchaseOrders where 'Order Date' < myDate);
    delete (select DeliveryNotes where 'Delivery Date' < myDate);
    delete (select JobDockets where 'Order Date' < myDate);
    'DeleteDate!' := null;
    alert("Records have been deleted from the system")
    else
    alert("Okay - Nothing has been deleted")
    end
    end
    end
    end

    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    After getting your wife back, are you still looking for a way to automatically delete records that are more than 6 years old, with a pivot date set at April, first?

    You can compute "myDate" with the formula below, and then use it for the cleaning procedure you already have:

    let myDate := date(year(today()) - 6 - number(month(today()) <= 3), 4, 1);

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    Hi Alain

    Yes thanks! - I did realise afterwards that I could set the script to run from user log in and either purge the oldest day or go from my fixed point of April 1 and delete in a batch.so effectively I could have my cake and eat it.

    Managed to do a script but it was clunky - your versions is more refined! ha ha - one day I might yet code som decent script!

     

    P.S The wife has never left me - why would she - she's my best friend! and has been for forty years :-)

    I'm just bemused by the invasion by the spammers who find it so easily to constantly invade this forum! and desoite the endless promissed of Nixox to do something they in my opinion - don't !

Login to reply

Content aside

  • 2 yrs agoLast active
  • 5Replies
  • 432Views