1 2 Previous Next 19 Replies Latest reply on Feb 13, 2012 9:32 AM by philmodjunk

    Summary - What would be the smartest way?

    DannyAccola

      Title

      Summary - What would be the smartest way?

      Post

      ... and my project goes on ... now i have the possibility to file orders with each client and i'm preparing to send out a proper order confirmation to the client.. so far so good ... now to my question.

      for each <style> various and unique <colors> are available ... which would be the smartest way to summarize the order for each style? ... like

      <item a> <style a> ordered in <color1222> <color2765> <color4111> 
      <item a> <style b> ordered only in <color1122>
      <item a> <style c> ordered in <color1343> <color8945>

      and so on ... i have a grand total of <units> and a grand total <value> for the entire order ...
      and i would also give the client a good overview about what he bought ... how can i summarize a total of how many <items> he actually bought ... 

      thank you very much ... you guys make this project fun .. and a special thank you to philmodjunk who helped a lot

      Screen_shot_2012-01-30_at_12.15.26_PM.png

        • 1. Re: Summary - What would be the smartest way?
          DannyAccola
          /files/d87a730623/Screen_shot_2012-01-30_at_12.39.36_PM.png 1013x186
          • 2. Re: Summary - What would be the smartest way?
            philmodjunk

            ColorID? don't see that field in your screen shots so I'm not sure what you meant by "does not update".

            What you appear to be describing is a summary report.

            You can set up a summary report based on your lineItems table where you can use sub summary layout parts with sorting to group the line items for a given order (or group of orders...) by Product, then by style, with the needed sub totals (if any) for each combination of Product and Style.

            Such a report can look like this:

            Customer info in header...

            Product 38776 Down Jacket,
                  Style:  Maverick Fur Hood
                      Color        Qty    Item Price         Cost
                      Black        50      960.00    48,000.00
                      Blue Black  1      960.00          960.00
                      Ice 1150    1
                      // and so forth
                  Style Subtotals:   Put summary fields here
            Product SubTotals:  Put Summary fields here

            The Product number and description go in a "print above" sub summary part that is "sorted by" Product ID

            The style and bold face text go in a "print above" sub summary part that is "sorted by" style

            The individual line items info is placed in the body

            The Style subtotals go in a "print below" sub summary part "when sorted by" Style

            The Product sub totals go in a "print below" sub summary part "when sorted by" Product ID

            You can use a trailing grand summary to report the totals for the entire order.

            Here's a tutorial on summary reports you may find helpful: Creating Filemaker Pro summary reports--Tutorial

            It's an old post that won't appear in recent items if you post a comment or question to it--and so I will likely miss any such. Instead, post any questions about it you may have here to this thread so that I can find and respond to them.

            • 3. Re: Summary - What would be the smartest way?
              philmodjunk

              Two shots up, I see exactly one product, perhaps view as form instead of view as list was selected?

              This last screen shot is what I would expect to see if the records are not sorted by ProductID and RelatedProducts::Style. If the records are not sorted in an order that includes the "sorted by" field specified for the sub summary layout part, that part will not appear.

              In the case where you cannot edit product colors...

              On what table occurrence is the layout based?

              Are you attempting to select a color for a purchased item or specify colors for one of your products?

              • 4. Re: Summary - What would be the smartest way?
                DannyAccola

                And this the print pre-view > in which the infos from the subsummary are missing

                The Layout OrderConfirmations is based on LineItems and as you can see ... the <colorID> are the same

                 

                • 5. Re: Summary - What would be the smartest way?
                  philmodjunk

                  To repeat, the records must be sorted.

                  How are they sorted at this time? (You have to either sort them by selecting sort from the records menu or by including a sort step in your script.)

                  • 6. Re: Summary - What would be the smartest way?
                    philmodjunk

                    They're not sorted correctly.

                    Take a look at your sub summary parts. One specifies "when sorted by ProductID". The other specifies "when sorted by Related Products::Style".

                    You must sort your records in a sort order that includes both of these fields in the sort order or the sub summary parts will not appear.

                    • 7. Re: Summary - What would be the smartest way?
                      DannyAccola

                      works out just fine Laughing

                      two basic functions i'm hasseling with

                      • i'm still confused what happens to the correct display of my colorID .. and can't find the problem
                      • i would like to use a script to print the order conf right after i typed the order ... but the one i use (adopted from invoices.fp7) keeps on telling me that i my lineitems are empty > but they are not ... what am i ignoring here .. 

                      thank you again ... 

                      • 8. Re: Summary - What would be the smartest way?
                        DannyAccola
                        /files/f752fc998c/Screen_shot_2012-02-06_at_1.48.26_PM.png 314x195
                        • 9. Re: Summary - What would be the smartest way?
                          DannyAccola
                          /files/e3a782cf02/Screen_shot_2012-02-06_at_1.55.10_PM.png 718x215
                          • 10. Re: Summary - What would be the smartest way?
                            philmodjunk

                            The most obvious error in your script is this line:

                            If [/*line items::OrderID*/]

                            /* */ are comment brackets that tell filemaker to ignore everything enclosed in them. When you imported or copied/pasted this script, FileMaker could not find line items::Order ID and enclosed them in comment brackets to preserve the calculation without triggering an error dialog so that you can then correct the problem after importing or pasting the script.

                            With regards to your colors, I can see that your field refers to ColorID from Related Colors, but don't know to what table occurrence box your layout refers. Open layout setup for this layout and check to see what is selected in Show Records From. From there, we can take a look at your relationship graph can check to see if the field reference refers to the correct occurrence of Related Colors.

                            • 11. Re: Summary - What would be the smartest way?
                              DannyAccola

                              COLORS >
                              my layout refers to LineItems ... and here are the relations i have defined ... what the heck did i miss this time ... i thought i figured it out (:

                              • 12. Re: Summary - What would be the smartest way?
                                philmodjunk

                                You are adding the field from Related Colors to your layout, but you have potentially many RelatedColors records for a given ProductID. Thus, selecting a ProductID is not enough information to indicate what color is being specified for this specific lineItem. In order for any data to appear in the field, you need records with the correct matching values in RelatedProducts, RelatedProducColors and RelatedColors.

                                Instead, use the ProductColors field defined in LineItems on your layout and give it a conditional value list from RelatedColors.

                                • 13. Re: Summary - What would be the smartest way?
                                  DannyAccola

                                  i'm not sure i fully understand what you are suggesting ...

                                  yes .. i do have many RelatedColors for each ProductID ... but since i use LineItems to record orders .. the information does already exist ... that's why i do not entirely understand the logic .. the ColorText shows correctly on the order confirmation (which > to me > is only a list of orders-records already made ... and i've made the same relation like i used before, for the definition of each Product.

                                  sorry if i'm slow ... 

                                  • 14. Re: Summary - What would be the smartest way?
                                    philmodjunk

                                    You have a post above titled "works out just fine..."

                                    Take a look at what you posted. The field shown is ColorID from the relatedColors table. It should be a field from LineItems if you intend to show the color selected for that LineItem.

                                    1 2 Previous Next