3 Replies Latest reply on Sep 18, 2013 1:48 PM by philmodjunk

    creating a custom invoice/tracking number



      creating a custom invoice/tracking number


           Hi all,


           I am new to FM. but savvy enough to break things and fix it. I just can't wrap my head around this... 


           I need to create a custom number for every new record... an internal tracking No. that needs to be formatted like:  


           example:  2013OVB-1  


           there doesn't need to be a zero place holder preceding the "1".


           this number is going to be related to every record in the DB. There are 4 tables in the DB that will use this number. It will be unique to every record. regardless from which table the record is generated from, I need the number to be sequential?


           any thoughts



        • 1. Re: creating a custom invoice/tracking number

               I would not use such a number for such a purpose. If you need such a value for uses external to your database, then set up a field for it in the main table. But for linking records and uniquely identifying them--a primary key as we call it--use a simple auto-entered serial number or Get ( UUID ) to produce your primary key for use within your database. Such keys should not have any special info "coded" into them anyway.


                    It will be unique to every record. regardless from which table the record is generated from,

               Why unique to every record regardless of the table? That seems a very strange and likely unnecessary requirement. It can be done, I just don't see the need for something that will add to the complexity of your database design but which does not add any benefits. Can you provide an example of why you need an identifier for a record in table A to be unique not only for the records in table A but also for the records in all other tables?

          • 2. Re: creating a custom invoice/tracking number

                 all the tables share that number....

                 some tables share that number for their respective function... for example

                 2013ovb-1 is internal number we use to track a project. but that project has a sale and a serive attached to it. so if you pull the service layout up.. it will populate only the service data.... if you pull the sales layout up it will pull the sales data only...


                 But, if im in the sale layout and generate a new record, that number need to able to be shared...

            • 3. Re: creating a custom invoice/tracking number

                   Yes, but don't use this field and value to link tables in your database. There should be a main table that where a record is created there first--presumably a project table. You can record such a value in a field in this table, but use the internally generated serial number to link to related records in sales and service tables. Any layout based on the Sales or service tables can show data from the related parent record in Projects, including your Project ID field that you would use for purposes EXTERNAL to the database itself such as for communication within your organization.

                   And none of this explains why you would need such a value to be unique for each record in different tables--instead you now appear to specify the opposite. As all sales, service and project data for a given project should all have the SAME value for identification/communication purposes.

                   To automatically generate that value in your Projects table, I'd need to know more details. I can get the Year and the -1 parts of the value very easily, but from where do you get the text in the middle?