Today I was asked about creating a group of Contacts who have had an activity completed today.
The simplest way to go about this would be to set a condition of
Contact.History.CompletedDate within the last 24 hours. Even by cutting
this down to 12 or 8 hours, this is not an exact formula and could
cause incorrect data to show.
A better and more accurate way to show this data would be to use an IN statement. Simply create a new group, add a condition for the ContactID field, set the operator to "in" and set the value is to "select contactid from history where convert(varchar, completeddate, 110) = convert(varchar, getdate(), 110)".
While this group did show all Contacts with an activity completed today, I wanted to see what those activities were. So I simply changed the Condition fom Contact.ContactID to Contact.History.Historyid in "select historyid from history where convert(varchar, completeddate, 110) = convert(varchar, getdate(), 110)". I then added some History Columns to the Layout and ended up with:
This group worked in Sage SalesLogix v7.5.0. The group runs a little slow so if anyone has any improvements, let me know.