1 2 Previous Next 25 Replies Latest reply on Oct 19, 2010 11:18 AM by TalienaCucura

    How do I create an authorization number based on date such as........

    TalienaCucura

      Title

      How do I create an authorization number based on date such as........

      Post

      We have client records that are claims.... some are authorized some are not therefore each record is not consecutive. 

      However, the claims that are authorized should consist of the following format.............. 

      Example , the first authorized claim for the day will be Authorization number = 101018001

      The  break down is the first two numbers from left to right represent the last two digits of this year "10"
      the third and forth two numbers left to right representing the month "10" 
      the fifth and sixth numbers representing the day of the month "18"
      and the last three digits representing the number of claim authorized on that day which is incremented by one each time a claim is authorized. 

      After all information is entered, we want to be able to click a button on the layout that issues this number in the above format.  ANY IDEAS ????????????

      Claims that are not authorized will not have this authorization number in the record, it will simply show it as rejected. 

      NEED HELP PLEASE THANK YOU!

        • 1. Re: How do I create an authorization number based on date such as........
          philmodjunk

          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.

          • 2. Re: How do I create an authorization number based on date such as........
            etripoli

            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.


            Nightly Script:

            • 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.

            • 3. Re: How do I create an authorization number based on date such as........
              TalienaCucura

              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!

              • 4. Re: How do I create an authorization number based on date such as........
                philmodjunk

                Using my suggested solution, you can generate the authorization code that you need "on command", by clicking a button.

                • 5. Re: How do I create an authorization number based on date such as........
                  TalienaCucura

                  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

                  • 6. Re: How do I create an authorization number based on date such as........
                    philmodjunk

                    You are missing a parenthesis.

                    cClaimAuthNumber: Right(Year (date) ;2 ) & Right ("0" & Month (date) ;2 )  & Right ("0" & Day (date) ;2 ) & SerialNumber

                    • 7. Re: How do I create an authorization number based on date such as........
                      TalienaCucura
                      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)
                      • 8. Re: How do I create an authorization number based on date such as........
                        philmodjunk

                        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.

                        Just enter:

                        Right(Year (date) ;2 ) & Right ("0" & Month (date) ;2 )  & Right ("0" & Day (date) ;2 ) & SerialNumber)

                        • 9. Re: How do I create an authorization number based on date such as........
                          TalienaCucura

                          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

                          • 10. Re: How do I create an authorization number based on date such as........
                            TalienaCucura

                            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.  

                            • 11. Re: How do I create an authorization number based on date such as........
                              philmodjunk

                              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.

                              • 12. Re: How do I create an authorization number based on date such as........
                                TalienaCucura

                                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 ..... 

                                • 13. Re: How do I create an authorization number based on date such as........
                                  TalienaCucura

                                  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 ? 

                                  Thanks

                                  • 14. Re: How do I create an authorization number based on date such as........
                                    philmodjunk

                                    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.

                                    1 2 Previous Next