On Thu, Jan 17, 2019 at 10:07 AM Rich Shepard <rshepard@appl-ecosys.com> wrote:
> The direct answer is that a completed activity has a row with either a
> future next-activity date or a null (which is the case when the status of
> that organization or contact is 'no further contact'.)
Off the top of my head (and this is a model I am quite familiar with
even if I'm doing this email at speed):
I'd suggest an actual activity table:
activity (
created_on date not null,
due_on date not null,
performed_on date null,
contact_id bigint not null references (contact)
);
contact (
contact_id bigserial primary key,
told_me_to_go_pound_sand boolean default false
);
Now, an exception report can be made for every contact where
"told_me_to_go_pound_sand" is false and there is no record on activity
where performed_on is null (and also pound sand is true and there is
one performed_on is null)
The report you want is basically everything on activity where
performed_on is null and due_on is today or earlier.
A unique partial index can be made (activity.contact_id) where
performed_on is null; to ensure that at most only one open activity
exists for each contact.
David J.