Relationship, Calc, Summary - First row only

[gwweber]

Hey everyone. I used to be a FMP enthusiast back on version 5.5, and am trying to bring myself up to speed with version 9. A lot of good things!

 

Here's my question:

 

I work for a resort developer, in the timeshare industry.

 

In one table called OWNERS I have all of our owners, with Owner_ID as a primary key.

 

In another table (actually, a separate file) called CONTRACTS is all of our contracts, with Contract_ID as the primary key.

 

The two are related via Owner_ID. Every owner has at least one contract associate with it, but many have multiple contracts.

 

We have ~25 resorts that our owners own at, and some of them own at multiple resorts.

 

I'm trying to count how many contracts each owner has to resorts XXX, YYY, and ZZZ. In the OWNER table, I have fields called "Marker_xxx", "Marker_yyy", and "Marker_zzz". These are simple calcuation fields that say "If(CONTRACTS::ResortID="xxx";"1";"0")" (for marker_xxx). (Note, it's not important to know if there are more than one contract to each resort.. only that they own at each resort)

 

I then use a summary field called Summary_xxx that gives me a total of the Marker_xxx field. The same goes for yyy, and zzz.

 

The idea here is that I can construct a found set based on owner criteria (i.e. if they have a valid address, or are located within x miles of a resort, etc) and then go to a layout that shows all the summary fields to tell me how many contracts are currently assigned to each resort from that found set of owners.

 

My hope is that, for example:

 

Owner_ID 111 has three contracts associated in CONTRACTS, one for each resort (xxx,yyy,zzz).

 

On the record of Owner_ID 111 in OWNERS, the resort count would show:

Marker_xxx = 1

Marker_yyy = 1

Marker_zzz = 1

 

What it's really showing is:

 

Marker_xxx = 1

Marker_yyy = 0

Marker_zzz = 0

 

It apparently only compares each calculation to the first related record, and not against all of the related records.

 

Any idea how to make it compare to all the related records?

 

Thanks, and I hope this wasn't too confusing.

[LingoJango]

Run your search from a layout based on Contracts but containing the requisite fields from Owner and Resort (you should probably have a separate table of Resorts, too, to keep track of common data such as name of grounds caretaker or whatever).

 

Once you have that, you can have a script that runs a subsummary report with a sub-summary part sorted by the resort field, or you can keep your separate hard-coded calc fields but make the calc something like

 

Case ( PatternCount ( List ( Contracts::ResortID ) ; "xxx" ) > 0 ; 1 ; 0 )

 

where "xxx" is each of the hard-coded resort IDs. I don't like this second option, but right now I'm too thick to see a relational option that will not require a report to be previewed.

[gwweber]

Thank you, you are my hero!

 

I opted with the second option (patterncount/list). This is a marketing campaign, so while it doesn't need to be very robust (as in knowing the caretakers' names), I do have to be aware of where each owner owns at so I don't allow cross-marketing.

 

I'm pretty familiar with patterncount, but the List function is new (and now invaluable) to me.

 

Thanks again, and many cheers!

 

g

[gwweber]

Although I do feel like an idiot. I had already solved this problem (but apparently in a late night update, I went with another method and lost myself).

 

I had created the marker_xxx calc fields in the CONTRACTS table, with If(resortid="xxx";1;"") and in the OWNERS table, the marker_xxx field equals: count(CONTRACTS::marker_xxx).

 

This yields the total number of contracts, not just their instance.

 

However, the pattern/list combination will still be very beneficial.

 

Learning experience!