13 Replies Latest reply on Dec 17, 2009 11:07 AM by ianmanning

    Value list takes 2.5 minutes to load

    ianmanning

      Title

      Value list takes 2.5 minutes to load

      Post

      I have a value list which uses "values from a field". The field is in a related database which holds the details of 131000 products. Both databases are hosted on a server running FM Pro 10 Server and users are all using FM Pro 10

      The field using the value list is set up as a drop down list and has autocomplete using value list enabled.

      The first time the field is accessed (since starting filemaker that session) it takes about 10 seconds to load for users connected in-house, however for our sales engineers connected via a VPN from thier homes it takes 2.5 miinutes to load.

      Is there any way of speeding this up?

      I have tried seting up the value list on the product database and using "value list from another file" but this did not appear to make any difference.

      Products are frequently added to the product database so the live link is nice but a static list could be used if there was an easy way to update it when new products are added ( 2-3 times per week)

      Thanks for your help

      Ian

        • 1. Re: Value list takes 2.5 minutes to load
          philmodjunk
            

          Here's one possibility:

           

          Define a table, ListItems, with one text field for your values.

           

          In a script:

           

          Go To Layout [ListItems (ListItems)]

          Show All Records

          Delete All Records [No Dialog]

          Set Variable[$ValueList ; Value: ValueListItems( get ( Filename ) ; "YourValueListNameInQuotes"]

          Set Variable[$Item; Value: 1]

          Loop

            New Record/Request

            Set Field [ListItems::textField ; GetValue($ValueList; $Item) ]

            Set Variable [$Item; Value: $Item + 1 ]

            Exit Loop If [$Item > ValueCount($ValueList) ]

          End Loop

           

          Use a server Scheduled script to run this job late at night when there are no users.

           

          No create a new value list that specifies this table. Since this table should have much fewer records in it, the value list should load much more quickly.

          • 2. Re: Value list takes 2.5 minutes to load
            ianmanning
              

            Thanks PhilModJunk,

            Unfortunately the 131000 products are all unique. We have 4 suppliers, some items are available from 2 or more so I have seperate tables for each supplier another table for those items we have actually sold before and therefore have a UK list price. I then import each table into a sixth table updating existing records and adding new records where there is no existing record. This reduces the record count from 170000 to 130000. it is the sixth table (all Products) that I use for the valuelist.

            I could specify the supplier before entering a product and use 4 valuelists but this would miss those products available from multiple suppliers.

            it is only the inital build each day that takes a long time once the field has been used it is then fast. Is it posible to build the list in the background?

             

            As a work arround I have added a global field the the product selection layout which will take the part number and added a button which will add the partnumber to to the line items table (as a new item) this way the item appears in the quote items portal but as the portal field on the layout has not been clicked the valuelist does not load. This is OK if they know the exact partnumber which can be 15 alphanumeric characters.

             

            Any Ideas greatfully recieved.

            Best regards

            Ian

             

            • 3. Re: Value list takes 2.5 minutes to load
              mrvodka
                 You really should be using a new window ( list view ) that lists your items.  You can script it to act as a popup window.
              • 4. Re: Value list takes 2.5 minutes to load
                philmodjunk
                  

                "I have seperate tables for each supplier "

                That doesn't sound like a good table structure and will greatly complicate the function of your database. You should store all such data in a single table and use a "supplier" field to distinguish between records for a given supplier. Then you don't have to do all the importing you describe and you can keep your data all in one place.

                 

                You might want to script a search tool where a user enters a name or keyword and clicks a button to initiate the search. The script could then search for the recordsin containing this name or keyword and pop up a list (which can be in a new window as Mr Vodkay suggests). The user then clicks an item from this much smaller list to make the final selection. If only one item is found, you can have the script directly enter its ID number into your layout field, otherwise the user clicks an item in the list and that button's script can enter the ID number.

                • 5. Re: Value list takes 2.5 minutes to load
                  FentonJones
                    

                  I have found (pun) that the fastest and most flexible method to do "choice" operations when there are tons of possibilities is a separate controlled window and a regular FileMaker List view, with additions for optimized filtering/finding. One question (which you may already have answered) is whether all choices are for a single Supplier's items (as might be true for a purchase order), or not. If so, you could pre-filter the list to that supplier. 

                   

                  But the fastest method is to always show a 0 found set when the window opens. It is pointless to show people a list of thousands of records. They need to learn how to Find. With FileMaker, and the ability to trigger a script upon modification or save/exit of a field, it is not hard to start the search in one or many fields.

                   

                  The window must be highly controlled, say with an infinite Loop (learn about first, do not turn Allow User Abort ["Off"] until you've got buttons with the option to Halt current script on the layout). Because it will have an "add to order" (or whatever) button on the layout (which will also use the option to Halt current script).

                   

                  Unfortunately I don't have an "example" file handy, as the method is currently inside a client's file. Maybe someone else has one, or I'll have time to pull that out of my file. But it is many times faster, and more flexible than a Value List. More work tho.

                  • 6. Re: Value list takes 2.5 minutes to load
                    ianmanning
                      

                    Thanks everyone for their advise.

                    The reason for having a seperate table for each supplier is simply that they update their prices at different times and some items will become obsolete. so when I receive a new price list I delete all the items in their table and import the new pricelist. I can now make any changes I need to the new list before importing it into the Complete list which is used by the Quote system. I can also identify any items in the complete list which are missing from the new list and mark them as obsolete.

                     

                    I like the idea of scripting a search tool to lookup full or part descriptions in a new list view window and enable the selection from there. I have already included a field to enable the remote workers to add an item directly into the line items table bypassing the valuelist If I add another button to this field they can perform the search from the same field.

                     

                    I can also add some pre-selection as one supplier accounts for 94000 items but is also the least used.

                     

                    Thanks again for your help

                    best regards

                    Ian

                    • 7. Re: Value list takes 2.5 minutes to load
                      philmodjunk
                        

                      "The reason for having a seperate table for each supplier is simply that they update their prices at different times and some items will become obsolete. so when I receive a new price list I delete all the items in their table and import the new pricelist. "

                      There are other ways to do this that do not require separate tables.

                      You can include "effective date" date fields to control when price changes go into effect and use a second field to drop obsolete items. This let's you keep an historical record of past changes--can be very useful for purposes of communication with your suppliers and also for audits.

                       

                      • 8. Re: Value list takes 2.5 minutes to load
                        ianmanning
                          

                        Hi PhilModJunk,

                        Thanks for the tip, I currently have a seperate price list database for each year going back 20 years so that I can check prices in previous years.

                         

                        I'm now having a problem in totalising a summary field in a report I think I may need to send my whole database so that the error can be found but I will start another post about that.

                        Thanks

                        Ian

                        • 9. Re: Value list takes 2.5 minutes to load
                          ianmanning
                            

                          Hi PhilModJunk

                          I am trying (Read Struggling) to implement the search for the key word, display in a list and return the item clicked.

                          The big problem that I'm facing is that the starting point is in the Quotes database (I have a field part number which can contain the keyword) but the data to be searched is in the pricelist database.

                          I do have a relationship between the two based upon the partnumber and I use this to pull in the list prices, descriptions and costs etc

                          I started writing the script but then hit a wall.

                          I have put the key word into a variable, opened the pricelist database but I cannot see how to run a find in the related database.

                          Thanks for your help

                          Ian

                          • 10. Re: Value list takes 2.5 minutes to load
                            FentonJones
                              

                            Variables are available to their own file, either locally within the script ($variable) or globally ($$variable). You need to pass the value to a script in another file via a script parameter. Then, in the other file's script, use Get ( ScriptParameter ) to retrieve the value. You can put it into a local variable right at the top of the script, and use it further down.

                             

                            BTW, you do not have to "open" a file explicitly in cases like this. It is best not to. If you call a script in another file it automatically opens. 

                            • 11. Re: Value list takes 2.5 minutes to load
                              ianmanning
                                

                              Hi Fenton,

                              Thanks for the reply.

                              I'm not shure how you Pass the script parameter.

                              I can see the get(scriptParameter) function in a calculation but I cant see how to reference the sending file etc.

                              I could not see the Get(scriptParameter) in the script manager.

                              I would appreciate a few more steps in the explanation.

                              Thanks

                              Ian

                               

                              • 12. Re: Value list takes 2.5 minutes to load
                                FentonJones
                                  

                                Script parameters are assigned via the box at the bottom of the dialog when you attach a script to a button. You can just type in that box, if you have a text value to assign. Or click the button to Edit it, and you'll see a standard FileMaker calculation dialog, where you can assign just about anything.

                                 

                                In the case where you have a main script calling a subscript, when you want to keep passing a script parameter of the main script, you'd add it as as the parameter.

                                Perform Script [ subscript; Get ( ScriptParameter ) ]

                                • 13. Re: Value list takes 2.5 minutes to load
                                  ianmanning
                                    

                                  Thanks Fenton,

                                  I had missed the fact that I can assign a sript from another file to a button.

                                  I just tested your method and I was able to find the parts in the price list bassed upon part of a description in my quotes database.

                                  I can now pass back the selected part to the quote using the same method.

                                  Best regards

                                  Ian