How to setup a join table in FileMaker Pro:
For our example we will discuss a typical many-to many-scenario: Students and Classes. We will have a Student table that contains a record for each student and a Classes table that contains a record for each available class. The primary key field StudentID uniquely identifies a student in the student table. Likewise, the primary key field ClassID uniquely identifies a class in the class table.
A student can take many classes and a class can have many students. Related these two tables together would be difficult without the use of a join table. The join table will contain the primary keys from the student table and the class table. The join table can be called anything you want. For this example we will call the join table “signups.”
- Create a new table in FileMaker Pro and call it "Signups"
- In the “SignUps” table, create a primary key field called “SignupsID.” This step is not necessary for the join table to work properly but it is good relational database practice for every table to have a primary key.
- In the "SignUps” table, create, at least, a “StudentID” field and a “ClassID” field. Since these fields will be populated when records in either the “Students” or “Classes” tables are created, no auto-enter field options are needed.
- Additional fields can be added to the “Signups” table as necessary. Some examples of other fields that might be appropriate for the “Signups” table would be a “date” field to keep track of when someone started a class or a “cost” field to keep track of how much someone paid to take a class. Join tables typically hold fields that might not make sense to have in any other table.
- Relate the tables together based on the primary and foreign keys
If a student signed up for three classes, a student would have one record in the Students table but would have three records in the “Signups” table – one for each class he or she signed up for.
A by-product of utilizing a join table is that you are able to access fields and data across tables without having to create a separate relationship. In our example, it would be very easy to display information from the Students table in the Classes table if need be and vice versa.
So it's the fact that I have two tables, with a primary key that links each, and not a third join table with a foreign key that it's not working?
If I set it up like your example, my existing calculation will work as desired in my OP?
Message: "Please Confirm these are the images you want used in the layout: " & PhotoPermissions_Photos::Photo_ID
You won't need a join table unless you need to link the same photo ID to multiple people. Here's a calculation that will allow you to include the entier list of photo ID's from the related table in the body of your email:
Substitute ( List ( PhotoPermissions_Photos::Photo_ID ) ; ¶ ; ", " )
The Substitute worked fine. Thank you.
I'd like to extend it a bit more however.
At the very least add a carriage return in the calculation.
Your images are ready.
Please Confirm these are the images you want used in the layout: 555_123, 555_456, 555_789, 555_999
Ideally, I'd like to include a URL in front of the returned records.
Thanks again for getting me this far.
Your images are ready.
Please Confirm these are the images you want used in the layout:
The new version no longer needs the substitute function as you need those returns back.
Add a calculation field, cURLs in the PhotoPermissions_Photos table as:
"URL Here" & PhotoID
Then you can use List ( PhotoPermissions_Photos::cURLs ) to produce the list of URL's shown in your last example.
The URL can be literal text in the calculation or your calculation can refer to this text in a field. The field may be global or in a related table. Using a field allows you to update the URL used without opening up the calculation and redefining it--now a regular user can update it instead of just the developer.
Manage>Database>PhotoPermissions_Photos>Fields>cURL (Type: Text)>Options>Auto-Enter>Calculated Value>Specify "http://www.mydomain.com" & PhotoID
Edit Layout>PhotoPermissions>Email Field>Button Setup...>Send Mail>Specify>Message>Substitute ( List ( PhotoPermissions_Photos::cURL ) ; ¶ ; ", " )
I also tried List ( PhotoPermissions_Photos::cURL ) with no luck.
I'm thinking I setup the calculated field wrong?
"With no luck" doesn't tell me how it failed.
What do you see in cURL?
I'd use a field of type calculation instead of a text field for this so that I don't have to go back and update the field's value for all my existing records, but you can get FileMaker to update the values if you want to: Updating values in auto-enter calc fields without using Replace Field Contents
Specifically, when I create a new email by clicking on the email address in the Layout, it produces an email, but the Message Body is empty.I changed the field from Text to Calculation and now get the results, but I still don't know how to code a line break (return). It looks like this now.
Your images are ready.Please Confirm these are the images you want used in the layout:http://www.mydomain.com/images/555_123, http://www.mydomain.com/images/555_456, http://www.mydomain.com/images/555_789,http://www.mydomain.com/images/555_999
Do not use the substitute function. It turns the line breaks into commas.
List ( PhotoPermissions_Photos::cURLs )
to produce the list of URL's shown in your last example.
Got everything working!
Thanks for working with me.