5 Replies Latest reply on Jun 7, 2016 8:39 PM by richardsrussell

    Using Global Field As Match Field in a Relationship

    richardsrussell

      I recall reading years ago that something or other in a relationship needs to be indexable, which Global fields can't be, but the FileMaker Pro documentation about Global fields specifically says that a Global field can be used as a match field in a relationship. Therefore I didn't think it was too big a stretch to imagine that a Global field could also be incorporated into a calculation that would serve as a match field. But it doesn't seem to be working.

       

      Here's the deal. We get in a bunch of orders from different customers over a prolonged period — a couple of weeks, say — and then we ship them out all at once. Each incoming order is associated with a Destination to which it'll be shipped. In the Destinations table, each record has the customer's name (say, XYZ Co.), an address, and a unique Location ID code (say, 12345). As the orders come in, they get entered in the Orders table, along with product type, quantity, Location ID code of where it's to be shipped, and an entry-batch number. So let's say that we're working on Batch #67 from May 15 to June 30, and on June 6 we get an order from XYZ Co. for 2 dozen brown widgets. That produces an Orders record with Location ID = 12345 and Batch No = 67. These are combined via the formula Location ID + Batch No / 1000 to produce the value LocnBatch = 12345.067. Maybe on June 10 we'll get another order from XYZ Co. for 30 pink trinkets; that would also give us a LocnBatch value of 12345.067. Come June 30, we'd like to round up all of those 12345.067 orders and ship them out to XYZ Co. in a single big box.

       

      Meanwhile, back in the Destinations table, I've got a portal in which I'd like to see all the orders for a given destination in the current batch. So I set up a Global field (Batch No Default) in which I enter the number 67 and a Calculation field (also called LocnBatch) that concatenates it with each destination's Location ID code. Then I go to the record for XYZ Co., which sure enuf shows me that LocnBatch = 12345.067, but the portal that supposedly links it to all the Orders that also have LocnBatch = 12345.067 is empty. I can see neither the brown widgets nor the pink trinkets.

       

      What am I doing wrong?

        • 1. Re: Using Global Field As Match Field in a Relationship
          Extensitech

          You can use a global field, or other unstored field, on one side of the relationship. The relationship will only work from the side with the unstored field on it, though, not to it.

           

          In anchor-buoy, this is often expressed as "you can't have unstored fields on the 'right-hand' side of your relationship.

           

          HTH

          Chris Cain

          Extensitech

          1 of 1 people found this helpful
          • 2. Re: Using Global Field As Match Field in a Relationship
            richardsrussell

            Thanks for your rapid response, Chris.

             

            I must say that the FileMaker Pro documentation is singularly unforthcoming about this limitation. However, I can hardly be the first person to have encountered it. Is there a known workaround which will let me display the desired data in the Destinations table?

            • 3. Re: Using Global Field As Match Field in a Relationship
              sccardais

              Chris:

               

              Thanks very much for this. I've read of FileMaker related docs and watched a lot of videos and never caught this. It clears up some confusion for me.

              • 4. Re: Using Global Field As Match Field in a Relationship
                keywords

                First, on the question of using globals and other unindexable data, I think of the process as a bit like a Find. On the "from" side of the relationship I am saying to FM, find this: XxxYyyZzz. In order to find it, FM has to search through and index of potentially matching values, which it can't do if the field I am asking it to search is itself unable to be indexed.

                 

                Second, to overcome this you have to somehow make the search field indexable. The most common and probably simplest approach would be to convert the field to a standard data field and use an auto enter calc, which will obviously be the same calc you currently have. The only issue then will be to make sure the field is able to update whenever the data that feeds the auto enter changes.

                • 5. Re: Using Global Field As Match Field in a Relationship
                  richardsrussell

                  I've basically come to the same conclusion as in your second point.

                   

                  However, I've got another wrinkle to consider. Every time I modify a Destination record, it changes a Date field called "Updated", which is set to auto-enter the modification date. I really want that to happen only when something substantive changes about a given Destination, and simply mass-replacing a bunch of batch numbers (something that happens maybe 10-15 times a year) doesn't qualify. That's why I was trying to use a Global field for "Batch No" in the first place.

                   

                  So what I'm planning to do is create a new intermediate table (IDLinx) in which each record will contain the Destination sequence number ("DesSeq", same as the primary key in the Destinations table), the "Location ID" code for that destination (which means I won't need to have that code directly in the Destinations table as at present), and an indexable "Batch No" that can be mass replaced every time I need to do it. I don't give a rip how often the IDLinx table gets updated, so it won't even contain an "Updated" field. Then I can connect Destinations to the latest batch of Orders via the IDLinx table. Kind of awkward, but at least it'll do what I need to have done.