Find problem using list from record in related table

[Frank E]

My question is similar to one posed by Ken M earlier today (newbie problem - relations and lists), but I can’t figure out how to solve my problem from the response posted by Martha

I have a file that is used to construct a life-history database for clients. The file includes 4 tables 1) Clients, 2) Sources, 3) Events, 4) Names.

Records in the Events table are tagged with themes from a value list. The field Theme in the Events table is defined as a repeating text field formatted as a pop up menu which displays the values from a field named Theme List, which is the Clients table. (Each client has his own list of themes). Each record in the Events table is associated with one particular client, and when I choose a new record, the Theme field shows the themes defined for that client. The Theme field is formatted as a text field with 4 repeating values from the list of themes in the related record in the Clients table. (While each client may have 15-20 themes in the value list, I don’t assign more than 4 themes to a given event.)

When I create a new record, I first enter the Source ID from the source I am entering data from (usually an interview or a document), which then automatically displays the name of the client and other information such as date of birth and the list of themes for that client from the related record in the Clients table.

The Events table is related to the Sources table, and the Sources table is related to the Client table. (Each Source records is associated with only one client) So the Events table is related to the Clients table via the Sources table.

My problem concerns Find mode: I am trying to find all the Events record for one client (call him Client A) which have been tagged with a certain theme, but I can’t. When I do a Find request in the Events table, I fill in the Client ID, but I can’t choose any themes from the Theme field because I just get the message “” where I expect to see the list of themes.

I realize that the Find request isn’t displaying a list of themes because it doesn’t know which client record to go to. How do I solve this? (I am using FM Pro 7.)

Thanks for any suggestions.

Frank

[PeterHW]

Hi Frank,

first of all, I think you should consider replacing your repeating fields with a related table. Although repeating fields can be useful, they are a poor substitute for relational data, especially if you should ever have the need for things like reporting (which would involve sorting/summarizing etc.) on the data, which is impossible using repeating fields.

It seems you need two additional tables : ClientThemes (related to Clients by ClientID, holding all the themes for a client) and EventThemes (related to Events by EventID).

That won't solve your immediate problem though : In this case, Filemaker does not work as you expect it to. The relationship from Events to Clients is not made as soon as you enter a ClientID in Find mode. In Find Mode, relationships are simply non-existant, that's why you are getting the no-values-defined.

When you use the setup as I proposed (separate table for EventTheme), you would normally create a record in the EventThemes through a portal (in Events, have a portal to EventThemes). Now a portal DOES allow you to do combined searches as you need on related data. If you go to find mode and enter the ClientID, and then the Theme, it would bring back all records for that client with the themes you specified. You will have to type the theme manually, though since a popup with related records will still not work (since you're in Find Mode).

BTW, note that this same trick will also work with your current setup, i.e. using repeating fields. If you change the field to use a drop-down in stead of a popup menu, you will be able to manually enter data in find mode, and it will also give you the records you are looking for. So in both scenarios, you will have to type the theme manually.

There are several ways of solving this but consider the attached example. The search mechanism starts from Client, but variations originating from Events are possible as well.

Regards,

Peter

test.zip

[Frank E]

Hi Peter,

Thanks for your response. I am going to try adding the 2 additional tables as you suggested. I have not used portals yet, but now is a good time to start.

I’m not sure what you mean when you say the search mechanism starts from Client (your last paragraph), but maybe it will become clearer when I start. I had been starting the search from the Events table, but maybe that’s not the way to do it.

Frank