Setting a field value to a count of...

[ABC]

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?

[Fenton]

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 )

[ABC]

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.

[Fenton]

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 )

[ABC]

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.