Get most frequent value when records are related.

[aapl]

Hi Guys, Hopefully someone can help me out here.

I am looking for a way to get a field to display the most frequent value of a field in another table which is related to it.

I have a Client table which contains ClientName, ClientID, and MemberOfStaff fields. A Service Table which contains ServiceName, ClientID, and MemberOfStaff. Looking for a way for a field MemberOfStaff in the Client table to display the most frequent value in the MemberOfStaff field in the Service table when records are related by equal ClientID's.

Can't figure out how to do this, hopefully someone can help!

[comment]

The best way to do this would be to produce a report from the Services table, summarized by Client and MemberOfStaff, reordered by a summary count field.

If you must do this "live" via relationships, define a self-join of the Services table matching on ClientID and MemberOfStaff. Add a calculation field cFrequency =

Count ( Services 2::ClientID )

Place a one-row portal to Services on the layout of Clients and sort it by cFrequency, descending.

A custom function could be another way - if you had the Developer/Advanced version.

[aapl]

I'm still a little confused.

Just to check is this the best way to find this.

For example say I have 1 Client in my table and have 5 related service records - MembersOfStaff being "Luke" x 3, "John" x 1 and "Ben" x 1 on these service records.

I need the field for MemberOfStaff in my Client table to say "Luke"

[comment]

Well, the idea here is to show the MemberOfStaff field from the Services table in a one-row portal - and making sure that the first related record is one of Luke's.

[David McQueen]

Well, the idea here is to show the MemberOfStaff field from the Services table in a one-row portal - and making sure that the first related record is one of Luke's.

You need an ordering field to sort on in the one row partal to cause the correct MemberOfStaff to show. There are probably other ways to do this, less cludgy.

1. Create a self join in the service table

ClientID=client ID

And

MemberOfStaffId=MemberOfStaffId

This gives you a window from any service record to all service records for that client served by that staff member.

2. Create a calculated field that is (not on a machine with fmp right now) I believe an aggregate field that is the count of MemberOfStaff for that relationship. Call it c_ServiceFrequency.

3. Back to Comment's remarks -

In the client table, show the one row partal and have it sorted by c_ServiceFrequency.

HTH

[comment]

And the difference is...?

[David McQueen]

And the difference is...?

Hmm ... Mainly that my alzhiemer's illiteracy problem turned itself on again and I missed you key line. :

[comment]

LOL, at least you have someone to blame...

[aapl]

I have done this and it works perfectly. Thank you!

The only thing is I need a way to search just the first row of the portal.

What is the best way to do this? Can I set it for only the first field in the portal to be searchable?

Or is there a way for a normal field to get the contents of the first record in the portal?

[comment]

Do you mean you want to find clients whose most frequent staff member is ___ ?

[aapl]

Yes thats right, so I can easily search for Luke, Ben & Johns clients.

[comment]

That's going to be more complicated - I'll have to think about this. There is also another complication that we haven't considered before: it's possible for a client to have more than one most-frequent staff member (a tie for the first place). How do you want to handle that?

[aapl]

I have currently worked it to have a script go to the first portal row, and copy and paste it into a separate text field. I would prefer though to have a calculation to do this automatically.

As for if there is a tie I had not considered this. For the purpose of what I am using it for I am only wanting a good idea of how many active clients each staff member is servicing so I am not too worried.

[comment]

I am only wanting a good idea of how many active clients each staff member is servicing

Hold on: that's an entirely different question. This CAN be calculated in a similar way, only mirrored (from the point-of-view of the staff table).

Now, do you need to know how many clients a staff member is servicing as the most-frequent staff member, or just how many clients a staff member is servicing?