I'm assuming that you need this to work for a file that has multiple users who may all be needing to issue such a code at the same time and that this code absolutely must be unique. If you will only have a single user doing this, then there are simpler approaches.
I'll call the table where you need this code Claims. In this field, you should already have an auto-entered serial number to serve as the primary key for the records in this table. If not, define one. I'll refer to it as PK_ClaimID. We'll use this ID number indirectly to generate your Claim Authorization Number.
Define a new table, ClaimAuthNumbers, with 4 Fields:
FK_ClaimID (number field)
Date (auto-entered creation date)
SerialNumb (auto-entered serial number)
cClaimAuthNumber ( calculation field: Right(Year ( date ) ; 2 ) & Right ( "0" & Month ( date ) ; 2 ) & Right ( "0" & Day ( date ) ; 2 ) & SerialNumb )
Create this relationship
Claims::PK_ClaimID = ClaimAuthNumbers::FK_ClaimID
In this relationship, enable the "Allow creation of records via this relationship" for ClaimAuthNumbers.
Now you can generate a unique claim number for any given record in Claims that does not already have such a number by performing this single script step from a layout based on Claims:
Set Field [ClaimAuthNumbers::FK_ClaimID ; Claims::PK_ClaimID]
How it works:
If no claim authorization number has been assigned, there is no matching record in the ClaimAuthNumbers table. The set field step will thus generate a new related record that will combine today's date with the serial number so that cClaimNumber can compute the desired value. FileMaker's auto-entered serial number feature will make sure that even when two or more users create a new record in ClamNumbers at the same time, each user's record will receive a different, unique number. If someone triggers this script for a Claim record that already has a claim number, the related record already exists, so no new number is created and thus the Claim's authorization number remains unchanged.
If you want this number series to start with 1 at the start of each new year, you can reset the next serial value for ClaimAuthNumbers::SerialNumb to 1 at the start of each new year. You can do this by hand or you can set up a script that does this automatically.
If it's a single-user database, there is a fairly simple way to do it. But since you said 'we', I can assume it's multiuser. There have been a lot of suggestions on how to do what you ask, but the best one I can think of is to use a nightly server-side script that resets an auto-increment serial number field, and an auto-enter calculated field that combines the year, month, and date together with the serial number.
- Show All Records
- Show Omitted Only
- Replace Field Contents [No dialog; Test DB::Serial Number; Serial Numbers]
What the script does is shows all the records in the table, hides them, then uses the Replace Field Contents script step with the options "Replace with serial numbers: Custom values, Initial value: 1, Increment by: 1". Even without any records to work with, it still updates the serial number in the Entry Options. Now here's the calculated serial number:
Let ( $date = Get ( CurrentDate ); Year ( $date ) & Month ( $date ) & Day ( $date ) ) & Right ( "00" & serialnumber; 3 )
This combines the date in YYYYMMDD format, along with the 3 digit '0' padded serial number. The only remaining piece to figure out is which records get the serial numbers.
Will i be able to use a button to perform the script each time an authorization is needed vs a nightly script. It is at the time the record is being reviewed we decide on whether it will be assigned an authorization number for that day and it must be issued over the phone at that time therefore it needs to be one at a time instantly when needed.
It is for now a single user, however, we'd like to keep the doors open for future use and growth, there will be other uses added at a later date, so I wouldn't want to confine ourselves to restricting this use to only one single user.
Your thoughts are more than welcome thank you!
Using my suggested solution, you can generate the authorization code that you need "on command", by clicking a button.
I am getting "Either an operator was omitted, this function cannot be found or "(" was not expected here. on the field name "cClaimAuthNumber".... and also "field not found on "2"..
What did I do wrong in the following code?
(cClaimAuthNumber: Right(Year (date) ;2 ) & Right ("0" & Month (date) ;2 & Right ("0" & Day (date) ;2 ) & SerialNumber
You are missing a parenthesis.
cClaimAuthNumber: Right(Year (date) ;2 ) & Right ("0" & Month (date) ;2 ) & Right ("0" & Day (date) ;2 ) & SerialNumber
I'm still getting "Either an operator was omitted, this function cannot be found, or ":(" was not expected here" and "cClaimAuthNumber" is being highlighted........ i don't get it..... I'm ready to pull my hair out......... this should be fairly simple.I pasted the following code from my calc field that I keep getting this error from.(cClaimAuthNumber: Right(Year (date) ;2 ) & Right ("0" & Month (date) ;2 ) & Right ("0" & Day (date) ;2 ) & SerialNumber)
OK, leave "cClaimAuthNumber:" out of the expression. In my first post, I listed the field name and its expression so I could save space in my post. Apologies for the confusion.
Right(Year (date) ;2 ) & Right ("0" & Month (date) ;2 ) & Right ("0" & Day (date) ;2 ) & SerialNumber)
I was still getting the error, however, I replaced the semi colon with commas and it seems to be working, just trying to figure out the script to assign the number to the claim via buttton.
The following is what I used that worked...
Right( Year( Date), 2 ) & Right("0" & Month(Date) ,2 ) & Right("0" & Day(Date) ,2 ) & SerialNumber
Ok, when add a record in my ClaimAuthNumbers.fp file, it adds a new record with the authorization number incrementing by one....
I created the relationship as well as the script to SET FIELD, however, its not assigning the authorization number in the claims file.
That is correct, it does not. It assigns it in the Claims table. However, you can add the field from this table to your Claims layout and the number, if assigned, will be displayed.
I don't know what I changed, but its not creating the record in the ClaimAuthNumbers file any more.... i did something to set field i think
I can't seem to get the field to set right again or else I screwed up the relationship and nothing seems to be working .....
Got it Fixed!!11!! Thanks for all the help,, works like a charm........... One last thing...... how do I reset the trailing three digits to 1 at the beginning of each day ?
Make sure Set Field has both parameters. In a script, it would look exactly like this:
Set Field [ClaimAuthNumbers::FK_ClaimID ; Claims::PK_ClaimID]
If you see any extra squared brackets , then you don't have it set up right.
Here's a demo file you can download and examine: http://www.4shared.com/file/ons_yHlq/OnDemandSerialNumb.html
It only assigns the straight serial number (no date inserted at the start), but once you can get that part working, the rest should follow pretty easily.