Jump to content

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


Matt Malyschko
 Share

This topic is 4380 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 4380 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.