5 Replies Latest reply on Jan 1, 2016 7:19 AM by ebsanford

    Conditional relationship; How?

    ebsanford

      I have a newbie question about creating a join relationship between two tables on something more than just the primary/foreign keys. Basic setup is that I have a Groups table that contains inventory grouping information. A group could be "Nuts & Bolts", "Adhesives", "Bulk fiber", etc.  There are many inventory Items in each group. This is simple enough, crate a Groups table with a GroupID primary key, and an Items table with an ItemID primary key and GroupID foreign key that references back to the Groups table. So far, no problem.  But here is where I'm losing it:

       

      Each group record also has a Level (Number) field.  All "raw material" items are assigned to a group whose Level <= 5.  Any item assigned to a group whose Level > 5 is considered a "WIP item" that may have subcomponents (also from the Items table).  How can I create a join relationship that reveals ONLY items assigned to a GroupID whose Level > 5?

       

      In SQL I would write something like:

       

        CREATE VIEW ItemsWIP

            AS SELECT ItemID, G.Level, ...

                FROM   ITEMS I,GROUPS G

                WHERE  G.GroupID = I.GroupID

                  AND  G.Level   > 5;



      I tried creating a global field in Items with a constant value of 5 (g5), along with a new table occurrence of ItemsWIP, linking it back to Groups with the foreign key, but with the additional AND condition that Groups(Level) > Items(g5).  This doesn't seem to work, as a layout based on the new ItemsWIP occurrence now shows <Index Missing> for the related Groups record fields.

       

      Also, is my thinking of a "table occurrence" to be functionally equivalent to SQL views even a valid concept?

      Thanks!

        • 1. Re: Conditional relationship; How?
          Mike_Mitchell

          ebsanford wrote:

           

          I tried creating a global field in Items with a constant value of 5 (g5), along with a new table occurrence of ItemsWIP, linking it back to Groups with the foreign key, but with the additional AND condition that Groups(Level) > Items(g5).  This doesn't seem to work, as a layout based on the new ItemsWIP occurrence now shows <Index Missing> for the related Groups record fields.

           

           

          One thing that's really important in the FileMaker environment is the concept of context. Every layout sits "on" a TO (table occurrence), which then determines how relationships are resolved. This is very different from a typical SQL environment, where there really isn't a concept like context; the view is created via a query and just kind of sits there.

           

          So, the notion that a TO is a view is partially correct. You can think of the Relationships Graph like stepping stones in a river. Which stone you're currently standing on will determine the implied found set in all related TOs via whatever filtering is on them. However, the actual data that exist in one TO is no different from any other TO linked to the same table; they all have all records in them at all times (so if I were to base a layout on any TO for a given table and show all records, I would wind up with the same exact records).

           

          So if I want to see all Groups that are related to a given Item, I can do that from a TO based on Items. However, what it sounds like you really want is to locate all Item records whose parent Group has a level > 5. That's not a function of the Relationships Graph; rather, that is a function of another FileMaker concept, the found set.

           

          FIleMaker doesn't have "queries" or "views" per se. If you want to locate a group of records that match certain criteria, you do that using a Find operation. This does not alter the data, nor does it create a new object; it merely presents the records to the user that match the criteria.

           

          If you want to know all the Items that have a parent Group level > 5, therefore, you would go to a layout based on Items and enter these Find criteria in a request:

           

          Group::Level > 5

           

          It's easiest if the Group::Level field exists on the layout already (that way, you can just enter the request from Find mode), but you can use a script to do it as well.

           

          Short version: To accomplish your task, you do not need another TO on the graph. Just perform a Find.

           

          HTH

           

          Mike

          • 2. Re: Conditional relationship; How?
            ebsanford

            Thanks, Mike, for the informative reply. That definitely gets me off a dead-end path, as I was burning hours on a concept of TOs that didn't really apply.  The end goal here is create a layout that allows the user to assign subcomponents to an inventory item, but ONLY if that inventory item is a WIP item (assigned to a group whose Level > 5), AND also to allow the assigning of subcomponents whose group Level is LESS THAN the group Level of the parent. This level enforcement prevents loops within the BOM hierarchy, and also allows for simple, rather than recursive, BOM costing.

             

            From your response, it would seem that such "rules" can't be enforced via TO relationships, but must be enforced from within the layouts themselves. Right? Wrong?

            • 3. Re: Conditional relationship; How?
              Mike_Mitchell

              Not necessarily. You can use data validation rules that use related information; works fine. However, in your case, you’re likely better off using a scripted approach. Restrict the found set to WIP items, and then do a check that won’t allow users to make the assignment if it doesn’t meet the criteria.

               

              Now, you can do some schema-level tricks in your sort of situation, such as echoing the Level field into the Items table via an unstored calculation, then setting up the relationship from Items to Subcomponents such that a required predicate is the Level field in Items. However, it’s a performance hit to do so and you’re likely better using scripts.

              • 4. Re: Conditional relationship; How?
                easyaspi

                Global fields can be used on the left side of a relationship (FROM) but not the right side (TO).

                That's why you are getting the <Index Missing> message; global fields can't be indexed.

                 

                I must admit, I am a little confused about how subcomponents fit into your schema.

                But basically you can create the relationship as long as you have an indexed field on the right side.

                 

                I can think of two ways to restrict the entry to groups whose level > 5:

                 

                1) Instead of making your g5 field a global field, just make it a regular calculation field = 5.

                2) If you are using FM 14, use the Hide Object When option on the portal to completely hide the portal when Group ≤ 5.

                • 5. Re: Conditional relationship; How?
                  ebsanford

                  Thanks again Mike. I'm slowly ingesting what you've said. Naturally, I had to put all life on pause last night for the Alabama vs Michigan State game. But I'm slowly making sense of what you've said. It's difficult for me to mentally shift from an SQL, HTML/CGI, ANSI-C world to the integrated approach of Filemaker. I'm hoping the time spent re-learning what I know will be worth the development & maintenance time savings over the long-term.

                   

                  EasyAsPi: Thanks for the added insight in regard to globals. And the suggestion to simply hide the BOM portal for items whose group level <= 5 makes good horse sense to me.