3 Replies Latest reply on Jul 11, 2014 12:25 PM by Mike_Mitchell

    Relational value list

    planteg

      Hi all,

       

      first I must say I come from the Access / MS SQL world. I am getting acquainted to FileMaker through FM12 Training Series.

       

      My question is about the field called Include only related values stating from: in the Specify Fields for Value List "Name_of_value_list" dialog. I could not complete the exercise because I couldn't figure out exactly what to do from the field name. I loaded the solution db file, and got a hint by changing the language to French . I must say that my native language is French but I simply can't stand usual terms in French. That being said I understand that the Table Occurrence named here would be where the value would come from, in other words, the value domain (or source) where to go to get the value. Is that right ?

       

      In Access I would have simply set the source of the List control to something like:

       

      SELECT __Kp_AddresID, z_Address1Line FROM table_name

      WHERE xxx = CustomerNumber

       

      I must say that the Relational graph is quite intriguing for me... I will ask later for questions about this.

       

      Thanks

        • 1. Re: Relational value list
          Mike_Mitchell

          Welcome to FileMaker.

           

          The Relationships Graph performs multiple functions that you might find in another database environment. It:

           

          1) Defines relationships between tables.

           

          2) Filters records based on the record currently being viewed (similar to a query, but without the typical creation of a separate object).

           

          3) Determines the basis for other queries using the ExecuteSQL function.

           

          This is where we get into the concept of "context". This is a crucial concept to understand in the FileMaker world. Every layout is attached to a Table Occurrence, and this determines the starting point for any relational path through the graph. Every "hop" from TO to TO carries with it an implied found set (similar to a query) based on the relational join between the two.

           

          This is where the answer to your specific question comes in. When you define a relational value list, you specify not one, but two TOs. If you take a look at the dialog where you specify the field with which to populate the list, you notice that you choose a TO for that field, but also, at the bottom, you determine a starting point for the value list:

           

          Screen Shot 2014-07-10 at 8.37.44 PM.png

           

          So, in your case, if you had a current table called Customer and the field you wanted to use was called CustomerNumber, you would choose table_name as the table in the upper left-hand corner, choose __Kp_AddresID in the list below that, and choose Customer in the lower right-hand corner pull-down (after selecting "Include only related values" as shown).

           

          Hope that makes sense.

           

          Mike

           

          P. S. There are some other methods for creating value lists based on an ExecuteSQL query, but they're more convoluted and confusing for someone new to FileMaker. I can point you to them if you like.

          • 2. Re: Relational value list
            planteg

            Hi Mike,

             

            I already suspected that the Relationship graph is not only used to define the data model (relashionships). As far as I understand, it is used to create JOIN and WHERE clauses for portals for example, without knowing it. In my case, the dialog looks like this:

             

            Rel_Value_List.jpg

             

            So the explanation is « I need a table with a relation to order_customer_ADDRESS ». Since ORDER is the TO of the layout, it has to be that table. As far as I understand, FileMaker will find out by itself that the related field is _kf_CustomerID and will make the JOIN on the current value of the Customer ID. Is that right ?

             

            I browse the Developper forum messages on a regular base and have seen that the concept of context is quite important. I will keep learning and ask a question if needed.

             

            Thanks

            • 3. Re: Relational value list
              Mike_Mitchell

              Essentially, yes.

               

              It's often useful to think of the Relationships Graph as if the TOs were stepping stones in a river. Pretend you are standing on a particular TO (stone), and picture what the other stones look like in relation to your current position. Now, imagine a foot bridge from your current stone to the stone next to it. That bridge is defined by the relationship between the TOs.

               

              One area where you'll need to shift your thinking a bit is to break the paradigm that joins are made on the fly. As a baseline rule, the "JOIN" is predefined. FileMaker doesn't "find out by itself" that, in this case, the foot bridge is based on _kf_CustomerID = _kp_CustomerID (presumably). Rather, that's what you told it when you defined the relationship between the two TOs (not tables).

               

              So the join between the TOs is defined on the Graph. And, you're correct; the WHERE clause (or its equivalent) is implied by the value(s) in the key field(s) (based on the current record in the current context).

               

              (Note that there can be some exceptions to this general rule. For example, you can create temporary joins using ExecuteSQL. However, the Graph itself is, for the most part, predefined.)

               

              HTH

               

              Mike