How to do a where clause?

[DragonDave]

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?

Thanks...

[MarkWilson]

Something like...

If[serviceType = "oil change" and Mileage > Max( OtherTable::MaxMileage )]

Set Field[ OtherTable::MaxMileage; Mileage]

End If

[Genx]

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

Dave,

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) ]

i.e.

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

Cheers.

Edited August 5, 2007 by Guest

[comment]

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.

[DragonDave]

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

Mileage

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]