5 Replies Latest reply on Aug 15, 2013 9:13 AM by philmodjunk

    Summary field? Or something else?

    ChrisGold

      Title

      Summary field? Or something else?

      Post

           I cannot figure out how to do this, but I feel like it must be possible. I’m very unskilled in Filemaker, so I hope my explanation of what I’m trying to do makes sense.

      Data:

           800 vendors. Each vendor can sell 50 products. The products can be priced differently at each vendor.

            

      Current Table & Layout:

           I have a table for sales information on each vendor.

           On the Vendor layout in question, the 50 products are listed. Every product the vendor has sold is indicated via a checkbox.

           Fields I have on my layout:

           a) Product [if sold, indicated by checkbox]

           b) $ amount of this product sold by the vender whose record I’m currently viewing

           c) Summary: # of vendors that have sold this product

           d) Summary: Total $ amount of all products sold by all vendors.

            

           What I want to add:

           e) Total $ amount of this product sold by *all* vendors combined. (This is the same idea as “c” above, just with $ amount sold rather than # sold.)

           Currently, I am able to get this information (“e”) by doing a find on a product. Then the “Total $ Amount” field (“d” above) shows the amount I’m looking for, since it only reflects the found set.

           However, I want to be able to view this information for all of the products at the same time, the same way I can with “c”. Having to do a separate find on each of the 50 products in order to obtain “e” for each one is really time consuming.

           I'm sad to say I've spent hours trying to figure out how to do this and just can't! The summary fields for "c" and "d" were simple -- just "Total of". But I don't know what to do to create "e".

           I hope this makes sense. I would appreciate any guidance or ideas you can offer.

            

        • 1. Re: Summary field? Or something else?
          philmodjunk

               Your design is not clear to me.

               

                    On the Vendor layout in question, the 50 products are listed. Every product the vendor has sold is indicated via a checkbox.

               How did you list the products? Is each product a different record? Did you use a portal or do you have one field with 50 products in a value list and a check box format? Or perhaps you have a list view?

               What you describe can be done with a summary report so that you can get both subtotals for each vendor broken down by product, but it requires a particular structure to your tables to pull off without unecessary complications.

          • 2. Re: Summary field? Or something else?
            ChrisGold
            I'm sorry I wasn't clear. Each vendor record shows all 50 products; the products do not have individual records. On the vendor records, the products are presented as a list with a checkbox beside each one, but not as a value list -- each one is a separate field. No portal.
            • 3. Re: Summary field? Or something else?
              philmodjunk

                   Then this design will make your reporting needs complex. It would make for a much more flexible approach if you used a related table with one record for each vendor's product instead of a set of individual fields in the same record.

              • 4. Re: Summary field? Or something else?
                ChrisGold
                Thank you for your reply. Do you mean create a table with 50 records, one for each product? Since I already have fields for each product in another table, how would I handle that?
                • 5. Re: Summary field? Or something else?
                  philmodjunk

                       You would create a table of related records with whatever number of records you need to list the products for each vendor. One vendor might supply 5 products, another might supply 75 and the same setup still works.

                       To move the data from your individual fields into new records in such a related table would require a script that loops through each vendor record and each of these fields, creating the needed related record for each such field that is not empty. This would be a one time update  and then you'd need to modify layouts and other aspects of your design to take advantage of the new, much more flexible structure.