Choosing names not ID numbers

[csharpmin]

I'm creating a database for my babysitting business. The three main tables are "Sitter", "Client", and "Jobs" with multiple jobs for each client and multiple jobs for each sitter. I think I have the basic relationships correct, but so far in order to add a sitter or client from the Jobs page, I have to type in the sitters ID or the Client ID. I want to be able to select client or sitter from a drop down list instead of having to know their ID numbers. Any suggestions? I've included a simplified version of the database to help things out.

Thanks in advance for your help.

Sitters.zip

[bcooney]

Hi! Love to help a resourceful babysitter. Here, I've added a bit to your system. It's not pretty (that's your job), but the essential functionality is there.

The answer to your question is to use value lists to enter the foreign keys in the job table.

Take a look and let me know any questions that you may have.

Sitters.fp7.zip

[csharpmin]

Thanks so much. You added some great additions.

It looks like you defined a value list with 2 fields, first the ID field, second the name field. Then you checked the box that has it only show the second field. It makes perfect sense.

I am curious about how you named ID fields.

_mF_SitterID and _kF_Main SitterID what do those prefixes mean?

Also noticed you made the JobID field a global field with the next serial value 20. Could you explain this.

Thanks again.

[bcooney]

Ahh! __kP_JobID should NOT be a global. Just change it back to a regular number field with an auto-enter serial and prohibit modification.

I use the field naming conventions that were published by FileMaker, FM White Papers . So, all primary keys are preceded by __kP_ and all foreign keys are _kF_xxx. I didn't change your relationship graph to the Anchor-Buoy method, but as the system grows, you will need to. I'll help, if you want.

Using a value list of IDs-Name and attaching it to popup is one way to have the user populate a foreign key, however many people do not like popup menus. A more sophisticated approach is to use a popup window, especially if the value list becomes very long, see this post

There's much more to add to your system. How about a portal of jobs on the Sitter layout, and then a total, similar to what I added on clients? You could add the client's address info and a comment field (always useful)--think mailings.

[csharpmin]

I have all sorts of plans for the database. Contact info etc. I just wanted to keep it bare-bones to begin with, so it's easier to see exactly what changes are being made when someone generous like you adds some features. I definately plan to make the changes in the Sitter layout like you did in the client layout. Thanks for the links too, I'll definately check them.

The next thing I'm working on is a page for sitter availibility. Currently I've added checkboxes in the sitter layout for weekdays availible. I'm working on a script that populates the sitter availibility table with the dates of each of those days. For instance, sitter is availible Sundays. The script would add records in Sitter Availibility for 3/25/2007, 4/1/2007, etc. It's working well so far, except I can't figure out how to add 7 to the date, then 14, etc. to the looping portion of the script. I've tried with little success using the "set variable" script command and "insert from last visited" command. Any hints? I'll keep playing around with it, and maybe post in the portal forum if I still need help.

[bcooney]

Calendars have been discussed on the forum, but I've never done one. Maybe this link will help Calendar Example

[csharpmin]

Thanks. will do.

I got it working by using a global "count" field. With each loop I had the script change the count field to increase by seven and then it would use the field in calculations.

Incidentally this is also how to make the loop stop after the "count" reaches a certain number.

Neat.