4 Replies Latest reply on May 19, 2017 9:11 AM by luffyfilemaker007

    Increment Field In portal Row

    luffyfilemaker007

      Hi guys,

       

      So I have a table for daily appointments, each record being a new day.

       

      And on this record I set up a portal row based on appointments with arrival time, name of the customer and more.

      Each row is a customer with some more information.

      So the main Table is Day and the portal row is based on table Appointments

       

      Now what I would like is to give a unique number to an appointment.

      So first arrived would be, 1, second 2,...

       

      So I set up a field with that number  lets name it "ArrivalNumber" and I increment it by one.

      The problem is first; if I create a new record the next day, the new blank portal row will start to increment based on the last one from the former day. But what I want it to do is to start from 1 again.

       

      The second problem is I would like to set up little buttons "arrow up" and '"arrow down" so I can move a customer up or down in the list of appointments  and it would automaticaly change his number to the proper position in the portal row.

       

      I tried a few things but it doesnt work out well so far.

       

      Any advice ?

      Cheers ,

        • 1. Re: Increment Field In portal Row
          philmodjunk

          Now what I would like is to give a unique number to an appointment.

          So first arrived would be, 1, second 2,...

          Those numbers would not actually be unique as you will have records with the same numbers, but linked to other days.

           

          One way to solve this issue is to do the following:

           

          a) Put a number field in the portal and set it to auto-enter a serial number. This field will not be visible in the portal so it doesn't have to start over with 1 each day and doesn't have to be a perfect integer sequence, but it will control the order in which the records appear in your portal.

           

          b) in the portal, use the text tool and the insert menu to insert a "record number" object into your portal row. This object will always perfectly number your rows 1, 2, 3..... You may need to carefully size and position this text object to fit it into the portal row and still be part of the portal and not just sitting on top of the portal (which would then fail to number each row correctly).

           

          c) for your arrow buttons, a script can use getNthRecord and the field from a) above to move records up and down in the portal.

           

          Set Variable [$Row ; value: Get ( activeportalrownumber ) ]

          If [$Row = 2 ]

             Set Field [ PortalTO::SerialNumberField ; GetNthRecord ( PortalTo::SerialNumberFIeld ; 1 ) / 2 ]

          Else If [ $Row > 2 ]
               Set Field [ PortalTO::SerialNumberField ; ( GetNthRecord ( PortalTo::SerialNumberFIeld ; $Row - 1 ) +

                                                                                   GetNthRecord ( PortalTo::SerialNumberField ; $Row - 2 ) ) / 2 ]

          End If

          Commit Records

           

          d) In Portal Set up, specify that the portal records be sorted by this serial number field.

           

          e) add another script for your down arrow button similar to c, but but using $Row + 1 and $Row + 2 and checking for the next to last portal row instead of the second portal row.

          1 of 1 people found this helpful
          • 2. Re: Increment Field In portal Row
            luffyfilemaker007

            Waw ! Thank you so much !!

            It almost works perfectly now.

             

            The only catch is that the  "up arrow" works well but the "down arrow" not.

            I changed the -1 to +1 and - 2 to + 2 but I am not sure how to : "checking for the next to last portal row instead of the second portal row."

             

             

            Please see screenshot if it can help:

            screen shot filemaker.jpg

            • 3. Re: Increment Field In portal Row
              philmodjunk

              I had to stop and think a bit to be sure this would work myself:

               

              Set Variable [$Row ; value: Get ( activeportalrownumber ) ]

              Set Variable [$Last ; Count ( PortalTO::SerialNumberField ) ]

              If [$Row = ( $Last - 1 ) ]

                 Set Field [ PortalTO::SerialNumberField ; ( GetNthRecord ( PortalTo::SerialNumberFIeld ; $Last ) +  GetNthRecord ( PortalTo::SerialNumberFIeld ; $Last ) + 1 ) / 2 ]

              Else If [ $Row < $Last ]
                   Set Field [ PortalTO::SerialNumberField ; ( GetNthRecord ( PortalTo::SerialNumberFIeld ; $Row + 1 ) +

                                                                                       GetNthRecord ( PortalTo::SerialNumberField ; $Row + 2 ) ) / 2 ]

              End If

              Commit Records

               

              I had to stop and think because I was originally going to just add 1 to the field if you were moving the next to last row down, but I realized that this might assign a value to the field that is more than the next serial number value and then adding another record might cause it to sort to be above this repositioned row.

              1 of 1 people found this helpful
              • 4. Re: Increment Field In portal Row
                luffyfilemaker007

                Oh Lord !

                It works wonders !

                 

                Thank you so much