It sounds like it would be a better idea to have a single Name table to enter and store names, and link that as necessary to the original table(s). Then you can get your value list from a single field in a single table.
You can create a calculated field (e.g. cAllNamesUsed) that combines the 14 separate fields into a List. Use this cAllNamesUsed field as the source of the Value List.
(and so on)
This assumes that the names are all coming from different fields in a single table.
If you have names scattered into fields in different tables, your only choice may be the recommendation from erolst to consolidate the names into a field in a single table.
thanks! Yes, all the text fields for the names are in the same table. I never worked with calculated fields before, so I have to check how to do this. How do I connect the fields in the calculation window? I tried placing them on their own line, and also using the AND command between the fields but that doesn't do it. in the image below, I would like to combine all the Character fields into a single list, for that I made the cAllCharacters list as you suggested. So now I need to fid the correct way combining them.
Thanks, i will give this a try. I am still new with filemaker so I have to check on even the basics a lot. I did some Lynda.com training courses and have a slight grip o the basis though.
The correct format to enter into the calculation box is
List(AntagonistName; Character1; Character2; Character3; (continue this for all character fields) )
To make it more readable you can break it onto separate lines. The key is to use the List() function and separate each field with a semicolon as the input values for that function.
... and so on
Looking at your table structure, you would be much better served to learn about using multiple tables. Your data begs to have a separate table for Characters where each character is a separate record instead of having each character in a separate field.
Thanks, I am getting close to what I want. I now get a drop down menu with the names of the fields I filled in somewhere else which is what I wanted, but cannot select anything from the menu, trying this gives the error message:
Your other suggestion to make a separate table for characters only, is something I tried before but couldn't not get it to work.
1 of 1 people found this helpful
The "field is not modifiable" indicates that the field where you are doing data entry is... not modifiable. The Value List and the menu appears fine (maybe just appears, more on that later).
Examine the field that you are trying to edit using the Value List. Is it perhaps the new, calculated field? It shouldn't be. The menu created by the Value List should be applied to a new field that is of type Text.
Note that the Value List will be pulling names from all Character fields in all records. Is that what you actually want? Is each record in this table for an independent work that has its own characters? Characters from other records will appear in this menu.
"The "field is not modifiable" indicates that the field where you are doing data entry is... not modifiable. The Value List and the menu appears fine (maybe just appears, more on that later).
Examine the field that you are trying to edit using the Value List. Is it perhaps the new, calculated field? It shouldn't be. The menu created by the Value List should be applied to a new field that is of type Text."
thanks, i used indeed the calculated list, so I made a new text field as you suggest, this works now as I wanted. The name appear in the menu and I can choose one from the list.
Making a new record does indeed show the names from the other record, which is not a disaster, but it would be nice to have an empty list when starting a new record. Every record represents a new novel with its own characters. So I think I should find a different way to populate the value list?
From the info so far, I'm guessing that you have
- One record in "The 4 Questions" per literary work
- Each literary work has a list of characters
- The record in "The 4 Questions" has at least one field that requires you to choose a character in the literary work
This scenario needs
- Table "The 4 Questions"
- Table "Characters"
The 4 Questions
questions_id (primary key unique to each record, e.g. auto-enter serial number)
character_id (primary key unique to each record, e.g. auto-enter serial number)
fk_questions_id (the id of the literary work record in "The 4 Questions")
In the relationship diagram
[The 4 Questions] <--- = ---> [Characters]
where the = connects questions_id to fk_questions_id
You probably want to check the box on the Characters side to "Allow creation of records via this relationship".
Put a Portal onto The 4 Questions layout containing the related field Characters::CharacterName and any other fields you want to edit in the Characters record.
You can then add characters to the literary work by entering them directly in the Portal in the blank row. The records in Characters will be created automatically.
You can then make a Value List to
Use values from field Characters::CharacterName
Include only related values starting from The 4 Questions.
This will generate a Value List you can use on The 4 Questions layout. It will be populated with the Characters that are in the Portal for that record. It will not show characters that are on other The 4 Questions records.
Thanks Tom. I learned about using relationships in the Lynda course but didn't think I needed this, because my project isn't that complicated. I think it's a good idea to adjust these elements and get a better organization. Right now all my fields are in a single Table.
1 of 1 people found this helpful
An alternate (more elaborate) way of using the Value List and filling in fields on The 4 Questions that require character responses is to respond with the ID of the Characters record instead of a copy of the CharacterName. The fields in The 4 Questions would then be
fk_Antagonist_id (foreign key of Characters record)
These would be values of the Characters::character_id field.
You would need to create additional Table Occurrences of Characters on your relationship diagram to connect these to instances of that table.
Your Value List would Use values from first field Characters::character_id and also display the second field Characters::CharacterName.
This type of structure would be used if you anticipate editing the CharacterName after using it for data entry filling in other fields on The 4 Questions. The records would be linked so the edits in Characters would automatically show up in the fields in The 4 Questions.
Thanks, although the database works now as I hoped for, I will fine tune it using your suggestions. I see my first setup as the 1st draft, now I will start with the 2nd and if needed a 3rd till I am happy with the result.