0

Testing a due date from the Global Trigger after open

I want to test for a table (jobDockets) for any occurance where the dispatch date (DispatchBy) is due within 2 days of today upon opening the database at the start of each day. Thus if I put the following into the "Trigger after open" in global options i would have expected it to work but it does nothing!

if (select JobDockets).DispatchBy = today() + 2 then
alert("WARNING - SOME PROMISED JOBS ARE FALLING DUE FOR DISPATCH")
end

also tried now() command etc...

Yet if i ignore the ref to the table etc (JobDockets) and do a simple test of the alert on its own

if today() then alert ("TEST") - this works

so I know the global does handle alerts

Any suggestions Please ?

16 replies

null
    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    Arrgh..... sorted it myself

    being stupid- got this omplety wrong - Pleaase ignore me

    I need .... select JobDockets where = DispatchBy = today() + 2 etc.... ;

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    Nope .. I'm making a pigs ear of this..

    the following is what I am trying to achieve ... in Global Trigger

    select JobDockets where DispatchBy = today() + 2;
    sendEmail({
    from: "mel@testemail.co.uk",
    to: "mel2@TestemailJobs.co.uk",
    subject: "Promised jobs are soon due for dispatch",
    text: "Promised",
    html: "REMINDER - Some promised jobs will soon be due be dispatched"
    })

     

    It works and sends the email if the 1st line is true, but still send sthe email even if it is false.

    I can see why cos send  is simply following on from the first line.

     

    However I have tried to wrap it all in an if then else but ninox is not playing ball

    how can i stop the email from firing if the select where is false ?

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    also tried

    if (select JobDockets).DispatchBy = today() + 2 then

    etc etc

    but this is ignored completely ?

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    Ninox changed the above script wher i had the brackets so should have read like this  

    if (select JobDockets.DispatchBy = today() + 2) then etc

    even so script is still ignored

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi Mel

     

    A select will always return an array, unless it is wrapped in an aggregate function, so your if statement would work better like this

     

    if cnt(select JobDockets where DispatchBy = today() + 2) > 0

    Regards John

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    Hi John

    Okay that makes sense thank you.

    I have tried your line but it still ignored thus i tge case of this test below no alert pops up

    if cnt(select JobDockets where DispatchBy = now()+ 2) > 0 then
    alert("Sending Email")
    end

    Bearing in mind I am setting this up in the global trigger..

    I can't see if any value is being picked up :-)

    Testing just the alert on its own works

     

    in essense as long as any jockdocket has a dispatch date that is 2 days away from today I want the alert to fire (using alert for testing but will actually finally uase the SendEmail to my dispatch guy)

    just can't get it to work as it either ignores the test and fites every time or as in tha cse of last test stops working

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi Mel

     

    Not sure why you swapped today() for now(). now()+2 does not add two days to it's value and comparing a date and a timestamp will always return false. Revert back to today() and you should start to get somewhere.

     

    Regards John

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    Hi John

    Sorry tested a variation and copied the wrong one to above.

    Thank you but even with your suggestion - I'm still stuck...- If i put this script onto a button directly on the form the alert fires (but it appears to ignoring the dispatch date as assumes everthing is true. so not good! i Can null date fields and it still fires!

    format(DispatchBy, "DDMMYY") = format(today() + 2, "DDMMYY");

    if cnt(DispatchBy = today() + 2) then
    alert("Sending Email")
    end

    also tried adding in line 1 re format to see if that helps but it don't

     

     Screenshot 2021-09-16 at 13.17.49

     

    However - I don't want this on a button on a form I want it to work from GLOBAL scripts on logging in

    Hence the need for the "select" to go find job Dockets table and gather the info

     

    if cnt(select JobDockets where DispatchBy = today() + 2) > 0 then
    alert("Sending Email")
    end

    But this is not doing anything - ie just refuses work? :-(

    All it has to do is check to see if ANY job has a dispatchby date due in 2 days time and if so alert (once working I will change alert to sendEmail.)

    • Ninox partner
    • RoSoft_Steven.1
    • 2 yrs ago
    • Reported - view

    Have you already tried to make a variable of the date first?:

    let d := today() + 2
    if cnt(select JobDockets where DispatchBy = d) > 0 then
    alert("Sending Email")
    end

    or with square brackets...

    if cnt(select JobDockets [DispatchBy = today() + 2]) > 0 then
    alert("Sending Email")
    end

    Steven

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi Mel

     

    After a while playing in the console I have found that date > today()  works and date < today() works but date = today() does not. I have also found that, as Steven has said, storing today() into a variable first does work. I have also tested this in a Trigger after open: script

     

    let a := today() + 1;
    let b := cnt(select Routines where 'Next Case' = a);
    if b > 0 then alert(b) end

     

    Regards John

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    Hi Steve

    Both of your suggested methods WORK !!!

    albeit the second seems slower to perform

    so going with the first option

     

    Thank you both John/Steven for the hlep on this:-)

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    Yes John I was finding that some time I could trigger and not others when testing greater than - less than and equals

    In the end i tried so many variations that i got tottally confused ! ha ha 

    I also could get it to trigger every time on a button on table form.

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    Okay - so ther is an obvious flaw in my logic

     

    in loggin in today (Monday) then the promised date check is 2 days awy (wednesday) great

    that is until I get to thursday or friday when it will try to check saturday and sunday (when we are closed)

    to make matters worse I woukd need to log in on the wekkend to check promise dates on Monday and Tues

    so all in all - I did not think this through very well!

    So i am guessing that somehow I need to build in some kind of test to trap the weekday or weekdayindex function

    which I think monday is 0 and Sunday is 6 so i have to test and omit day 5 & 6 out fof my logic

    am i on the right track or is there a better way to do this?

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    I can't seem to get global scripts to accept weekday without an error

    so had a thought and decided that what if I set the code out aas follows!

    let d := today() + 2;
    let e := today() + 3;
    let f := today() + 4;
    if cnt(select JobDockets where DispatchBy = d) > 0 then
    alert("Sending Email") or
    if cnt(select JobDockets where DispatchBy = e) > 0 then
    alert("Sending Email") or
    if cnt(select JobDockets where DispatchBy = f) > 0 then
    alert("Sending Email")
    end

    I'm sure there is a better way of scripting this but .... Ninox accepts this

    the when the script runs after loggin in.. if today is 21st it will look for the first occurance of the promisedby date of 25th (d) or 26th(e) or 27th(f)

    However - When I run the script it actually only finds the first if statement works the last two are ignored.

    if i make e first it works and same for f.

    I'm not sure how to tackle this now without help.

     

    for clarity i want to do one of either

     

    Option 1 - Login today and test promisedby date in 2 days time 

    when logging in on either on Thursday or Friday - skip the weekend (hence change variable d from 2 days to 3 or 4 days so it moved to Monday/Tues promisedby date.

     

    Option 2 - Login and test what day it is ie Monday test for promisedby date due on Wednesday (2 days ahead)

    However avoid weekend so thursday must test following Monday and Friday must test following tues

    in all cases the script olny need to find the first occurance of a promisedby date that is valid to trigger the alert message or a sendMail message

     

    err help please :-)

    • Ninox partner
    • RoSoft_Steven.1
    • 2 yrs ago
    • Reported - view

    Something like:

    let d := if weekday(today()) = 3 then
    today() + 4
    else
    if weekday(today()) = 4 then today() + 4 else today() + 2 end
    end;
    if cnt(select JobDockets where DispatchBy = d) > 0 then
    alert("Sending Email")
    end

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    Steven/John

    Applogies for late reply but I wanted to test logging in a few days to ensure that above skips the weekend to look for mon/tues promise dispatchby dates.

    WORKS PERFECTLY - Thank you!

    Really appreciated :-)

Content aside

  • 2 yrs agoLast active
  • 16Replies
  • 855Views