Skip to Content
0
Jun 03, 2015 at 04:55 PM

Excluding Wrong Records

20 Views

Good Morning,


What I'm trying to accomplish is to identify all companies in our database with whom we have had no contact in the last 365 days. Contact is determined by whether a communications record has been created in that time frame. If a communications record was created withing the last 365 days, we have had contact with that company and it should not appear on the report.


After joining the tables I've put the following in the Record Selection formula...

{Communication.Comm_CreatedDate}<today()-365


What this is doing is returning ALL companies and ignoring any communications records created within the last year.


To summarize...

We have a Company table and a Communications table.

The tables are joined on Company.comp_CompanyID > Communications.comm_CompanyID.

The relationship is "one to many." One company has many communications records.

I want to omit from the report all companies that have had a communications record created within the last year.

Instead, all companies are being returned and just the communication records created within the last 365 days are being omitted.

How can I skip the company if the company has had a communications record created within the last year?

Thanks for any help.