If the 'inventory' field is calculated from other relationships that can't be indexed, then it can't be indexed, and therefore can't be used as the 'right side' of a relationship. I suspect that's where your problem lies, as you probably make it calculate running totals of transactions in another table.
Sorbsbuster is correct. You need an indexed field with the inventory information. Also, there is no need to use two TOs to implement the desired relationship.
You could create a “stock” field, which is indexed and where you put in the current number in stock. You need to adjust this number via script every time you create a line item for a product. Then set up a relationship between a global field with the calculation ("= 0") in the “Dashboard” (the left side) and the indexed stock field (the right side), using the “is smaller than” operator. This “finds” all records in the Products table where “stock” is greater 0 (and you can do away with the cumbersome calculation field).
Hi guys, thanks for your help.
Sorbsbuster, you do seem to be right about the inventory field, this non-indexed calculation seems to be breaking the relationship. I'm still a bit confused as to why this is happening though. As this is actually in the "left side" or originating side of the relationship.
Oliver, Thanks for the tips on the indexed method. I've been hesitant to use a scripted approach because our implimentation has a lot of moving parts and I fear missing a use-case. We have, for example, inventory that expires which is accounted for by embedded age calculations. Then there's the regular regular sales, production, etc. So there are a lot going on there. Ultimately, though, we might have to go down this road.
"As this is actually in the "left side" or originating side of the relationship" - but it's on the right side of the relationship from the Dashboard.
"I've been hesitant to use a scripted approach - I agree. Using scripts to maintain inventory is fraught with many many steps of potential break down. You could try removing the stock non-zero requirement and filter portals with that criterion instead. Works for portals, but can also work for on-screen totals, too.
but it's on the right side of the relationship from the Dashboard.
The relationship to Dashboard does not use this field. It uses "one" instead.
Realised that after I'd posted...
In the Products Table can you create a field with the same calculation as If(inventory>0; product_id; 0), show it on the layout, and see if it displays what you think it should?
And is that calculation set to return the same result type (text or number) as the field ProductID is?
Sorbsbuster: Yes, the calculation displays as predicted, and the data types correspond.
In testing this, I temporarily changed "inventory" to a regular number field and set the field to 1. In this case all of the products showed up in the portal as expected. That's why I do think you're right in thinking that the non-indexed inventory level is a culprit here. I just really don't understand why.
The relationships used to establish the inventory field are all functioning as expected, and don't use calculations as foreign keys.
So what happens when,on a layout that is based on Products, you draw a portal of Products 2? You should get the portal drawing correctly by our desired logic, but you may get it not displaying if it is consistent with what you're seeing so far.
And for the sake of completeness while you're at it, can you draw a portal of Products on Dashboard?
I'm guessing that you will correctly see the portal draw for the single 'Hop 1', and correctly see it draw for the single 'Hop 2'.
If the Dashboard portal draws correctly (showing all Product Records, of course), what displays when you add to it one field from the Products 2 relationship?
I did as you suggested. Created a layout of products with a portal to "products 2" When I select a record that has inventory the appropriate records appear in the portal, as one would expect.
A portal from dash to products displays all products (not just products with current inventory) as expected.
I also set up a circumstance as you suggested. I put a portal on dash to "products" and then included the field "Products 2::productID". The portal displayed as expected. "products 2::productID" showed the ID when the inventory QTY was > 0 and was null when QTY = 0.
HRM. This is very strange. I just discovered that this functionality is actually working in my development application, it just doesn't work in the production application. Pretty bizarre considering that the production application is a direct copy of the development app at this point.
I have found a discrepency. The "product" data is maintained in an external database called "site-data." When I pull up this inventory independently the "INV_is_not_zero" calculation is working properly. Similarly, when I open the external inventory records from within a "products" layout in the dashboard app, the calculation works correctly. But when I put this field into the portal on the dashboard in the application database the results are all over the place. Somehow the calculations aren't being made properly in this context.
The crucial test was that when you added the field from the Product 2 relationship it displayed the data correctly.
What happens if you duplicate that portal on the dashboard, and sneak up on it? Gradually change all of the fields to be from the Product 2 relationship (like the last one you added)? Does it work?
"Pretty bizarre considering that the production application is a direct copy of the development app at this point."
Then, in your development copy, if you string those tests together... What happens if you import the data from the Production application into your development copy and look at the same records?
The production and development data are pretty much identical. I did an import yesterday. The only difference that I can tell is that the Development application uses a different "site number" (we have multiplie locations with site data differentiated by "site number").
When I set up a portal to "products" and set the fields to "products 2" the portal works as expected except that it creates an empty line for each product. Products with Inventory > 0 have actual data on their line. Again, this to me sort of means that the relationship is working. But for whatever reason. It doesn't work when you set the portal to "products2"
Thanks for the help. I've solved this problem. I have a complicated system set up here with severa external data sources. When I committed an upgrade from our development environment I missed an external data source reference. My bad!