5 Replies Latest reply on Aug 26, 2014 10:29 AM by philmodjunk

    Cartesian Relationships

    j.hall

      Title

      Cartesian Relationships

      Post

           Ok, so I'm in the middle of a debate and I want to see if I can find a better way than what I'm doing. What I'm doing is working, but as we know that doesn't mean it's always the best way to do something. And it's a bit slow for my tastes to be honest.

           I have 2 tables, Strip Inventory and String Design related with a Cartesian relationship based on the String Number. On the layout I have two portals that use this relationship and use filtering. On the right it shows all the available strips and allows you to "Move" them to the portal on the left that shows the strips that have been assigned to the string design. The portal on the right can be filtered with global variables so that you can filter out the list. But a strip may be used once and only once and there is almost 25000 records in that table.

           I can't filter things in the relationship directly So I'm wondering...

           Which would be better (or if someone has a better solution I'm all Ears.. Phil I'm looking at you :) )

           Solution A) break the cartesian relationship and use 2 additional TOs to relate one for the right portal and one for the left and then set the portal on the right to have a relationship based on the global fields selection?

           Solution B) Setup another field in the String Design table that I can filter out "Used" strips?

           Solution C) No, I'm already doing it the best way anyone can think of?

           Thanks

           Hope to hear from you soon.

        • 1. Re: Cartesian Relationships
          philmodjunk
               

                    I have 2 tables, Strip Inventory and String Design related with a Cartesian relationship based on the String Number.

               If it's a Cartesian relationship, it's not actually based on any value.You could even delete the match fields from the two tables and your cartesian relationship still works. (But including a specific field in a cartesian relationship can be a way to force certain layout elements to update better.)

               

                    I can't filter things in the relationship directly So I'm wondering...

               You'll need to explain why that is the case here. Since Solution A seems to indicate that you can use the relationship to filter values.

               What happens in your database when you " "Move" them to the portal on the left that shows the strips that have been assigned to the string design."?

               Basically, I'd need to see what relationships you currently have in place and how you are using them before I can evaluate what options might work better.

               It might even be the case that a list view of this data with data from the parent record shown in the header might actually work as an option to consider...

          • 2. Re: Cartesian Relationships
            j.hall

                 Sorry, I'm using portal filtering currently to filter the data.

                 When you move them, it's setting the "String Number" field in the Strip Inventory Table. The Portal on the LEFT only shows strips that are for that String. The portal on the RIGHT shows all available, unassigned strips.

                 The list view would be too much information. There are 25000 records in the table, but at any one time, there maybe 1500 to 2000 of them that are available for use. And it's not too bad as far as performance, but I was just hoping to speed it up a bit when the layout loads.

            • 3. Re: Cartesian Relationships
              philmodjunk
                   

                        Sorry, I'm using portal filtering currently to filter the data.

                   And I'm asking you to describe that portal filtering in detail so that I can tell if it is possible to use other means in place of that portal filter.

                   And I really need to understand what relationships, if any that you have in place here besides that Cartesian join.

                   Also, what version of FileMaker are you using? ExecuteSQL may be a tool of value here if you are using version 12 or 13.

                   

                        The list view would be too much information. There are 25000 records in the table, but at any one time, there maybe 1500 to 2000 of them that are available for use.

                   That's not necessarily a problem for a List View any more than it is for a portal. There are a number of ways to manipulate the found set of even 2000 records to make this quiet manageable. But I'm not actually recommending this approach yet. I'm just trying to avoid getting stuck thinking inside any boxes, but rather to consider all possible approaches towards a workable solution.

              • 4. Re: Cartesian Relationships
                j.hall

                     Here is a screen shot of what I'm doing.

                     That might help with the visual.

                     The Portal on the far right is reference information only. They don't need anything other than to see that information.

                     The one in the middle is the list of strips available

                     The one on the right is the ones that have been assigned to this specific String

                • 5. Re: Cartesian Relationships
                  philmodjunk

                       A screen shot of Manage | Database | Relationships would be much more informative. Also a copy and paste of your portal filter expression would be helpful.

                       What can make a filtered portal slow is the fact that FileMaker has to loop through all the related records (With a Cartesian join, this is all the records in the table) and evaluate the filter expression for each record in order to determine which records to omit and which to show in the portal. Thus, anything that we can do to reduce the size of the related set of records (the records you'd see in the portal with no portal filter), the lighter the "load" on FileMaker when you need to refresh this layout after editing one of the fields used in the portal filter expression.

                       Thus, if you are using = or > operators in your portal filter expression without using such partial value matching expressions such as PatternCount, you can modify your relationship to include those fields as additional match field pairs and the result should update more quickly than what you have in place now.

                       And if you are using a portal filter to show what strips have been selected in the left hand portal, changing this to match by an ID value by assigning the String record's ID to the selected strip record, that will also reduce your "update load" on this layout.