Conditional colours

This forum is Read Only . If you feel a thread should be added to the FAQ please contact a moderator

Conditional colours

Postby Roj » Thu Aug 12, 2010 2:12 pm

Hu There,
I have a form that lists all company vehicles and the date of their mot and inspection dates, I can get the fill colour to change from white to red if the mot has expired : -

if Current date >= NEXTMOTDUE then
Fill.Color.Red := 255 .
Fill.Color.Green := 0 .
Fill.Color.Blue := 0 .
else
Fill.Color.Red := 255 .
Fill.Color.Blue := 255 .
Fill.Color.Green :=255 .
end

I would like to put in another condition that makes the fill amber if the mot due date is within 14 days of the current date. I tried this :-

if Current date >= NEXTMOTDUE then
Fill.Color.Red := 255 .
Fill.Color.Green := 0 .
Fill.Color.Blue := 0 .
if Current date between NEXTMOTDUE -14 to NEXTMOTDUE then
Fill.Color.Red := 237 .
Fill.Color.Green := 135 .
Fill.Color.Blue := 33 .
else
Fill.Color.Red := 255 .
Fill.Color.Blue := 255 .
Fill.Color.Green :=255 .
end
end

it didn't work, so obviously i am missing something!
Can anyone help me, please?
Roj.
Illegitemi non Carborundum.
Roj
 
Posts: 42
Joined: Wed Jul 30, 2008 2:44 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Conditional colours

Postby Phil Winkler » Thu Aug 12, 2010 2:45 pm

Hey, Roj,

Not positive, but since the between operator is inclusive of the two dates you have the same date in two conditional statements. Try this, instead:

if Current date between (NEXTMOTDUE -14) to (NEXTMOTDUE - 1) then
Fill.Color.Red := 237 .
Fill.Color.Green := 135 .
Fill.Color.Blue := 33 .
else
Phil Winkler
PLM Consulting, Inc.
pwinkler@plmconsulting.com
Phil Winkler
 
Posts: 889
Joined: Fri Sep 07, 2007 12:45 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Conditional colours

Postby Roj » Thu Aug 12, 2010 3:24 pm

Hi Phil,
Thanks mate, that worked a treat!
One other question, is it possible to send an e-mail depending on the condition of a field? say if the mot test was overdue, an e-mail would be sent to the person responsible for that vehicle telling them that the vehicle could not be used untill it has been tested.
Roj.
Illegitemi non Carborundum.
Roj
 
Posts: 42
Joined: Wed Jul 30, 2008 2:44 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Conditional colours

Postby Adrian Jones » Thu Aug 12, 2010 4:56 pm

What you need to determine first is what event is going to trigger the sending of such emails.

By which I mean, if you simply don't access your database, there is nothing within Ffenics itself that will say -- hey, this guy's MOT is due.

But you could create a 'robot' user, have a windows scheduled task run a set of routines that do things like check who is overdue and send out emails. This could run at the start or end of each day.

Then the question is: how do you email from Ffenics. There are many options for that -- I suggest you try searching this forum for them! (My preferred option is to get Ff to generate a VBscript and then execute that.)
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: Conditional colours

Postby Phil Winkler » Thu Aug 12, 2010 5:42 pm

Adrian is spot on with his advice to run a daily procedure to generate the notifications. We have a large Dispatching app with Driver licensing, truck inspections, certifications, etc., etc. that all expire on a certain date. Therefore, a procedure to detect these and provide notifications (these vary with each type of event) only need to run once each day.
Phil Winkler
PLM Consulting, Inc.
pwinkler@plmconsulting.com
Phil Winkler
 
Posts: 889
Joined: Fri Sep 07, 2007 12:45 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Conditional colours

Postby Roj » Fri Aug 13, 2010 10:08 am

Thanks Adrian / Phil,
We run an overnight procedure in our DFD database using a "robot" user, of course we can't do anything like send e-mails with it but I understand the principles!
Roj.
Illegitemi non Carborundum.
Roj
 
