5 Replies Latest reply on Feb 10, 2011 4:10 PM by philmodjunk

    Summary fields from dynamic field names

    david583

      Title

      Summary fields from dynamic field names

      Post

      FM Pro 10 & 11, Filemaker server 11, Shared Windows Network (XP & Vista & 7)

      I have a table with product fields (many)

      Most of the products come in three variations (c, b, s) There is a field for each, as well as a combined calculation that totals them (all) (four fields per product)

      I want to create a report where I can select a product from a drop down list and it will show me a summary total of the 'c', 'b', 's' and 'all' fields.

      I have created a 'on object modify' script trigger that produces the four field names, depending on the product selected, using four unstored text calculations when a global product ID is selected

      i.e. select.....  Product = 20x30 Print (select this from value list)

           create..... fieldnames  pr_c_2030, pr_b_2030, pr_s_2030, pr_all_2030

      Where I'm having trouble, is using the calculated results as field names when I want to create a summary total. Is there a way of allowing calculated results as field names in creating summaries? or have I been going down a dead end path here?

      Any help would be appreciated

        • 1. Re: Summary fields from dynamic field names
          philmodjunk

          I believe you'll need to back up and rethink here. If you can define a relationship to the records being summarized, you might be able to do this with a sum function wrapped in turn with an evaluate function.

          I think, though, that if you can avoid this sticky detail: Most of the products come in three variations (c, b, s) There is a field for each, as well as a combined calculation that totals them (all) (four fields per product)

          If you use a table of related records instead of dedicated fields, this level of indirection may no longer exist at all.

          • 2. Re: Summary fields from dynamic field names
            david583

            Thanks PhilModJunk

            I think I am having a little trouble understanding what you mean by avoiding the sticky detail, and I think I may have misled you as to my relationship setup.

             I have a table of 'Products' and each product has a unique code (ie pr_c_2030 = 20x30 colour print) used as a foreign key in a join table relationship with an orders table (line items join table). By necessity, a colour product (c) is seperate to a b&w (b) or a sepia (s) [obviously a photography buisness]

            What they are trying to achieve is a simple selection of say a 20x30 product and display a total of (c), (b) and (s) products and a total of (all) within a given date range. It then has to find the 'Orders' in that range and calculate the four totals from the related 'Line Items' table. This is why I went down the dynamic field name path thinking I could use the field names in the calculations.

            I have read in online help and the users guide about 'Evaluate' but am not sure how I can use it in this context, any further help would be appreciated

            • 3. Re: Summary fields from dynamic field names
              philmodjunk

              I think you have this structure to your relationships, though I may have a diferent table name here or there...

              Invoices---<LineItems>----Products

              Here's the key detail I need to understand: "By necessity, a colour product (c) is seperate to a b&w (b) or a sepia (s)"

              It sounds like you have a separate field for Colour, a separate field for B&W and a third field for Sepia. I don't see the need for this.

              Define a single field, PhotoType and put either "Colour", "B&W", or "Sepia" in this field in Products to identify the photo type. For faster results in your report, you can define a matching field in LineItems that looks up this value--but I wouldn't do that unless I'm pulling together a large number of records in my report and am experiencing visible delays in getting the report to come up.

              With such a field, you can get summary sub totals for all three categories with a single summary field. On a layout based on LineItems, you can place such a field inside a sub summary part when sorted by PhotoType to get a sub total for each category. You can perform Finds to limit the records in your report by date range and also by photo type if you need to.

              There are also ways to see these totals from an invoices layout, but that requires more design work to pull off.

              • 4. Re: Summary fields from dynamic field names
                david583

                OK, I'm beginning to see what you mean. I actually have each one as a seperate record in the table of products. I'll change it to just have the one and add a field to 'Line Items' table to define type. I can make it selectable during the order process from a pop-up. Should work for me.

                Thanks again PhilModJunk, i will get to work on that now.

                • 5. Re: Summary fields from dynamic field names
                  philmodjunk

                  You can keep separate records in Products for each type if they have unique ID's. Just use the Phototype field to group product or line item records by the values in this field.