Jump to content

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


This topic is 4398 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This topic is 4398 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.