Posts: 42
Joined: Wed Jul 30, 2008 2:44 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Conditional colours

Postby Phil Winkler » Fri Aug 13, 2010 12:32 pm

Ahem, I've been emailing from a DFD app for years. Here is part of the format:

.group header
From: [field1]
To: [field2]
Subject: NSS Renewal Notice
Reply-To: nss@caves.org
Errors-To: nss@caves.org
Reply-to: nss@caves.org
Apparently-From: nss@caves.org
Sender: nss@caves.org
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html><PRE>

.items
<B>National Speleological Society
Membership Email Renewal Notice
</B>

Fellow NSS Member;
Phil Winkler
PLM Consulting, Inc.
pwinkler@plmconsulting.com
Phil Winkler
 
Posts: 889
Joined: Fri Sep 07, 2007 12:45 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Conditional colours

Postby Roj » Fri Aug 13, 2010 1:33 pm

Hi Phil,
what I meant was we as in our company can't email from dfd (lack of expertise!).
I have had a look around the forum and the net and found Adrian's post with the VB script, When reading it I can sort of understand how it works but how I would run it from FF ? I'm afraid Vb and C++ etc are a kind of black magic to me ! I used Trinas example of sending an email with ShellexecuteA which worked a treat until i find that it won't send the email automatically, unless you know different!
Roj.
Illegitemi non Carborundum.
Roj
 
Posts: 42
Joined: Wed Jul 30, 2008 2:44 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Conditional colours

Postby Phil Winkler » Fri Aug 13, 2010 2:29 pm

Computers don't do things automatically on their own. There must be some event that initiates the action.

In Dfd we use SENDSMTP to pick up the file and 'burst' the emails, i.e. send them individually, but all are in a single file.
Phil Winkler
PLM Consulting, Inc.
pwinkler@plmconsulting.com
Phil Winkler
 
Posts: 889
Joined: Fri Sep 07, 2007 12:45 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Conditional colours

Postby Adrian Jones » Fri Aug 13, 2010 3:57 pm

Hi Roj,

You don't need to understand VB for this, really. Just make some educated guesses as to what to substitute and what data you need to extract from your database.

You can execute the VBscript using shellexecuteA, as per the code box a bit later on in that post:

viewtopic.php?f=5&t=925

Copy the VBscript in the link. Create a procedure along the lines of:

Code: Select all
define global "gID"   text 255 .

for WhateverTableHasYourPersonToBeEmailed with IdForthisTable = global gID ;
  list records
    email ;
    MOTdatedue .
end

