[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

cfschedule cfmail containing hit results with start date set to date column in db

I have a tricky cfschedule/ cfmail task- My site is almost done and I need to try to keep it completely automated since I have a real job. I already have welcome mail that is sent with results of their db insert at enrollment time and it works great. Now I need to email a news letter (eventually will be a billing statement when site is worth charging for) complete with individual hit results to my members on a monthly basis. I have a column in dbo.members called "member_since" which contains the date/ time of insert (enrollment). Here is what I have but I know the syntax is wrong before I even run it. Please keep in mind, I need this to happen on every 30 day anniversary for every member. I'll need to change the static part of the message body from time to time but I got the cfmail part down pretty well. Thanks in advance, Andy- electriciansnet.com

<cfquery name="schedMembers" datasource="#enet#" maxrows="1">
SELECT CompanyName, member_Since  FROM dbo.members
</cfquery>

<cfschedule action = "update"
   task = "Status Report For #CompanyName#"
   operation = "HTTPRequest"
   url = "mailer/mailer_status.cfm"
   startDate = "#schedMembers.member_Since#+#CreateTimeSpan(30,0,0,0)#"<!---GOT TO BE WRONG--->
   startTime = "12:25 PM"
   interval = "monthly"
   resolveURL = "Yes"
   publish = "No"
   requestTimeOut = "600">
0
Electriciansnet
Asked:
Electriciansnet
  • 2
1 Solution
 
Scott BennettCommented:
You should be using dateformat for that start date like:

<cfschedule action = "update"
      task = "Status Report For #CompanyName#"
      operation = "HTTPRequest"
      url = "mailer/mailer_status.cfm"
      startDate = "#dateformat(schedMembers.member_since,'mm/dd/yyyy')#"
      startTime = "12:25 PM"
      interval = "monthly"
      resolveURL = "Yes"
      publish = "No"
      requestTimeOut = "600">
0
 
Scott BennettCommented:
On closer inspection you don't seem to have anykind of ID being passed into Mailer_status.cfm to tell it which companies scheduled taks is being run.  you should probably select your primary key in your query as well and put that in the url.

something like:

<cfquery name="schedMembers" datasource="#enet#" maxrows="1">
SELECT MemberID, CompanyName, member_Since  FROM dbo.members
</cfquery>
<cfschedule action = "update"
      task = "Status Report For #schedMembers.CompanyName#"
      operation = "HTTPRequest"
      url = "mailer/mailer_status.cfm?MemberId=#schedMembers.MemberID#"
      startDate = "#dateformat(schedMembers.member_since,'mm/dd/yyyy')#"
      startTime = "12:25 PM"
      interval = "monthly"
      resolveURL = "Yes"
      publish = "No"
      requestTimeOut = "600">


Although I personally would not do it this way at all. I would only create one scheduled task that sends the emails for everyone rather than schedule them all separately.
0
 
ElectriciansnetAuthor Commented:
Upon further research I agree with your last comment. I figured out that I can create 1 task that points to mailer/mailer_status.cfm with an interval of 1 or 2 times daily and put a query like this at the top of mailer/mailer_status.cfm:
<cfquery name="<cfquery name="getUsers" datasource="#enet#">
select* from members
where datepart(day, member_since) = datepart(day, getdate())
</cfquery>
But this creates a new problem which is a) to detailed to put in this question/  b) not related to cfschedule
I'm going to go post that Q now
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now