9 Replies Latest reply on May 12, 2011 11:09 AM by philmodjunk

    Tabulating a list

    RGoyette

      Title

      Tabulating a list

      Post

      I have a database of proposals that lists the name of the principal investigator as well as other team members.  Lets say I have two-hundred proposals, and  John Smith is the principal investigator on twelve of them.  I want a script that finds all twelve of John's proposals, getting the proposal number and title and then sends him an email asking him to review all his proposals, listing them on seperate lines, showing the proposal number and title on each line.  I do not want to include in that list proposals where he is a team member, but not the principal investigator.

      I can perform a simple find that shows the proposals where he is the PI, but I am having trouble generating the list of twelve proposals (number and title concatinated) to include in the email.

      Suggestions?

      FileMaker Pro version 11 on WIndows 7.

        • 1. Re: Tabulating a list
          philmodjunk

          From the layout that's current when you send the email, are you able to create a portal that lists the data like you want to see it in the email?

          If so, use a calculation field set to return text that combines the data from your various fields to form one line of this list that you want. Define this calculation in the same table you would use for the portal. Then the calculation List ( relatedTableName::CalcField ) will generate your list for inclusing in the body of the email.

          • 2. Re: Tabulating a list
            RGoyette

            I naively thought that I would just perform a find using the PI name, and then create an email message using List(PRPSL_ID) to generate a list of twelve proposals for which he is the PI.  Unfortunately what it did was gave me a list of only one proposal:  the one I had selected.

            • 3. Re: Tabulating a list
              philmodjunk

              That's why I described the technique in terms of a related table of records.

              Say you have this table structure:

              People::PeopleID = Projects::TeamLeadID

              A portal to Projects from People will list your 12 projects. (And more sophisticated relationships can be used to link to selected projects for a given team leader so you can omit projects that are now finished, for example.)

              Then List can be used from a People Layout to produce your list of projects.

              You can do what you want from a find, but it takes a more complex script and a few more layouts.

              1. Put just the fields you want in your list on a layout.
              2. Perform your find on this layout and use CopyAllRecors to copy the data to the clipboard.
              3. Now change layouts and paste into a text field. The copied data will be pasted with tabs separating fields and returns seprating records.
              4. refer to this text field in your email.
              • 4. Re: Tabulating a list
                RGoyette

                I think this is going to work.  Right now I can generate the list of proposals for each PI, and can generate the bosy of the email.  One final stumbling block is how do I set up a layout that shows the name of each PI and all the proposals.  I cannot get all the PI's proposals consolodated on one page.  So if Joe Smith has twelve proposals, I want one page that has the name Joe SMith at the top and then a list of twelve proposals below the name.  Right now I get twelve pages each with the name Joe Smith at the top, but only one proposal number below.

                • 5. Re: Tabulating a list
                  philmodjunk

                  Is this the layout of a report that you will save as PDF and attach or is this what you are getting when using Copy All Records to copy the data to the clipboard?

                  • 6. Re: Tabulating a list
                    RGoyette

                    I did not go the copy all records route, i used the relationship you suggested and that gave me what I wanted.  I am trying to create a layout that has one page per PI, with all the proposals for that PI.  I will not save the layout as pdf, but I will use it to gather statistics on each PI (how many proposals they have, etc).  I will also locate a button on each PI's page that runs the script to send the email.  Right now, my main problem is aggregating all the PI's proposals on one page.  Is it sub-summary when sorted by PI Name?  That seems to ring a bell, but still does not  get me what I want.  Something like this is what I want:

                    Joe Smith

                    101

                    103

                    104

                    211

                    ---new Page---

                    Amy Clark

                    210

                    301

                    306

                    411

                    ---new page---

                    You get the idea.  What I get now is:

                    Joe Smith

                    101

                    ---new page---

                    Joe Smith

                    103

                    ---new page---

                    so you see my problem.

                    • 7. Re: Tabulating a list
                      philmodjunk

                      Printing the report from a layout is a different task with different solutions than what we started out to do here.

                      On which table is the layout based? People or Projects? (Use your names in place of mine)

                      A list view layout based on projects can be set up where you put the name field(s) in a sub summary part like you describe. You can add another empty sub summary part with the same "sorted by" field and only a few pixels tall, but with the "print below" option. Then you can specify a page break after every occurence to force a new page with each new person.

                      • 8. Re: Tabulating a list
                        RGoyette

                        How about this issue instead:  I can make a list of all proposals for which a user is a PI and use List(PRPSL_ID) in an email that shows the PI which proposals he has in the system, but how do I generate the same kind of list only for those proposals marked COMPLETED?  I can do a find and omit in my list view so that only the completed ones show up, but when I run the script which uses List(PRPSL_ID) I get all of his proposals, not just the completed ones.

                        • 9. Re: Tabulating a list
                          philmodjunk

                          I assume your are using: List ( RelatedTable::PROPSL_ID ) ? Don't see how just the field name would work here.

                          If so, then you'd need to use a relationship that only links to Completed proposals for this individual. Here's where performing a find and using copy all records (or a loop if you don't want to alter the clipboard like this) is sometimes a better option as you can more easily adjust for different criteria without having to define a bunch of relationships.

                          Here's a relationship that would only link to completed proposal records:

                          People::PeopleID = CompProposals::PeopleID AND
                          People::constCompleted = CompProposals::Status

                          CompProposals is a new table occurrence of Proposals. Status is the field where you enter "completed" to show that it's completed and constCompleted is a calculation field defined to always return the text "completed" so that the relationship only links to completed records.