List first occurance of a related field in portal

[Guy Weymouth]

I have three tables: Companies, Communications, and Home Page.

Companies = list of companies with key_company_ID as the unique field for relationships

Communications = list of communications with key_communication_ID. (phone calls, emails, etc). Each communication also records the key_user_ID and the creation_date for the communication.

Home Page = global field where key_user_ID is set (after logging in).

When a user logs in, I want to present their own "home page" using the Home Page table. I want the home page to list in a portal the last 10 companies they dealt with. The way to determine this would be to look at all the communications a user has made with that company, and create the "top 10" list from there.

Unfortunately, if a user has communicated with "Company A" three times then my portal lists "Company A" three times.

So I want the portal to only list the FIRST OCCURANCE of "Company A" so the portal lists the top 10 companies, not the top 10 communications and the corresponding company for that communication.

I don't want to change the modification date of the "Companies" table every time a user creates a Communication in the "Communications" table, but will if I have to.

I tried using a self-join to find duplicates in the "Communications" table but then I can't use that calculated field in the relationship that lists the top 10 companies.

How can I solve this?

[Ender]

I think I see your dilemma. Since the Date field is necessarily in Communication and not Company, it's not such an easy thing to show a portal of recent Companies.

How about using a Recent table that only contains up to 10 records per User, representing the last 10 User-Communications. It would be sort of like a queue structure, where the oldest lines are deleted as new lines are added. I've worked out a sample file that shows how this could work.

It seems like there may be a more direct way, but I can't think of it right now.

LatestContacts.fp7.zip

[transpower]

You can use an auto-enter calculation (rather than a calculated field) for the self-join; use Evaluate so that the field will recalculate when necessary.

[Guy Weymouth]

Ahhh! Just as I was about to loose faith in FMForums as I thought no one had replied to my message, I realised I didn't turn email notification on.

So, thanks for the replies!

Ender, thanks for the example file - legend. I agree with you though, that it is complex.

As for using the evaluate function, I didn't get around to trying that because I solved it this way...

RELATIONSHIP TABLE:

STAFF (key_current_user) --> ACTIONS (staff_member)

ACTIONS (key_company_ID) --> COMPANIES (key_company_ID)

The portal exists in the staff table and uses the relationship COMPANIES. By doing this, it seems the portal asks for all companies for this staff members, but can't do it directly and uses the actions table, which I guess, is an implied relationship.

I'd love to know why this works because I've heard about implied relationships, and I assume this is an example of one, but can't explain quite why it works.

Widey

[Ender]

Another idea is to use a User_Company table that's a join between User and Company. When a user creates a Communication, it set a Last Communication Date in User_Company. Now from User, show a portal of User_Company records, reverse sorted by Last Communication Date.

This table could also be used to grant Users access to specific Companies, if your business rules required it.