Jump to content

How to do a where clause?

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

Recommended Posts

  • Newbies

Apologies if this is a dumb question, but I'm new to FileMaker, and I'm trying to do something I could do with other DB products I've used on the job.

Let's say I have a table with the following fields:

- Service type (e.g. oil change, tire rotation)

- Mileage

- Cost

- etc.

Now, say I want to set a field in another table with the highest mileage for a particular service type (e.g oil change).

In the past, I'd do something like this: Max(Select mileage from

How can I do something similar in FileMaker?


Link to comment
Share on other sites

Mark, I'm not 100% sure what your suggestion will actually achieve.


Add another table occurance (TO) of your ServiceTypes table to the relationships graph. Name it "ServiceTypes_CurrentType". Relate it to your primary ServiceTypes TO by your service type field.

Now... essentially you would do a find in a layout based on your primary ServiceTypes TO and then go: Set Field[ OtherTable::MaxMileage ; Max(ServiceTypes_CurrentType::Mileage) ]


Go To Layout[ ServiceTypes_SummarySearch ];

Enter Find Mode[]

Set Field[ ServiceTypes::ServiceType ; "Oil Change" ]

Perform Find[]

Set Variable[ $maxMileage ; Max(ServiceTypes_CurrentType::Mileage) ]

Go To Layout[original Layout]

Set Field[myField ; $maxMileage ]

Hope that helps, welcome to the forum, and if you're confused at all, speak up :)


Edited by Guest
Link to comment
Share on other sites

I believe there may be a simpler way, depending on what's your purpose: make this "another table" a table of Service Types. Relate it to your current table by ServiceType, and sort the related records by Mileage, descending. Place the related Mileage field on a layout of Service Types, and you're done. If you're in List view, you can see the last mileage of each service type.

Link to comment
Share on other sites

  • Newbies

Well, I tried a bunch of different things, but still having problems. It could be because I simplified the problem a little. Here's what I actually have:

Table: Vehicles

Fields: Veh Name

Table: Service Events

Fields: Veh Name

Service Event Type


The two tables are related via Veh Name.

I want a field on the Vehicles layout to show the max Mileage for all "Oil Change" events. So, for the time being, I added a new field to the Vehicle table called "Max Oil Change". I added a script to calculate the value, and plug it into the field.

The script is below (based on the script provided by Mark). Really thought this would work, but no luck. I AM getting back a Oil Change mileage for the correct vehicle, but it isn't necessary the "max" one. Just seems to be picking a random value.

Any thoughts?

Set Variable [$vehName; Value:Vehicle:Veh Name]

Go to layout ["Service Events"(Service Events)]

Enter Find Mode[]

Set Field [service Events::) Service Event Type; "Oil Change"]

Set Field [service Events::Veh Name, $vehName]

Perform Find[]

Set Variable [$maxMileage; Value:Max(Service Events::Mileage)]

Go to Layout[original layout]

Set Field[Vehicles::Last Oil Change; $maxMileage]

Link to comment
Share on other sites

This topic is 6162 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
  • Create New...

Important Information

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