10 Replies Latest reply on Jan 27, 2014 1:17 PM by erolst

    Serial number in a portal row ?

    bartprins

      Hi everybody,

       

      I have a form with clientdata with a portalrow with productionrecords. Now i want to make a serialnumber in each row in the portal, however if i use the serialnumber option i run into the problem that when a user switches to another client and makes a new record in the portal the serialnumber is made there.

       

      I want the serialnumber to add 1 for each portalentry for that specific client.

       

      So;

      Client1

      PortalRow1 = 20140001

      PortalRow2 = 20140002

      PortalRow3 = 2014003

       

      Client2

      PortalRow1 = 2014001

      PortalRow2 = 2014002

       

      And when i want to add more portals later on then it should add one again.

       

      How can i perform this?

       

      Again much oblidged!!

       

      Gr

       

      Bart


        • 1. Re: Serial number in a portal row ?
          wimdecorte

          An auto-enter calc that uses the max() across the relationship and adds 1 should do it.

           

          When you say "serial #", don't ever use those as primary or foreign keys of course...

          • 2. Re: Serial number in a portal row ?
            bartprins

            Hi Wim thank you for your quick reply !

             

            I looked into the MAX function but don't really understand it, this was what filemaker online says:

             

            If the users of your solution will need to reset the values of your serial numbers, a more user friendly approach to allow this is by creating a script.  The script step that you will need to use is the "Set Next Serial Value."  The "Set Next Serial Value" script step resets the next serial value in an auto-entry number field.  This script step allows you use any calculated expression to determine the next serial value of a field.  For example:

            Set Next Serial Value [Invoices::Invoice ID; Max ( Invoices::Invoice ID ) + 1]

            In the example above, the MAX function is used to calculate the next available invoice number for the field "Invoice ID"

             

            But in this example i believe it will only act as an serial number + 1 but i need to exclude the possbility that another customer gets that serialnumber.

            In that case the numbers in the portal for that specific client aren't consecutive anymore.

            So when i use the standard serial i could get :

             

            Client1

            Portalrow1 = 2014001

            Portalrow2 - 2014003

             

            Client2

            Portalrow1 = 2014002

            Portalrow2 = 2014004

            Portalrow3 = 2014005

             

            Where as i would like it to in consecutive order per client. I think i have to adress something like (in dumb english )

            When a new portalrow for this client is made -- > then look at the number from last portal row + 1

             

            Any idea is welcome !!

             

            Thanks

             

            Ps not using it as key...thanks

            • 3. Re: Serial number in a portal row ?
              wimdecorte

              Trying to use a true serial # using the FM feature is going to be too complex I think.  An auto-enter calc should be much easier.

              • 4. Re: Serial number in a portal row ?
                bartprins

                Hi wim,

                 

                Yes i want to use an autocalc field but don't know how to get the trigger previous portal row for that

                specific client?

                • 5. Re: Serial number in a portal row ?
                  erolst

                  Create a self-join for ProductionRecords by clientID = clientID. In the auto-enter calculation, use Max() via that relationship to get the highest existing serial# for that client and add 1.

                   

                  This is on the relationship level, not the display level, so it doesn't matter if you create the new record in a portal or by other means.

                   

                  (Or – with the usual caveats – use ExecuteSQL to do it without a new TO.)

                  • 6. Re: Serial number in a portal row ?
                    bartprins

                    Hi Erolst,

                     

                    Thank you for you reply, tried this but ran into the same problem, perhaps another idea which would work is to count the number of portalrows for that client and build a consecutive number upon that information is that possible.

                     

                    Something like  aClient has 4 portalrows so the next portal row would have number 20140005 in the serialnumber field ?

                     

                    Don't know where to begin, Portal keep giving me problems hahah

                     

                    Much oblidged!!!

                     

                    Bart

                    • 7. Re: Serial number in a portal row ?
                      erolst

                      Bart .

                       

                      this seems to be a timing problem. Check this out; note that “Do not replace …” is deactivated; it seems that otherwise the initially empty value won't be overwritten with the calculated one.

                       

                      Also, if you want to start over yearly, you need to tweak the calculation a bit.

                      • 8. Re: Serial number in a portal row ?
                        bartprins

                        Hi Erolst,

                         

                        Thanks now i understand it !!!

                         

                        Special thanks for your example, super !!!

                         

                        Gr

                         

                        Bart

                        • 9. Re: Serial number in a portal row ?
                          flybynight

                          I'm definitely going to check out erolst's example file. I have a similar situation with Jobs (think "Line Items") on a parent JobTicket, where we want each job to have a line number. (Actually a line letter, but that's another thread!)

                           

                          The solution that I had landed on was that in my scripts for adding, duplicating, or deleting line items, I have a "Reset Job #s" subscript that runs. The basics of it are:

                          GTRR [Show only related records (from current record only); From table: Jobs; Using layout: Jobs; New window]

                          Sort records by Job ID field (or by creation date if you are using UUIDs)

                          Go to Record [First]

                          Replace Field Contents [No dialog; Jobs::JobPart#; Serial numbers]

                          Close window [Current Window]

                           

                          Some other error trapping before and cleanup after, but you get the idea.

                          It's been working fine, but this method is probably much more efficient and less error prone.

                           

                          Thanks!

                          -Shawn

                          • 10. Re: Serial number in a portal row ?
                            erolst

                            flybynight wrote:

                            The solution that I had landed on was that in my scripts for adding, duplicating, or deleting line items, I have a "Reset Job #s" subscript that runs.

                             

                            Duplicating is OK, but if you're deleting records and want to avoid gaps, you will still need a script.

                             

                            Of course, if the numbers aren't really business-critical and their purpose is merely cosmetic (and this seems to be the case if you're allowing deletion), you could as well use a calc field instead of auto-enter.

                             

                            Calculate the record's relative position within a sorted list of “similar” (by client, year etc.) records, which would automatically update when you delete one of its “siblings”.