Bradley M. Law

A common question that resonates through the CRM customer base is allowing for filtered lookups between entities. I am currently in the final stages of a client who wanted this functionality between some custom relationships set between contacts and opportunities and another custom entity and opportunities. Here are a couple of resources on the two:

Application Based

http://www.stunnware.com/

Code Based

http://crm.georged.id.au/post/2008/02/16/Filtering-lookup-data-in-CRM-4.aspx

//force the result to the database
crmForm.all.DesiredField.ForceSubmit = true

http://www.microsoft.com/dynamics/crm/using/customizing/defaultvalues.mspx

I installed Windows 7 today and in my Virtual Box and wanted to install the Guest Additions. I found this link on how to install the Guest Additions using Windows 7 Compatibility Mode.

Sites with Date Formatting

http://www.sql-server-helper.com/tips/date-formats.aspx

http://www.blackwasp.co.uk/SQLDateTimeFormats.aspx

http://www.mssqltips.com/tip.asp?tip=1145

Other Date Formats
SELECT MONTH(CURRENT_TIMESTAMP);- Return a Month(6)

SELECT DAY(CURRENT_TIMESTAMP);-Return a Day:(22)

SELECT DATE(CURRENT_TIMESTAMP); – returns a date (2004-06-22)

SELECT TIME(CURRENT_TIMESTAMP); – returns the time (10:33:11.840)

SELECT DAYOFWEEK(CURRENT_TIMESTAMP); – returns a numeric value (1-7)

SELECT DAYOFMONTH(CURRENT_TIMESTAMP); – returns a day of month (1-31)

SELECT DAYOFYEAR(CURRENT_TIMESTAMP); – returns the day of the year (1-365)

SELECT MONTHNAME(CURRENT_TIMESTAMP); – returns the month name (January – December)

SELECT DAYNAME(CURRENT_TIMESTAMP); – returns the name of the day (Sunday – Saturday)

SELECT WEEK(CURRENT_TIMESTAMP); – returns number of the week (1-53)

Last 30 days
BETWEEN dateadd(month, -1, getdate()) and getdate()

This month
DateTime between (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0)) AND (DATEADD(dd, DATEDIFF(dd,0, getdate()), 0))

Last Month
DateTime between (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0))

Yesterday
DateTime BETWEEN (DATEADD(dd, DATEDIFF(dd,0, getdate()) -1, 0)) AND (DATEADD(dd, DATEDIFF(dd,0, getdate()) , 0)))

Often I need to produce data for a SQL Reporting Services report where the data is filtered by the last day of the month. I found a solution to this query at this site HERE:

----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth