Creating a database that ties invoices to client records for an auto detailing center. Each customer car is assigned a unique CarID. On each visit, an invoice is generated that is tied to that specific CarID. I'm trying to create a field that will be set to a value equal to the number of times that car has visited, by doing a lookup on the number of line item records in the invoices database that match the particular CarID. Each time a customer's car record is displayed, it should show the number of visits. Can't seem to wrap my head around this one. Any help?

The Count() function can count a field across a relationship, to get the number of related records. From the Car record:

Count ( CarVisits relationship::CarID )

Fantastic, thanks. I was flirting with that but I guess I was trying to make it more complicated that it is. One additional level to the problem though...I need to add a conditional to the count; that is, what I really want is the number of visits for the car in the current month. Did more research and even bought a book but I can seem to figure out if I can add a conditional to the count function in my calculation. Trying not to complicate the procedure if I don't need to. Thanks again for the reply, and for any additional help if you can.

This is actually simple in 7. Just add another line to the relationship for that table occurrence, from Cars to Visits. Your only problem then is to have a "month" field on each side. There is a Month(date) function, which produces: 1, 2, 3, etc.. That's OK, but it really only works if you add the year also.

_cMonthVisit (result text) =

Year ( DateVisit ) & "_" & Month ( DateVisit )

That's the field on the right side. On the left you'd have an Unstored calculation (important, in Storage Options).

_cMonthThis (result text) =

Year ( Get ( CurrentDate ) ) & "_" & Month ( Get ( CurrentDate ) )

Relationship:

CarID = ::CarID

AND

_cMonthThis = ::_cMonthVisit

If you get a little fancier, you can "pad" it so that the field sorts correctly. Then _cMonthVisit can also be used as the sort field for a Subsummary part, to give you a month-separated report.

Fields:

_cMonthVisit = Year ( DateVisit ) & "_" & Right ( "0" & Month ( DateVisit); 2 )

_cMonthThis = Year ( Get ( CurrentDate ) ) & "_" & Right ( "0" & Month ( Get ( CurrentDate ) ); 2 )

Ahhhh... sure. The conditional is in the relationship. Much easier actually. Brilliant. Thanks a ton for the help. You sure you are only an 'advanced' user? Kinda makes me feel like I don't really know anything! Really lovin' 7, lots to discover but already the major changes are SO much better.

Thanks again.