4 Replies Latest reply on Sep 29, 2014 1:55 PM by MichaelHart

    Script to Find All Records in Related Table

    MichaelHart

      Title

      Script to Find All Records in Related Table

      Post

      I have a DB with these related tables: Artists, Titles, Impressions. The Artists and Titles are one-to-many. Likewise, the Titles and Impressions are related one-to-many. All 3 tables have Firstname and Lastname fields for each record, as well as, serial # fields (Title_ID, Impression_ID, Artist_ID). The Artist_ID field in the Impressions-table records are, as yet, empty. Currently, the first and lastname fields are related but that is awkward due to common lastnames, common titles (ie - Untitled) and spelling mistakes. I think it would be better if I could relate a single field (Artist_ID) among the 3 tables, instead of the First and Lastname fields... that way spelling typos and common names wouldn't matter.

      My idea is to have a script that will display all the Impressions by a given artist so I can then populate the Artist_ID(s) into the corresponding Impressions-table records by utilizing Replace Field Contents function.

      I've only got rudimentary scripting skills and am going in circles trying to figure it out via trial and error so, was hoping someone could point me in the right or better direction before I spin off into space.

      I'm using FMP-13.

      Thanks

        • 1. Re: Script to Find All Records in Related Table
          philmodjunk

          I think that your relationships are:

          Artists----<Titles-----<Impressions (---< means "one to many" )

          Apparently, you have these match fields:

          Artists::Firstname = Titles::FirstName AND
          Artists::Lastname = Titles::LastName

          Titles::FirstName = Impressions::FirstName AND
          Titles::Lastname = Impressions::Lastname

          When you should have:

          Artists::Artist_ID = Titles::Artist_ID
          Titles::Title_ID = Impressions::Title_ID

          The Artist_ID field in the Impressions-table records are, as yet, empty.

          If I have correctly recreated your relationships, this field should be removed from the Impressions table as it is not needed.

          If your current names in the first and last name fields are correctly matching by name, you can set up the needed links by using Replace Field Contents to copy over all the Artist_ID and Titles_ID values.

          Example:

          Go to a layout based on Titles.
          Show All Records
          Click or tab to put the cursor in the Titles::Artist_ID field
          Select Replace Field Contents from the Records menu and select the calculation option.
          Select Artists::Artists_ID as the sole term in the calculation.
          Do the replace Field Contents operation.
          Now update your relationship between Artist and ID's to match by Artist_ID instead of by the name fields.

          Note:

          My assumption is that your statement: "Likewise, the Titles and Impressions are related one-to-many." means that you have possibly many records in Impressions linked to one record in Titles. If that is correct, you would not link records in Impressions to records in Artist by Artist_ID, nor would you link Impressions to TItles by Artist_ID unless you intend a signicant change in your relationship where your impressions link to an artist instead of a title. This would produce the same set of impressions for every title linked to the same Artist record.

           

          • 2. Re: Script to Find All Records in Related Table
            MichaelHart

            Thanks. That makes sense. Your assumptions are mostly correct.

            "If your current names in the first and last name fields are correctly matching by name, you can set up the needed links by using Replace Field Contents to copy over all the Artist_ID and Titles_ID values."

            Therein lies the rub. The names are not all correctly matching between Artists, Titles and Impressions. I am working with imported tables where some data is missing or incorrect (mis-spelled, double-entries, etc.). In a few cases, the Artist names are "unknown" or "?" and so I'm getting doublings of Impression records in some of my layouts such as Drawer-Contents and/or Packet-Contents (where the prints are archived). 

            I'll try the Replace Field Contents procedure as you outlined. I see I was doing it wrong (not using a calculation). 

            Your point about not needing the Impression::Artist_ID is well taken but for my need to rehab the old tables. I guess I should work on making the screwed-up first and last names to be unique somehow like, assigning numbers to "unknown" and/or "?". That might be simpler.

            • 3. Re: Script to Find All Records in Related Table
              philmodjunk

              It sounds like you'll need to do a lot of data review and "fixing" as you go. For example, you can do a find for an artist name using the first few letters of their name(s) and maybe some wild cards or you can use multiple find requests or extend found set. Then review your list and omit any records that aren't for the same artist. Once you have a found set of records all for one artist, you can use replace field contents on one record that does correctly match by name to pull over the ID number for all records in your found set by NOT using the calculation option but just the "current value" setting.

              This could be a very tedious process with a lot of checking back and forth. Got a bright eager intern to stick with the task? wink

              • 4. Re: Script to Find All Records in Related Table
                MichaelHart

                Where is the Bright Eager Intern button, anyway? That's what I was looking for, all along. But no..... I can only find the tedious process button. wink