8 Replies Latest reply on Aug 19, 2013 8:09 AM by philmodjunk

    Asset Code based on fields and serial

    AmjadMiandad

      Title

      Asset Code based on fields and serial

      Post

           Hi friends,

            

           I'm working on a database for our Asset management. Below please have a look on fields and sample data

            

           Asset Code: ---------------

           Asset Name: Laptop

           Asset Type: computers

           Asset Model: Insprion N5050

           Asset Category: IT Equipments

           Project: School Safety Program

           Donor: My Donor Pvt. Ltd.

           Now what we want is the auto asset code to be created using abbreviation from the data of some fields and at the end auto number 0001 , 0002 , 0003, 0004, 0005 (separate series for every Asset Type, i.e. laptops, scanners, etc)

           ABCD/PROJECT/DONOR/ASSET CATEGORY/ASSET NAME/0001

           Now have a look on actual asset code so you may understand what sort of abbrevation I taken from the data mentioned in above fields.

           ABCD/SSP/MAYDONOR/ITEQ/LAPT/0001

           The abbreviation could be random pick from entered data in the field with any calculation.

           Please note that ABCD will remain constant in my case it is RDPI/  Projects short name / donors major or short name or abbreviation/ four alphabates from item category /four alphabates from item name/serial number

            

            

           Thanks.

        • 1. Re: Asset Code based on fields and serial
          philmodjunk

               What is the reason for using such a complicated asset code? (The more complex a system, the more and more complicated ways that it can fail...)

               I strongly recommend that you not use such a code as a primary key to link records in your assets table to other tables in your database. Use a spearate auto-entered serial number for that purpose and use such a code only if your users require it of you and then set it up as a field in your Assets data base to only use for printing on labels and for searches/sorts of your asset records. That way errors in the code or user mandated changes to how this info is encoded won't screw up your database.

               This part of your code can be a relatively straight forward string concatenation:

               ABCD/SSP/MAYDONOR/ITEQ/LAPT/

               Field1 & "/" & Field2 & "/" and so forth... I suggest you set up related tables of projects, donors, Asset categories and Asset types so that you can set up your abbreviations in records in these fields that your calcuation can reference to pull together the needed text.

               The sticky part is the serial numbers that each start at 1 for each asset type. You can set up a self join relationship that matches to all records of the same asset type:

               Assets::Asset Type = Assets|SameAssetType::Asset Type

               Then this expression returns the next serial in the series.

               Max ( Assets|SameAssetType::SerialNumber ) + 1

               And this expression:

               Right ( "000" & SerialNumber ; 4 )

               will supply the needed leading zeroes.

               But take very careful note of the fact that if two users create new Asset records for the same asset type at the same time, it is possible to get two assets with exactly the same serial number. It is wise to add a text field with this expression:

               Asset Type & SerialNumber

               as an auto-entered calculation so that you can then set a unique values validation field option on it to catch any instances where this rare but possible event take place.

          • 2. Re: Asset Code based on fields and serial
            AmjadMiandad

                 thanks for the help, you are right I should create new tables for asset type and rest with abbreviation, will request for more help if my try for calculation didn't work.

            • 3. Re: Asset Code based on fields and serial
              AmjadMiandad

                   hi Phill,

                   I have used this calculation temperorily to get letters as abbreviation using the existing fields and tables, (will work on separate tables and abbreviations later )

                    

                   "CUSTOME TEXT/ " &
                   Upper     (

                           Case        (
                                   not IsEmpty ( Project) ;
                                   Left ( Project ; 5 ) ;
                                   Right ( Project ; 5 ) ;

                               
                                   " -- "
                                   )
                           )
                    & "/" &
                   Upper     (

                           Case        (

                                   not IsEmpty ( Donor ) ;
                                   Left ( Donor ; 4 ) ;
                                   Right ( Donor ; 4 ) ;

                                   
                                   " -- "
                                   )
                           )
                    & "/" &
                   Upper     (

                           Case        (


                                   not IsEmpty ( Asset Category ) ;
                                   Left ( Asset Category ; 4 ) ;
                                   Right ( Asset Category ; 4 ) ;
                                   
                                   " -- "
                                   )
                           )
                    & "/" &
                   Upper     (

                           Case        (


                                   not IsEmpty ( Asset Type ) ;
                                   Left ( Asset Type ; 4 ) ;
                                   Right ( Asset Type ; 4 ) ;
                                   
                                   " -- "
                                   )
                           ) & "/" &
                   SerialIncrement ( "000" ; ID )

                   it gives me this result showing number of letters I mentioned in calculation above:

                     CUSTOM TEXT / PROJECT / DONOR/ ASSET CATEGORY / ASSET NAME / 000ID (as serial)

                    

                   Now the issue is serial number of each asset name according to asset type, I would appreciate if anyone can help me in this regard.

              • 4. Re: Asset Code based on fields and serial
                philmodjunk

                     Hmm...

                     Take a closer look at your case functions:

                       Case        (
                                     not IsEmpty ( Project) ; Left ( Project ; 5 ) ;
                                     Right ( Project ; 5 ) ;  " -- "
                                     )

                     The first part makes sense, if the project field is not empty, return the left 5 characters in that field, but Right ( Project ; 5 ) makes no sense your code returns the right 5 characters of an empty project field as a boolean expression where it needs to evaluate to True or False.

                     Case        ( not IsEmpty ( Project) ; Left ( Project ; 5 ) )

                     will return an identical result.

                     I think that you want something like this:

                       Case        (
                                     not IsEmpty ( Project) ; Left ( Project ; 5 ) & Right ( Project ; 5 ) ;
                                     " -- "  // else result here
                                     )

                • 5. Re: Asset Code based on fields and serial
                  AmjadMiandad

                       yup thats okay till the serial number what to do with serial number.

                       I don't want to have serial number as my data row or entry ID, I need a separate serial number or series for all the asset types. i.e.

                  CUSTOM TEXT / PROJECT / DONOR/ IT Equipment / Laptop / 0001

                  CUSTOM TEXT / PROJECT / DONOR/ IT Equipment / Laptop / 0002

                  CUSTOM TEXT / PROJECT / DONOR/ IT Equipment / Laptop / 0003

                  and

                        

                  CUSTOM TEXT / PROJECT / DONOR/ IT Equipment / DigCam / 0001

                  CUSTOM TEXT / PROJECT / DONOR/ IT Equipment / DigCam / 0002

                  CUSTOM TEXT / PROJECT / DONOR/ IT Equipment / DigCam / 0003

                  CUSTOM TEXT / PROJECT / DONOR/ IT Equipment / DigCam / 0004

                  or

                  CUSTOM TEXT / PROJECT / DONOR/ Furniture/ Chair / 0001

                  CUSTOM TEXT / PROJECT / DONOR/ Furniture/ Chair / 0002

                  CUSTOM TEXT / PROJECT / DONOR/ Furniture/ Chair / 0003

                  CUSTOM TEXT / PROJECT / DONOR/ Furniture/ Chair / 0004

                  CUSTOM TEXT / PROJECT / DONOR/ Furniture/ Chair / 0005

                  or

                  CUSTOM TEXT / PROJECT / DONOR/ Furniture/ table / 0001

                  CUSTOM TEXT / PROJECT / DONOR/ Furniture/ table / 0002

                  CUSTOM TEXT / PROJECT / DONOR/ Furniture/ table / 0003

                  CUSTOM TEXT / PROJECT / DONOR/ Furniture/ table / 0004

                       rest of the calbulation before serial number 0001 you have seen above what should I do so it auto assign a seprate serial to each item type

                  • 6. Re: Asset Code based on fields and serial
                    philmodjunk

                         I've already described how to get that value:

                         Assets::Asset Type = Assets|SameAssetType::Asset Type

                         Then this expression returns the next serial in the series.

                         Max ( Assets|SameAssetType::SerialNumber ) + 1

                         And this expression:

                         Right ( "000" & SerialNumber ; 4 )

                         will supply the needed leading zeroes.

                         The only way this should not work is if an asset type can be used in more than one of the above groups. Should that be a possibility, just use a field with all of the above except the number as the field to use in place of Asset Type as your match field.

                    • 7. Re: Asset Code based on fields and serial
                      AmjadMiandad

                           oh please consider me a beginner

                           please guide me where and how to add

                            

                           Assets::Asset Type = Assets|SameAssetType::Asset Type

                           and

                           Max ( Assets|SameAssetType::SerialNumber ) + 1

                            

                           and then

                            

                           Right ( "000" & SerialNumber ; 4 )

                           should I add this in calculation code of my serial field

                      • 8. Re: Asset Code based on fields and serial
                        philmodjunk

                             First you need a self join relationship that matches a given record to all other records in the same table that will be part of the same number sequence. If asset type is unique to each number series, you can use it as the match field which is what I originally suggested. This requires a duplicate table occurrence:

                             In Manage | Database | relationships, make a new table occurrence of Assets by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be: Assets|SameAssetType.

                             We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                             Add it to your relationships like this:

                             Assets::Asset Type = Assets|SameAssetType::Asset Type

                             There is more than one way to use the following expression. the safest is to run a script once you have selected/entered the Asset Type that then assigns the serial number;

                             Set Field [ Assets::SerialNumber ; Max ( Assets|SameAssetType::SerialNumber ) + 1]

                             In this example, SerialNumber is a field with just the numeric portaion of the serial number. Your long complex string of text plus number should be a different field.

                             and Right ( "000" & SerialNumber ; 4 ) would be part of the calculation that you define for that field.