2 Replies Latest reply on Jan 19, 2011 11:39 AM by HB

    scripts finding related records of one field in a recordset



      scripts finding related records of one field in a recordset



      I am a beginner. I am making a script to find a set of records that are related to one field in another set of records.


      table: Cars


      id is 12, TypeField is Ferrari, ColorField is red


      ID is 44, NameField is Capri, ColorField is red


      ID is 789 , NameField is Ferrari, ColorField is yellow


      ID is 122 , NameField is Camaro, ColorField is blue


      I want to do a search that finds all the ferrari's, then find all the cars sharing the colors of the Ferraris'

      -->in this case should eventually show 3 records

        • 1. Re: scripts finding related records of one field in a recordset

          Step one, finding the car type is easy. Step two, finding all matching colors regardless of car type can be a bit complex.

          This doesn't sound like a query I'd actually want to perform in the real world. (Why would you want to find the record for an economy car just because it was the same color as one of the Ferrari records?) That suggests that you may be creating an example in place of the real data you want to search on. Be aware that such an approach in describing your problem can lead to less than optimum suggestions should the person making it base their response on details from your example that don't match the real situation.

          That said, I'd use a relationship for finding the final list of records:

          CarTable::ColorField = CarTableByColor::ColorField.

          Where CarTableByColor is a new table occurrence of CarTable. ( Tutorial: What are Table Occurrences? )

          Then this script will work, if the user first selects/enters the cartype into the global text field, gCarType.
          Enter Find Mode[]
          Set Field [CarTable::CarType ; CarTable::gCarType] // this will only work if Global storage is selected for gCarType
          Set Error Capture[on]
          Perform Find[]
          Go To Related Record [show only related records; match found set; from table:CarTableByColor ; using layout: <current layout>]

          Note: <Current layout> must be specified for this to work correctly should there not be any records found.

          Note:  for car records at a car dealership, this should work fine. If you are searching very large numbers of records, you may encounter a delay during the Go To Related Record step.

          • 2. Re: scripts finding related records of one field in a recordset


            All the Ferrari's I have painted have come back with corrosion - I just want to check how the other cars are doing. Just kidding...yes its fictitious for clarities sakes.  Amt of records will be no less than 500 on the first and maybe 100 on the second.

            Thanks for the help - I was on the right track I think, but must have made an error - I'll check it closely against yours.

            Thanks again