13 Replies Latest reply on Aug 28, 2012 6:37 AM by JoeMan

    Changing line item order in a portal

    JoeMan

      Title

      Changing line item order in a portal

      Post

      With the Invoices Starter Solution, is there a way to change the order in which line items appear in the portal on the Invoice Details layout? Ideally a drag and drop system would be best, but I'd be happy with an 'up' or 'down' button with the correct script attached. Are any of these options possible? 

        • 1. Re: Changing line item order in a portal
          philmodjunk

          I've seen a demo that actually used Drag and Drop, but don't have a down load link for it at hand. It's a really wild demo and my hat's off to the folks that produced it.

          Using up and down arrows could be very simple to do.

          Go to Manage | database | Fields and select Invoice Data (what they called the line items table in this file) from the table drop down.

          Add PortalOrder as a number field and specify in field options that it auto-enter a serial number.

          Go to your Invoice Details Layout, enter layout mode, double click the portal (don't double click a field in the portal) to open Portal Setup... and specify that the portal be sorted on PortalOrder in ascending order.

          Place Up and Down arrows in the portal row on this layout.

          The "up" script would look like this:

          Set [Invoice Data::PortalORder ;Let ( If ...]
          Commit Record

          The calculation inside the calculated result portion of the set field step would look like this:

          Let ( R = Get ( RecordNumber ) ;
                 Case  ( R = 2 ; GetNthRecord ( PortalOrder ; 1 )  ;
                             R > 3 ; GetNthRecord ( PortalOrder ; R - 2 ) + GetnthRecord ( PortalOrder ; R - 1 )
                          ) / 2
                )

          For your down arrow use:

          Let ( [ R = Get ( RecordNumber ) ;
                    C = Count ( InvoiceData::INVOICE ID MATCH FIELD )
                 ];
                 Case ( R = C - 1 ; 2 * GetNthRecord ( PortalOrder ; C ) + 1 ;
                            R < C ; GetNthRecord ( PortalOrder ; R + 1 ) + GetnthRecord ( R + 2 )
                          ) / 2
                )
                   

          • 2. Re: Changing line item order in a portal
            JoeMan

            Thanks Phil,

            The Up script has no errors, but it's not doing what it's supposed to.

            The down script, however, isn't computing.

            Here's a screen shot:

            • 3. Re: Changing line item order in a portal
              philmodjunk

              "not doing what it is supposed to do" doesn't tell me what it IS doing. If I knew that I might have a suggestion for you.

              I have a typo in the last expression. It should be:

              Let ( [ R = Get ( RecordNumber ) ;
                        C = Count ( InvoiceData::INVOICE ID MATCH FIELD )
                     ];
                     Case ( R = C - 1 ; 2 * GetNthRecord ( PortalOrder ; C ) + 1 ;
                                R < C ; GetNthRecord ( PortalOrder ; R + 1 ) + GetnthRecord ( PortalOrder ; R + 2 )
                              ) / 2
                    )

              • 4. Re: Changing line item order in a portal
                philmodjunk

                Note that all of this will only work if you start with a new invoice after adding the portalOrder field. This field will be blank in existing invoices and thus won't work for them without doing some updates on the value in this field first.

                • 5. Re: Changing line item order in a portal
                  JoeMan

                  I placed the PortalOrder field into tho portal to watch its value:

                  When 'up' is pressed on a line, the value changes to a '?'. I expanded it very wide, still a ?, and no change in the order of the line items.

                  When 'down' is pressod on a line, the value changes to '0'. And no changes to the order.

                  Conversely, if I press the down button first (i get a 0) and follow it with the up button (I get a ?)

                  Hope that helps

                   

                  As a side note: whenever I create a new invoice with a different customer (or the same), the PortalOrder field value increases rather than starting over at 1 again for each new record.

                  • 6. Re: Changing line item order in a portal
                    philmodjunk

                    Yes the Portal Order field will always increase with each new record. The fact that it does should not affect the restults as long as the portal is sorted in ascending order on that field. The scripts are supposed to compute mid point values that drop it between the previous or following pair of portal records in order to move them up or down a portal row--providing they are not already in the first or last portal row.

                    Up to this point, it's been a  mental excersize for me. Time to break out a copy of the starter solution and see what's going on. GetNthRecord returns a ? when it references a record that can't exist such as a 0 or negative value.

                    • 7. Re: Changing line item order in a portal
                      JoeMan

                      Also if it helps:

                      I flipped backwards in my records. The record immediately before the one I was working with had the following values:

                      1.25

                      .5

                      .5

                      Unfortunately, I can't remember what I did and on what line, but I think it has to do with the "/2" within the calculation. Interesting that it affected the previous record. 

                      • 8. Re: Changing line item order in a portal
                        philmodjunk

                        Found a few issues, the main one is that get (RecordNumber) is not the right function here.

                        UP:

                        Let ( R = Get ( ActivePortalRowNumber ) ;
                               Case  ( R = 1 ; 2* Invoice Data::PortalOrder ;
                                           R = 2 ; GetNthRecord ( Invoice Data::PortalOrder ; 1 )  ;
                                           R > 2 ; GetNthRecord ( Invoice Data::PortalOrder ; R - 2 ) + GetNthRecord ( Invoice Data::PortalOrder ; R - 1 )
                                        ) / 2
                              )

                        Down:

                        Let ( [ R = Get ( ActivePortalRowNumber ) ;
                                  C = Count ( Invoice Data::INVOICE ID MATCH FIELD )
                               ];
                               Case ( R = C ; 2 * Invoice Data::PortalOrder ;
                                          R = C - 1 ; GetNthRecord ( Invoice Data::PortalOrder ; C ) + Int ( GetNthRecord ( Invoice Data::PortalOrder ; C ) ) + 1 ;
                                          R < C ; GetNthRecord ( Invoice Data::PortalOrder ; R + 1 ) + GetNthRecord ( Invoice Data::PortalOrder ; R + 2 )
                                        ) / 2
                              )

                        • 9. Re: Changing line item order in a portal
                          JoeMan

                          I tried it.

                          This is what I did, in order, to get this screen shot:

                          1. Pressed up on the 3rd row

                          2. Pressed up on the 4th row

                          3. Pressed up on the 5th (Last row)

                          NOTE: It shows 6 rows, but the 6th is always empty.

                          I have the portal set to sort by PortalOrder, but clearly it doesn't refresh.

                          • 10. Re: Changing line item order in a portal
                            JoeMan

                            Wait! 

                            It's working when I click outside the portal.

                            This solution is really thinking outside the box! I'm inspired...

                            • 11. Re: Changing line item order in a portal
                              JoeMan

                              Commit Records fixed the refresh...

                              Thanks Phil!

                              • 12. Re: Changing line item order in a portal
                                davidanders

                                Sorting Portal Rows with drag and drop functionality
                                sorting portal rows with drag and drop functionality

                                • 13. Re: Changing line item order in a portal
                                  JoeMan

                                  Thanks for that David,

                                  I drilled down into that link and found the white paper and file for the drag and drop via excelisys. 

                                  It will take some time to really understand them but I got stymied here:

                                  In the Drag-and-Drop Solution, inside Manage | Database | Relationships, there are no tables, yet there is an ERD. I don't know how I'm going to implement this into my solution, as I didn't know that was possible.

                                  My first impression was to open this file alongside my solution and add the scripts, step by step. Before I go down that road, I was wondering if there is an easier way. Copy/paste didn't work. I have FMP 12 Advanced.