I am pretty new to FMP, and I am having trouble building a calculation that will automatically generate intake numbers for my organization.
The intake number works as follows:
- Last two digits of the year
- Digits of the month
- Hyphen
- Number of complaints received thus far that month
Sample result: 1803-119
The first four digits are currently being set manually at the start of each new month, but this creates inaccuracies, because the person in charge of re-setting it often forgets, so the first 10-25 intakes for the new month get entered on the old month. In case it matters, we never have a need (or a desire) to go back and enter new intakes on an old month; the moment the month changes, the intake should be identified by the new month.
I have pieces of the calculation worked out, I think:
If ( Length ( Get ( CurrentDate ) = 9 ); ( Right ( Get ( CurrentDate ) ; 2 ) & "0" & Left ( Get (CurrentDate) ; 1) & "-" &
I have tried adding Serial Increment at the front and the back of this in a whole range of different ways, and I can't get the number to increment. (I keep getting 1803-2.) I don't know if the problem is that I don't understand the hierarchy of functions, or if my whole approach is wrong, or if I don't even know enough to know where I'm screwing up.
In any event, thanks for any help you can offer!
You have to have a value to increment before you can increment it with serial increment.
You might define a field as an auto-entered serial number. A script controlled by a server schedule could automatically reset the serial number's "next serial value" on the first of each new month.
Your calculation might then be:
Let ( T = Get ( CurrentDate ) ;
Right ( Year ( T ) ; 2 ) & Right ( "0" & Month ( T ) ; 2 ) & "-" & SerialNumberFieldHere
)
But do not use this calculated value as a primary key field used to link to other records in relationships. Use it only as a label and as a value on which you can search/sort.