AnsweredAssumed Answered

Using Global Field As Match Field in a Relationship

Question asked by richardsrussell on Jun 7, 2016
Latest reply on Jun 7, 2016 by 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?

Outcomes