14 Replies Latest reply on Apr 26, 2012 1:01 PM by johnsimms

    "Ugo" method for tunneling with self-join tables

    johnsimms

      Title

      "Ugo" method for tunneling with self-join tables

      Post

      Hi Everyone,

         I have a database with a dashboard on which I would like to display certain product data, but only if we have inventory of that product. In the past I've done this by creating a portal to all products and filtering / sorting the portal. I've found, recently, though, that filtering and sorting portals can perform quite badly; especially over the network. With FM Go it gets pretty gnarly.

         I've found that accomplishing these tasks in the relationship map can provide better performance. So, I tried to accomplish the task with what I understand to be called the "ugo" method. My set up is like this:

       

        DASHBOARD                Products                      Products 2

              one         ------->      one

                                        INV_is_not_zero  ------>   product_id

       

      INV_is_not_zero is defined as a calculation that looks something like this:

      if(inventory>0; product_id; 0)

       

      In case it matters, the "inventory" field is also a calculation.

       

      so, i put a portal on the dashboard to "products 2" and I expect it to display all products for which inventory > 0. But it doesn't display anything. I've gone out of my way here to make the calculations land on the initiating side of the relationship. What went wrong?

       

      THanks for your help!

        • 1. Re: "Ugo" method for tunneling with self-join tables
          Sorbsbuster

          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.

          • 2. Re: "Ugo" method for tunneling with self-join tables
            erolst

            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).

            • 3. Re: "Ugo" method for tunneling with self-join tables
              johnsimms

              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.

              • 4. Re: "Ugo" method for tunneling with self-join tables
                Sorbsbuster

                "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.

                • 5. Re: "Ugo" method for tunneling with self-join tables
                  philmodjunk

                  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.

                  • 6. Re: "Ugo" method for tunneling with self-join tables
                    Sorbsbuster

                    Realised that after I'd posted...

                    • 7. Re: "Ugo" method for tunneling with self-join tables
                      Sorbsbuster

                      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?

                      • 8. Re: "Ugo" method for tunneling with self-join tables
                        johnsimms

                        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.

                        • 9. Re: "Ugo" method for tunneling with self-join tables
                          Sorbsbuster

                          Oh-kay...

                          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?

                          • 10. Re: "Ugo" method for tunneling with self-join tables
                            johnsimms

                            Sobsbuster.

                            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.

                            • 11. Re: "Ugo" method for tunneling with self-join tables
                              johnsimms

                              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.

                              • 12. Re: "Ugo" method for tunneling with self-join tables
                                Sorbsbuster

                                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?

                                • 13. Re: "Ugo" method for tunneling with self-join tables
                                  johnsimms

                                  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"

                                  • 14. Re: "Ugo" method for tunneling with self-join tables
                                    johnsimms

                                    Hey Everyone,

                                       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!