10 Replies Latest reply on Apr 14, 2014 8:26 AM by philmodjunk

    Inventory

    DavidDicken

      Title

      Inventory

      Post

           So I'm creating a  a database that has a inventory layout with 11 categories; cat. 1 electronics, cat 2 fittings mechanical, cat 3 sundry, etc. My issue I have run into here is each category has its own numbering. The only different identifying number is the cat number. so i could have a part number 3-5125 and a 1-5125 the prefix is  the category. I currently have two fields named Category Number and Part number. The goal here is to have the user create a new record and enter the category number and have FMP13 issue the part number as I can't have duplicate numbers in the same category. Each category has around 5000 parts. I tried the serial number and add 1 that doesn't identify separate categories. .How can I get FMP 13 to issue the number?

           I was thinking something like this

           If ( inventory::Category Number: = 1; Inventory Part Number + 1) that didn't seem to work though 

           thanks in advance

           David

        • 1. Re: Inventory
          philmodjunk

               This is far from the ideal way to identify items uniquely. I strongly recommend that you not use these values as the primary key for your table of parts. If you need this value to support established procedures, you can set up fields for this in the Parts table, but set them up as simple data fields and use a different field with an auto-entered serial number of UUID as the primary key.

               You can set up a field for the category number and a field for the sequence number and use a self join relationship to assign the next value in the series for a given part category.

               The relationship can be:

               Parts::Category = Parts|Cat::Category

               Where Parts and Parts|Cats are two Tutorial: What are Table Occurrences? of the parts table.

               Then this expression:

               Max ( Parts|Cat::SequenceNumber ) + 1

               returns the next value in the sequence for the specified category.

          • 2. Re: Inventory
            DavidDicken

                 Phil,

                  You'll be happy to know that I did not use the primary field for the Part Numbering fields UUID.

                 Thanks for helping me and being patient. This is my first attempt at starting a database from scratch.  I must have done something wrong. I tried to decipher you solution and apply it. this is what I tried

                 1st I created another Table Occurrence of my inventory table.

                 2nd I created two fields one is Category Number (Text) and the other is Part Number (Text)

                 3rd In the relationship graph I connected TO 1 Category Number to TO 2 Part Number  (This made a many to many relationship)

                 4th I Then tried to use the expression Max ( Category Number:: Part Number) +1 to do this though it seemed as I had to change the  Part Number Field to a calculation field and I kept getting issues with the expression until I used Max (Category Number:; Part Number) +1  this would change the Part Number +1 but would not differentiate between the Category Numbers.

                 So now I'm confused with what I should do. What my goal is, to find the user selected Category Number then the highest number in that specific category then add one to that number and put that result in the Part Number field. What seems to be happening is a disregard for the Category Number field. I have 11 Categories each one of those has a Part Number Field  starting at 0001. I would like to apologize for not completely understanding. If you could please explain the things I'm doing wrong.

                 Thank You,

                 David 

            • 3. Re: Inventory
              philmodjunk
                   

                        3rd In the relationship graph I connected TO 1 Category Number to TO 2 Part Number  (This made a many to many relationship)

                   You should match Category Number to Category Number in this relationship.

              • 4. Re: Inventory
                DavidDicken

                     Phil,

                     The Part Number Field will either add one to the last number created regardless of the individual category number or I can make it add one to the Category Number. Neither of which is what I'm trying to accomplish.

                     Lost

                     David

                • 5. Re: Inventory
                  philmodjunk
                       

                            4th I Then tried to use the expression Max ( Category Number:: Part Number) +1 to do this though it seemed as I had to change the  Part Number Field to a calculation field and I kept getting issues with the expression until I used Max (Category Number:; Part Number) +1  this would change the Part Number +1 but would not differentiate between the Category Numbers.

                       The expression should be: Max ( Category Number 2::Partnumber ) +1

                       Max must refer to the part number field from the related table occurrence. When the relationship matches by category, this relationship matches only to record with the same value in Category and then Max + 1 can correctly return the next number in the series for that category.

                  • 6. Re: Inventory
                    DavidDicken

                         Phil,

                         It's exactly the same results.

                         So I don't know where the issue is.     Max ( Category Number 2::Partnumber ) +1

                    this adds 1 to the category number and puts it in the Part Number Field

                          

                          Max (Inventory 2::Part Number;Category Number:)+1

                    this adds 1 to the category number and puts it in the Part Number Field

                          

                         Max (Category Number:;Part Number)+1

                    this adds 1 to the category number and puts it in the Part Number Field

                          

                         Max (Category Number:;Inventory 2::Part Number)+1

                    this adds 1 to the category number and puts it in the Part Number Field

                          

                         Max (Inventory 2::Category Number:;Inventory 2::Part Number)+1

                    this adds 1 to the category number and puts it in the Part Number Field

                          

                    some of the other variations ads one to the last number regardless of category number or nothing goes in the part field at all.

                    What to do?

                         David

                          

                          

                    • 7. Re: Inventory
                      philmodjunk
                           

                      this adds 1 to the category number and puts it in the Part Number Field

                      That makes absolutely no sense. What you have should add 1 to the maximum part number, not the category number. And where it is "put" will be determined in what field is specified in the set field step that uses it-- a detail not shown in what you have posted here.

                           

                                Max (Category Number:;Inventory 2::Part Number)+1

                           

                                Max (Inventory 2::Category Number:;Inventory 2::Part Number)+1

                           are impossible unless you've added a : as the last letter of a field name. What you have posted here should produce an error message when you try to close the specify calculation dialog. In any case, they won't produce the value you want.

                           I think you need to back up and describe your tables and relationships in detail first, then post the script that uses this calculation to assign a part number to a new record. If the above expression is adding one to the category number, something is seriously not right with what you have set up in your database system.

                           A screen shot of your relationships diagram would also be helpful.

                      • 8. Re: Inventory
                        DavidDicken

                             Phil ,

                             Hope you had a nice weekend. Yes I have : After the field names. I can remove them if this causes any problems. I took your advice and I started over. I have recreated the inventory table I have made a Table Occurrence of that table and matched Category Number: to Inventory 2::Category Number:. I have made a screen shot of the relationship graph and the Specify Calculation and stopped.

                        • 9. Re: Inventory
                          DavidDicken
                          /files/e0e123b2ed/Specify_calculation.PNG 865x780
                          • 10. Re: Inventory
                            philmodjunk

                                 Please remove the : from your field names. This makes for very confusing calculation expressions.

                                 The correct expression that you want is:

                                 max ( Inventory 2::Partnumber ) + 1

                                 Note that there are two colons here, no semi-colons and no colons used as the last character of a field name.

                                 And partnumber cannot be defined as a field of type calculation. You either set it up as an auto-enter calculation or use set field in a script with this expression to assign the next part number in the series.

                                 If you use an auto-enter calculation, make sure to clear the "do not replace existing value" check box. Also, be aware that any changes made to an auto-enter calculation do not affect the value shown in an existing record. The change will only affect the next new record. When using the auto-enter option, you may need to commit the new record after creating it before it updates to show the part number.

                                 In either case, also be sure to select "unique values" for your part number as added insurance that you don't accidentally get two records with the same exact part number. (This can happen with either script or auto-enter calculation if you have two or more users adding new records at the same time and do this for records in the same category at just the wrong split second in time...