{"id":685,"date":"2011-04-05T09:31:04","date_gmt":"2011-04-05T09:31:04","guid":{"rendered":"https:\/\/gofilemaker.de\/?p=685"},"modified":"2022-03-29T07:33:46","modified_gmt":"2022-03-29T06:33:46","slug":"1n-relationships-example-customers-and-articles","status":"publish","type":"post","link":"https:\/\/gofilemaker.de\/en\/2011\/04\/1n-relationships-example-customers-and-articles\/","title":{"rendered":"1:n relationships in FileMaker Pro using the example of customers and articles"},"content":{"rendered":"<h2>Build a relationship<\/h2>\n<p><img decoding=\"async\" class=\"alignleft size-full wp-image-708\" title=\"1:1 relationship in FileMaker Pro using the example of a customer-article database.\" src=\"https:\/\/gofilemaker.de\/wp-content\/uploads\/artikelbild_1n_beziehung_filemaker.jpg\" alt=\"1:1 relationship in FileMaker Pro using the example of a customer-article database.\" width=\"202\" height=\"160\"><\/p>\n<p>To build this relationship, you either need to be in the <a href=\"https:\/\/gofilemaker.de\/en\/glossary\/table\/\" target=\"_self\" title=\"What is a table in FileMaker? A table is used in FileMaker...\" class=\"encyclopedia\">Table<\/a> \"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 <a href=\"https:\/\/gofilemaker.de\/en\/glossary\/reference-table\/\" target=\"_self\" title=\"What is a related table in FileMaker? A related table is a...\" class=\"encyclopedia\">Reference table<\/a>. In this article, I describe how such a solution can be implemented in a FileMaker<a href=\"https:\/\/gofilemaker.de\/en\/glossary\/database\/\" target=\"_self\" title=\"What is a database? A database (database system, DBS) is understood to be...\" class=\"encyclopedia\">Database<\/a> can be implemented. <!--more Weiterlesen &gt;&gt;&gt;--><\/p>\n<p>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.<\/p>\n<figure id=\"attachment_686\" aria-describedby=\"caption-attachment-686\" style=\"width: 397px\" class=\"wp-caption alignnone\"><img decoding=\"async\" class=\"size-full wp-image-686\" title=\"Relationship\" src=\"https:\/\/gofilemaker.de\/wp-content\/uploads\/Beziehung.png\" alt=\"FileMaker relationships in the diagram\" width=\"397\" height=\"323\" srcset=\"https:\/\/gofilemaker.de\/wp-content\/uploads\/Beziehung.png 397w, https:\/\/gofilemaker.de\/wp-content\/uploads\/Beziehung-300x244.png 300w\" sizes=\"(max-width: 397px) 100vw, 397px\"><figcaption id=\"caption-attachment-686\" class=\"wp-caption-text\">Fig. 1 Structure of the relationships<\/figcaption><\/figure>\n<p>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 <a href=\"https:\/\/gofilemaker.de\/en\/glossary\/value-list\/\" target=\"_self\" title=\"What is a value list in FileMaker? A value list is a list of values in...\" class=\"encyclopedia\">Value list<\/a> 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.<\/p>\n<figure id=\"attachment_8342\" aria-describedby=\"caption-attachment-8342\" style=\"width: 468px\" class=\"wp-caption alignnone\"><a href=\"https:\/\/gofilemaker.de\/en\/filemaker\/training-coaching\/\"><img decoding=\"async\" class=\"wp-image-8342\" src=\"https:\/\/gofilemaker.de\/wp-content\/uploads\/468x60r_training_coaching.gif\" alt=\"FileMaker Training and Coaching\" width=\"468\" height=\"60\"><\/a><figcaption id=\"caption-attachment-8342\" class=\"wp-caption-text\">Display<\/figcaption><\/figure>\n<h2>Attitude for relationships<\/h2>\n<figure id=\"attachment_688\" aria-describedby=\"caption-attachment-688\" style=\"width: 633px\" class=\"wp-caption alignnone\"><img decoding=\"async\" class=\"size-full wp-image-688\" title=\"Customer relationship\" src=\"https:\/\/gofilemaker.de\/wp-content\/uploads\/Kunden.png\" alt=\"\" width=\"633\" height=\"578\" srcset=\"https:\/\/gofilemaker.de\/wp-content\/uploads\/Kunden.png 633w, https:\/\/gofilemaker.de\/wp-content\/uploads\/Kunden-300x273.png 300w\" sizes=\"(max-width: 633px) 100vw, 633px\"><figcaption id=\"caption-attachment-688\" class=\"wp-caption-text\">Fig.2 Setting the relationship \"customer : article to customer\"<\/figcaption><\/figure>\n<p>&nbsp;<\/p>\n<figure id=\"attachment_689\" aria-describedby=\"caption-attachment-689\" style=\"width: 633px\" class=\"wp-caption alignnone\"><img decoding=\"async\" class=\"size-full wp-image-689\" title=\"Fig.3 Relationship detail article\" src=\"https:\/\/gofilemaker.de\/wp-content\/uploads\/Artikel.png\" alt=\"Fig.3 Relationship detail article\" width=\"633\" height=\"578\" srcset=\"https:\/\/gofilemaker.de\/wp-content\/uploads\/Artikel.png 633w, https:\/\/gofilemaker.de\/wp-content\/uploads\/Artikel-300x273.png 300w\" sizes=\"(max-width: 633px) 100vw, 633px\"><figcaption id=\"caption-attachment-689\" class=\"wp-caption-text\">Fig.3 Setting the relationship \"Article : Article to customer\"<\/figcaption><\/figure>\n<h2>Placement as a section in the layout<\/h2>\n<p>The \"Articles for customers\" table is displayed as <a href=\"https:\/\/gofilemaker.de\/en\/glossary\/excerpt\/\" target=\"_self\" title=\"What is a portal in FileMaker? A portal is used in FileMaker...\" class=\"encyclopedia\">Excerpt<\/a> in the Customer layout. The following fields are inserted in the section.<\/p>\n<ol>\n<li>_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 \"<a href=\"https:\/\/gofilemaker.de\/en\/glossary\/drop-down-list\/\" target=\"_self\" title=\"What is a drop-down list in FileMaker? A drop-down list is a list of...\" class=\"encyclopedia\">Fade-in list<\/a>\" selected.<\/li>\n<li>df_Bezeichnung, the field comes from the \"Article\" table and is a result of the relationship.<\/li>\n<li>df_Description, the field comes from the \"Article\" table and is also a result of the relationship.<\/li>\n<\/ol>\n<p>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.<\/p>\n<figure id=\"attachment_692\" aria-describedby=\"caption-attachment-692\" style=\"width: 606px\" class=\"wp-caption alignnone\"><img decoding=\"async\" class=\"size-full wp-image-692\" title=\"Layout view\" src=\"https:\/\/gofilemaker.de\/wp-content\/uploads\/Layoutansicht.png\" alt=\"Customer layout in layout mode\" width=\"606\" height=\"531\" srcset=\"https:\/\/gofilemaker.de\/wp-content\/uploads\/Layoutansicht.png 606w, https:\/\/gofilemaker.de\/wp-content\/uploads\/Layoutansicht-300x262.png 300w\" sizes=\"(max-width: 606px) 100vw, 606px\"><figcaption id=\"caption-attachment-692\" class=\"wp-caption-text\">Fig. 4 Section in the customer layout<\/figcaption><\/figure>\n<h2>Value list for article selection<\/h2>\n<p>Structure of the value list for the _reArticle field in the \"Article for customer\" section.<\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n<figure id=\"attachment_693\" aria-describedby=\"caption-attachment-693\" style=\"width: 491px\" class=\"wp-caption alignnone\"><img decoding=\"async\" class=\"size-full wp-image-693\" title=\"Value list Selection of articles\" src=\"https:\/\/gofilemaker.de\/wp-content\/uploads\/Werteliste_1.png\" alt=\"\" width=\"491\" height=\"403\" srcset=\"https:\/\/gofilemaker.de\/wp-content\/uploads\/Werteliste_1.png 491w, https:\/\/gofilemaker.de\/wp-content\/uploads\/Werteliste_1-300x246.png 300w\" sizes=\"(max-width: 491px) 100vw, 491px\"><figcaption id=\"caption-attachment-693\" class=\"wp-caption-text\">Fig. 5.1 Value list for the selection of articles<\/figcaption><\/figure>\n<p>&nbsp;<\/p>\n<figure id=\"attachment_694\" aria-describedby=\"caption-attachment-694\" style=\"width: 591px\" class=\"wp-caption alignnone\"><img decoding=\"async\" class=\"size-full wp-image-694\" title=\"Value list - field selection\" src=\"https:\/\/gofilemaker.de\/wp-content\/uploads\/Wertelsite_2.png\" alt=\"\" width=\"591\" height=\"500\" srcset=\"https:\/\/gofilemaker.de\/wp-content\/uploads\/Wertelsite_2.png 591w, https:\/\/gofilemaker.de\/wp-content\/uploads\/Wertelsite_2-300x253.png 300w\" sizes=\"(max-width: 591px) 100vw, 591px\"><figcaption id=\"caption-attachment-694\" class=\"wp-caption-text\">Fig. 5.2 Field selection for the value list<\/figcaption><\/figure>\n<h2>Layout in browse mode<\/h2>\n<figure id=\"attachment_695\" aria-describedby=\"caption-attachment-695\" style=\"width: 603px\" class=\"wp-caption alignnone\"><img decoding=\"async\" class=\"size-full wp-image-695\" title='The &quot;Articles for customers&quot; table in the layout' src=\"https:\/\/gofilemaker.de\/wp-content\/uploads\/Das-Layout.png\" alt=\"\" width=\"603\" height=\"516\" srcset=\"https:\/\/gofilemaker.de\/wp-content\/uploads\/Das-Layout.png 603w, https:\/\/gofilemaker.de\/wp-content\/uploads\/Das-Layout-300x256.png 300w\" sizes=\"(max-width: 603px) 100vw, 603px\"><figcaption id=\"caption-attachment-695\" class=\"wp-caption-text\">Fig. 6 The \"Articles for customers\" table in the Customers layout<\/figcaption><\/figure>\n<p>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.<\/p>\n<p>Jens Liebelt, April 2011.<\/p>","protected":false},"excerpt":{"rendered":"<p>Beziehung aufbauen Um diese Beziehung aufzubauen, ben\u00f6tigt man entweder in der Tabelle \u201eKunden\u201c ein Feld f\u00fcr die Artikelnummer, was aber nur einen Artikel pro Kunde zul\u00e4sst, oder in der Tabelle Artikel ein Feld f\u00fcr eine Kundennummer, was ebenfalls nur einen Kunden pro Artikel zul\u00e4sst. Diese Art von eindeutigen Beziehungen (1:1) ist aber nicht immer gew\u00fcnscht. [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":32984,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":143,"footnotes":""},"categories":[1,5],"tags":[],"class_list":["post-685","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-filemakerpro","category-tipps-anleitungen"],"_links":{"self":[{"href":"https:\/\/gofilemaker.de\/en\/wp-json\/wp\/v2\/posts\/685","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gofilemaker.de\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gofilemaker.de\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gofilemaker.de\/en\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/gofilemaker.de\/en\/wp-json\/wp\/v2\/comments?post=685"}],"version-history":[{"count":0,"href":"https:\/\/gofilemaker.de\/en\/wp-json\/wp\/v2\/posts\/685\/revisions"}],"wp:attachment":[{"href":"https:\/\/gofilemaker.de\/en\/wp-json\/wp\/v2\/media?parent=685"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gofilemaker.de\/en\/wp-json\/wp\/v2\/categories?post=685"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gofilemaker.de\/en\/wp-json\/wp\/v2\/tags?post=685"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}