6 Replies Latest reply on Dec 16, 2014 2:43 AM by MaherQ

    Generate Serial Number from list based on field data



      Generate Serial Number from list based on field data


      hi , if someone may help me i will be grateful for him

      i have table for submit new customer with field salesman name 

      and i have table for new customer ID based on city ( data all enterd for every city new Customer ID Serial Number)

      Salesman table include his city

      what i want is if the salesman create new customer , the Customer ID will take the next Serial from the Customer ID table based on the salesman city 

      Ex. of Customer ID table

      CustomerID             City

      2000                        Den

      2001                        Den

      3000                        WC

      3001                        WC

      and so on


      is this possible to do and if yes how can i make it

      thank you in advance 


        • 1. Re: Generate Serial Number from list based on field data

          An important question is why do you want to generate your serial numbers in this fashion? What problem does that solve for you?

          And how will you use it? If this field is to uniquely identify each customer record and will be used as a match field in relationships, it's not an optimal design for your database, A simple auto-entered serial number where all customers get an ID from the same series of values without regard to the city they are from is a better option for that function in your database.

          That said, it IS possible to generate such a series of serial number values with a self join relationship that matches records by the value in City such that the relationship matches to all other records in the same table with the same city. Then Max ( Table 2::SerialNumb ) + 1 will return the value to be assigned to the next new customer from the specified city. Care must be taken if two or more users might be generating these values for the same city at the same time or you can get to client records with the same city and serial number.

          • 2. Re: Generate Serial Number from list based on field data

            Dear Phil,

            first thanks for your replay

            The problem is etch city have their own Serial # for there new customer ,so accounting can identify these customer by Their ID

            the current way is the city accounted received the new customer request and based on his City excel sheet he enter the next serial # in FM

            so we would like to update our software to achieve that throw FM without the need to enter that Manuel

            all salesman sometime enter new record in same time

            we have additional 1 main Serial # for all the City record to show the current Customer Count and its auto from the FM ( if it can help)

            if there a way to achieve your idea if 2 salesman enter record in same time ?

            and thanks again for your help


            • 3. Re: Generate Serial Number from list based on field data

              Yes but it's much simpler to simply include an identifier column for the city separate from the client's ID so that you can identify the client's city by their city ID (can be a name, but since city names are not unique, a city ID number is a better option). This add simplicity avoids a number of implementation issues needed to ensure that the client ID is always unique.

              • 4. Re: Generate Serial Number from list based on field data

                Dear Phil ,

                thanks for the replay and sorry for the late answer ( changing the internet provider toke much time than expected )

                how can i implement your idea in the solution 

                i have table for Customer Code and it include the field

                SN , City Code , City Name

                which the the New Customer Table will request the code from

                Can you guide me throw the necessary setup or point me to where to look

                thanks in advance again


                • 5. Re: Generate Serial Number from list based on field data

                  Ideally, especially to use as a match field in relationships, your Customer code field should just be an auto-entered serial number with no added auto-enter details. If you have multiple users collecting this data on multiple copies of your database that you then merge into a single table of data, I suggest using a text field with Get ( UUID ) as the auto enter calculation and that you investigate one of the third party Sync tools available such as those offered by SeedCode and 360Works.

                  I would then recommend that you set up a related table with one record for each city where you record at least the name of the city and define a similar ID field for the city ID there though this table is a good place to record any other data you may need specific to a particular city. You'll also need to consider whether you need to include additional design details to handle situations where records for different cities with the same name might be entered into such a table.

                  You'd then link your current table of records to this City table by CityID fields defined in both tables.

                  When you export this data, you can include not only the cityID field as a column of exported data, you can also include any other fields from the related city record--such as the city name if you need that info.

                  "Encoded meaning" identifiers should really only be used for two purposes as far as I can imagine when it comes to possible uses for them:

                  a) for printing labels where some people reading the info need to be able to "decode" key details from a label where there isn't enough space to simply print out all of the information in plain text.

                  b) to support "legacy systems" out side of the immediate function of your database--such as interfacing with another DB system that requires this code or to keep users happy that are requiring it as this is how they did things in the past.

                  For both a) and b) you'd set up such a field and put it where needed on layouts, but you wouldn't use such an ID field as the primary key for use in your relationships to link records in this table to related records.

                  • 6. Re: Generate Serial Number from list based on field data

                    Thank You So much Phil

                    i checked both of them and i think i will go with 360 work demo and read the manual , before buying it

                    and then start what you suggest to me