5 Replies Latest reply on Mar 18, 2017 9:32 AM by philmodjunk

    Formula: Increment Number by 1 with 2 condition if


      Hi guys;


      So I am working on this project and hope someone can help me out to find the formula i am looking for:


      I have my Customer Table with Customer_ID  field(which works great normally)


      But I also need a separate Customer_File_Number field.

      That is because we have two types of customers ( Normal and Archives) and the way their Files are stored physically is different (one in the office, one in the attic). I need this number because we have to know where to physically store the IRL documents related to the customer.


      So I have set a Customer_Type field (which data is "Normal" or "Archive") and what I would like to do is to automate on creation the Customer_File_Number when I enter a new record that is based on whether it is a Normal or an Archive.

      So what I would like is click "New Record", Tick Box the Customer_Type field (Normal or Archive) and then automatically have the Customer_File_Number entered for me.


      Now the problem is that they have two different styles of Number:

      The archives start   "A201001" and then should increment by one

      and the Normal start "000001"

      But i can not just +1 or it would mess up the two types of records.


      Now the formula I thought about but I can not code it properly is :


      If (Customer_Type Field = "Normal";

      look for the highest value in Customer_File_Number that do not have the letter A and then increment by 1,

      but if Customer_Type Field  = ''Archive";

      look for the highest value in all the Customer_File_Number that do have the letter A and increment by 1

      Also I want to make sure that a number can not be used twice.



      I hope this is clear enough and would really appreciate if anyone can sort this out.


      Looking forward to your help;


        • 1. Re: Formula: Increment Number by 1 with 2 condition if

          It's possible to assign a unique ID "on command" using a related table, but it's not clear why you would need to do this every time you "create a record" as this implies that you are not creating new custom records, but rather records that should be related to customer. Such records should be linked by a primary key defined in Customer that is a simple serial number or UUID string.


          The "filing codes" that you need would be stored in fields in the customer table not these new related records. You would have one such value created automatically for a "new" customer (can be auto-entered serial number) that can then be changed to an archived value when you move the physical files to your archive storage. This new serial number can be generated via a script that creates a related record that contains a serial number for the archive serial number series of values.

          • 2. Re: Formula: Increment Number by 1 with 2 condition if

            Hi Philmodjunk,


            Thanks for trying to help but I am not sure I understand what you mean as I am quiet new to Filemaker.

            But I think you get what I am looking for specially  in your second paragraph.


            I think it is a good idea to keep the "FilingCodes" Field in the Customer Table.

            Do you have any idea what the formula on creation of the auto-entered serial number would look like ?

            My intuition is that it is pretty basic;

            if it's Archive, just have to lookup the last highest value for "FillingCodes" that contains the letter "A" and increment by one;

            if it's Normal, lookup the last highest value for "FillingCodes" that do not contain the letter "A" and increment by one;


            But my skill are to poor to figure it out properly;



            • 3. Re: Formula: Increment Number by 1 with 2 condition if

              Doesn't every customer start out as "normal" and then change to "archive"?


              I don't really see a "formula" here, but rather two different states for a given customer record with the second filing code assigned at the tie a "normal" customer changes to "archive". Or am I misunderstanding the process?

              • 4. Re: Formula: Increment Number by 1 with 2 condition if

                Hi ,


                No, in my process a customer is either Normal or Archive, but a Normal does not necessary will become an Archive.

                But it can, and an Archive can become a Normal again.

                We only Archive customer that have few chances of coming back. and We archive directly on creation if we know the customer is not going to come back.

                So on creation a customer is ether Normal(Regular if you want) or Archive.

                And  I cantype in manualy their PhysicalFileNumber, we just have to look at the next empty number of the in the Files drawer, but there are two drawers , one is near the reception and is used for Normal, and one is stored far away for the Archives (files we are rarely supposed to use again but it might happen)

                • 5. Re: Formula: Increment Number by 1 with 2 condition if

                  Add two new tables to your solution. Give each these fields:






                  Define FilingSerial as an auto-entered serial number and in each table, set the next serial value as needed for your next label of that type.


                  For normal the normal customers table, define FilingCode as a text field with this auto-enter calculation: Right ( "00000" & FilingSerial ; 6 )


                  For archive, use: Right ( "A00000" & FilingSerial ; 7 )


                  link both of these tables to your customer table by customer ID fields and enable the "Allow Creation" option for the new tables.


                  To to generate a "normal" filing code, perform this script:

                  Set Field [ NormalFilingCode::_fkCustomerID ; Customer::__pkCustomerID ]


                  You do the same to get an Archive FilingCode but refer to the other table in the set field step.


                  You can can put the FilingCode fields from these related records on your customer layouts to show any assigned filing codes. 


                  This method is pretty safe. If your file is hosted from a server and two users generate filing codes at the same time, they will not get duplicates. If you accidentally run the same script twice from the same customer record, the value will not change.