2 Replies Latest reply on Mar 18, 2011 5:17 PM by CraigFiore

    Auto Numbering



      Auto Numbering



      I am hoping I can get a little help with using FMP to automatically put in numbers. 

      Here is my situation:

      In one database I have a field called job numbers. They are written like this: 11-0012. What I would like to do is create a portal to another database to create ID Number like this: 11-0012 CO1, 11-0012 CO2, 11-0012 CO3....and so on. Not every job will a CO# and some will have one but other may have 8.

      So, how do I do consecutive numbering based  on another field?

      I hope I explained it correctly.


        • 1. Re: Auto Numbering

          Keep the first part of the number, 11-0012 in the parent record. You don't need it in the related records. Anytime you need the full number in combination, you can include the field from the parent record as merge text to the left of the second value.

          So if you have this relationship:

          Jobs::JobID = JobDetails::JobID   (and JobID should NOT be 11-0012, make it an auto-entered serial number. Put 11-0012 in a different field.)

          We'll call the CO1, CO2, .... field, JobCOLabel.

          Define a calculation field in Jobs, cMaxCO as max ( JobDetails::JobCOLabel )

          Define the JobCOLabel field as a number field with this expression:

          Order::cMaxCO + 1

          Clear both of these options: "Do not evaluate if all referenced fields are empty" and "Do not replace existing value..."

          To make sure that the values enter/update correctly when editing data in the portal, add an OnPortalExit trigger that commits the record.

          Note: if two users try to create detail records for the same job, you might get two records with the same CO value.

          • 2. Re: Auto Numbering


            It took me a while and with a few minor adjustments, I made it work.