8 Replies Latest reply on Oct 29, 2011 12:04 PM by david_lalonde@d-cogit.ca

    Avoiding second field in Valuelist - to gain speed



      Avoiding second field in Valuelist - to gain speed


      I have noticed that it is a big difference on the loading speed on a valuelist if it is based on key field and a second field (Name or Product).

      But often you want the key from the product to be inserted in the key field for the lineitem, but you need to see the Productname, so I use two fields, and showing only second field in the Valulelist dialog.

       Is there a good way to use only one field in the valuelist, like product name, and then the key for that product is inserted in the keyfield instead of the name?

      I consider to do this with a search dialogbox that finds the product, puts the key in a variable, and then this is inserted in the keyfield.

      Any better way?

      The problem is for remote users and valuelist with more than 300 items. the fields for the valuelist is not calculations, but ordinary fields.

        • 1. Re: Avoiding second field in Valuelist - to gain speed

          You might consider setting up a conditional value list where the user selects a category in one field and then the value list update to only show values from that category. If your items fall into obvious categories, this can trim your 300 items down to much shorter sub lists--and you can still include an 'all' option that lists all of the items when you can't figure out the category of which it's a member.

          Otherwise, using a search script that looks up by name or a filtering selection portal are two scripted methods that may work better here.

          Here's a demo file that compares several of these methods: http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

          • 2. Re: Avoiding second field in Valuelist - to gain speed

            Hello Phil and thanks for your demofile.

            I am already using a scripted search, and from remote location this runs ok, but when writing the letters you get a lag between every letter about 0,5 sec and this is to much.

            I feel now I have found the ultimate solution for me. I have only unique contact names and product names, so I actually can use the contactname and productname as the key. But of course I dont want to use this long names, but want to use the key fields. So I have made a second keyfield based on  the name or product, and also created a second relation to the maintable based on the names, and then I use this relation to get the key into a variable, that is inserted into the secondary keyfield based on number. This is done by a scripttrigger when the namebased keyfield is exited, and then I have got the number into the field from the variable and set field. Quick and clean!!

            Then I can use the standard filemaker valuelist based on only the name instead of key+name (and show only second field). And this list loads in 1 sec instead of almost 60sec for the first time loading.


            I just cant understand why I have not figured out this before..

            • 3. Re: Avoiding second field in Valuelist - to gain speed

              Very useful discovery. I would not expect such big speed difference between one-field and two-fields value lists.

              By the way, besides script triggers, you may also consider auto-enter calculation or lookup for populating the second key field.

              • 4. Re: Avoiding second field in Valuelist - to gain speed

                The main concern I have with this approach is that you are again using names as a key between tables. It's a "secondary key" but you still end up with a name entered into two or more tables instead of just one. This may be a worthwhile approach anyway, but if you use this approach, you should also set up a "name change" mechanism that will correctly find and change the names in a way that does not break a link between tables by making the needed change in all affected records in both tables.

                • 5. Re: Avoiding second field in Valuelist - to gain speed

                  You are right about that concern, but I just use this relationship to get the key, and then it doesnt matter if the relation based on names breaks in the next second, since the key is then already saved into the keyfield. The secondary relation should definitly not be used for anything else, the name-keyfield can only be used to open the valuelist again in the aproximatly correct position.

                  • 6. Re: Avoiding second field in Valuelist - to gain speed

                    As Nicolay rcorrectly responds, it's not an issue if you only use the name for the initial selection of the internal key. You do not even have to store it in a field permanently, can use a global field for the selection.

                    • 7. Re: Avoiding second field in Valuelist - to gain speed

                      Yep. Missed that detail the first time around.

                      • 8. Re: Avoiding second field in Valuelist - to gain speed

                        If I recall, when a value list is based on one field alone, the field's index is used. However, when a value list is based on two fields, the records are used. If all of a table's records are included, then all records are fetched before the value list is resolved. If the value list displays values based on a relationship, then only those records that satisfy the relationship are used to resolve the value list. I do not know if FileMaker Pro fetches only the required fields when fetching records for value lists or if the entire records are fetched.

                        I would appreciate someone confirming or refuting my recollection. It would also be nice to know if required fields or entire records are fetched.