1 2 Previous Next 15 Replies Latest reply on Jul 8, 2011 3:45 PM by brian.curran

    What's the best way to find records

    brian.curran

      Title

      What's the best way to find records

      Post

      Hi all,
      In the main, I have 3 sets of customer addresses:

      Sites
      Clients
      Groups

      The set that will be used/searched most is Sites so I would like to add a Portal so that we can search alphabetically, click on a Site name and then have that record load up on a multi-tabbed Dashboard.

      The portal setup wouldn't let me choose the relevant table as the layout is based on the same table (I think). So I created a duplicate TO and linked it to the original using the primary key. This allowed me to add a portal with a list of company names. (I will need to filter this to only show Sites later)

      The bit I'm stuck on is how do I get the clicked portal entry to fire a Find script or something similar to display the relevant record?

      Any suggestions or advice would be great, many thanks...
      Brian. 

        • 1. Re: What's the best way to find records
          brian.curran

          Ah, that didn't quite work as planned as only one record is being displayed at a time in the portal!

          • 2. Re: What's the best way to find records
            philmodjunk

            are

            Sites
            Clients
            Groups

            3 different tables or 3 different groups of records within the same table?

            I'd recommend these all be records in the same table with a field to identify the group. Then a field that is part of the portal filter expression or the portal's relationship can be used to select a group, with an additional text field included for entering the a part of the name so that only records from the selected group and that match that partial string are displayed.

            Take a look at the portals in this demo file and see if they are similar to what you want here. They use a text field in two different ways so that the text field serves as a key by key filter of the records listed in the portal: 

            http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

            • 3. Re: What's the best way to find records
              brian.curran

              Hi Phil,
              All the addresses are in the same table with an 'address_type' field that feeds off a Checkbox set so that each address can be assigned as a Site, Client, Group or combination of all three. All this is as per your previous, excellent advice...

              Looking at the demo file, I like the look of the 'Pattern Matching Search' although it would be nice to also display a list of all addresses for a manual browse possibly

              • 4. Re: What's the best way to find records
                philmodjunk

                You can add an address type field as the filter expression. The expression can also be modified to display all matching records when the search text field is empty.

                Add a global field, gAddTypes, set up with your group check box values.

                Then this filter expression will show all matching records if the search text and gGroups are both empty.

                IsEmpty ( YourTable::gAddTypes & YourTable::gSearchText ) or
                ( IsEmpty ( gSearchText ) And ValueCount ( FilterValues ( YourTable::gAddTypes ; PortalTable::Address_Type ) ) ) or
                ( ValueCount ( FilterValues ( YourTable::gAddTypes ; PortalTable::Address_Type ) ) and Patterncount ( PortalTable::Name ; Yourtable::gSearchText ) )

                Make sure you use a script trigger on both gAddTypes and gSearchText to refresh the portal with each change made to either field.

                • 5. Re: What's the best way to find records
                  brian.curran

                  Hmm, I must not have this set up correctly.

                  In my Addresses table, I have two global fields:
                  g_address_type
                  g_search_field

                  In the Relationships window, I duplicated the Addresses TO and joined both instances using the primary keys.

                  On my layout, I have two text fields (one for each global field) and the address type is set as a drop down list
                  Each text field has the following Script trigger:
                  OnObjectKeystroke = Refresh Window [Flush cached join results] 

                  I also have a portal on the layout that displays records from the Addresses 2 TO
                  I've tried each of the three filter expressions but nothing seems to work. 

                  • 6. Re: What's the best way to find records
                    philmodjunk

                    The relationship I assumed was to use a single pair of fields (any pair will do) and the X operator so that it's possible to match to all records in the portal unless the portal filter expression filters them out.

                    • 7. Re: What's the best way to find records
                      brian.curran

                      It's getting closer but I'm not quite there yet:

                      I copied the script triggers from your demo file and amended them to suit my fields. However, the same results appear in the portal no matter which selection I choose from the address_type drop down box.

                      I've had a look at the filter but think it might even be the script trigger?

                      • 8. Re: What's the best way to find records
                        philmodjunk

                        Did you set up the address_Type drop down with the OnObjectModify trigger to run the script to commit the record and refresh the window? Or perhaps OnObjectExit?

                        (I assumed you'd use a check box group for this field and wrote the filter expression accordingly. If you are going to use a drop down list or pop up menu, we can simplify the filter expression.)

                        I may need to look at your file. You can upload it to a file share site and post the down load link here if You'd want to do that.

                        • 9. Re: What's the best way to find records
                          brian.curran

                          Hi Phil,
                          Yes the address_type drop down is set to OnObjectModify trigger that fires a script containing
                          Commit Records/Requests []
                          Refresh Window [Flush cached join results]
                          Set Selection [ addresses_table::g_address_type; Start Position: Length ( addresses_table::g_address_type ) + 1; End Position: 0] 

                          Oops! I've changed the drop down to a checkbox set now.

                          This is the filter expression I'm using at the moment:
                          ( ValueCount ( FilterValues ( addresses_table::g_address_type ; addresses_table 2::g_address_type ) ) and PatternCount ( addresses_table 2::company_address ; addresses_table::g_search ) ) 

                          I'll try uploading a file somewhere, maybe Dropbox but I'll have to strip the data out of it first...

                          Thanks
                          Brian. 

                          • 10. Re: What's the best way to find records
                            philmodjunk

                            Save a copy as | clone is a really fast way to get a copy without any data in it.

                            Your filter expression is incomplete. you need all three parts:

                            IsEmpty ( YourTable::gAddTypes & YourTable::gSearchText ) or
                            ( IsEmpty ( gSearchText ) And ValueCount ( FilterValues ( YourTable::gAddTypes ; PortalTable::Address_Type ) ) ) or
                            ( ValueCount ( FilterValues ( YourTable::gAddTypes ; PortalTable::Address_Type ) ) and Patterncount ( PortalTable::Name ; Yourtable::gSearchText ) )

                            The first line shows all related records if both fields are empty.

                            The second shows all records of a specified combination of address_types if the search text is empty.

                            The third line pattern matches on the search text, but filters out all records that don't have one of the selected address types.

                            • 11. Re: What's the best way to find records
                              brian.curran

                              Oh dear, I thought they were three different filter options not a single expression! Embarassed Just sent you a message.

                              I think it might be the second line of the filter that's incorrect...

                              • 12. Re: What's the best way to find records
                                philmodjunk

                                Your referencing the global address type field in the filter expression where you should be referring to the portal's non global address type field.

                                change your filter expression to:

                                IsEmpty ( _003_Addresses::g_address_type & _003_Addresses::g_search ) or
                                ( IsEmpty ( _003_Addresses::g_search ) and ValueCount ( FilterValues ( _003_Addresses::g_address_type ; _003_Addresses 2::address_type ) ) ) or
                                ( ValueCount ( FilterValues ( _003_Addresses::g_address_type ; _003_Addresses 2::address_type ) ) and PatternCount ( _003_Addresses 2::company_address ; _003_Addresses::g_search ) )

                                • 13. Re: What's the best way to find records
                                  brian.curran

                                  Fantastic, that works perfectly now!

                                  Next, I need to have the ability to click a portal row and have that record load up in the layout. Is that possible?

                                  • 14. Re: What's the best way to find records
                                    philmodjunk

                                    Yep.

                                    In Layout mode, select all the fields, then use button setup... to turn them into a button.

                                    Then you can use a script with Go To Related Records or Perform find to pull up the clicked record in the layout's table occurrence instead of the portal's. (For those playing along, the portal and the layout use different occurrences of the same data source table in a self join relationship.)

                                    1 2 Previous Next