4 Replies Latest reply on Feb 21, 2013 8:42 AM by ahcho

    Creating a search layout

    ahcho

      Title

      Creating a search layout

      Post

           Hello!

           I've spent the last few hours reading about searches and my mind is now a bit frazzled. My requirements are:

           -creating a layout that searches multiple fields in one table. This table is fairly large and contains ParentID's and ChildID's (i.e. tasks and sub-tasks are in the same table)

           -filtered drop down values (i.e. as the user digs deeper in the data, they are restricted by whatever is left within the found set in each drop down list)

           The database will be running through this setup:

           A. IWP through FMS 12

           B. FMGO through FMS 12

           So the possible scenarios that I've come up with to solve this problem are:

      1. Filtered Portal by having the user select from a set of dropdown menus

           This would be simple to create but filtered portals through FMGO don't perform well. Subsequently, I am unable to think of a way that I could easily filter the drop down values as the user searches through the data.

      2. Filtered Portal via relationships

           I would prefer this method but how do I take into account the multiple search criteria's? Say I have the following relationships

           Search Overlord-----------<Tasks

           Adding search fields onto the Search Overlord would result in quicker performance through the internet but these relationships only work if ALL the search fields are filled out.  Is there a way around this?

           With this option, as the data is searched, the drop down lists are constrained which I can't do with option 3.

      3. Searcheable layout based on table

           This option seems like the easiest to do but how would I constrain the values in the drop down lists?

           ----------------------------------------------------------

           So with that wall of text, here are my questions:

           a. Is there a way to filter a portal through multiple relationships that may not be filled out (i.e. is there a wildcard variable that shows all)?

           b. Is there a way to create constrained drop down lists for on layout searches (i.e. Find mode)?

           Thanks,

           Aaron

        • 1. Re: Creating a search layout
          philmodjunk

               a. Possibly, there are ways to set up a calculation field that produces a return separated list of values, if any one of the values on the list matches a value in the portal table's match field, the record appears in the unfiltered portal. It depends on the kind of criteria that you need to specify in your search.

               b) This is possible with scripts that generate a list of ID numbers based on the ID's present in your found set, but it's much simpler to set up a conditional value list that uses the criteria specified by the user. But once again, this depends on what kind of criteria...

               

                    filtered portals through FMGO don't perform well.

               Can you expand on that comment just a bit? I'm not an FM GO user, but there is more than one way to set up a filtered portal and perhaps a small change in how you set it up will make it much more responsive.

               Would this be on an FM GO client of a hosted database or a file loaded on the FM Go device?

          • 2. Re: Creating a search layout
            ahcho

                 a. A return separated list of values? Can you elaborate? I've read about them but I haven't been able to get them to work. My understanding of them was that I could populate a field with the foreign keys separated by the "¶" character and relate that to a table that the foreign keys corresponded to.

                 b. The criteria will come from a managed set of lists (names, assets tasks) and are all related via foreign keys. These lists contain Parent/Child ID's as well (like a cooperate hierarchy) so thhe actual table that the layout is mostly comprised of foreign keys. Where would I start with the conditional value list that is user specified?

                 Regarding the filtered portals via FMGO, I believe that the conditions of the filtered portal get applied after the data is loaded onto the mobile device. Filtering through the relationship puts the onus on the server to return the appropriate records so the iOS device doesn't have to do any of the filtering. There is a significant performance improvments using the filtered via relationship.

                 For this search, there will be 2 layouts, one for FMGO and one for IWP. I'd rather have the base function of the layout be the same and design 2 different UI's rather than having 2 completely different layouts/functions.

                                                         

            • 3. Re: Creating a search layout
              philmodjunk

                   a) Essentially correct. So if you perform a find and get 5 records with the PrimaryKeyID numbers of 1, 23, 456, 987 and 2, You can use a layout were this field is the only field on the layout and CopyAllRecords will copy those 5 values separated by returns to the clipboard and then the values can be pasted into a text field to serve as the matchfield in a relationship to a table that also has those same ID (foreign key or self join to primary key...). A looping script could also loop through the found set and build up the same list without using CopyAllRecords, but this takes longer.

                   b) that's a bit too vague for me to have an answer. Could you post an example?

                   Good observation on filtered portal performance, but it's not unique to FM GO, it's just that the slower hardware/connection rates make it much more noticeable.

                   Please note that conditional value lists often do not work well in IWP due to the need to submit the data back to the server via a commit record step after selecting a "category" value in the first value list before the conditional value list can display the values that are a member of the user selected category.

              • 4. Re: Creating a search layout
                ahcho

                     Thanks PhilModJunk,

                     I got part a working. Haven't time to solve b but I think I know what i have to do.