Testing a due date from the Global Trigger after open

M

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 ?

 

M

Arrgh..... sorted it myself

being stupid- got this omplety wrong - Pleaase ignore me

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

M

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 ?

M

also tried

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

etc etc

but this is ignored completely ?

M

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

 

 

J

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

M

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

 

J

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

M

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 Profile

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

1 2
Reply