multiple values - beginner's question

[susan siegel]

GOAL: to set up a field in which I can add multiple values that cannot be predetermined; the values would be added at the "new record" stage and could be modified over time. For each specific record, there could be some with no values in the field and others with several.

Then I'll want to sort and list the records for specific values.

Example of a test file called "hobbies". Let's say there are 2 fields: name and hobbies

in the "hobbies" check box I would list: golf, tennis, fishing, etc., with a carriage return after each entry.

So far, I have no problem when I sort by the hobby field and then to a find for "golf" -- I do get only the records that have golf , whether golf was the only hobby or one of three three possible hobbies in the hobbies field.

example: (As I can't set two colums in this message, I'm going to separate the data that comes up in the hobby field with ","; in FM they are each on a separate line. The first field, "name" is set off by the ":"

John: baseball, fishing, golf

Tom:tennis, golf

Peter:golf

what I want to do, though, is to sort the hobby list (and print it) so that each hobby comes out on a separate line, along with other data in the layout, e.g.,

(hobby is set off by ":", followed by "name" separated by ",". You'll see that names get repeated if they contain more than one hobby.)

baseball: john

fishing: john

golf: peter, tom, john

tennis: tom

this type of sort is an important part of how I use the database and is also a procedure that I'm able to do with my current DB software. However, for a variety of reasons, I want to replace my current DB software with FM8 (i'm using the trial version right now) -- but I'm trying to make sure that it can do what I need doing.

In my "old" software, the hobbies were entered as a special "keyword" field and then when I sort, each keyword shows up as a separate line. So far, I haven't found a similar like procedure in FM.

I suspect that FM8 can do it -- I just can't figure out how. Am I entering the data in the wrong way? Is the problem in the layout?

Thanks in advance for your help.

Susan

[Razumovsky]

Hi Susan, welcome.

I would suggest taking some time to read up on 'designing your database' in the filemaker help file as well as on this forum - you probably can exactly re-create everything from your old DB in FM8, but it will probably not take the most advantage of FM's features.

Basically, you will want to set this up as at least two different tables; People and Hobbies, and most likely a third PeoplesHobbies table (run a search for 'Join Table').

People would relate to PeoplesHobbies PeopleID=PeopleID

PeoplesHobbies would relate to Hobbies

HobbyID=HobbyID

Everything about a hobby would be kept in fields in Hobbies table. PeoplesHobbies would only have 2 fields (PeopleID and HobbyID) unless there was data specific to an individual persons hobby (frequency of participating, etc..). These would be additional fields in the PeoplesHobbies table.

You could view all people associated with a hobby from a portal to People on a layout based on the Hobby table.

To get your text 'sum' of related people, Create a related value list "RelatedPeople" of people names from your hobby table, and a unstored text calculation in the Hobby table:

HobbyName&": "&substitute(ValueListItems(Get(filename); "RelatedPeople"); "¶"; ", ")

-Raz

[susan siegel]

Thanks for your quick response.

For other applications (without multiple values), I've been successful in joining multiple tables. I understand the concept. However, I still have issues/questions as to whether the multiple table approach within one file, or as joined tables in separate files, can achieve my goals.

Let me give you a "real" example, rather than the simplistic one in my first example.

I publish directories -- in hard copy and online. Each record (a used book dealer entry) has about 10-15 fields describing the dealer; three of the fields may have multiple values.

These 10-15 fields are uploaded to our web site with the dealer's page showing the multiple values that may appear in the appropriate fields.

A typical field with multiple values would list the dealer's specialties, e.g., American history, Civil War, literature, religion.

(The web site can be searched by specialty area; that's a function of how my web site designer has set the page but the search is up based on the file I've given him.)

If I had to set up a separte "specialty table" in Fm, lets say with the dealer id and then the list of specialties for that dealer, would that mean:

1) that on the "main" dealer table where I enter address, hours, travel directions to the store, etc, after that information has been entered, I would then than have to go to a second table to enter the specialties for the same dealer, matching dealer ID?? And then, open the other tables where multiple values are listed?

2)Then, When I'm ready to upload that dealer's record, how would FM deal with the fact that information about the dealer may be located in three different tables -- but all in one file? I select the fields that are uploaded; not all fields are.

Note: I haven't gotten as far as reading the chapters dealing with the web yet.I'm still dealing with the basics.

Thanks again.

Susan