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;