Newbie Question - not sure right place to ask...

[raisputin]

Sorry if this is the wrong place to ask this, but I was definitely NOT sure where to ask and this seemed the most appropriate.

I have finally somewhat figured out relationships (they are working at least, and working the way I intend them to), so my question is thus:

I have a table called 'trips' when entering data, the user will choose what type of vehicle is being used which will also determine how many places there are to sit on that vehicle.

In this trips table, I need to make it so that if a vehicle contains 4 places to sit, there are places to enter 4 peoples names/ID's, if there are 6 there needs to be a way to enter 6 peoples names/ID's.

In this database people can be on multiple trips/day and since tey get charged for each trip, I will need to add that charge to their account as well.

So, my question is, how do I get filemaker to display the correct amount of fields depending on the vehicle used?

Thanks In Advance,

Greg

[Vaughan]

"if a vehicle contains 4 places to sit, there are places to enter 4 peoples names/ID's, if there are 6 there needs to be a way to enter 6 peoples names/ID's"

I'd do this with another related table. Actually to do it right there'd be a couple more tables.

Firstly, make a Vehicles table. Use it to store each type of vehicle and the number of passengers each can hold.

Each Trip record will have a Vehicle ID field to relate it to a vehicle. That way it can see the number of passengers.

Make Passenger table, related to the Trip table. Using scripting you can limit the number of passengers (related records) that are created to the number the vehicle can accommodate.

[raisputin]

Ah, then I was on the right track at least :

I do currently have a vehicle table related to my trips table. So that's good.

Each trip also has a unique ID (Trip_ID) and an ID for the specific trip (daily_ID)

SO I guess the next question would be, how do I relate those two tables? Maybe with the Trip_ID?

Thanks again

[Vaughan]

Hmmm, this Daily_id is a new development.

So, there are set *Routes* that the vehicles run on. A *Trip* is therefore a *Route* run on a particular day (or day/time) using a particular *Vehicle* which carries a certain number of *Passengers*.

Routes -- I'm thinking bus routes here, like in Sydney a 272 goes from Willoughby Depot to Wynyard via the Freeway. The 273 goes from Willoughby to Wynyard via Crows Nest.

Vehicles -- this is where the number of passengers is stored.

Passengers -- the people in the vehicle on the trip. It might be better to rename this "Seats" and have a separate table for "People" so you can set up accounts for people (a Seat is assigned one People record) and track who the regular customers are.

Trip -- This is a Route, a Vehicle, a Date/Time, and a certain number of Passengers (depends on Vehicle).

I don't see where a Daily_id comes into it: it's not giving any information that the Trip record doesn't already have. You could have a table for Days and relate the trips to the days, but I don't really think it's necessary and might in fact make data entry harder.

I'm thinking that what you reffered to as TripID I have called RouteID: what you called DailyID I've called TripID.

In which case we're both on the same page just useing different language. You should call the entities whatever is best understood in your industry.

[raisputin]

Hey Vaughan Thanks for your replies so far. Perhaps I should explain the database in more detail so I can better understand what you are talking about. I will lay out the tables I have and maybe that will make what I am trying to do more clear to both you and I, because I think I got confused hee hee, sucks being a newb

tables:

Customers, Aircraft, Manifests (Trips), Account_info

Those are the ones that seem to matter anyway, there are others, but they all relate to the Customer database and are working properly.

I have say n customers each with their own ID

I have 1 aircraft (but there could be more)

Each Aircraft has a unique ID as well

Then there is the Manifest.

In this case, I need to choose the plane that will be used for any particular trip (Manifest_ID)

Additionally, I need to track how many trips said plane makes per day (Trip_ID)

Now once I have the plane information in the form, I need x amount of fields based on the number of seats in that aircraft.

Once those fields are available (after choosing the plane) then I need to enter a Customers name into that field

I think that is enough for now, there is more I need to do, like be able to increase each customers trip_count by one each time they sign up, but i figure I can deal with that after I get this first part working.

