7 Replies Latest reply on Feb 10, 2009 1:17 PM by ralfthewise

    AutoIncrement 2nd part of a number, based on the first.



      AutoIncrement 2nd part of a number, based on the first.


      Good afternoon.


        I'm trying to build a Terminal Management System for a client, who has a specific need.


        They have Contracts and then Loads against those Contracts.  For example, Contract # = 10001 might require 3 loads to complete, so the 3 loads would be 10001-001, 10001-002, & 10001-003.  And Contract 10002 might require, 10002-001, 002, 003, & 004.


        The previous software package, which I have been asked to replace, performs on the following logic:

      1. User selects contract #.
      2. The system looks for the next available load # for that specific contract and returns that for the load number

        Example:  User selects Contract # = 10001.  The system looks and finds 10001-001 and 10001-002 currently exists, so it returns the next available load 10001-003.


        Example:  User selects Contract # = 10002. The system looks and finds that no loads have been assigned to 10002 and as such, returns 10002-001.


        I cannot for the life of me, figure out how to get a calculation that will perform this function.


        I have 2 tables.  Contract (ID, Contract#) and Load (ID, Contract #).


        The reason I have to stick to this format, is because the client intends to have different vendors setup their own loads, and requires the system to just give them the next number, rather than allow them to select their own number.


        Your help and advice would be greatly appreciated.  


      Level Newbie,



        • 1. Re: AutoIncrement 2nd part of a number, based on the first.

          Welcome to te forum, ralfthewise,


          For my understanding:


          You have a Contracts Table

          You have a Load Table, related to the Contracts Table by ContractNumber


          So in the Load Table you may have against Contract 10001 and 10002 the following records:


          Contract Number   Load Number

          10001                     001

          10001                     002

          10001                     003

          10002                     001

          10002                     002

          10002                     003

          10002                     004


          ...and you want to add another Load to Contract 10001, and call it Load 004?


          I suggest you create a self-join relationship for the Load Table by ContractNumber, and set the LoadNumber field to auto-enter by calculation:

           = Max (SelfByContractNumber::LoadNumber) + 1


          If you create a new record in the Load Table it will auto-generate the next number as soon as you enter the Contract Number.  If you make it easier for the user to create a new Load by allowing them to view an existing Contract with a portal showing all of the existing loads for that Contract (with a relationship that 'Allows creation of new records'), then it will again auto-fill the Load Number with the next one you want.




          • 2. Re: AutoIncrement 2nd part of a number, based on the first.



             Thanks for the prompt reply.  Having some problems, probably a result of my newbie status as much as anything else.


              1. Self Join:  I drug loads:contract number to loads::load number.  It asked to create another table and did so, naming that table = Loads 3.  The relationship went Many on the loads::contract number side and has a slight vertical bar on Loads3::Load Number table.

              2.  The "SelfBy" to which you refer in for the calculation.  I have a "Self" but no "SelfBy". I'm running FMP10, am I missing something.


            Thanks for your patience.



            • 3. Re: AutoIncrement 2nd part of a number, based on the first.

              Found part of my problem.  I dug further into the Type "Number" and found that you meant I should use "Auto-enter Calculation" under the Number area.  I was attempting to create the Load Number field as a Type Calculation.


              However, I'm still having problems with "SelfBy".  Any direction?




              • 4. Re: AutoIncrement 2nd part of a number, based on the first.

                Sorry for the lack of detail in my explanation - it's always hard to know how much people already know (so laziness makes me tend to err on typing less...)


                You should set up a new relationship (File ->Manage -> Database ->Relationships tab) with the Load Table on the left and the same Load Table on the right.


                The two listings below will look the same - they are both the listings of the fields in the Load Table.


                In the left hand list choose the field 'ContractNumber', under the 'Operators choose "=", and in the right hand list choose 'ContrcatNumber.'  This will make each record in the Load Table check through the other records in the Laod Table and find all the ones that have the same ContractNumber as itself.


                You should now have a Table ('Occurance', strictly) called 'Load', and another called 'Load 4', joined with a line from 'ContractNumber' to 'ContractNumber'.


                When you click 'OK' you will be prompted (probably) to accept the name for this relationship that FM suggests.  It will probably be like 'Load 4'.  Just accept it now - you can change it later.


                In the 'Define Fields' tab go to the Load Table and the LoadNumber field and define the calculation I previously suggested - by changing the Table Occurance at the top left of the calculation dialogue to be 'Load 4' (or whatever the name was the you accepted as that new relationship name.)









                • 5. Re: AutoIncrement 2nd part of a number, based on the first.



                    Thanks for working with me on this.....I must still be missing something.


                    I have 2 tables:

                  1. Table 1 "Loads 020309" with 2 Fields "Load Number" and "Contract Number".  Contract number is a text field using a Value List for the possible Contracts, from which the user selects the contract via a drop down.
                  2. Table 2, which is created when I drag Field "Contract Number" to Field "Load Number" in Table "Loads 020309", winds up being called "Loads 020311".  The is a many to many relationship drawn between "Contract Number" in both tables.
                  3. The relationship between the two tables is (Left Side) "Loads 020311 --> Contract Number" = (Right Side) "Loads 020309 --> Contract Number". 
                  4. The calculated value for "Loads020309 --> Load Number" = MAX (Loads 020311::Contract Number) + 1

                    The result in Table view is I can add a Contract Number via the drop down and the Loads field remains empty.


                    If I change 4 above to  = MAX (Loads 020309::Contract Number) + 1 I select Contract Number and the Load Number becomes that Contract Number + 1.  EX:  Contract Number = 10001, then Load Number comes back 10002.  Load number should come back 10001-1 (or -2, etc).




                  • 6. Re: AutoIncrement 2nd part of a number, based on the first.

                    The field 'LoadNumber' should just be made a 'Number' field, when you defined it in Field Definitions.


                    But when you click through the 'Options...' button in the bottom right of the Field Definition box you will get another few tabs. 

                    - Select the  'Auto-enter' tab, and click 'Calculated value'.

                    - Enter the calculation MAX (Loads 020311::Contract Number) + 1, and when you specify a Contract Number (from your value list in the field 'ContractNumber') you will see that it populates it with the correct next Load Number.


                     You may want to then create a concatonated field similar to:

                     ContractNumberLoadNumber = Contract Number & "-" & Load Number.


                    This will give you the text in the form 10001-1.


                    If you get those steps to work we can work on making it in the form you originally suggested: '10001-001' etc.


                    We'll get there!




                    PS: Strictly speaking, you have (or should have) only one 'Table' from your description.  The other is another 'Occurance' of the same table: Loads 020309'.  They are the same table with the same records, fields and data as each other.  Filemaker uses the different 'Occurances' to let you link Tables in different ways.  For example, you may want a Contracts Table to link to the Load Table by matching Contract Numbers.  Or you may want it to link by matching Customer Names.  When you want to make a calculation that is using a link between the two tables, Filemaker has to know which link to use - by ContractNumber, or by CustomerName.  So it makes you call each 'Occurance' a different name, so you can be specific about which one you want ot use in the calculation.


                    PPS: I think you would be better having two Tables - one to hold one record for each Contract, ('Contracts Table') and one to hold one record for each load ('Load Table'.)

                    • 7. Re: AutoIncrement 2nd part of a number, based on the first.



                        I did finally get this to work.  You are the bomb.