1 2 3 Previous Next 60 Replies Latest reply on Jul 12, 2015 7:57 AM by philmodjunk

    Tricky Inventory Number System

    AndrewJudd

      Title

      Tricky Inventory Number System

      Post

      I'm working on a database in which the client would like to have quite a complicated numbering system (reference number for each record). So far, I've been able to achieve the numbering system by using the below -

      Left (SURNAME;5) & DATE & "_" & Inventory Number

      Inventory Number is just set to auto enter a serial number which is pulled into the calculation.

      However.. the client would like the numbers to run in accordance with the date. To describe, what they would like -  examples are as follows: 

      SMITH2014_001
      SMITH2014_002
      SMITH2014_003
      ...
      SMITH2015_001
      SMITH2015_002
      SMITH2015_003
      ...

      INSTEAD OF:

      SMITH2014_001
      SMITH2014_002
      SMITH2014_003
      ...
      SMITH2015_004
      SMITH2015_005
      SMITH2015_006
      ...

      Does anyone have ideas on how this can be achieved? What I think I need is a calculation for the inventory number which understands what the date is and what the last number for say, 2014, was.

      THANK YOU

       

        • 1. Re: Tricky Inventory Number System
          SteveMartino

          First I would still use the standard primary key/foreign key for relationships (doesn't have to be on the layout), and use this just for reference.

          I guess the first part of this puzzle, where does 001, 002, 003, 004, 005, and 006 come from? 

          I assume you want to reset the last 3 digits when the year changes? 

          Is it always 3 numbers max, starting with 001?

          • 2. Re: Tricky Inventory Number System
            AndrewJudd

            thanks, yes reset the last three (or upwards) when the date changes.

            The numbers are currently auto enter serial numbers pulled from another field into the calculation. The problem is getting them to start fresh when the year changes, and then run in sync. So each year is in order. If that makes sense... The numbers atart at 3 digits and build from there - 

            001, 002, 003… 100.. 1000.. 2000.. 10000

             

            • 3. Re: Tricky Inventory Number System
              AndrewJudd
              /files/9e82d590bc/Screen_Shot_2015-06-12_at_21.24.42.png 735x463
              • 4. Re: Tricky Inventory Number System
                MaxEh

                I use something similar where I have a text field called 'serial' with the following calculation and a global field for the year (I sometimes have to go back and add something to a previous year, the global field lets me do that). I never have more than 99 courses so for me the following works.

                You would need to add in calculations for numbers greater than 9 but less than 100 - a Case statement instead . 

                Let (
                [$serial = Max (YourTable::serial) + 1];

                If ( 
                Length ( $serial ) = 1 ; "0" & $serial;
                $serial
                )
                )

                So in your case I think the following would work

                Let (
                [$serial = Max (YourTable::serial) + 1];

                Case ( 
                Length ( $serial ) = 1 ; "00" & $serial;

                Length ( $serial ) = 2 ; "0" & $serial;
                $serial
                )
                )

                So you would end up with Left((SURNAME;5) & DATE & "_" & serial (or Inventroy Number) You could add additional Case statements to have the same number of digits up to 99999 to fill the field equally.

                • 5. Re: Tricky Inventory Number System
                  philmodjunk

                  To put leading zeroes to the left of a number with a variable number of digits, there's a simpler calculation:

                  Right ( "0000" & NumberFieldHere ; 5 )

                  will produce text with up to 4 leading zeroes.

                  • 6. Re: Tricky Inventory Number System
                    AndrewJudd

                    Not having much luck with this. Have i got this right -

                    I've created "Serial" which is a text field with auto-enter calculation as follows:

                    Let (
                    [$serial = Max (Serial) + 1];

                    Case (
                    Length ( $serial ) = 1 ; "00" & $serial;

                    Length ( $serial ) = 2 ; "0" & $serial;
                    $serial
                    )
                    )

                     

                    I then have my field called "inventory" which is a calculation field as following: Left(SURNAME;5) & DATE & "_" & Serial

                     

                    However I'm not getting any numbers returned on the calculation. Where do those come from?

                     

                    THanks

                    • 7. Re: Tricky Inventory Number System
                      philmodjunk

                      The method discussed here requires a relationship in order for the Max function to work.

                      You need this syntax: Max ( RelatedTable::Field ) to get the max of all related records.

                      In this context, you need a "self join" relationship that matches your table of inventory ID's with an occurrence of the same table that matches by the fields that uniquely identify the group of records that all belong to the same series. In your case that looks like a case of matching by EmployeeID and the current year. (I would not match by surname or even employee full name as names are not unique nor do they remain unchanged in all cases.)

                      BTW, I often call these "secret decoder ring IDs" as they encode some kind of added "meaning" into the ID which can then be decoded from the ID by a knowledgeable person. This has its uses on labels with limited space where people need to access the encoded data without looking it up from the database (such as someone reading the layout in a warehouse) or to support legacy systems that routinely used such methods when available data storage (and even field size) could be limited. In "new build" systems that do not need such "encoded labeling", there is no practical need for such an ID as much more user friendly and powerful methods for identifying records and accessing/sorting/analyzing data are available that do not need such an ID.

                       

                      • 8. Re: Tricky Inventory Number System
                        AndrewJudd

                        thanks, not sure I completely follow. I need to make a new number field that is matched to the date in the same table. How does this match and new field know when a user enters an artist name as John Smith or John Jones for example. Especially if the artist surname is not the link.

                        If possible could you send a few more instructions? This is my current relationship table if that helps..

                         

                        • 9. Re: Tricky Inventory Number System
                          AndrewJudd
                          /files/6e269e61e3/Screen_Shot_2015-06-14_at_19.23.32.png 845x557
                          • 10. Re: Tricky Inventory Number System
                            AndrewJudd
                            /files/0c4e966da6/Screen_Shot_2015-06-14_at_19.24.07.png 943x489
                            • 11. Re: Tricky Inventory Number System
                              AndrewJudd

                              Do I match "Serial" to "Date"? In the same table.. to return the highest number from a group of records from a same series (ie. lets say all artworks in the inventory from 2014) and then adds the new number to a new entry when it is made? 

                               

                              • 12. Re: Tricky Inventory Number System
                                AndrewJudd

                                I've matched serial and date.. still not getting any numbers in my inventory field.. any ideas? thank you

                                • 13. Re: Tricky Inventory Number System
                                  GuyStevens

                                  Hi Andrew, I think this video on my Youtube channel might show a similar technique. Maybe you should give that one a look.

                                  • 14. Re: Tricky Inventory Number System
                                    AndrewJudd

                                    Thank you Guy, I used the more simple method towards the end of that video. I have it working with one problem... Inventory numbers do run in the right order under the same name. I.e If john Jones has three artworks from 2015, when I enter a new one it does know it's the fouth entry and gives it the number 4. However... if I enter a different name - say Sam Smith, and enter 2015 as the date, it thinks it's the 5th entry, it should start again at 1 for a new name..

                                    Tricky to explain. I'll paste a picture of my graph. you can see I've added c_date, which returns the calculation of the date. Do i need to add one for the names? Otherwise how will it know it's a new person (artist)?

                                    1 2 3 Previous Next