10 Replies Latest reply on Mar 8, 2012 9:11 AM by philmodjunk

    Product totals

    RobinNeeves_2

      Title

      Product totals

      Post

      Can you please help,

       

      I have created a field that has a list of products in, I want to calculate the quantities of each products that will be printed on a report.

       

      Thank you for your help.

        • 1. Re: Product totals
          philmodjunk

          Please describe the design of your table in more detail.

          I have created a field that has a list of products in

          Does that mean you have multiple items inside the same field of a single record?

          Or do you have one item listed in the field, with one record for each listed item?

          • 2. Re: Product totals
            RobinNeeves_2

            I have multiple items in the one field these are entered automatically by using a formulae that whereby each item has a ID number that I enter in another field then the item is entered in to the field automatically

            • 3. Re: Product totals
              philmodjunk

              Why do you put more than one item in the same field of the same record? This really complicates computing any kind of meaningful total.

              Can you post an example of the data that might be present in this field and the total(s) you'd want computed?

              • 4. Re: Product totals
                RobinNeeves_2

                I have attached a pdf file, the field listing the type is is the field that i need the totals for, although this field lists the items each item has it own ID that are listed in a separate folder, I will need to list the type, size and totals

                • 5. Re: Product totals
                  RobinNeeves_2

                  I have attached a second pdf that might help.

                  • 6. Re: Product totals
                    philmodjunk

                    PDF's cannot be uploaded. They must be files of type jpg, gif or png. All other file types will not appear in the forum when uploaded.

                    • 7. Re: Product totals
                      RobinNeeves_2

                      I dont no how to save as jpg,gif or png

                      • 8. Re: Product totals
                        philmodjunk

                        If you are using Windows, you can use the ctrl-shift-print Screen to capture a screen shot in your clipboard. You can then open the Paint accessory, paste and edit your screen shot. THen you can save the image as a jpg, png or gif file.

                        Similar steps are possible for Mac, but I haven't done that in years so don't remember the exact sequence of steps to use--nor if they have changed.

                        Files of other types--such as a PDF--may be uploaded to a file sharing site and then the down load link can be posted in your next response to this thread.

                        • 9. Re: Product totals
                          RobinNeeves_2

                          Hi, I have set up the following Table & Fields, I have attached a copy of layout, this may help

                           

                          Table,

                          Product Type

                           

                          Fields,

                          Product Type ID

                          Product Abbreviation

                          Size

                           

                          Types

                          Co2

                          Dry Powder

                          Foam

                          Hydro Spray

                          Water

                          Wet Chemical

                           

                          When entering a product to a site I enter the required ID code this enters the correct type to the site and whether the type is measured in kilogram, letter or centre meters then I will enter the capacity/size, as I enter these to a site I want to caculate each type and size for each site so that I can enter a field on a report to display the quantities.

                          • 10. Re: Product totals
                            philmodjunk

                            Unfortunately, we are still dealing with the same question about records vs. fields. What you show could be a repeating field with 9 repetitions or it could be 9 separate records.(Either would look the same as what you have in the above image.)

                            What you request will be much easier to set up if you have 9 records instead of 9 repetitions.

                            Assuming 9 records here instead of repeating fields...

                            A summary report (which would require a different layout design than what you show) could easily use a summary field and sorting to group the records by size and type to compute a subtotal for each along with a grand total for the whole group of records.

                            The layout you show is also possible, but requires creating a summary table with which to compute and display the sub totals for each pairing of size and type.

                            You'd have table occurrences/relationships like this:

                            Sites-----<InspectionItems>------SizeTypeSummaries---<InspectionItems 2          (---< means "one to many")

                            InspectionItems and InspectionItems 2 are two occurrences of the same table.

                            Your layout would be based on Sites--which has field defined for SiteID, Address fields, ect. Witha portal to InspectionItems to provide the upper list of items inspected with a portal to SizeTypeSummaries to list the totals shown at the bottom of this layout.

                            Sites::__pk_SiteID = InspectionItems::_fk_SiteID

                            InspectionItems::Size = SizeTypeSummaries::size AND
                            InspectionItems::Type = SizeTypeSummaries::Type AND
                            InsepctionItems::_fk_SiteID = SizeTypeSummaries::_fk_SiteID

                            InspectionItems 2::Size = SizeTypeSummaries::size AND
                            InspectionItems 2::Type = SizeTypeSummaries::Type AND
                            InsepctionItems 2::_fk_SiteID = SizeTypeSummaries::_fk_SiteID

                            You'll need to create one record in SizeTypeSummaries for each unique combination of size, type and site. A script trigger on the size and type fields can perform a simple script to create the matching record in SizeTypeSummaries.

                            Define a Summary Field in InspectionItems as the "count of" _fk_SiteID or any other field that is never blank to produce your total count of items inspected of that size and type.

                            In your portal to SizeTypeSummaries, place this summary field, but selected from Inspection Items 2 in the portal row to show the totals. Place this same field outside of the portal to show a grand total count of all items inspected. (Note: Calculation fields that use Count can be defined in SizeTypeSummaries instead of using the summary field in InspectionItems. That option can be the better choice if you are using the upper portal for data entry as the totals will update more smoothly.)