Find if a record exists with two criteria and return a value

[Matt Malyschko]

I have two tables one called customers, one called contacts line items, related via the customer id field.

The contacts lines items table records all contacts that I have with the customers, and contains the fields: customer id, contact id (serial), date.

In the customers table I want to have a field called 'contacted this week', where if there is a record in the contacts line items table, that has the customer id & a contact within a specified range (within the past 7 days), it will return yes, and if there is no such record - ie, no contact has been added within the past 7 days, then it returns no.

What would be the most viable way of doing this?

A progression from this would be, '# of contacts this week', which would count the number of contacts to each associated customer within this week.

Any help would be greatly appreciated!

[comment]

Try something like:

Max ( Contacts::Date ) ≥ Get ( CurrentDate ) - 7

If this is for display only, you could use conditional formatting instead of a field.

A progression from this would be, '# of contacts this week',

No. that would be something else. Again, if this for display, you could use a filtered one-row portal containing a summary field - Count of ContactID - from the Contacts table.

[Matt Malyschko]

Try something like:

Max ( Contacts::Date ) ≥ Get ( CurrentDate ) - 7

This works a treat!