Creating a master list, and being able to select multiple choices

[Philip Trauring]

I have several fields in my table that all should be selected from a master list of options. In some cases the fields should only have one selection from that list, in some it might need more than one selection.

So I've set up a second table which is the master list. I know that I can set a filed to have to select from that list, but what about when I need more than one value? How do I have a field select multiple items from that list?

Once that's done, when I want to present the information, how do I output the multiple selection items in a comma-delimited text string?

Thank you.

[LaRetta]

Hi Philip,

Since you posted in value lists, you know that you need a value list. YOu would use a checkbox. However, two additional things to consider: 1) If the list is long, a checkbox won't work well and 2) if you have several fields in a table that all should be selected from a master list of options, that might suggest that your fields should be records in a related table.

Anyway, to get a master list, create a calculation which concatenates your fields, something like: Field1 & ¶ & Field2 & ¶ etc. and use that field (called cCombine) as your value list (specify 'all values'). Attach this value list as checkbox to Field3. Then to get a comma-separated string of the selected values on Field3, create calculation (text)

Substitute ( Field3 ; ¶ ; ", " )

But it would help to understand the data and purpose behind your request because we might have better suggestions. Depending up on the type of data in the list, you might want to use a portal for selection purposes and, since you use vs. 11, you can filter the portal. So can you provide more information? :wink2:

[Vaughan]

I have several fields in my table that all should be selected from a master list of options.

It sounds as though you are tracking multiple occurrences of the same "thing" so they should be in a related table, not in multiple fields. A related table will enable duplicates to be avoided and the export to be created very simply.

[Philip Trauring]

Thank you. No problem explaining more. Basically the list is a list of names. There are over a thousand names, so I don't think checkboxes will work.

The database is an analysis of first names. Fields include the name in different languages (one value each), a field for related names (which might have more than one value) and a field for nicknames (which might have more than one value). I want to make sure that any name that shows up in any of these fields is in the main name list, which is a kind of mega-index of all the names, nicknames and variants that exist.

Thus, each time someone is working on the database, and wants to add a value to one of these name-related fields, I want to make sure that the value (or values) exist in the main name list (and if it doesn't, then let the person add the name to the main list before allowing it to be added as a value to a field).

[comment]

let the person add the name to the main list before allowing it to be added as a value to a field

This doesn't sound like a good method. Each name should be entered only once, into a master table of all names. with a type (name|nickname) and language indication. Links between related names should be recorded in a separate join table (assuming the relationship is many-to-many).

Alternatively, you could go with what you have now, and produce the index on demand by a series of scripted imports.

[LaRetta]

Hi Philip,

The developer consensus seems to be that the names should be ONE table with a 'type' to identify them, instead of several fields. But I still need clarification, if you wouldn't mind:

I have several fields in my table that all should be selected from a master list of options. In some cases the fields should only have one selection from that list, in some it might need more than one selection.

I still cannot understand what you are doing here. Are your Users talking to a person and they want to select many names to identify this person, such as "The person is Robert but goes by Robbie and sometimes Bob." So they want to select all three names for this person?

What do you mean by a comma-separated text string? Is this for display on each record? For display to send in an email of all the master names? If you using FileMakers index, you don't need a master list - you can concatenate as I suggested (even using the value list itself to get ALL names in a string). Do you want to export this list or what is its purpose? You say 'when done I want to present this information' but you don't indicate how it should be presented.

No, checkboxes won't work. But you can use a filtered portal to display the names for selection (or even filtered relationship or a 'portal checkbox' or clairvoyance; type a letter and the 'list' would filter down as you type) but again, we need to know the purpose (from User perspective) and whether the comma separated text string is for display of the names assigned to the current record or display of ALL master names or used for a comma-separated export.

If you use FileMaker's index, you can let Users add any name they wish and it will automatically appear in the master index - no need to stop them and make them go add a record into another table. This would be true whether you use fields or whether (as we all suggest) you use records.

I/we would be happy to provide a demo of either process once we understand your needs more fully. :wink2:

[Philip Trauring]

So the comment on how to structure the data is very interesting. It's been a long time since I worked on a relational database, and I'm definitely rusty. I need to start thinking relationally, and I'm not sure I'm there yet.

Let me a explain a little bit more. The final output for this project is probably InDesign. The database itself is what I can best describe as a research project into first names. The goal is to output a kind of first name dictionary which will be laid out in InDesign. My goal is to export my final data into XML and import that into InDesign. That's a whole different problem, but first I need to get the database completed.

I had looked at the first layout I put together, from which I enter most of the names as sort of the canonical view of the data, but perhaps that's the wrong approach. I've been calling this my Working Layout as it's where I've been entering all my data about the names. A given name might have several language versions, a list of related names, equivalent versions in the opposite gender, etc. In addition to what is a lot of one-to-one and one-to-many linking between the names in the master list, there is additional data that I've been entering which I've been adding to only the name which is the kind of headword - headname I suppose - in the dictionary. Thus many names in the master index would be directed to the headname - there is no reason to have separate entries for what is essentially many variants of the same name. Where I suppose it gets complicated is that a nickname might be used for more than one name.

So perhaps what I need to do is create the master list of names and add anything needed to determine what goes into my headname table (my working layout). Thus for the fields that include one or more names from the master name table, those fields will be automatically generated based on a series of scripts or calculations. I wouldn't mind if the fields that contain multiple names would be pre-formatted as comma-delimited, to make my life easier later when exporting and I will need to format them that way anyways.

Thus the headname table takes what it needs from the master name list table, and lets me add what I need to it for all the headnames. I will then be able to export the headname table to XML, or rather I would probably build a most export-friendly layout with exactly what I need to export, and use that instead for exporting.

On a related note, when importing multiple lists of names (from various sources) what happens when a field is set up to validate as unique, and you import a list of names that includes repeats of some of the names? Will it just ignore those, or will the import fail?

Thanks for everyone's help.

Philip