5 Replies Latest reply on Mar 18, 2011 8:28 AM by philmodjunk

    Need help with record summary!

    JohnDeere

      Title

      Need help with record summary!

      Post

      I have a database that contains chemical records.  There are five fields in each record that contain chemical names.  Each field has a unique name.   Each of these fields are populated by the same pop-up value list, which contains about 20 chemical names. Each record is a different combination of these same chemicals.  Each record may have 1-5 of these fields filled.  Each chemical field also has a Qty. field associated with it, also each with a unique field name.

      Heres my problem.  I want to be able to get a summary of the total qty of each chemical used for a group of records.  They will also be grouped by other criteria in each record.  (region, state, etc)  The problem is that each chemical could be in any of the 5 chemical fields, and in different combinations depending on the order the user entered them.  How do I get the total qty of chemical "A", when it can appear in any of the 5 fields in each record??

      I would really appreciate some help!

      thanks in advance.

        • 1. Re: Need help with record summary!
          philmodjunk

          Take your 5 different fields and replace them with 5 separate records in a related table. Now you can generate summary reports based on this new related table, but pulling data as needed from your original table and need no longer deal with the issue of having a given chemical listed in different fields of different records.

          Your original 5 fields might be replaced with a 5 row portal to thenew related table for this purpose, though with this change, you are no longer limited to 5 chemicals per record.

          • 2. Re: Need help with record summary!
            JohnDeere

            OK, I understand what you are saying. But now I can't figure out how to have multiple records from another table, in one record from my current layout.?? I know I can put fields from a different table into my layout, but how do you get multiple records from that table into just 1 record??  I understand what you are saying about using a portal, but I think your first suggestion is what I want to do.

            thanks again for your time!

            • 3. Re: Need help with record summary!
              philmodjunk

              Using a portal is exactly how you would have "multiple records form another table in one record from my current layout." That would be useful for data entry and general "browsing". A report layout based on the portal's table, not the original table, would be used for your summary report.

              Take a look at this simple invoicing demo file created and uploaded by Comment: 

              http://fmforums.com/forum/showpost.php?post/309136/

              When you look at it, think of the "invoices" table as your current table. LineItems as the new table that I am recommending here for listing each chemical and "Products" could be a table where you can list each chemical a single time with needed info unique to that chemical. (Molecular weight, VOC, Density, Viscosity, etc.)

              Note how you can enter and record data in a portal. Note how you can print out an invoice. This method for printing the invoice that does not use a portal is a good starting point for how to set up your summary report as you can list any number of items from multiple invoices on this layout.

              This tutoial an summary reports may also be useful:  Creating Filemaker Pro summary reports--Tutorial

              • 4. Re: Need help with record summary!
                JohnDeere

                You have been very helpful, thank you for your time.  Ok, now I have a portal in my original layout with 5 records from a related table, which contain the chemicals used in a single record from the original table.  I having this portal able to add records as needed, up to 5, on my original layout.  This part works fine.

                To better clarify what I am doing, this DB is chemical application records for a farm, to meet EPA requirements.  Each record in the main table/layout contains a single spray "job", which includes  "state", "farm-name", and "plot-name" fields to indicate location of spray job.  What I want is a summary report to give the total units of each specific chemical applied to a specific "farm-name" and then a grand total of those which would be by state.  The portal for the 5 chemicals applied, already has the total units of each chemical used for each "job".  My report layout is doing this fine but......

                My problem now is, when I created a report layout (I used the report generator), it only shows the first record from the chemicals portal that is in each individual spray record from the original layout.  The "state" and "farm name" fields are in the original table, which are used in the sort criteria, the rest of the data (chemical name and total qty) are in the portal from the related table.  My guess is, that if the "state" and  "farm-name" fields where also in the portal, this would not be happening because all report data would be coming from the portal alone.

                Note: I have a auto-serial number in the original table that is being used as a "spray job ID".  I have also related this to the chemical portal with a "spray job ID" field.  It is correctly putting the same "spray job ID" number into each of the 5 chemical records that can be added in the portal for each spray job.

                What am I doing wrong?

                thanks again;

                • 5. Re: Need help with record summary!
                  philmodjunk

                  I take it that you have this relationship:

                  Applications::ApplicationID = ChemicalsApplied::ApplicationID
                  Chemicals::ChemicalID = ChemicalsApplied::ChemicalID

                  Your layout should be based on ChemicalsApplied with at least these layout parts:

                  Sub Summary when sorted by Applications::FarmName---------------

                  Applications::FarmName

                  Sub Summary when sorted by Chemicals::ChemicalName------------

                  Chemicals::ChemicalName, ChemicalsApplied::sTotalApplied and any other fields specific to one chemical

                  Sub Summary when sorted by Applications::FarmName------------

                  Summary fields for totals for one farm (such as total billed for application) go here

                  Note that there is no "body" layout part, you can have headers, footers and grand summary parts at the beginning and end of your report if you need them, but you'll need at least the above parts to get the report I think that you want here.