Discovering FM and struggling with this situation:
People table: last_name, first_name, address, etc.
Role table: librarian, reader and driver, etc. (little list)
One person can have several roles; one role can be assigned to several persons.
I tried a joint table. Seems ok till I create a portal in the People layout and add a checkbox with my Role values from the Role table.
Many hours on this. My joint table refuses to add one row per couple (id_people, id_role). Often, I cannot fill in the checkbox (layout blocked). Last but not least, I am not confident with my understanding of the difference, in the inspector, between "display data from" and "Values from".
Any suggestion welcome. Could be a direct answer to my problem or a suggestion for a tutorial or other ressource.
The joint table is the solution, where you have a (single) record with id_people and id_role. I say a single because having more than one record with the same id_person and id_role would not serve any purpose. That we may take care later.
This is not what is expected. You are talking about checkboxes, it would help if you would show the Layout used to assign role(s) with to a person. Another thing that would help us help you would be to add a screenshot of the Relationships Graph as well as the definitions for the relationships.
Display data from is used to set which field is displayed in a control on the Layout. To each Layout, a TO (Table Occurrence) is selected. A TO is a Table shown on the Relationships Graph. A TO is an Object that represents a specific Table, and a TO has a name. A TO is some kind of alias for a Table.
Values from is used to specify the set of values that the contents of the field is restricted to. Those values come form what is called a Value List.
I have introduced in my reply a few concepts that may be new to you. On the Claris site there are some tutorials available. Log on to the Community and click on the Academy link at the top
The relationship graph is sound, although I would delete records of the join table regardless if a contact or categorie is deleted.
The checkbox approach will not work because a checkbox set looks for the data within a field, not in a set of records. I suggest you change the checkbox for a pop-up menu based on the values of a table. In this case, the values would be the IDs of the categorie table. Display the role instead of the id field.
Things are going better with the pop-up menu at condition that I do not follow @bdbd's suggestion to have the IDs of the categorie table as values. I get a result (I can fill in the pop-up menu with the role values) when the values come from the role fields.
What does annoy me: ievery time I fill in the pop-up menu, a new record is created in the categorie table (see screenshot). I do not want this. I do not want my three unique list values (Lecteur, Resp. Transferts, Resp. Senebier) having as many IDs as items filled in with help of the pop-up menu.
How can I get my categorie list limited to 3 records only? To put it another way, how can I get my participant_categorie table (join table) with the field categorie_id having only three possible values?
You must be creating records there instead of in the join table.
Ensure that the portal is based on the join table. The fields should both be from the join table. The value lists should be based on the data from contacts and categories.
Keep in mind you want to create join records when you assign a role to a person. Your portal therefore displays records primarily from the participant_categorie table.
Your participant_categorie table needs only contain two fields: contact ID and categorie ID. The ID field is redundant, as the id_contacts and id_categorie field pair make a unique ID in and of themselves.
Note that the id field of the categorie table is stored in the join table and not the role field. That is why I mentioned you want the value list of the pop-up menu to be based on the ID field and not the role field. You want the user to select the ID. On the other hand, you want to display the role when the list is displayed as this is more user readable.
Your relationship graph allows the creation of related records, so when a user selects a categorie ID via the pop-up menu, a record will automatically be created in the join table for the contacts record being displayed. The only left to do is to ensure a user can't select the same categorie more than once for a given contacts.
Thanks @Malcolm@bdbd
I got the right result with the below config (screenshot). @bdbd: I will have to read carefully your last post. My next challenges, indeed:
ensure a user can't select the same categorie more than once for a given contact (as you mentioned)
ensure a user can delete a categorie when he selects too many
@bdbd and @planteg , why are you suggesting a join table? it seems as though a single field with a checkbox list would be adequate? Is it time to reconsider the advice?
Mostly because @silf started off with a join table and didn't go into details as to why he / she used a join table. There can be many reasons for going that route and I made no assumption of his current or future needs. I believe the join table approach is, in the long run, the most flexible approach.
While I wrote you only need two fields in the contacts_categorie table, you can have more and there are often good reasons to have more. For example: you may want to know when the role assignment is effective… so add an effective date field; perhaps one person is the lead for a given role… so add a lead checkbox field; etc.
Well, using a field in the Contact table, controlled by the Category value list ensures that you get points 1 and 2. You need to add some user controls to add new categories, but it's faily easy to do that.
There is no way to define a multi-field primary key in FileMaker. The workaround: create an auto-entered field that computes the primary key and then validate that its result is unique.
Make a deletion button that deletes the portal row record.
Make a layout based on the categorie table where you can add and delete records via buttons or the menu. Categories live in this table and this is the table that populated the categories value list.
--
As to your comment about efficient ways of easily finding solutions… ask a consultant.
Finding the solution elsewhere means you didn't do it. Someone else did. You can certainly re-use someone else's solution. It will speed up your development. The price may be that you won't master the platform or the techniques. Avoid this pitfall by ensuring you understand the solution you are re-using. As a newbie, you need to educate yourself. Heck! Even us pros need to constantly educate ourselves.
FileMaker's help files should go a long way to helping you understand the platform and its vocabulary. You also need to educate yourself about database theory, databases in general and programming techniques, be they generic or FileMaker-specific. There are schools, manuals, websites and other resources that can help you there.
Then there are resources like, <ahem>, The Soup where people are happy to help.
Lastly, you need to gain experience. There is no workaround or substitute. You will need to sweat some to get better and, more importantly, to understand what you are doing. Hopefully you like this enough that it will all be fun.
@Malcolm's suggestion, using a field instead of a join table, has its merits. It simplifies a lot of things but, as with all design decisions, it comes with its limitations. If you don't intend on adding information to the assignment of roles to contacts (an effective date field, a lead checkbox, etc), then I think the field approach is the better option instead of the join table approach.
As a newbie here, I am not authorized to send '@'words more than to two people; so, please, tell planteg that my gratitude goes to him also.
As to your comment about efficient ways of easily finding solutions… ask a consultant.
@bdbd If I were reasonable and modest enough, I would certainly ask a consultant...
[1] A friend of mine just told me: "The problem with asking for help in a forum is that you need to say 'thank you' every ten seconds." (this was a joke from my friend, but a meaningful one)
I got your gratitude reading your post. The way the forum works is once one post a reply, one is notified he has an unread post.
It is true that having a consultant acting as a coach can help a lot. One f these days I should write a post on Value Lists, the dos and donts. When you talk about about a user being able to add roles, do you mean for a company superuser being able to edit the roles list. If so I have created a way to manage these kind of Value List that don't break things and let you generate reports that can include roles - or anything else - not existing anymore. This way the past is not lost !
When you talk about about a user being able to add roles, do you mean for a company superuser being able to edit the roles list.
In my wished system, there will be a majority of let's say client contacts. And a minority of contacts who will be interacting with the system to manage movements of books from one place to the other. 'Role' will be used to differnciate the categories of contact.
You said you were interested in learning more about value lists. Here are links to a couple of videos I've made on related value lists which you might find helpful
You'll find quite a number of other videos on FileMaker™ on my channel.
As to not allowing a role to be selected twice, the way I do it is to create a calculation which lists all of the selected id fields. I then use that in a 2nd relationship to exclude ones that have already been selected. In the portal, you would have two versions of the same field; one with all values and ones with values that have not been selected. The first one shows when no values have been selected and is hidden as soon as one is; the 2nd field is the opposite. (I have an Example file somewhere; I'll try and find it and then post it here).
