8 Replies Latest reply on Dec 7, 2010 9:24 AM by PaulSt

    Use of value list greatly slowing portals and layouts

    PaulSt

      Title

      Use of value list greatly slowing portals and layouts

      Post

      My Setup:

      Orders layout based on Orders table.

      Portal on orders layout based on OrderLines table.

      OrderLines table stores fields: OrderLineID, ItemID, Price.

      Item table stores fields: ItemID, ItemName.

      If each orderline in the portal displays only the OrderLineID and the ItemID (both fields in the orderlines table). This works well and is fast. Since I want to display the ItemName instead of the ItemID, I am using a pop-up menu style control with a value list based on the Item table. The value list is set to use values from the Item table with ItemID as the first field and also display values from a second field, but only show values from the second field. This way, the portal shows the OrderLineID and the ItemName, which is what I want. However, this slows portal scrolling greatly. It also slows the Orders layout when browsing through order records. There is about a 1 sec delay in scrolling in the portal and about a 2 second delay when browsing through order records. All of the fields involved are indexed. This seems like too much of a slowdown to be acceptable.

      I am using FM11 Pro Advanced. So...

      1. Is this kind of slowdown typical? It seems to me that this would be a very common use of value lists.

      2. Is the slowdown due to indexing of fields? Should I NOT index certain fields?

      3. Are there faster ways to do the same thing?

      Thanks!

      Paul

        • 1. Re: Use of value list greatly slowing portals and layouts
          philmodjunk

          Are there faster ways to do the same thing?

          Make your pop up menu a drop down list. You can resize this drop down to be either just big envough to display the number, or you can even set it to use the arrow and make it just wide enough to show just the arrow. Then place your ItemName field from the related table next to it to display the item name.

          Your pop up menu functions as a type of "query" into your related table of items that has to recalculate each time you scroll the portal or flip through order records. This alternative should greatly reduce the amount of calculations needed when you do either of these operations. (You can even define a field in OrderLInes that looks up the ItemName, some users prefer this approach as their invoices will then show exactly what ItemName was current at the time the order was placed instead of having this name possibly change after the fact if they decide to modify the item name in the future.)

          • 2. Re: Use of value list greatly slowing portals and layouts
            PaulSt

            "(You can even define a field in OrderLInes that looks up the ItemName, some users prefer this approach as their invoices will then show exactly what ItemName was current at the time the order was placed instead of having this name possibly change after the fact if they decide to modify the item name in the future.)"

            I have done as you suggest here and it is back to the normal speed. I was hoping to hide the ItemID on some layouts though. Thanks for the suggestions!

            • 3. Re: Use of value list greatly slowing portals and layouts
              philmodjunk

              Note that if you reduce the width of the field until only the drop down's arrow is visible, you have indeed hidden the item number.

              • 4. Re: Use of value list greatly slowing portals and layouts
                PaulSt

                True, you have hidden the item number, but you can not click on the itemNAME and get the drop down list. You must click the arrow to the side. Which does work well, it's just not what I had hoped for...

                • 5. Re: Use of value list greatly slowing portals and layouts
                  philmodjunk

                  Some people have played around with hiding a drop down list (no arrow) formatted field behind the item name field. Entering the item name field then triggers a script That uses Go To Field to enter this hidden field and then drop down the list of values. It's a bit tricky to set up and it's been too long since I last tried this to get you a detailed step by step, but it can be made to work.

                  • 6. Re: Use of value list greatly slowing portals and layouts
                    PaulSt

                    A script trigger is another good idea, thanks. I will likely just keep the itemID and itemNAME both visible on the portal. It works, is fast, and is easy to maintain. It is too bad that the Filemaker provided solution using values lists is so sloooooooow.

                    Thanks!

                    Paul

                    • 7. Re: Use of value list greatly slowing portals and layouts
                      philmodjunk

                      I'm guessing here that either the table that serves as a source of values has a very large number of records or the relationship is more complex than a simple = link between two data fields. If you can find a way to reduce the number of records in your value table and/or simplify the relationship details, you may be able to get acceptable response times for your layout using the original pop up menu with field 1 hidden.

                      • 8. Re: Use of value list greatly slowing portals and layouts
                        PaulSt

                        The relationships are simple. There are 3K records in the Orders table, only 500 records in the Items table (which is the source of the value list), but 12K records in the OrderLines table and more are added often. I don't want to reduce the number of records in any table, so I will live with the layout as is.

                        I am now assuming that this use of value lists is just slow for everyone, not just me.