Choicing between two tables.

[Delights]

For Apptment times There are the following options for the week and different times for the weekend. For example

Week: 10:00 AM, 13:00 PM, 15:30 PM, 18:00 PM, 20:00 PM

Weekend: 10:00 AM, 12:00 PM, 14:00 PM and 16:00 PM

Now, i have it so that it checks the times that are avab and it is showing the first one. However i would like to setup so that if the date is "Saturday" then it gets the times from table "AppttimesWeeked", else it gets the times from "AppttimesWeek".

Is there a way to set up a script or something simular under "Field Format" - "Define Values" - "Use values from Field" ?

Edited March 16, 2006 by Guest
Subscribe to Topic.

[comment]

Try it this way?

AppointmentTimes.fp7.zip

[Delights]

Excellent idea, it is simular to that i was trying to do.

I have had a look at what you did, howver i had to try and implement into my own program, i have everything working except the times. I even added another field to the "Slots" called "Company" that way i can have more then one company and different times for different companies. Everything works but the times. Can you see if there is something i have missed please.

I set the Guest to full access, when you do goto open the filemaker file, just select Guest..

Workdatabase_Clone.zip

[comment]

In my file, there's a relationship from Appointments to Slots, based on the day of week. The value list is derived from this relationship. I don't see a similar relationship in your file, but then it also complains about a missing file, so I don't know.

As an aside, just about any time you percieve the need for multiple fields e.g. Date1, Date2, Date3, etc. you should break them out into separated records in a related table instead.

[Delights]

Opps, i used the fields "Company" to link the files...

[Delights]

okey, got it working but the problem i am having is that i have 6 dates,

1 Appt and 5 Rescheduals, if i like use the line

"DayOfWeek ( Appdate )" on the field cDayOfWeek then it only uses the one date for the times. How would i go about it using the other 5 date boxes that are listed as Appdate2, Appdate3, Appdate4, Appdate5 and Appdate6.

[comment]

You will need to either:

- define 6 calc fields, 6 relationships and 6 value lists;

or (just as I said before):

- put the 6 dates into 6 related records.

[Delights]

put the 6 dates into 6 related records.

Okey, how would i go about that?

[Delights]

okey, got it all finsihed, i just did the

"- define 6 calc fields, 6 relationships and 6 value lists; " option and it works well...

Thanks alot of all your help.

[comment]

If I understand your file correctly (and that's a big IF), you need the following structure:

TABLES:

Customers

Jobs

Appointments

Consultants

Slots

RELATIONSHIPS:

Customers <-> Jobs (match on CustomerID)

Jobs <-> Appointments (match on JobID, alow creation of Appointments)

Appointments <-> Consultants (match on ConsultantID)

Appointments <-> Slots (match on ConsultantID AND DayOfWeek)

Your main layout is based on the Jobs table and it shows a portal to Appointments, where you can schedule the six appointments each Job can have.