8 Replies Latest reply on Dec 6, 2011 9:09 AM by philmodjunk

    find in a mapping table

    Fagreement

      Title

      find in a mapping table

      Post

      hi,

      in a script i would like to search in a mapping table for ceratin record and return the value found, but I don't want to use open layout/find/set variables/...etc

      is there another simple and direct method (query) ?

        • 1. Re: find in a mapping table
          juancardona

          Maybe "Go to related record"?

          • 2. Re: find in a mapping table
            Fagreement

            no, in fact in the current layout the table where i wand to fetch is not related

            • 3. Re: find in a mapping table
              juancardona

              Many of the steps you want to avoid won't show, have you tried?

              • 4. Re: find in a mapping table
                philmodjunk

                in a script i would like to search in a mapping table for ceratin record and return the value found, but I don't want to use open layout/find/set variables/...etc

                Why do you not want to use this method? (Knowing why can help us suggest an alternative or possibly a way to use this method that works for you.)

                What kind of search is it? (what criteria results in a "match" in this table?

                It might be possible to set up a relationship so that you can access the matching data in that table via the relationship.

                • 5. Re: find in a mapping table
                  Fagreement

                  In fact I have an "Advanced Search" layout (see attach), layout search table = GlobalFields (global store in the DB)

                  in the first column (gField1) I'm displaying a menu where it listed all the "searcheable" fields in the DB. Those fields comes from different tables.

                  In my script (search method) I have to know the related table of the use choice from the menu list. (in a seperate table i have the filed name and the table where it comes): valueX tableY, valueZ valuesZZ...

                  So practically in the script I want to seek the value in this seperate table and find the related table.

                  • 6. Re: find in a mapping table
                    philmodjunk

                    Checking for understanding.

                    If I have two "searchable" fields: Crops::CropName and ProductionHistory::Year

                    Your table, SearchFields, has two records:

                    Field Name  | Table Name
                    Crops            CropName
                    Year              ProductionHistory

                    If so, then this relationship:

                    Globals::Field1 = SearchFields::FieldName

                    would match selected field name to table name, but only for the first global field, field1. This could be set up with 12 different occurrences of SearchFields, each linked to a differnt global field, but that's a lot of relationships.

                    I can think of two alternatives:

                    Use a table for your search critieria that does not use global fields. This brings it's own complications, each value would need to be copied into a variable and if this is a multi-user system, you'll need to manage the records in this table by Account or user name to keep each user's criteria selections separate from each other, but would work for a single.

                    Or

                    You can use the method you said you didn't want to use. Pass the contents of the global field as a script  parameter to the script, switch layouts, perform the find on the SerachFields table, then copy the value into a matching global field so that you now have pairs of fields, one with the field name and one with the table name.

                    This can be done before you enter find mode and start setting up search criteria.

                    • 7. Re: find in a mapping table
                      Fagreement

                      ok thanks

                      but you point me a pbm that I may have: I'm using the global fields storage so it will not be corect if I use multi user search machine !

                      • 8. Re: find in a mapping table
                        philmodjunk

                        That's why I stated:

                        if this is a multi-user system, you'll need to manage the records in this table by Account or user name to keep each user's criteria selections separate from each other

                        Define a table, Users and create one record for each account name. Use that for the layout shown in your screen shot. Use a script to search this table for a record with the user's account name and that creates such a record if one is not found. Then the search criteria can be displayed in a portal linked to that one record--keeping the criteria specified by one user separate from the other users' criteria.

                        It would also be very easy to extract the data from these portal records into variables using the List function, then your script can enter find mode and loop through the values in the variables' lists. (One list could be a list of the fully qualified TableOccurrence::FieldNames and another could be the criteria to be entered into each.)

                        Also, as stated before, it's not your only option here.