2 Replies Latest reply on Jan 28, 2010 6:29 PM by k1ngf1sher

    Include sub-summary data in related table

    k1ngf1sher

      Title

      Include sub-summary data in related table & create new record in related table

      Post

      I am working with a relational database in FM 9 Pro Advanced and I’m currently stuck on two issues.  The goal of this database is to create a management plan based on location data (parent table; ‘Locations’) and summary statistics that are calculated in a report based on a related table (‘Samples’).  Here are the details:

      I use the parent table (‘Location’) to describe the location of livestock operations.  On a layout (‘MMP’) based on the ‘Location’ table, I would like to include summary calculations from a related table (‘Samples’).   For each Location, I have several related entries in the ‘Samples’ table that are based on the dates when the samples were collected.  I created a script to summarize data (averages) by dates from a found set of records in the ‘Samples’ table.  How would I present these averages in a table in the ‘MMP’ layout?  Would a portal be involved?

      Once I have this summary information (i.e., averages), I then want to creat a new record in a related table (‘Management’).  On the ‘MMP’ layout, I believe I can use a field (Management::Plan_type) to create a new record in the ‘Management’ table, but how do tell FM to copy all the current information (including the location information and specific averages that were calculated from the ‘Samples’ table) from the ‘MMP’ layout into fields with the same names in the ‘Management’ table?

      Thank you for your time and attention to my message.  I would appreciate any help in solving these issues.

      Cheers,
      K





        • 1. Re: Include sub-summary data in related table & create new record in related table
          philmodjunk
            

          "I use the parent table (‘Location’) to describe the location of livestock operations.  On a layout (‘MMP’) based on the ‘Location’ table, I would like to include summary calculations from a related table (‘Samples’).   For each Location, I have several related entries in the ‘Samples’ table that are based on the dates when the samples were collected.  I created a script to summarize data (averages) by dates from a found set of records in the ‘Samples’ table.  How would I present these averages in a table in the ‘MMP’ layout?  Would a portal be involved?"

          I recommend that you design a summary report based on your samples table that groups sample records by location and date. You can put fields from the location table into sub summary parts to include location data for each group of sample records. Here's a link to a simple tutorial on setting up summary reports that you may find useful (Think of Invoices as your location table and lineitems as your sample table):

          Creating Filemaker Pro summary reports--Tutorial

           

          "I then want to creat a new record in a related table (‘Management’).  On the ‘MMP’ layout, I believe I can use a field (Management:: Plan_type) to create a new record in the ‘Management’ table, but how do tell FM to copy all the current information (including the location information and specific averages that were calculated from the ‘Samples’ table) from the ‘MMP’ layout into fields with the same names in the ‘Management’ table?"

          There are a variety of method for "copying" data from one table into another, but first you have to ask a key question: "Do you really have to do this?" As shown in the above tutorial, you can include data from a related record from a different table on a layout and this often enables you to get the effect of copying the data without the work of doing so. In addition, should changes be made to the data in its original table, you won't have to do anything to update the "copied" data to match.

          • 2. Re: Include sub-summary data in related table & create new record in related table
            k1ngf1sher
              

            Phil, Thanks once again for your reply; your comments have helped me on several occassions and I really appreciate your contributions to the FM forum.

             

            A couple of things related to my post and your comments:

            My first issue I didn't clearly state because your response was something I had already done.  What I want to do is take the summarized data that was generated in a separate summary report and include the results (the averages) in my MMP layout.  How would I run the report and then include that information in the MMP layout?

             

            My second issue: let me explain a little more about my reasoning for populating the 'Management' table with a new record based on all the information gathered in the MMP layout.  My client would like to pull together the information in the MMP layout, but then save that data as a snapshot in time (since additional Sample data is always being collected) that he can refer back to within the database, rather than print .pdfs of the completed layout and store them on his server.  With that in mind, could you recommend a way to populate another table with the data assembled in the MMP layout.

             

            Thanks again for your assistance.

            K