Join Tables in 7

[purplemaji]

Now that I have FMP7, we can finally make join tables where it makes sense to do so. For example, I have a Person table, and a Project table. Multiple people can be assigned to multiple projects. So here I added a PersonProject table, that relates the two based on the person_id and project_id.

So far, so good. Correct db structure, makes logical sense, and all that. Now, when actually using the database, I want a person to be able to:

a) add a new project to their list

add an existing project to their list.

The first is fairly trivial, I've been able to do it in two methods, by setting the relationship up to create from left to right in both relationships, a user can just type into a portal to create a new project, which is cool. The second uses a script to take the user to the project layout and fill in info for the new record and then adds the appropriate join.

Now, my question is, how can I go about adding an existing record? Anyone have any advice on this, or working with join tables in FMP7 in general?

Thanks in advance,

-PM

[KurtW]

Not sure if this is what you are going for, but could you populate an edit field with all existing projects, then have a user pick an exisiting project from that field to add to their project list?

Kurt

[hayesk]

Whenever I make a table, I always make a field that always contains "1". (or any constant, it doesn't matter). Then when I need to relate to *all* records, I make a relation on that field in each table.

To add an existing project to a person(or vice versa), make a new layout based on the join table that contains a portal containing all of the projects. Then make a script that pops up a new window containing that layout (use "Go To Related record" so the current person is selected), allow the user to scroll through (or search) for the project, and then select it, and add it to the join table, and close the window.

To add an existing person to a specific project, do the reverse.

There are other ways to do what you want. This is just how I would do it - the portal allows me to put more fields in the layout so the user has more to go on than just the project title.

[Kenoli]

I've been trying to do a similar thing. You can create a value list made up of the "project" field. Then under the format menu in layout mode with the "project" field selected select "field format." It will let you asign the "project" value list you have selected to the field. Select "include other item..." in that menu. This will give the user a pull down menu allowing them to choose an existing project or enter a new one.

This works erratically with fields in a portal. It lets you choose an existing project, but not enter a new one. I think the problem here is that when doing this through a portal, a new record doesn't get created in the linking table. I haven't actually figure out how to deal with this. I think it will require using a script to initiate the new record.

--Kenoli

[-Queue-]

Note the the constant 1 field for 'global' relationships is obsolete in version 7. The Cartesian join operator (X) will link any two fields 'globally'.

[bruceR]

Now that I have FMP7, we can finally make join tables where it makes sense to do so. For example, I have a Person table, and a Project table. Multiple people can be assigned to multiple projects. So here I added a PersonProject table, that relates the two based on the person_id and project_id.

So far, so good. Correct db structure, makes logical sense, and all that. Now, when actually using the database, I want a person to be able to:

a) add a new project to their list

add an existing project to their list.

The first is fairly trivial, I've been able to do it in two methods, by setting the relationship up to create from left to right in both relationships, a user can just type into a portal to create a new project, which is cool. The second uses a script to take the user to the project layout and fill in info for the new record and then adds the appropriate join.

Now, my question is, how can I go about adding an existing record? Anyone have any advice on this, or working with join tables in FMP7 in general?

Thanks in advance,

-PM

Nothing prevented you from doing any of this in FM6 or FM5, though the convenience of having multiple tables in a single file is great. And the methods would all be the same - except that now you do have a new option to open a "project picker" window based on the same file. You have several possible ways of doing this. One is with a portal that shows all projects, when you click a record in the portal your script captures the ProjectID and uses it to create a new join record.

[purplemaji]

Thanks for the feedback.

Yes, you could do this in previous versions of FMP, but being able to reference fields more than one table away is what really lets the join be functional, in my opinion.

Anyway, what I've done is when you click a button on the person layout, it opens up a project list. When you click a project, it grabs the id of that project, and then creates the join record.

I have a portal in the Person layout that lists all current projects. I would like to have a button to remove a project from that person's list. How can I capture the ID of the clicked on portal row, and pass that in as a parameter?

Thanks again everyone, it is good to get some different points of view on these things. 7 is new all over again.

-PM

[bruceR]

Any time you click a button on a portal row, FileMaker "knows" which one you are on. So your script parameter would just be relation::recordID. This is the same thing that you would do in all prior versions of Filemaker, except in prior versions you had to set a global with the value: set field [gRecID, relation::recordID]. And of course you can still use the global if you want.