DateBreak out

Root > 3. Customisation
 
To break a date out a date into component parts you need the actual date field and somewhere to receive the results of the breakout.
 
 
 
The example below uses the field Eval_expires shown on the layout below with the HTML underneath
 
 
 
<!-- field that contains the date -->
<tr><td class=ContactBoldText><#Label field=eval_expires></td>
<td class=Text><#Input class=ContactmediumTextField field=eval_expires format=date></td></tr>
 
<!-- fields that receive the breakout data -->
<tr><td class=ContactBoldText><#Label field=evalday></td>
<td class=Text><#Input class=ContactmediumTextField field=evalday></td></tr>
<tr><td class=ContactBoldText><#Label field=Eval_MTWTFSS></td>
<td class=Text><#Input class=ContactmediumTextField field=Eval_MTWTFSS></td></tr>
<tr><td class=ContactBoldText><#Label field=evaldayenglish></td>
<td class=Text><#Input class=ContactmediumTextField field=evaldayenglish></td></tr>
<tr><td class=ContactBoldText><#Label field=evalmonth></td>
<td class=Text><#Input class=ContactmediumTextField field=evalmonth></td></tr>
<tr><td class=ContactBoldText><#Label field=evalmonthenglish></td>
<td class=Text><#Input class=ContactmediumTextField field=evalmonthEnglish></td></tr>
<tr><td class=ContactBoldText><#Label field=evalyear></td>
<td class=Text><#Input class=ContactmediumTextField field=evalyear></td></tr>
 
<!-- fields are set up as shown below -->
 
Mark the field with the data in it as a Trigger field - see next screen shot
 
To create a trigger that will break out the data go to Site Settings | Misc settings | Advanced Field functions | Field Trigger Profiles
 
Creating Triggers
 
 
Create a new profile as shown here and save it
 
 
Having saved the profile re-open it and complete as shown below so that WiredContact knows which values to place into what field when the "trigger" runs
 
 
Supporting detail
In Help you may find Field rigger Profiles of value
 
date breakout [parameter list] This function will take the value in a date field, and break out the year, month, day and day of week values and put those in separate fields. Year, Month, Day and Day Of Week can go into Integer fields, and Day Of Week and Month can go into String fields. You need to specify one (or more) of six destination fields for year, month, day, day of week(integer), day of week(string) and month (string). The syntax would be:

date breakout field="[field name]" table="[table name]" limits="(month is null) or (month = 0)" dayfield="[day field]" monthfield="[month field]" yearfield="[year field]" dayofweekfield="[day of week field]" dayofweeklongfield="[day of week long/string field]" monthlongfield="[month long/string field]"

The long/string field values are taken from the long day/long month in the locale settings.

 
 
To have a Query that locates the correct record for a given date you need to construct your query so that you’re looking for the days/months and not the years.  So you would be using something like:
 
 
To achieve this 
 
Select contact, uniqueid, EmailCampaign from wce_contact where (dripfeed like  '%999999%') AND ((Not Hold = 'Y') or (hold is null)) AND ((evaldayenglish = 27) and (evalmonth = 4))
 
Within the Site Notification Profile this looks like
 
Select contact, uniqueid, EmailCampaign from wce_contact where (dripfeed like  '%999999%') AND ((Not Hold = 'Y') or (hold is null)) AND ((evalmonth = <#date offset=30 format=M>) and (evaldayenglish = <#date offset=30 format=D>))
 
 
Select contact, uniqueid, EmailCampaign from wce_contact where (status like  '%999999%') AND ((Not Hold = 'Y') or (hold is null)) AND ((evalmonth = <#date offset=30 format=M>) and (evalday = <#date offset=30 format=D>))
 
For an eval date of April 27th, the above query would look like:
((evalmonth = 4) and (evalday = 27))
 
You’ll need to make sure your SQL formatting is correct so you use quotes when it’s a text field, and you don’t use quotes when it’s not a text field.  Used in this way, you don’t have to worry about the number of days in a month.  The above is looking for an expiration that is +30 days from today.  If you’re using the date in a different way, you’ll have to adjust the SQL.
 
If you wanted to make sure you covered a date range, then you would need something like:
 
Select contact, uniqueid, EmailCampaign from wce_contact where (status like  '%999999%') AND ((Not Hold = 'Y') or (hold is null)) AND ((evaldate >= <#date offset=25 format=sql>) and (evaldate <= <#date offset=35 format=sql)) and
((evalmonth >= <#date offset=25 mask=M>) and (evalmonth <= <#date offset=35 mask=M>)) and
((evalday >= <#date offset=25 mask=D>) and (evalday <= <#date offset=35 mask=D>))
 
Because of the date range, if the range goes across a month, you’ll have possibly 2 months (4 and 5 for example) with multiple days (25 through 4 for example) and you’ll need to make sure that you exclude the days that don’t belong to the correct month (in this case – if the date is in month=4 day=27, you don’t want month=5 day=27 to hit, but you do want month=5 day=3 to hit) so you need to add the complete date test to make sure you’re in the correct span.

Add Feedback