export to "c:\Motemail.vbs" .
.items
{paste VBscript code here, swapping out the email address for @f[1,1], and putting the @f[1,2] for the MOTDateDue somewhere in the strBody bit (this is just one long concatenation).
.end


this will write the file to disk.

Then have a control proc that goes something like:

Code: Select all
define global "gID"   text 255 .

define "t" number .

for WhateverTableTellsYouTheirMOTIsDue with MOTDate [between/<= etc] ;
  gID := IDForThisTable .
  run procedure "WhateverYouCalledTheProcWithTheCodeAbove" .
  t := shellexecuteA ( 0 , "open" , "c:\Motemail.vbs" , "" , "" , 0 ) .
end


Or if you find that the called procedure is overwriting the VB file before you get a chance to execute it, use the shellandwait CDf, or create unique names for the VBS file.

This make more sense?
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: Conditional colours

Postby Roj » Fri Aug 13, 2010 4:11 pm

Phil,
The event that triggers sending the email would be a procedure run every night that scans the mot due dates and if a vehicle is out of test or comming up for the test then an email would be sent to the person responsible for that vehicle.

Adrian,
Yes, that makes more sense to me now, I must admit I had visions of running different programs to run VB script etc!
I will play around with these scripts over the weekend and see if i can make it work (that should keep me out of mischief - a couple of bottles of Lancaster Bomber and all will become clear!)

I will let you know how I get on next week,
Thanks Lads,
Roj.
Illegitemi non Carborundum.
Roj
 
Posts: 42
Joined: Wed Jul 30, 2008 2:44 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Conditional colours

Postby Fred Kingston » Sat Aug 14, 2010 3:13 pm

Adrian..

This is a good meat and potatoes thread..

Ya'll should have a special icon ya'll can tag threads like this one with so folks can find them.. :wink:
Fred Kingston
 
Posts: 281
Joined: Sun Aug 01, 2010 10:54 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Conditional colours

Postby Roj » Tue Aug 17, 2010 1:41 pm

Hi Adrian,
I have been working with the script you posted above and have come up with this : -

define global "gID" text 255 .
global gID := any COMPANYVEHICLES with NEXTMOTDUE Current date -14 REGNUM .
for COMPANYVEHICLES with REGNUM = global gID ;
list records
EMAILADD ;
REGNUM .
end

export to "c:\Motemail.vbs" .
.items
strFrom = "enquiries@bowerselec.co.uk"
strServer = "server01"
strTo = @f[1,1]
strSub = "M.O.T. Test Warning"
strBody = "The M.O.T. Test for Vehicle"
strBody = strBody & Chr(10) & Chr(10)
strBody = @f[1,2]
strBody = strBody & Chr(10) & Chr(10)
strBody = "will expire in 14 days "
strBody = strBody & Chr(10) & Chr(10)
strBody = "Please arrange to have the vehicle tested"
set objEmail = CreateObject("CDO.Message")
objEmail.From = strFrom
objEmail.To = strTo
objEmail.Subject = strSub
objEmail.Textbody = strBody
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strServer
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send
.end

it creates the file ok but there is no content, i'm sure it must be my interpetation of the script that is wrong but i can't see where.
I'm sure you will point out a mistake that is staring me in the face!
Roj.
Illegitemi non Carborundum.
Roj
 
Posts: 42
Joined: Wed Jul 30, 2008 2:44 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Conditional colours

Postby Adrian Jones » Tue Aug 17, 2010 2:00 pm

The export command parses the tables referenced in the DQL in the order they occur, and numbers them accordingly.

The first table you reference is in the 'any' statement. Therefore the fields you want to list from are the second table. Or even #3 if some how this construction invokes the DQL scheduler. Look the the data model for this procedure to get a clue. (Always look at the data model, in fact!).

IOW, you need to change the @f[ bits. Unless you are literally saying that the file is emtpy, in which case your selection is not matching anything (you need to actually have cars due a fortnight ago for this to work...).

However, I wouldn't do it this way.

Have a main procedure do this:
Code: Select all
define global "gId" text 255 .

define "t" number .

-- to get all cars that are exactly a fortnight overdue


for COMPANYVEHICLES with NEXTMOTDUE Current date -14 ;
  gId := Regnum .
  run document "ProcToCreateTheVBSFile" .
  t := shellexecutea ( 0 , "open" , "c:\Motemail.vbs" , "" , "" , 0 ) .   
end

Then your exporting routine remains as it is, but changed to:
Code: Select all
define global "gID" text 255 .
for COMPANYVEHICLES with REGNUM = global gID ;

i.e. loses the global assignment, since that is taking place in the other controlling procedure.

Make sense?

Make sure, though, that you actually have records that match the criteria.
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: Conditional colours

Postby Adrian Jones » Tue Aug 17, 2010 2:06 pm

oh, and you need to repeat the strBody variable with the '&' concatentation each time:

so:

strBody = "The first bit"
strBody = strBody & " which is joined to the next bit"
etc

just like, if this were DQL:

define "tStr" text 255 .

tStr := "the First Bit" .
tStr := concat ( tStr , " which is joined to the next bit" ) .
tStr := "whoops, forgot to join the previous contents of the string, so I've lost the first few bits."

It's just that most languages have string contcatentation as an operator, not a function. So VB has '&', C# uses +, php just '.', etc. Whereas Ff uses the concat function (or the jointext function, in the old days).
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 
 
Next

Return to Ffenics FAQ

Who is online

Users browsing this forum: No registered users and 1 guest

cron