4 Replies Latest reply on Dec 5, 2016 9:44 AM by anchhor

    Get Record Number where field is equal to x

    anchhor

      Hi,
      I would like to search for a data record in my layout recordset where the content of a certain column equals my variable. Is that posible?

       

      I am trying to go through my parent_ID's and build the breadcrumb trail with the names of each row.

        • 1. Re: Get Record Number where field is equal to x
          erolst

          Try a script

           

          Enter Find Mode [ no restore ]

          Go to Layout [ LayoutToSearch ( TableToSearch ) ]

          Set Field [ TableToSearch::fieldToSearch ; $x ]

          Set Error Capture [ on ]

          Perform Find

          Set Variable [ $foundID ; TableToSearch::id )

          Go to Layout [ original ]

          # [ use $foundID ]

           

          or, if you want to delve into SQL:

           

          ExecuteSQL ( "

            SELECT id

            FROM TableToSearch

            WHERE fieldToSearch = ?

            " ; "" ; "" ; $x

          )

           

          or create a relationship based on a utility field and the field to search, set the utility field to $x and read the value from the related record (if any).

           

          Consider what to do in these scenarios if there is more than one record where fieldToSearch matches $x.

          • 2. Re: Get Record Number where field is equal to x
            David Moyer

            Hi,

            there are many ways to go about this.  Is the value you are searching for unique in the table? record set?  Is the record number really what you want?

            There's a method of populating a global field in your parent table with the search criteria.  This global field is then related to the data field in your child table.  This relationship can be used to retrieve the primary key of the child record.

            • 3. Re: Get Record Number where field is equal to x
              philmodjunk

              Please note that a Record number as returned by the Get ( Recordnumber ) function is just the record's position in the current found set. So it's important to distinguish between getting a record's unique ID--best gotten from a field set up as the primary key in your table, from it's current position in the found set--which can change simply by performing a find or re-sorting your records.

              • 4. Re: Get Record Number where field is equal to x
                anchhor

                Thanks. I used SQL and left all that record number business aside. Worked like a charm.