Incorrect records shown when querying a child table relational via globals

[Cable]

I can't figure this out so I thought I'd see if anyone could shed some light.

I have a pricing database solution that includes 4 tables relevant to this issue.

The Customer table contains all of the customer records. There is a record for each customer's location in this table (called a SHIPTO). Every customer will have a customer number and a SHIPTO code that identifies the particular location.

There is an inventory table that list all of our inventory. Each record there is uniquely identified by an ITEMNUMBER.

There is a pricing table where a custom price can be specified for that customer. Pricing can vary by location. The pricing table is a SQL table in another sytem.

There is a temporary pricing table where the person doing the entry works on modifying prices. Once they are done, any changes are saved from the temp table over to the live table. There are various reasons for this but the bottom line is that I do not want users directly touching the live SQL data.

What I have set up is that when someone wants to enter custom pricing they select a customer. They are then taken over to the inventory table and two globals are set, one for the customer number and one for the shipto code. Any current pricing is copied over to the temporary pricing table. The temporary pricing table is related to the inventory table by item number (ITEMNUMBER = ITEMNUMBER), customer number (gCUSTNUMBER = CUSTNUMER), and shipto (gSHIPTOCODE = SHIPTOCODE). I have the relationship set to allow records to be created in the temp table via the relationship.

All of that is working fine.

The problem I'm having is that I have a button set to show only the inventory items that have been assigned a custom price. If I hit that button it shows every item that has a corresponding record in the temp table whether it is a valid relationship or not (someone else could be pricing items for another customer).

Similarly, if I query a specific price it shows all items with that price even if not in the current relationship.

To illustrate:

I'm working on Customer 1, shipto 1. I have 5 items priced.

Another person is working on Customer 2, shipto 2. That person has 10 different items priced.

So, there are 15 records in the temp table.

If I query for all items with a price > 0 I'm getting 15 inventory items returned. Only my 5 will actually show prices on the screen (correctly).

If I query for a specific price, such as $19.99, and I have one item at that price but the second person has two items at that price, my found set will show 3 items. Only my item will show the price and the other two will be blank.

I have no idea how this is possible. FileMaker is showing the correct relational price seems to be ignoring the global parts of the relationship for queries. I have tried clearing the indexes but no luck.

Any ideas?