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.
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!
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:
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
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?
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---------------
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.