Help with formula functions so Filemaker auto-fills text fields...
I could really use some help with formula functions, and getting my database to use text values in two separate boxes to fill out a third box. I have the following lists as part of my database:
Client List – basic contact information
Vehicle List – list of vehicles owned by each client, linked via Client ID#
Event List – List of events I run (Event ID code and Name)
Event Signup – linked via client ID, this sheet lists the Event ID they’ve signed up for, the assigned car number for that event and the role of the client in the car (Driver, CoDriver, Passenger)
I am attempting to create an event sheet listing all cars, and this is where I need the formula’s that I have ZERO idea on how to create properly. Lets say I have an event next month called April 2015. I would like to create a list which shows me driver and codriver, sorted by assigned car number. I think I need to do the following:
- Create a new list, called “April 2015”.
- Setup my fields so that I have Car number, Driver Name, CoDriver Name, and Passenger
If I start a new record in the this list, I would like to be able to type “1” into the car number field and have the following happen:
If “EventID” in the “Event signup” list = "April 2015"
AND “assigned car number” in the “Event Signup” list = "1"
AND “role in the car” in the “Event Signup” list = "Driver"
THEN use the text from “First Name” field from the appropriate Client ID in the Driver Name field.
IF “role in the car” in the “event signup” list = CoDriver
THEN use the text from “First Name” field from the appropriate Client ID in the CoDriver Name field.
IF “role in the car” in the “event signup” list = Passenger
THEN use the text from “First Name” field from the appropriate Client ID in the Passenger Name field.
How would I go about doing this? I definitely have more questions regarding formulas, but if I can learn this one I can experiment a bit and that might just teach me all I need. If not I can follow up with another question.
Thanks in Advance,