Retrieve record value based on the max value of another field in the same table

[geleng]

As stated in the topic title, I need some guidance on how to retrieve the value a record based on the max value of all the relevant records in the same table.

Information about the problem:

I have 3 main tables - Status, Agents and Companies. Each agent is assigned to one or more companies; each agent can trigger one or more status change in the company. I am storing all status changes and I have created a StatusID that automatically generates a unique serial number for all status change (regardless of company).

Now, I am trying to create a layout that displays the following details:

Agent Details like Name, Contact Number etc

Information about the companies that the agent is assigned to (like Company name etc) as well as the latest status update.

I figured that I am supposed to do a portal. So my layout is based on the Agent table and the portal is based on the CompanyAgent joint table. The CompanyAgent joint table is based on a composite key of AgentID and CompanyID. I manage to get the simple details of the company to appear by creating a calculation field in the CompanyAgent joint table which retrieves the relevant information from the Company table. Now, I am trying to retrieve the latest status but I am not sure how to do it.

In my mind, it's supposed to work by

1. Isolating the statusID of status for the specific company

2. selecting the Max of those StatusID

3. Get the Status Message for the max(statusID)

I am not sure how to do that using a calculation field in filemaker.

Would appreciate any help or direction that anyone can give. Thank you!

[comment]

So my layout is based on the Agent table and the portal is based on the CompanyAgent joint table. ... I manage to get the simple details of the company to appear by creating a calculation field in the CompanyAgent joint table which retrieves the relevant information from the Company table.

This is not necessary: you can place fields from the Companies table directly in the portal (make them non-enterable, so they won't be modified by mistake).

Now, I am trying to retrieve the latest status but I am not sure how to do it.

Latest from which point-of-view? The latest status of a company is not necessary the latest status caused by the current agent - for that company or overall.

[geleng]

This is not necessary: you can place fields from the Companies table directly in the portal (make them non-enterable, so they won't be modified by mistake).

I will try this in a while. Thank you!

Latest from which point-of-view? The latest status of a company is not necessary the latest status caused by the current agent - for that company or overall.

The latest status of the company (need not be caused by the agent that I am viewing).

Thank you very much for your help again.

[comment]

You could create a calculation field in the Companies table =

Last ( Status::AnyField )

This will return data from the last (in the sort order of the relationship) related record in the Status table.

Alternatively, you could sort the relationship by date, descending. Then put fields from Status directly in the portal.

[geleng]

Alternatively, you could sort the relationship by date, descending. Then put fields from Status directly in the portal.

I tried sorting the individual tables earlier on and did not know you can sort a relationship. I tried this method and it worked!

Thank you very much!