14 Replies Latest reply on Feb 3, 2012 11:32 PM by RayCologon

    Checking for Expiration with Month and Year

    MattLeach

      We've integrated credit card processing into our FileMaker solution using X-Chare's filemaker plugin and everything has been going well.

       

      Our solution stores a clients credit card information such as type, mmasked acct # and expiration. When i mean store, the card is actually not stored in our solution. It is stored on X-Charges secured server and assigned an alias which is entered into filemaker.

       

      What i'm looking to do is build in some error checking in regards to the expiration of the credit card. Normally this would be a no brainer but since the expiration contains only the month and the year, not an actual date, i'm not quite sure how to go about this.

       

      Basically im needing to check the month and year of the current date again the month and year of the card expiration date to see if the card is expired or not.

       

      The expiration date is stored in a field called CC_Exp and is in the format of MM/YY.

       

      I'm assuming that i would need to parse the field as assign a variable for month and year?

       

      ANy help is appreciated. Thanks

        • 1. Re: Checking for Expiration with Month and Year
          RayCologon

          Hi Matt,

           

          A calculation along the lines of the following:

           

          Let([

          d = Get(CurrentDate);

          x = Left(CC_Exp; 2) &"20" & Right(CC_Exp; 2);

          y = Right("0" & Month(d); 2) & Year(d)];

          y  <  x

          )

           

          ...will return a 1 if the card has expired and a 0 if it's still valid.

           

          Regards,

          Ray

          ------------------------------------------------

          R J Cologon, Ph.D.

          FileMaker Certified Developer

          Author, FileMaker Pro 10 Bible

          NightWing Enterprises, Melbourne, Australia

          http://www.nightwingenterprises.com

          ------------------------------------------------

           

          Message edited byRay Cologon Note: There's a transcription error in the calc provided here, as noted below

           

          Message edited by Ray Cologon Note: There's a transcription error in the calc provided here, as noted below.

          • 2. Re: Checking for Expiration with Month and Year
            MattLeach

            I went ahead and tried this script and it does not appear to work correctly.

             

            I modified the script to include the TO and field reference in my database:

             

                      Let([

                      d = Get(CurrentDate);

                      x = Left(CC_Alias::CCA_fExp; 2) &"20" & Right(CC_Alias::CCA_fExp; 2);

                      y = Right("0" & Month(d); 2) & Year(d)];

                      y  >  x

                      )

             

            To test the output i created a new script which shows a dialog with the result of the above calculation.

             

            My test record has an expiration date of 12/11 but the result of the above calculation shows 0 which indicates it's still valid although it is not.

            • 3. Re: Checking for Expiration with Month and Year
              MattLeach

              I dug in a bit further and based on the calculation, the above answer is correct.

               

              Using the calculation and my example record:

                        x = 122011

                        y = 022012

               

              therefore y < x

               

              Seems to be referencing as numbers instead of a date.

              • 4. Re: Checking for Expiration with Month and Year
                PSI

                Variables store data as text. try this...

                 

                Let([ 

                d = Get(CurrentDate);

                x = Left(CC_Alias::CCA_fExp; 2) &"20" & Right(CC_Alias::CCA_fExp; 2);

                y = Right("0" & Month(d); 2) & Year(d)];

                GetAsDate ( y ) > GetAsDate ( x )

                )

                 

                John Morina

                Pueblo Systems, Inc.

                MattLeach wrote:

                 

                I dug in a bit further and based on the calculation, the above answer is correct.

                 

                Using the calculation and my example record:

                          x = 122011

                          y = 022012

                 

                therefore y < x

                 

                Seems to be referencing as numbers instead of a date.

                • 5. Re: Checking for Expiration with Month and Year
                  comment

                  MattLeach wrote:

                   

                  Seems to be referencing as numbers instead of a date.

                   

                  Actually, it's text. See also:

                  http://fmforums.com/forum/topic/82285-expired-credit-card-date-calc/page__p__381516#entry381516

                  • 6. Re: Checking for Expiration with Month and Year
                    MattLeach

                    Result is still 0

                    • 7. Re: Checking for Expiration with Month and Year
                      comment

                      PSI wrote:

                       

                      try this...

                       

                      Have you?

                      • 8. Re: Checking for Expiration with Month and Year
                        MattLeach

                        Using the calculation in the reference post works:

                         

                        Let ( [
                        exp
                        = Date ( Left ( Expiry ; 2 ) + 1 ; 0 ; 20 & Right ( Expiry ; 2 ) )
                        ] ;
                        Get ( CurrentDate ) > exp
                        )

                         

                        The result is now a 1

                         

                        Thanks!

                        • 9. Re: Checking for Expiration with Month and Year
                          PSI

                          Sorry matt, I didn't look at the complete structure of the let. This would work as well but the version you just posted is more succinct.

                                Let([

                                    d = Get(CurrentDate);

                                    x = Left( Payg::gPayCardExpMth; 2 ) & "/1/" & 1 & Payg::gPayCardExpYR;

                                    y = Right("0" & Month(d); 2) & "/1/" & Year(d)];

                                  
                          GetAsDate ( y ) > GetAsDate ( x )

                                    )

                           

                          John Morina

                          Pueblo System, Inc.

                           

                          MattLeach wrote:

                           

                          Using the calculation in the reference post works:

                           

                          Let ( [
                          exp
                          = Date ( Left ( Expiry ; 2 ) + 1 ; 0 ; 20 & Right ( Expiry ; 2 ) )
                          ] ;
                          Get ( CurrentDate ) > exp
                          )

                           

                          The result is now a 1

                           

                          Thanks!

                          • 10. Re: Checking for Expiration with Month and Year
                            comment

                            The reason why Ray's calc didn't work for you is that it puts the month before the year. Alphabetically, "022012" comes before "122011". It would have worked if you had reversed the order =

                             

                            Let ( [

                            exp = "20" & Right ( Expiry ; 2 ) & Left ( Expiry ; 2 ) ;

                            now = 100 * Year ( Get (CurrentDate) ) + Month ( Get (CurrentDate) )

                            ] ;

                            exp < now

                            )

                            • 11. Re: Checking for Expiration with Month and Year
                              MattLeach

                              That makes more sense now that i think about it. Either one will work (tested with reverse order)

                               

                              Thanks guys!

                              • 12. Re: Checking for Expiration with Month and Year
                                comment

                                PSI wrote:

                                 

                                      Let([

                                          d = Get(CurrentDate);

                                          x = Left( Payg::gPayCardExpMth; 2 ) & "/1/" & 1 & Payg::gPayCardExpYR;

                                          y = Right("0" & Month(d); 2) & "/1/" & Year(d)];

                                        
                                GetAsDate ( y ) > GetAsDate ( x )

                                          )

                                 

                                Note that:

                                (a) this will work only if your file is set to use m/d/y as the date format;

                                (b) GetAsDate (y) returns the current date, doesn't it? Couldn't you get it directly, instead of going through d and y?

                                • 13. Re: Checking for Expiration with Month and Year
                                  PSI

                                  Point A – correct however my systems are all setup that way.

                                   

                                  Point B – that’s why I said the one you posted from FM Forums was more succinct.

                                   

                                   

                                   

                                  John Morina

                                   

                                  Pueblo Systems, Inc.

                                   

                                  CCQ-FM Inc.

                                  • 14. Re: Checking for Expiration with Month and Year
                                    RayCologon

                                    MattLeach wrote:

                                    That makes more sense now that i think about it. Either one will work (tested with reverse order)

                                     

                                    Thanks guys!

                                     

                                    Hi Matt,

                                     

                                    Yes, sorry for the confusion, and thanks to Michael and John for chiming in.

                                     

                                    I'm afraid I typed my reply in transit (not a good idea, obviously!) - and did indeed put the strings in the reverse order. What I *should* have written was:

                                     

                                    Let([

                                    d = Get(CurrentDate);

                                    x = "20" & Right(CC_Exp; 2) & Left(CC_Exp; 2);

                                    y = Year(d) & Right("0" & Month(d); 2)];

                                    x  <  y

                                    )

                                     

                                    ...which is functionally equivalent to the variation Michael posted. The intent was that the comparison strings be text, ie since the stored exp string is text, the calc brings both it and the current date into a text format for comparison.

                                     

                                    Again, apologies for the misdirection, and glad you've meanwhile arrived at a solution.

                                     

                                    Regards,

                                    Ray

                                    ------------------------------------------------

                                    R J Cologon, Ph.D.

                                    FileMaker Certified Developer

                                    Author, FileMaker Pro 10 Bible

                                    NightWing Enterprises, Melbourne, Australia

                                    http://www.nightwingenterprises.com

                                    ------------------------------------------------