5 Replies Latest reply on Jul 26, 2010 3:21 PM by philmodjunk

    scripting question

    MiriamSilverman

      Title

      scripting question

      Post

      I am moving from MS Access to Filemaker Pro. My programming skills are negligable and I am hoping that scripting is more user friendly and I can do what I need to in Filemaker.

      I write indexes for journals. The indexes have a section where each article is listed by authors. For example: an article titled Fun with Filemaker has 3 authors named Mary Smith, Jane Jones, and Happy Pappy. I would end up with 3 index entries. 1. Smith, Mary, Jones, Jane, and Pappy, Happy, Fun with Filemaker, pages 3-6; 2. Jones, Jane, Pappy, Happy, and Smith, Mary, Fun with Filemaker, pages 3-6; 3. Pappy, Happy, Smith, Mary, and Jones, Jane, Fun with Filemaker, pages 3-6. The order of authors is first the order they were entered, and then each successive entry moves first author to the end and everyone else moves up 1.

      In access, because of my pathetic programming skills, I had a table with title, page range, and fields for 4 authors. Using queries and macros I made tables with formatted index entries and exported them as .txt files for import into my indexing software.

      I sometimes have as many as 12 authors, and know the correct way to structure this is with a table for articles, and a related table for authors, which I have set up. My articles table includes these fields: key, title of article, page range, number of authors. My authors table includes: key, foreign key for articles table, first name, last name, suffix (Jr. for instance), and author number which increments by 1 for each author in that article. Now I need a way to format and export the data, which means for each article I have to generate as many records as there are authors, each with a different order of authors.

      Any help would be greatly appreciated!!

        • 1. Re: scripting question
          philmodjunk

          Seems to me you should have three tables instead of two (I'd do the same in Access also...).

          Articles----<Article_Author>----Authors

          That way, you have exactly one record in authors for each different author that can be assigned via the Article_Author join table to any number of different articles and any given article can be linked to any number of different article records...

          Question, does it truly matter who is listed 2nd in the list? Since you need to find each author by searching your alphabetized list, the first name in the list is critical, but seems to me which name is second or third is not.

          If so, Adding an extra table occurrence to link in other contributing authors would seem to do the trick here. To implement the above relationship, you'd have key fields like these:

          Articles::ArticleID = Article_Author::ArticleID
          Authors::AuthorID = Article_Author::AuthorID

          Making a second table occurrence of Article_Author, Et_Al and linking it like this:

          Article_Author::ArticleID = Et_Al::ArticleID AND
          Article_Author::AuthorID ≠ Et_Al::AuthorID

          You could define these calcuations in Article_Author to generate your index entries:

          AuthorName: Author::AuthorName
          IndexEntry: AuthorName & ", " & Substitute ( List (Et_Al::AuthorName) ; ¶ ; ", " )

          Now you can do a find of all Article_Author records for a given ArticleID, sort the records by IndexEntry and export the records to get your index entries.

          PS. To make the 2nd table occurrence in Manage | Database | Relationships, click the Article_Author box to select it and then click the button with two plus signs.

          • 2. Re: scripting question
            MiriamSilverman

            I actually have 4 tables. Articles, and authors, which I use as a portal in my articles layout. I also have a table of first names and one of last names that I use as drop down lists for entering an author. When I am done creating an index I delete all the records in articles, and authors. The only permanent data are the first and last name tables. I used to have a permanent list of authors, but separate first and last names turns out to be easier to work with. The majority of author names are never repeated.

            The order of names is important. I input the names in the order they appear in the Journal TOC, and they have to stay in that order. Editors can be very picky about details! That is why I included an author number (really an author sort number) in the authors table. First one in is #1 and so on from there, starting over at 1 for each new article. I thought I could sort by number (article number, then author number) instead of alphabetically. So, for each article I have linked authors in a set order. Each author record is unique because of it's article key + author number. Each article record has a field with the number of authors for that article. I need to calculate the formatted index entries and put them in a table for export to a .txt file. For each article I have to generate 1 record per author, with each of those records containing all the authors, in rotating positions. Each record will have a calculated field that looks like this, including punctuation

            author1, author2,..., and authorlast, article title

            and a second field with the page range for the article.

            I believe I need a script that, for each article, sorts the related authors and generates a record, then resorts the authors and generates another record. It would stop when it reaches the number of loops = to the number of authors for that article (which is in a field in the article table). Then it goes to the next article and does it all over again. To add just a little complexity to this, articles with 1 and 2 authors are formatted differently--their punctuation is different. In addition, the number of authors vary from article to article, and the last article in the formatted output has different punctuation in front of it than the rest of them.

            This was waay beyond my abilities in Access, so I used  make table queries to create calculated fields individually for each combination from 1 to 4 authors. After 4 authors I just created more than one record per article and edited them all in my indexing program. If I could do this right it would save me a lot of time.

            Below are the entries as they appeared in the index I sent my editor for one article with 7 authors, so you can see what the calculated field looks like that I have to create.

            Agrawal, Shipra, Kanthi, C.N., Naidu, K.V.M., Ramamirtham, Jeyashankher, Rastogi, Rajeev, Satkin, Scott, and Srinivasan, Anand. Monitoring Infrastructure for Converged Networks and Services, No. 2, 63–77

            Kanthi, C.N., Naidu, K.V.M., Ramamirtham, Jeyashankher, Rastogi, Rajeev, Satkin, Scott, Srinivasan, Anand, and Agrawal, Shipra. Monitoring Infrastructure for Converged Networks and Services, No. 2, 63–77

            Naidu, K.V.M., Ramamirtham, Jeyashankher, Rastogi, Rajeev, Satkin, Scott, Srinivasan, Anand, Agrawal, Shipra, and Kanthi, C.N. Monitoring Infrastructure for Converged Networks and Services, No. 2, 63–77

            Ramamirtham, Jeyashankher, Rastogi, Rajeev, Satkin, Scott, Srinivasan, Anand, Agrawal, Shipra, Kanthi, C.N., and Naidu, K.V.M. Monitoring Infrastructure for Converged Networks and Services, No. 2, 63–77

            Rastogi, Rajeev, Satkin, Scott, Srinivasan, Anand, Agrawal, Shipra, Kanthi, C.N., Naidu, K.V.M., and Ramamirtham, Jeyashankher. Monitoring Infrastructure for Converged Networks and Services, No. 2, 63–77

            Satkin, Scott, Srinivasan, Anand, Agrawal, Shipra, Kanthi, C.N., Naidu, K.V.M., Ramamirtham, Jeyashankher, and Rastogi, Rajeev. Monitoring Infrastructure for Converged Networks and Services, No. 2, 63–77

            Srinivasan, Anand, Agrawal, Shipra, Kanthi, C.N., Naidu, K.V.M., Ramamirtham, Jeyashankher, Rastogi, Rajeev, and Satkin, Scott. Monitoring Infrastructure for Converged Networks and Services, No. 2, 63–77

            • 3. Re: scripting question
              philmodjunk

              I don't see any need for separate tables for first and last names and I wouldn't if I were using Access either. The name information should be in your Authors table and any value lists you define for first and last names can draw their data from those fields in the Author table.

              Be that as it may, I believe a script can loop through your articles and authors to create author lists in the rotating alphabetized order you specify. The remaining text can then be appended from other text fields to form you index entries.

              We'll focus on this relationship:

              Article::ArticleID = Article_Author::ArticleID  (specify a sort order that sorts Article_Author records by LastName, FirstName for this relationship.)

              Whether and how you link other tables to this won't matter as long as you have one record for each author in Article_Author for a given article that either contains or can access (via a relationship) the Author's name. The index entries will exist in this table so that exporting them forms your text file for your index.

              The script:

              Go To Layout [Articles]
              Show All Records
              Go To record [ first ]
              Loop
                 Set Variable [$Authors ; value:  List ( Article_Author::AuthorName ) ]
                 If [ Not IsEmpty ( Article_Author::ArticleID ) ]
                     Go To Related Records [show only related records ; from table: Article_Author ; using layout: Article_Author]
                     Loop
                         Set Field [ Article_Author::AuthorList ; Substitute ( $Authors ; ¶ ; ", " ) ]
                         Set Variable [ $Authors ; value:  List ( RightValues ( $Authors ; ValueCount ( $Authors ) - 1 ) ; LeftValues ( $Authors ; 1 ) ) ]
                         Go To record [ next ; exit after last ]
                     End Loop
                     Go To Layout [Articles]
                 End If
                 Go To Record [next ; exit after last ]
              End Loop

              This builds the list of authors portion in the text field AuthorList. From there, it should be a simple matter to define a text field that combines AuthorList with the remaining text in your index.

              • 4. Re: scripting question
                MiriamSilverman

                You are definitely right about the separate tables for first & last names. What I was avoiding was doing what my previous database did: each record was a complete name. For the purpose of choosing a name (or appending it to the table) I wanted them separate. But very late at night it didn't occur to me that I could simply reference different fields in the same table.

                The script looks like what I am trying to do, except for sorting alphabetically. The authors are usually not listed alphabetically in the journals, and I need to follow the order in which they are printed. So I guess I have to sort by number instead of name. Thanks for setting me on the right track!

                • 5. Re: scripting question
                  philmodjunk

                  Your 7 author example shows the authors listed alphabetically for the first entry and then the names rotate one author to the left for each subsequent entry. If there's a different order, just specify that order in the relationship and it'll still work as long as the "rotate one name to the left" strategy still gives you the order you need for each subsequent entry for the same group of article authors.