8 Replies Latest reply on Oct 10, 2014 7:23 AM by philmodjunk

    Problem with sorting on a Sub-Summary layout part

    Vincent-Brian:BodiedelaMross

      Title

      Problem with sorting on a Sub-Summary layout part

      Post

      I have a layout named "Invoice Preview" which includes a Sub-summary part sorted by a text field named "Description" in a table named "Line Items". This layout is used only in Preview mode for printing purposes. There is another layout named "Invoice Detail" on which the line items are entered in a portal.

      I want to control sorting of each line item on the "Invoice Preview" layout by other than the "Description" field, so I have added a number field named "Line_Item_Order_Number" in the "Line Items" table, added it to the portal in the "Invoice Detail" layout, and tried setting sorting in the sub-summary part on the "Invoice Preview" layout by the "Line_Item_Order_Number" field, but it does not work.

      Both fields are included on the "Invoice Preview" layout, within the Sub-summary part. When I change the sort field to "Line_Item_Order_Number", the Sub-summary does not function; in fact the "Line_Item_Order_Number" field does not even show up in the layout, though it does show up when I change sorting back to "Description". Any ideas why?

        • 1. Re: Problem with sorting on a Sub-Summary layout part
          philmodjunk

          It would appear that your records are still sorted by Description, not Line_Item_Order_Number. Sub Summary layout parts--which have been visible in Browse mode as well as Preview since the release of FileMaker 10, are not visible unless the records in your found set are sorted in a sort order that includes the sub summary part's "When sorted by" field.

          • 2. Re: Problem with sorting on a Sub-Summary layout part
            Vincent-Brian:BodiedelaMross

            I assume by "the records in your found set" you mean the records currently showing in the portal... Hmmm... I'll be right back.

            • 3. Re: Problem with sorting on a Sub-Summary layout part
              Vincent-Brian:BodiedelaMross

              I tried adding "Line_Item_Order_Number" to the sorting in the portal and then sorting the Sub-Summary part by the same, and it still doesn't work. I also tried eliminating "Description" as one of the portal's sort fields, but that didn't help either, and when I changed the Sub-Summary sorting back to "Description" (without having put "Description" back in the portal's sort list), sorting of the Sub-Summary part worked again, which seems to contradict what you said above (assuming I read you correctly).

              Any other thoughts?

              Vince

              • 4. Re: Problem with sorting on a Sub-Summary layout part
                philmodjunk

                I did not mean records showing in a portal. A found set has nothing to do with what records appear in a portal. Sub summary layout parts cannot be placed in portals either. A summary report using sub summary layout parts is a List view based on your layout's current found set. Changing the sort order of the records in the portal will have no effect here. I am referring to pulling down the records menu and using sort record to sort the layout's records, not the portal's.

                The typical way in FileMaker to print out an invoice that is linked to a table of related line items is to set up a list view layout based on the lineItems table to be used when printing an invoice. A scripted find or Go to Related Records can pull up the Line Item records for a given invoice and fields from the Invoice parent record can be included in the header, footer and/or grand summary layout parts while the line item, and sometimes fields from a related products table are placed in the body.

                • 5. Re: Problem with sorting on a Sub-Summary layout part
                  Vincent-Brian:BodiedelaMross

                  Ah yes, now I understand. There is a button on the "Invoice Detail" layout which runs a script which includes a step that sorts the line items when going from the "Invoice Detail" layout to the "Invoice Preview" layout, and changing this sort order from "Description" to "Line_Item_Order_Number" was all that was needed, thanks!

                  On another note, I would now like to link "Line_Item_Order_Number" to "Description" somehow, so that when I change  "Line_Item_Order_Number" for one line item, all other line items on that invoice with the same "Description" also change automatically, so that the user doesn't have to change them all manually before previewing the invoice. Any ideas on this?

                  • 6. Re: Problem with sorting on a Sub-Summary layout part
                    philmodjunk

                    What is the purpose to Line_Item_Order_Number and why would you want to change it?

                    • 7. Re: Problem with sorting on a Sub-Summary layout part
                      Vincent-Brian:BodiedelaMross

                      Line items are grouped by Description on the invoice:

                             
                      • Description C      
                                    
                        • Line Item 9
                        •           
                        • Line Item 4
                        •           
                        • Line Item 6
                        •      
                             
                      •      
                      • Description A      
                                    
                        • Line Item 2
                        •           
                        • Line Item 3
                        •           
                        • Line Item 5
                        •      
                             
                      •      
                      • Description B      
                                    
                        • Line Item 7
                        •           
                        • Line Item 1
                        •           
                        • Line Item 8
                        •      
                             

                       

                      I want to able to set the order the line item groups are displayed on the Invoice Preview layout from within the portal on the Invoice Detail layout. I have already achieved this with the field Line_Item_Order_Number, which is a popup menu using a value list containing the letters A through Z. The problem is I have to make the change separately for each line item.

                      I have to change Line_Item_Order_Number for six line items to change the above example to this:

                             
                      • Description A      
                                    
                        • Line Item 2
                        •           
                        • Line Item 3
                        •           
                        • Line Item 5
                        •      
                             
                      •      
                      • Description C      
                                    
                        • Line Item 9
                        •           
                        • Line Item 4
                        •           
                        • Line Item 6
                        •      
                             
                      •      
                      • Description B      
                                    
                        • Line Item 7
                        •           
                        • Line Item 1
                        •           
                        • Line Item 8
                        •      
                             

                       

                      In other words, Line_Item_Order_Number needs to act on the Description field rather than the actual line item, so that when it is changed, it changes all line items with that description. Hope this helps.

                      • 8. Re: Problem with sorting on a Sub-Summary layout part
                        philmodjunk

                        Much can depend on how that description field is set up. This can be a field from a related products table, it can be a calculation field that reference the description from the related products field or a field that uses an auto-enter field option to copy over the description from Products.

                        Am I correct that you have a Product ID field and, to quote from your last example, Line Item 2, 3 and 5 all have the same value in this Product ID field?.

                        What I am working towards is that you can set up a related table of records with one record for each unique combination of product ID and Invoice ID values. This would link items 2, 3 and 5 to the same record so that you only have one value to change in order for this group to sort into a different location on the invoice. But this requires that the fields with the same description have a field with a value in common. If we have to, this can be the description field, but it would be better to use ProductID.