AnsweredAssumed Answered

Formula: Increment Number by 1 with 2 condition if

Question asked by luffyfilemaker007 on Mar 17, 2017
Latest reply on Mar 18, 2017 by philmodjunk

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;