Multiple fields in db A--> one popup menu in db B

[Endophyte]

I'm an awfully new user of FMPro using 5.5 on a Mac (Os 9.2), and I'm wondering whether I might receive some advice to get my foot in the door -- I think I'm missing something fundamental either about design or FM capabilities, and I would very much appreciate any insights.

My question: in one database, I have a series of fields used to identify components in each of four different processes (in this case, primers used in specific polymerase chain reactions; they're analogous to ingredients in a soup). Thus, the user can enter 'onions' for 'Soup 1', 'beef' for 'Soup 2', etc., up to Soup 4. The ingredients are selected by the user from a pulldown menu containing a static and predefined value list (onions, beef, potatoes...).

What I would like to do is to generate, in another (related) database, a field containing a pulldown menu in which the user can choose among the soups that are selected in the first database. That is, if there were records for onion soup and beef soup, but not for potato soup, I'd want a value list containing only onion and beef soup -- and so on, for all combinations.

I just can't seem to get this to work, and I would be most grateful for any comments (as specific as you might be able to offer, given my excessively new status). Please forgive my naivete; I hope to be off and running once a few fundamental issues become clear for me.

With thanks --

[Ugo DI LUCA]

Well, in that case, assuming the "Soup Name" is a value in the Main file, you just need to format your field in the related database as a value list using the values of "Soup Name" from the Main File.

In that particular case, you even do not need a relationship.

Go to format menu.

Define a new value list called "soups"

check "Use values from another file"

Choose the field "Soup Name" from the list.

To go a little more into the process, you could even choose the ingredients according to the "soup" selected in a related file, using a relationship to this file. Make a search on "Conditional Value List" on the Forum...

to use the values from the Main file

[Endophyte]

Thanks for your quick reply -- I really appreciate your help!

I think that I'm having trouble because Soup 1 is a field, Soup 2 is a field, and so on for the rest of the soups; yet I want a conditional list of soups to work with: how to make a conditional value list when the components of that list would come from different fields? I can see how to do this if all of the soups came from the same field, such that selecting the value list items to come from a field would be fine; but here, I am still a bit stuck.

I may just need to think about your reply a bit further, so that it will click for me.

Again, many thanks --

[Ugo DI LUCA]

Why do you hold this Soup 1, Soup 2,... format. Why not use relationship to a Soup database ? Tell me more about your structure. I'm sure you miss something essential from FM relationships here.

[Endophyte]

I am starting to see more clearly how I need to do it. Part of the trouble is that I am working on a pre-existing database that in some ways was one-dimensional (while other parts of it are relational), and I know that I need it to exist in a much more relational state as it grows.

The 'soups' began as independent fields because they represent a set of experimental conditions from which, in the end, a DNA sequence may be obtained. It was desired to know the conditions for each 'soup' (i.e., ingredients, temperature, etc.), so that one would be able to associate each sequence with a specific soup. That is, one would have a sequence, and would be able to choose from a pulldown menu the soup used to generate that sequence.

That's the historical perspective; however, I feel that the current structure is not functional. I suppose that a better question for me to be asking (of myself, or of anyone who might be willing to spend more time on this newFMer's issues) is to figure out a better overarching structure -- as you say, to find the essential understanding of how to get the database built in a coherent way.

Thanks again -- will continue to read and think, inspired by your kind assistance --

[jasonwood]

Why not have separate records for each soup?

Is this currently a single-record database?

[Endophyte]

Jason -- thanks for your note. The problem is that there are multiple soups for a single specimen, and the database is built around specimens (defined by a unique ID).

To mix the metaphor even more -- I guess the specimens are the restaurants, each of which can serve up to four soups....

I appreciate your taking the time to post your reply; thanks very much.

[Ugo DI LUCA]

I would definitely split the structure into a Soup File (with Unique_ID) and an Ingredient File (with Unique_ID) and make these two files relate eachother.

You could therefore have listed

- all ingredients for a specific soup

- all soups that use a specific ingredients