You'll need to tell us about your tables and relationships.
Does each row of data shown represent a single record or are all these different fields from the same table. Any repeating fields? What version of FileMaker are you using?
Fields in product type:
Product Type ID(An ID number is automatically created for this field when a new product is created)
Capacity Typei.e. (KG, LT and CM) (When entering a product to a customer site A KG, LT or CM is added automatically)
Size(this field is used to enter the size of the product manually i.e. 2, 6, 9)
Fields in site products:
Site Product ID (Each product added to site automatically get a unique number)
Blanket_Count (Calculation for BlanketCount)
BlanketCount (Field on Inspection Report to give total)
CO2_Count (Calculation for CO2Count)
CO2Count (Field on Inspection Report to give total)
DryPowder_Count (Calculation for DryPowderCount)
DryPowderCount (Field on Inspection Report to give total)
Foam_Count (Calculation for FoamCount)
FoamCount (Field on Inspection Report to give total)
HydroSpray_Count (Calculation for HydroSprayCount)
HydroSprayCount (Field on Inspection Report to give total)
HoseReel_Count (Calculation for HoseReelCount)
HoseReelCount (Field on Inspection Report to give total)
Hydrant_Count (Calculation for HydrantCount)
HydrantCount (Field on Inspection Report to give total)
Water_Count (Calculation for WaterCount)
WaterCount (Field on Inspection Report to give total)
WetChemical_Count (Calculation for WetChemicalCount)
WetChemicalCount (Field on Inspection Report to give total)
RC(This field is used to entered a date automatically next to the product it the product is due for recharging)
Site Products ----- to ----- Customer Site
Site Products ----- to ----- Product Type
Inspection Report ----- to ----- Product Type
The first column needs to show size, sound column to show capacity type, third column to show quantities, forth column to show the quantities due for recharging.
The Additional ReCharge column are left blank so when I visit a site if any additional ReCharging is carried out I will fill in manualey.
Referring to the image at the bottom of your last post, what does one row represent? A group of records in Site Product for a specific site and of the same Type?
If so, you can set up a list view summary report based on Site Product where you remove the body layout part and add a Sub Summary part "when sorted by Type".
Perform a find or use Go To Related Records to pull up all the Site Product records for a specified site, sort them by Type and you'll have your list with one row for each type of fire extinguisher located at that site. Summary fields can produce the numbers needed for Quantity and ReCharge Due.
I have attached a photo of the layout that I wont, The size, Type, RC Due & Additional RC I have entered manually for you to view The Numbers are added from created field however they are made up from individual fields within site products.
I believe the body area needs to stay to list all products on site. I also use Footer for page numbering and tital footer is set up for last page numbering as well as the text that is entered.
The Traling Grand Summary in used for the total's.
I do find that if a site has a lot of products the Title Footer prints on the first page with some of the products in the Body above Title Footer and some will print below Title Footer and the Trailing Grand Summary will print last.
So I need to print in order as follows.
Title Header, Body, Trailing Grand Summary and finally Title Footer.
I also would like to only print the items in the Trailing Grand Summary if there is a value with the items moving up so there are not any spaces, in the attached photo you can see there are not any Foam, Blankets or Wet chemical these do not need to be printed.
If I need to change parts of the document or add/change any field I will do so.
THANK YOU FOR YOUR HELP
So the totals that you need are the "summary Recap" found in the trailing grand summary...
If you have FileMaker 12, you can use a large field set to slide up/resize enclosing part with ExecuteSQL used to populate the field with the needed rows of summary data: http://forums.filemaker.com/posts/2d245a1d51.
If you are using an older version of FileMaker, you can set up a portal with one row for each type of fireextinguisher placed at the site being inspected.
The relationships you'd need to set up will require adding more table occurrences of for your existing files. In the following realtionships, the data source table is given in ALLCAPS.
SITEPRODUCTS::_fkProductID = SiteProductsPRODUCTTYPES::__pkProductID
SiteProductsPRODUCTTYPES::__pkProductID = SiteProductsProductTypesSITEPRODUCTS::_fkProductID AND
SiteProductsPRODUCTTYPES::gSiteID = SiteProductsProductTypesSITEPRODUCTS::_fkSiteID
gSiteID is a field defined with global storage. You'll need to use a script--either from an OnLayoutEnter trigger or as part of the script that finds and sorts your records for the report To set it to the desired siteID value.
Summary fields from SiteProductsProductTypesSITEPRODUCTS can be used for the counts and totals or you can define calculation fields in ProductTypes that evaluate "from the context of SiteProductsPRODUCTTYPES" that total or count data from SiteProductsProductTypesSITEPRODUCTS.
Thanks for the information, Can you explain a little moor please, were and how to create the additional fields and global field, Thanks
The allcaps portion of the table occurrence name identifies the name of the data source table.
So SiteProductsPRODUCTTYPES::gSiteID refers to a field you'd define in Product Types. Field Options would be used with this field to specify global storage. You open Field Options by double clicking the field definition in Manage | Database | Fields or by selecting it and clicking options.
In like manner the summary fields, if you use them, would be created in SITEPRODCTS, but when adding them to the portal row, you'd select them from the SiteProductsProductTypesSITEPRODUCTS table occurrence.
And if you used the calculation fields, they'd be defined in ProductTypes just as you did with gSiteID. But calculation fields have a "context" specified in the "context" drop down at the very top of the specify calculation dialog. These calcualtions would need to specify: SiteProductsPRODUCTTYPES as the context and should refer to fields from the SiteProductsProductTypesSITEPRODUCTS table occurrence, such as:
Sum ( SiteProductsProductTypesSITEPRODUCTS::Qty )