12 Replies Latest reply on Oct 26, 2011 10:16 AM by philmodjunk

    Query a table to produce a total based on number of records and show the results in a field of...

    kpmoy

      Title

      Query a table to produce a total based on number of records and show the results in a field of another table

      Post

      I have several tables for our department. 

      Table 1:  Names of Faculty in our Department

      Table 2:  Published Documents by our Faculty (usually more than 1 faculty member per document)

      Table 3:  Awards Received by Faculty

      etc.

      I'm new to Filemaker Pro so I may not have the correct terminology.  Is there a way to put a field in the Faculty Layout that says "Documents Published in 2010" that will query the documents in Table 2 (that show the faculty member as being one of the authors) and return a quantity in that field?

      Would this be the correct way to set up a database for what I'm trying to do?

      Thanks!

       

       

       

       

        • 1. Re: Query a table to produce a total based on number of records and show the results in a field of...
          philmodjunk

          Since you a Faculty Member can publish more than one document and a document can be published by more than one Faculty Member, you have a many to many relationship here. How did you implement that relationship?

          Useing a separate relationship that matches by publication year and Faculty ID number you can get that count, but the details depend on the structure of your database.

          And a further question:

          What will you do next year? Do you want to see one total for 2010 and one total for 2011 or should the count of documents for 2010 automatically update from documents published in 2010 to documents published in 2011?

          • 2. Re: Query a table to produce a total based on number of records and show the results in a field of...
            kpmoy

            The Faculty database has a unique id # for each faculty member

            The Documents database has the year the document was published and the unique id # for each faculty member that collaborated on the document, each listed in a separate field.

            I would like to have a field on the Faculty page showing:

            Documents published in 2010 : #

            Documents published in 2011: #

            etc. 

            I want to leave the 2010 and 2011 count there when 2012 rolls around and then have a 2012 count as well.

            Should I put all of the collaborators in a single field separated by commas or keep them in separate fields like I have them?

            • 3. Re: Query a table to produce a total based on number of records and show the results in a field of...
              philmodjunk

              You need to get rid of those separate fields as they complicate the relationships needed to match records. I'll describe a work around you can use at the end, but will stress that it's a poor second to the more inolved change that I am recommending you do instead.

              You should add a new table to manage the many to many relationship between faculty members and the documents tables:

              faculty----<Faculty_Document>------Documents

              Faculty::FacultyID = Faculty_Document::FacultyID
              Documents::DocumentID = Faculty_Document::DocumentID

              With this structure, a portal to Faculty_Document can be placed on the Faculty layout to list all documents published by that faculty member. A portal to Faculty_Document placed on the Documents table would make it possible to list all Faculty Members listed as co-authors of that document. This approach is fully flexible when it comes to linking faculty members to a given document as it will support any number of authors needed.

              Add a PublicationDate field to Faculty_Document that looks up the publication date from the documents table. We'll use this indexed field to get a documents published by year count.

              Add a new table: PublicationYears. You'll need to add one record each year ot this table that records the current year. Define gFacultyID in this table and specify global storage for it.

              Create a new occurrence of Faculty_Document and name it PubYearFaculty_Document.

              Build these relationships:

              Faculty::anyfield x PublicationYears::anyField

              PublicationYears::PublicationYear = PubYearFaculty_Document::PublicationYear AND
              PublicationYears::gFacultyID = PubYearFaculty_Document::FacultyID

              Define a "count of" summary field in Faculty_Document that counts a field in that table that is never empty such as the DocumentID field.

              Set up a portal to PublicationYears and put the Summary field from PubYearFaculty_Document in the portal.

              Finally, add a script with these two steps:
              Set Field [PublicationYears::gFacultyID ; Faculty::FacultyID ]
              Commit Record

              and use the OnRecordLoad script trigger to perform this script each time you access a record on the faculty layout.

              Here's the alternate method:

              keep your separate fields, but add this calculation field to the documents tables: cAuthorList and define it as:
              List ( FacultyIDField1 ; FacultyIDField2 ; FacultyIDField3 ) and so forth so that all facultyID fields in Documents is includded in this list of fields. If you ever have to add another FacultyID field, you'll need to update this calculiton field to also include it.

              You could also replace your group of FacultyID fields with a single repeating field. This takes more work to use to update from existing records in Documents, but makes adding additional ID fields, should they be needed, easier to do.

              Define this relationship:

              Faculty::FacultyID =Documents::cAuthorList (or the repeating field would go here). in order to be able to list all documents authored by a given Faculty member in a portal to Documents.

              Your relationships then become:

              Faculty::anyfield x PublicationYears::anyField

              PublicationYears::PublicationYear = PubYearDocuments::PublicationYear AND
              PublicationYears::gFacultyID = PubYearDocuments::FacultyID

              Where an occurrence of Documents replaces an occurence of Faculty_Document.

              You'll still need that triggered script to set the global field with the Id of the current Faculty record. So you have much the same complexity either way, but with less flexibilty than you have with the Faculty_Document table serving as a "join table" linking the two. (On the other hand, you won't need to copy the publication year into the matching join table record like you do with the join table.)

              Here's a recently updated demo file on many to many join tables you may find worth examining: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

              • 4. Re: Query a table to produce a total based on number of records and show the results in a field of...
                kpmoy

                "Set up a portal to PublicationYears and put the Summary field from PubYearFaculty_Document in the portal."


                Sorry, when did you have me set up a summary field?  You're not referring to "Define a "count of" summary field in Faculty_Document that counts a field in that table that is never empty such as the DocumentID field", correct?

                • 6. Re: Query a table to produce a total based on number of records and show the results in a field of...
                  kpmoy

                  I'm confused on this part:

                  Finally, add a script with these two steps:
                  Set Field [PublicationYears::gFacultyID ; Faculty::FacultyID ]  Should these be on the same line?  How do you do that?
                  Commit Record

                  and use the OnRecordLoad script trigger to perform this script each time you access a record on the faculty layout.

                   

                  • 7. Re: Query a table to produce a total based on number of records and show the results in a field of...
                    philmodjunk

                    A script is what you create with Script Manager by selecting Manage | Scripts and then clicking the "New" button. You'll find that Set field and commit

                    record are steps available when you write a script.

                    Give this script a name such as "Update gFacultyID"

                    Then enter layout mode, select layout setup and select the Script Triggers tab.

                    Select OnrecordLoad and specify that this trigger run "update gFacultyID".

                    • 8. Re: Query a table to produce a total based on number of records and show the results in a field of...
                      kpmoy

                      Yes, I see that "Set Field" and "Commit Record" are available steps, but when I set up the "Set Field", I can only do it on 2 separate lines: 

                      Set Field [PublicationYears::gFacultyID}

                      Set Field [Faculty::FacultyID]

                       

                      Instead of both on one line like you've shown.  How do I get them both on the same line within the some brackets?

                      Set Field [PublicationYears::gFacultyID ; Faculty::FacultyID ]

                      • 9. Re: Query a table to produce a total based on number of records and show the results in a field of...
                        philmodjunk

                        When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                        • 10. Re: Query a table to produce a total based on number of records and show the results in a field of...
                          kpmoy

                          I apologize for what must be seemingly very basic questions.  I literally starting using Filemaker Pro 2 days ago.

                          Add a PublicationDate field to Faculty_Document that looks up the publication date from the documents table. We'll use this indexed field to get a documents published by year count. 

                          When I created this field as a "lookup", it no longer says it's an indexed field, correct?


                          Add a new table: PublicationYears. You'll need to add one record each year ot this table that records the current year. Define gFacultyID in this table and specify global storage for it.


                          What does it mean when you put "g" in front of a field name?  Is created in a different way than a regular field?  I just created a field (like any other) and checked "global storage". 

                          • 12. Re: Query a table to produce a total based on number of records and show the results in a field of...
                            philmodjunk

                            I literally starting using Filemaker Pro 2 days ago.

                            You're doing very well for such a "newbie". Do you have prior experience with other database systems?

                            You may want to put this project on hold and invest some time in getting up to speed on FileMaker before returning to finish this project. This is an unusual project due to the need to match values from three tables instead of the usual two. Using a script to set a value in the global field is the only method I could come up with to make this work. I'd welcome an alternative approach that isn't so messy.

                            Frankly, your project sounds so much like another one where I'd helped out, that I assumed a much higher level of experience and expertise on your part. Apologies for that assumption! There are a number of training resources and books on FileMaker that you can invest in to help get up to speed.

                            When I created this field as a "lookup", it no longer says it's an indexed field, correct?

                            This should be defined as a number field. In Manage | Database | Fields, double click it's definition to bring up Field Options. Select the Auto-enter tab and then choose the looked up value option in order to set this up by selecting the publication year field from the related table. It should not show up as "unstored" or "unindexed"--indications that you did something different, perhaps defining a calculation field instead of a look up.

                            What does it mean when you put "g" in front of a field name?

                            This is just a common naming convention for identifying a field as a global field that you will find is used by many developers. The field won't become a global field just because you put that g in the beggining of the name, however. You have to bring up field options and specify global storage on the storage tab. (See my instructions for the looked up value settings above.)

                            Here are some links that you may find helpful in getting up to speed:

                            Many to many demo file: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

                            What are Table Occurrences?: Tutorial: What are Table Occurrences?

                            Anchor Bouy: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/