Build a relationship
To build this relationship, you either need to be in the Table "Customers" a field for the article number, which only allows one article per customer, or in the Articles table a field for a customer number, which also only allows one customer per article. However, this type of unique relationship (1:1) is not always desired. A possibility is therefore sought for a 1:n relationship in which a customer points to several articles, or the same article points to several customers. To implement this type of relationship in FileMaker Pro, you need another Reference table. In this article, I describe how such a solution can be implemented in a FileMakerDatabase can be implemented.
I will call this reference table "Item to customer". This table initially only has two fields: "_reCustomers" and "_reArticles", which are related as shown in the diagram (Fig. 1). The "Article selection list" table is not another FileMaker Pro table but a view of the Articles table, as can be seen very clearly in the "Appearance in diagram" column in the table definition.
Why do I need the "Item selection list" table? Well, the "Articles" table is related to the "Articles for customers" table and can be used as a Value list within the relationship, but I need a value list to select the items, so there is the "Item selection list" table, which is the data basis for a selection list.
Attitude for relationships
Placement as a section in the layout
The "Articles for customers" table is displayed as Excerpt in the Customer layout. The following fields are inserted in the section.
- _reArticle, the field originates from the table "Customers to article" and is the reference field of the table to the reference table "Article", the contents (articles) are entered via a value list as "Fade-in list" selected.
- df_Bezeichnung, the field comes from the "Article" table and is a result of the relationship.
- df_Description, the field comes from the "Article" table and is also a result of the relationship.
No data field needs to be placed for the relationship to the customer because the relationship is defined in such a way that new data records in the portal automatically receive the reference value and the table is placed as a portal in the Customer layout, which always provides the reference value for the current data record. See Fig. 2 for the settings of the relationship.
Value list for article selection
Structure of the value list for the _reArticle field in the "Article for customer" section.
As already mentioned, the Item table cannot be used as a source for the value list because it is itself part of the relationship and therefore only provides values as soon as the relationship contains a valid value. The occurrence of the Item table in the diagram under the name "Item selection list" is therefore used for the value list.
Create a new value list, menu bar, File, Manage, Value lists. Click the "New" button in the dialog box that appears. I have attached the following two screenshots to show you how to proceed.
Layout in browse mode
With the relationship created, any number of articles can now be assigned to each customer. The "Customers to article" table also provides any number of customers for an article.
Jens Liebelt, April 2011.
Liebelt Polaris specialist components
Main street 23
21646 Holvede
- FileMaker databases
- Dynamic websites with FileMaker
- E-commerce with FileMaker
- FileMaker as NewsBlog System
- FileMaker distance learning, with free time management
- FileMaker Support via TeamViewer
Hello Markus
The title is misleading because it is about an m:n relationship that is split into two 1:n relationships in FileMaker by creating a crosstab.
The relationships in your first section are not 1:1 relationships, but 1:n relationships, as the same article could be in several persons and one person could be in several articles.
Just because :-)
How do you do?
This article has helped me - thank you! Two questions remain:
(1) I didn't quite understand why you have to have a table appear again in the diagram to use it as a value list. In my crosstabs, I directly specified fields from tables that were part of the relationship and got correct values.
(2) I have three tables that I want to join using three crosstabs [+ two more for self-joints, which I will leave out here].
The tables are called, for the sake of clarity, "Facts" (A), "People" (B) and "Places" (C). The linking scheme is as follows:
A x B, A x C; B x A, B x C; C x A, C x B - each with each (see image with circled 'problem relationship') https://www.dropbox.com/s/q2y9m79bpz0pdt9/Beziehungen.png?v=0mcn). The problem: all these relationships work perfectly except for the C x B link - here the relationships between the data records are not entered correctly and are also not displayed correctly in the section area. Is it because each table is linked to each other?
Many thanks for your help
Moritz
Hello
However, if you now delete an article from the section again, only the field value of _reArticle is deleted in the reference table "Articles for customers", but not the complete data record...
How can this be changed? Or is it a tragedy if such "half" data records remain in the reference table "Articles for customers"?
For all those who have the same question as John - you can delete the entire data set by adding a button in layout mode with the key tool with the function "Delete clipping row".