7 Replies Latest reply on Jul 10, 2012 6:46 AM by philmodjunk

    Product Sorting

    RobinNeeves_2

      Title

      Product Sorting

      Post

      Please can you help,

      I want to be able to slide up in my layout, I have A product type, size & recharge due date example below, I only want to show items that are on a customer site, the size may very and sum may be due for recharging, I have added these to Trialling Grand Summary but don't know if this can work in this part of the document, I am using in my document Title Header, Body, Trailing Grand Summary, Footer & Title Footer. Can this be don.

      Size     Type     How Many     Recharge Due

        9      Water          2                  1

        2      Co2            4                   2

        5      Co2            3                   0

        • 1. Re: Product Sorting
          philmodjunk

          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?

          • 2. Re: Product Sorting
            RobinNeeves_2

            Tables:

            Custormer Site

            Product Type

            Site Product

            Inspection Report

             

             

            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)

             

            Relationship:

            Site Products ----- to ----- Customer Site 

            Site Products ----- to ----- Product Type

            Inspection Report ----- to ----- Product Type

             

            Summary,

             

            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.


            Example

             

            Size

            Capacity type

            Type

            Quantity

            ReCharge Due

            Additional ReCharge

            3

            LT

            Hydro Spray

            3

            1

             

            2

            KG

            Co2

            5

             

             

            6

            LT

            Foam

            6

            3

             

            9

            LT

            Water

            10

            3

             

            5

            KG

            Co2

            2

             

             

            6

            LT

            Hydro Spray

            6

            1

             

             

             

             

             

             

             

            • 3. Re: Product Sorting
              philmodjunk

              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.

              • 4. Re: Product Sorting
                RobinNeeves_2

                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

                • 5. Re: Product Sorting
                  philmodjunk

                  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---<SiteProductsPRODUCTTYPES------<SiteProductsProductTypesSITEPRODUCTS

                  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.

                  • 6. Re: Product Sorting
                    RobinNeeves_2

                    Hi,

                    Thanks for the information, Can you explain a little moor please, were and how to create the additional fields and global field, Thanks

                    • 7. Re: Product Sorting
                      philmodjunk

                      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 )