5 Replies Latest reply on May 12, 2011 9:08 AM by LaRetta_1

    specifying field in which to perform find

    JingXia

      Title

      specifying field in which to perform find

      Post

      I am trying to perform the same find across 6 different fields, is there some way I can do this with a script? (using parameters?) 

      I was thinking that I could enter find mode and then use "set field" to fill in the constraints, but because my layout does not have the fields as value-enter-boxes this would be a little bit convoluted, unless there is no better way. 

        • 1. Re: specifying field in which to perform find
          LaRetta_1

          The best way is not have 6 fields which hold similar information that you would want to search all 6.  It indicates that those 6 'like' fields should be related records.

          That aside, you can search each of the 6 fields by using an OR search like this:

          Enter Find Mode [ uncheck pause ]
          Set Field [ Field1 ; $$lower_bound & "..." & $$upper_bound ]
          New Record/Request
          Set Field [ Field1 ; $$lower_bound & "..." & $$upper_bound ]
          New Record/Request
          Set Field [ Field3 ; $$lower_bound & "..." & $$upper_bound ]
          ... etch
          then perform find

          Each new request is an 'OR' search.

          • 2. Re: specifying field in which to perform find
            JingXia

            What do you mean by "related records"? The 6 fields are actually attributes of restaurants "spiciness" "noisiness" etc. where I'm converting a "high/medium/low" interface to a numerical search. 

            I will play around with your suggestion, because currently my information is presented as merge labels rather than fields. I'm thinking I will use another layout, perform the find there and then return to the result-set layout 

            Thanks for the pointers!

            • 3. Re: specifying field in which to perform find
              LaRetta_1

              "The 6 fields are actually attributes of restaurants "spiciness" "noisiness" etc. where I'm converting a "high/medium/low" interface to a numerical search. "

              There are several reasons I bring this up and it is important to understand:

              1) Attributes are 'like' items; meaning that, what if you wish to add another attribute ... you currently would have to add another field in the table.  If you had a table of Attributes then you would simply add another record.

              2) Every time you need to search, as you are currently having to do, you need to search all six fields (and maybe more as time goes on which will mean modifying every script).  If those Attributes were records in a related table then you would simply search ONE field to find all associated records.

              3) If you wish to generate a report which groups by Attribute, it will not be possible with fields.  If a record has 'spiciness' and 'noisiness' the single record simply cannot appear in two places in the report to be grouped.

              4) You will never be able to count 'spiciness' or 'noisness' without adding one individual calculation FOR EACH attribute.  And if you add an attribute you will need to remember to add another calculation to count specifically it.

              The type of structure you are describing is the number one roadblock to design.  You will hit brick walls at every turn and complicate your project many times over unnecessarily.

              "I'm thinking I will use another layout, perform the find there and then return to the result-set layout"

              I am not sure why you think you must change layouts.  If the layout is based upon the same table and you indicate that you are going to script your find, you can specify the fields and perform the find right where you are. You haven't indicated how you are scripting your find ... you should be using Set Field[] and not Insert Calculated Result[].  If you use Insert Calculated Result[] then the fields must be on the layout but if you use Set Field[], they do not.  Rarely should Insert Calculated Result[] be used; Set Field[] is preferred in 99% of the cases. Laughing

              • 4. Re: specifying field in which to perform find
                JingXia

                ah you are right ^^;; I do not need a new layout to enter criteria into my find. 

                Ah and I'm starting to see what you mean. I should make a new "associative" table with the fields "attribute; high/medium/low; restaurant_ID" is this what you mean? and so to find restaurants with medium spicyness and high noisiness I could search for [ (attribute = spicy and level = medium) constrain found set: (attribute = noisy and level = high)] ? this would be searching two fields however...

                ((in case there have been misconceptions: my data is currently one flat table with [restaurant ID, spiciness, noisyness, etc.] where each of the restaurant attributes has a number 1 - 11 allocated to it; the search interface allows users to select a value "high/medium/low" to search up restaurants ))

                I was aware that my data base has design flaws, but since I am building something that only needs to apply to a specific use I was not too worried about changing it. But it is always good to know how I can do this better. You have been exceedinly patient ^^

                • 5. Re: specifying field in which to perform find
                  LaRetta_1

                  "this would be searching two fields however..."

                  And you are searching how many fields now?  And you are correct ... your perception of using the Attributes table are right but can even be a bit simpler.  Attributes table would look like this:

                  AttributeID (unique auto-enter serial)
                  RestaurantID (foreign key to designate this attribute goes to this restaurant)
                  Attribute (data with either spicy, noisy etc which can be easily added to)
                  Level (low, medium, high)

                  You would then place checkboxes on the layout and check the attribute and level and wish and the script would do the rest.  Even if only for a specific use, you are already hitting the points where it would be worth changing.  For example, find all restaurants with low level of noise ... search ONE table for the values in the two fields then perform a Go To Related Record [ Restaurants ] to get a found set of all the restaurants to recommend.

                  Anyway, I am pleased that you feel you are moving forward but using a related table will truly save you time and energy so I would highly suggest considering do it now; easier to implement it now while your solution is still early than changing it later when you are kicking yourself for this structure.  We would be happy to help you establish the proper relationships and to understand it; it actually would be simple for you to pick up - you are obviously very bright.  Just let us know if you want that assistance. Until then, good luck to you on your project!! Laughing