I am going to keep working on it, but any advice you could give would be wonderful. I can also upload the database for your perusal if needed. I am probaby doing some things in a way that could be done better, but I can adjust later or rewrite once I get more skill.

Kind Regards,

Greg

[Vaughan]

"Now once I have the plane information in the form, I need x amount of fields based on the number of seats in that aircraft."

Nope. You've got to think relationally. You need x number of records related to the aircraft, one for each seat.

If each seat is a separate record, and it has an aircraft, a trip, and a customer associated with it... won't it be easy to get all sorts of information out of the database!

[raisputin]

Hey vaughan,

I think I understand what you mean but I am unsure how to go about implementing it. It seemed straightforward enough when I read your message, but when it comes to doing it, it is stumping me completely.

Any chance that you could create a quick example for me or take a look at my database and tell me what I am doing wrong/need to do? I am assuming that you are probably quite busy, but figured it couldn't hurt to ask since I am just lost on this for some reason...

Regards,

Greg

[Vaughan]

"It seemed straightforward enough when I read your message, but when it comes to doing it, it is stumping me completely."

Yeah I know, what you mean: a friend of mine explained US Bond Futures to me years ago. While he was talking I understood. The moment he stopped my understanding just evaporated.

Realtional design is, to some extent, paint by numbers.

First of all get the tables right. Each needs its on unique primary key.

SOme tables link to others, these need th=eir own primary keys PLUS they need a field to link them to other tables. Name these fields the same as the primary key in the other table.

Then it's a metter of linking the fields together in the various tables, matching the names up.

[raisputin]

OK so here are the current tables based on our discussion. I have to let you know first that I REALLY appreciate your help. This is a newbie nightmare

Customers (Customer_IDpk=primary Key)

ContactVia (Contact_IDfk=foreign key)

Account_Info (Account_ID=foreignkey)

Cities (City_IDpk=primary key)

States (State_IDpk=Primary key)

GearInformation (Gear_IDpk = primary key)

USPA_Info (USPA_IDpk=primary key)

That part is working just fine from what I can see, though looking at the db I think I need to make the Contact_IDfk into the primary key and do the same with the Account_IDfk.

Next is the Manifest section, which is what we have been discussing. I was loathe to use the actual table names, so bear with me

So we have the following tables.

Manifest

Aircraft

Avionics

Seats

Jump Types

Passengers

I will explain these a bit just to double-check that we are on the same page

Manifest, this is where a user will enter plane information and people on that particular plane. I was planning on each individual manifest having a unique ID number, as over the course of a week there will typically be ~30-100 individual manifests.

Each time a plane goes up it is called a trip or in the terms of skydiving a "load" so each manifest will be needing to have a load number that auto-increments and then resets to 1 at the end of the day (I can maybe figure out the script for that part I think).

So I will need to be able to choose from multiple aircraft for any given "trip" as we discussed and then allocate seats based on how many people that aircraft can carry.

I think that should cover the Aircraft and Seats tables which are currentlty set up as follows:

Aircraft (aircraft_IDpk=primary key)

Seats (slot_IDpk=primary key)

SO I now have a passengers table an Avionics table and a Jump_Type Table left and they are set up as follows

Passengers (PassengerIDpk=primary key)

Avionics (AV_IDpk = primary key)

The avionics table has to be related somehow to the aircraft so I can enter multiple pieces of avionics into a record that relates to the aircraft...

JumpTypes (Jump_Type_IDpk=primary key)

This has to relate to the manifest(?) somehow I believe because there are different types of jumps that cost different amounts and I will need to be eventually adding to a balance on a customers account info when this information gets entered. for my purposes here, we can say there are 3 types of jumps "fun" tandem" and "learning"

I think I have the correct tables set up now, but I am not 100% sure. If those are correct, then I think I am down to the relationships between those parts that is the sticking point. The customers part SEEMS to be working correctly so I am not sure what is messing me up here..

Thanks for your time Vaughan, it is most appreciated

Greg