11 Replies Latest reply on Feb 24, 2010 2:54 PM by SCOPe

    Dynamic Search Script

    SCOPe

      Title

      Dynamic Search Script

      Post

      So I'm working on a "dynamic" search script. Basically it searches as you type, and it works really well! I've been working off a few samples that other people have created. My question is though. Why is searching on a field, in a different table, but the same database so much slower? The only way I've been able to increase performance is to create a duplicate field in the table I'm searching from, and make it a lookup to the original field on creation. Is there any other way to increase the performance?

        • 1. Re: Dynamic Search Script
          mrvodka
             Searching is related tables are much slower. The optimal way would be to search the related table directly and then constrain
          • 2. Re: Dynamic Search Script
            SCOPe
              

            mr_vodka wrote:
            Searching is related tables are much slower. The optimal way would be to search the related table directly and then constrain

            The problem is that I need to search on some fields in the related table, and some fields in the current table. Based on whatever the user wants to search for. As mentioned above the only way I thought of being able to accomplish this efficiently is to create a duplicate of the field in the related table, in the current table and use that duplicate field to perform the searches. Horrible database design..Duplicate information that is..But it's minimal information. I was just wondering if there was a better way someone has done it.


            • 3. Re: Dynamic Search Script
              philmodjunk
                

              Sometimes you can do a two stage find to speed things up:

               

              Stage 1:

              Perform a find limited to specifying criteria fields that are in the current parent table and indexed.

               

              Stage 2:

              Re-enter find mode and specify the rest of the criteria

              Constrain the found set from stage 1;

               

              Since the more time-consuming criteria is applied against a potentially much smaller group of records (the found set from stage 1), the find usually is much faster.

              • 4. Re: Dynamic Search Script
                SCOPe
                   Interesting...I get the whole idea of searching on a smaller found set. I don't think that will work in my situation though, I'm only searching on one field, wither that field is in the current table or the related one. I'm still leaning to the duplicate lookup field solution...
                • 5. Re: Dynamic Search Script
                  philmodjunk
                    

                  Try this approach instead then:

                   

                  Freeze Window

                  Set Variable [$Criteria; Value: //specify field or expression that supplies your search criteria ]

                  Go to Layout [layout referring to related table]

                  Enter Find mode[]

                  Set field [layouttable::field; $Criteria]

                  Set Error Capture [on]

                  Perform Find[]

                  #Process find results and error trap here

                  Go To Layout [original layout]

                  #If you need to put the cursor back in a given field add a go to field step here

                   

                  You might also be able to use the Lookup function instead this script

                  • 6. Re: Dynamic Search Script
                    SCOPe
                      

                    The above did not work, and that might be because of my relationship? Anyways I've looked more into the LookUp function but I'm not quite sure how I would use it in my situation. Would I still need a "duplicate" field in my current layout and set that field to a LookUp of the field in the related table? If that's true, what's the difference in doing that aside from OnCreation of the duplicate record, set the value to the lookup of that field in the related table. Then in the find, searching on the duplicate field?

                    • 7. Re: Dynamic Search Script
                      philmodjunk
                        

                      I'm suggesting alternatives that may avoid the need for the duplicated value field. "Did not work" doesn't shed much light on the issue. How didn't it work?

                       

                      Can you post an example of the search you are doing where you have had to use the duplicated value field to get your search to work?

                      • 8. Re: Dynamic Search Script
                        mrvodka
                          

                        As I said earlier, its best to work with smaller sets and constrain. Searching in the related field from a parent layout is really slow.

                         

                         

                        You may want to take a look at this premade product that Matt Navarre has produced. His file showcases how to search records from multiple tables and return the results in a quick manner.

                         

                        http://www.seedcode.com/ccp51/cgi-bin/cp-app.cgi?pg=cat&ref=fmsearchresults

                         

                        • 9. Re: Dynamic Search Script
                          SCOPe
                            

                          PhilModJunk wrote:
                          Can you post an example of the search you are doing where you have had to use the duplicated value field to get your search to work?

                           

                          Set Error Capture [On] Allow User Abort [Off] If [(IsEmpty ( BillingPackets::_search_value)) or (IsEmpty (BillingPackets::_search_field))] Show All Records Go to Record/Request/Page [First] Exit Script [] End If Enter Find Mode [] Set Field By Name ["BillingPackets::_search_field; BillingPackets::_search_value] Perform Find [] Go to Field[BillingPackets::_search_value]

                           Definitions:

                          _search_field = Text, Global

                          • Drop-down List(Displays "Search Field Names") 
                          • OnObjectModify (search script) [Above Script]

                           

                          _search_value = Text, Global

                          • Edit Box 
                          • OnObjectModify (search script) [Above Script]

                           

                           Search Field Names[Value List]

                          Values:

                          • Account # (duplicate field)
                          • Office
                          • Date Received
                          • Date Submitted
                          • Last Name (duplicate field)

                           

                          • 10. Re: Dynamic Search Script
                            philmodjunk
                              

                            That's an interesting script. If I interpret it correctly, it narrows your found set of matching records with each additional letter typed in the search field. I can see why entering criteria into a related record's field causes undesirable delays and why my suggested script won't do the job.

                             

                            Sometimes, denormalizing some data is the price you have to pay to speed up performance. This might be one of those times.

                             

                            I've worked with "diminishing portal lists" that produce similar results. Essentially, the text you enter matches to a calculation field that returns a list of partial string values separated by carriage returns. We used to use that technique before we had auto-complete value lists. I think that approach here would not be manageable given the multiple fields you can base your search on, but am putting it up here in case it triggers an idea on some elses part that will work...

                            • 11. Re: Dynamic Search Script
                              SCOPe
                                 Yes that is exactly what the script does, and it works quite well (with my duplicate fields). Thanks!