Done a lot of database work for websites with MySQL, and just got acquainted with Filemaker. And everything seems suddenly much more complicated...

Here's the aim.

Made a "Productions" database with information on films. Got a "contacts" database elsewhere. One film can be directed by several directors. One director can work as director on many films, but also as editor on others, and so on.

Here's an organisation.

Created two other databases:

- professions (director, editor, etc. with minimum wages and stuff)

- contacts_productions (links a profession ID, a contact ID and a film ID)

Now how can I allow users, in the "productions" layout, to choose one or several directors, editors, etc. from the list of contacts, ideally from a auto-completed list?

The result should look like this:

Director(s):

[contact1]

[contact2]

(...)

[contactn]

Same for editor, chief operator, etc.

Thanks for helping a newbie out...

Try putting the assignments in a portal of Contact_Production. For each portal row, you'll need to assign one Contact and one Profession, probably by choosing from a value list of each. Be sure to set and select the IDs rather than the corresponding names. The relationship between Production and Contact_Production should have 'Allow creation of records in this table via this relationship' selected on the Contact_Production side, so that record creation can be done through the portal.

I don't think you'd make a choice in the Productions per se. Because, as you said, there can be more than 1 director per production. So you would make in Contacts_Productions (CP). Which could be in a Portal in Productions (a portal is a window into another table, using a relationship; you'll want to use them).

The way you have it set up (which is reasonable), you could only filter the contacts by profession for existing records in CP. Because these are records attached to a file. You do not have an actual "Contacts_Professions" join table, where you would pre-enter combos for a person. So, unless a person was a director on a previous film, you are going to have to enter the director ID some other way the 1st time for him.

So, what you're looking for is all people in CP that are previously directors. This would mean a self-relationship on ID Production. Then a Value List based on that self-relationship, starting at CP. You would have to choose the ID Profession first.

The field would be the ID Contact. But you would also "Show values from a 2nd field". That field would be from the Contacts table (which is 1 step away, but a valid logical relationship, 1-to-1 with ContactID). You would want a concatenated calculation field in Contacts of the full name, probably LastFirst (since you only get to show 1 field). You could show the ID, but Sort by the 2nd field. Or you could hide the ID and "Show values only from 2nd field."

Thanks a lot to both for your answers.

I had tried the portal solution, but it meant that users chose by themselves the profession field, and this is not suitable, as users should be told which professions must be assigned to contacts, and ideally be able for other professions (as the profession list can change from a production to another: special effects, etc.) to freely relate a profession (chosen in an open list) to a contact by themselves.

As for the self-relationship solution, what if I need to add a new director that was not previously listed in Contacts_Productions? And what kind of field should I use in the "productions" layout to choose several directors, authors, editors? Besides, wouldn't I need to create a TO for each profession?

I just don't understand why this seems so complicated. In PHP/MySQL, I would add a hidden field with profession=director as value. And then I'm done. If this kind of hidden "default value" had been possible with portals in Filemaker, then one portal per profession would have solved the problem.

Or perhaps I'm missing something there?

You can add a few static profession IDs keys in your Production table and make relationships using these keys. I.e. you'll make a relationship

Production::ID Profession Director = Contact Production::ID Profession

Production::ID Production = Contact Production::ID Production

Make as many static keys and as many relationships as you need and place these portals on the Production layout. Set the relationships to create records automatically.

Now this works! But...

For this relationship to work, I need to create new TOs of "contacts_productions" based on the same relation.

Here's what I get:

But then I need as many TOs as there are professions... And strangely I cannot edit any other portal than the directors' one: I get an error message saying I must assign a "correct value" to Contact_productions Auteurs. But the global value I inserted seems correct.

Besides I dunno how to make an auto-complete field out of these portal rows, so directors' names (or editors', etc.) are completed as we type them in (from the whole "contacts" table), and not chosen from a long pop-up menu.

Edited August 15, 200619 yr by Guest