4 Replies Latest reply on Jul 2, 2012 9:23 AM by philmodjunk

    Orders numeration (Serial)



      Orders numeration (Serial)


      Dear Friends,

        i'm afraid that my question was here already, but it is very hard to find it. Please don't be angry with me. 

        I have made a DB with following tables: customers, contracts, orders. 

        Every customer has it's unique number as well as contract, but as for the orders - i run them in single table, but I want them to have serials linked with contract number (to be independent), for example:

      contract #1, order 1, 2, 3, 4

      contract #2, order 1, 2, 3, 4

       As far as I see it - there must be related table to my contract like [orders.serial] with only ID and SERIAL fields. 

       Is it possible to do it without using scripts? i doubt it. But what could be the most convinient way of running this script? I would like to avoid buttons. 

       Thank you for all your suggestions! 

        • 1. Re: Orders numeration (Serial)

          You were probably due for a new thread anyway, but if you want to search for it, you can select the advanced search option and specify your name--capitalized and spelled exactly as you see it here in the forum to find only threads where you've either posted the original post or posted comments.

          What you want can be done without scripts, but care must be taken if you have more than one user doing this--or might expand to that point in the future. If two users do this at the same time, it's possible to get duplicate values.

          Take a look at this demo file: https://www.dropbox.com/s/ggjy88cgkescopy/CategorySerialNos.fp7

          Key picky details:

          Value in Field is NOT used as a primary key.

          OrderNumber field auto-enters a 0 and then a calculation uses a self join relationship to enter the Max ( relatedtable::orderNumber ) + 1.

          Do Not evaluate if all referenced fields are empty must be cleared.

          Do note replace existing value must be cleared.

          A script to commit the record is needed when you use a portal to create the records. I used OnObjectExit on the portal to do this. Otherwise, you can create a record in row one, click or tab into row 2 and get the same orderNumber.

          To guard against duplicate values, note the options specified on the validation tab for orderNumber that specify unique values.

          • 2. Re: Orders numeration (Serial)

            Dear Phil,

            I'm sorry for late reply as I have time to work on my database only during weekends. I tried your script and I got you logic. But there is some kind of a bug in it. I made 2nd customer and trying to add another order and after it a have an error: 

            “OrderSerialNumber” is defined to contain unique values only. You must enter a unique value.

                I spent hours to fix it, but couldn't.

            • 3. Re: Orders numeration (Serial)

              Ooops, now, when I posted here - i read it myself again and found out problem. I unchecked box of unique value in OrderSerialNumber and it is working like a magic, but... does it bring us any side effect? 

              • 4. Re: Orders numeration (Serial)

                Validation should be enabled to enforce unique values. With that validation unchecked, there is no safeguard against two users accidentally getting the same value in different records.

                I think you need to look deeper for the cause of this error. Note that the demo file does not have this issue and it enforces unique values.