CC Expiration date corrections

[PiedPiper]

Me again ... sorry to be so needy right now.

vs. 6, WinXP - I just looked at a field I need to clean up. New system will validate, but this is old DOS data. I am unsure whether to write several scripts and run individually or group them. And I am afraid of the mess I might make of it ...

Text field CCExpire contains these varieties of information:

MM YY (01 05)

M/D/YYYY (1/1/2005)

YYYY (2005) ... oh geez ...

MYY (105) ... good Lord ...

I assume I should leave this as a text field. In 7 I will use auto enter calculation and validation but I need to clean this up before migration. How can I script this? What calculation(s) will fix this mathematical text-function nightmare?

I thought of searching for one type first and fixing, etc. But even searching would produce possible bogus results and I would change the set incorrectly - multiplying the problem. What result do I want? Unsure, although something I could clearly identify as month year atleast. Whatever most credit card validations accept. Eventually in 7, this data will be uploaded to credit card service which accepts 01 05 so I assume I would want that type of result.

A consistent format I think is the most important - I can manipulate it if need be for upload (or produce a message if it has expired) or change its format ... but I need something other than what I have. Three fields? Month/Day/Year - and they only fill in what they want? Then I'll know if the card has expired and can produce a message?

I would sure appreciate the help with it.

Pete

[Vaughan]

First, make a backup of the file and play around with the backup.

I tend to play around with calculated fields a lot with data massaging, because it leaves the original field untouched. Once the calculation is working to my satisfaction I use it in either a scripted Set Field or Replace calculation.

If you only have data entered in 3 different formats, I reckon you've got it easy!

Going from your list above: type 1 is easy to parse, just look for the space character in the string.

Type 1 is easy too: look for the forward slash characters.

Type 3 and 4 are going to be hardest to parse. I'd start by adding a leading zero to all data that is only 3 characters long, then consider them as numbers: any number bigger than 1300 is type 3, everything else is type 4.

It's all too easy... I just hope that the users haven't made infinite variations of the four types (typos etc) because they will make the process much harder.

[PiedPiper]

That is all the variations except some of the fields are blank. I did a Control-I to view all the types of entries. That shows them all, right? The last one threw me bigtime - it displayed 103 - took a minute to figure it out.

So I will use calculations first to get the results I wish. Then loop and set a second identical field as a test. So would you store this information in three fields or one ... and what do you suggest as the final format? Number or text? "It's all too easy." ?? Maybe for you!! But I'll learn from it. Thank you, Vaughan!

[ESpringer]

Your final result should surely be just one date field! Presumably you'll treat year-only dates as the first of January on that year, right?

The great things about date fields are: (1) that display is super-flexible (show only year, or spell out month with two-digit year, or whatever you like), and sorts work correctly.

[PiedPiper]

I considered that. What do I put for day if there is none? Most credit cards do not even list the day; but some do. I was hoping someone also knew the correct combinations accepted. I believe there is one credit card that I've seen that requires the day also but I'm unsure on that.

And if I put in a bogus day just to appease FileMaker dates, I was uncertain on the validation piece. Just not crazy about entering something I'm unsure of. It was certainly the most logical ... and flexible and would make consistent, for those requiring a day also. But I was thinking a number for month/day/year also because ... then the day could remain empty if that is what the card says and I could just validate month year because they all require that. And sorting on number lists right, doesn't it? Thanks for helping me.

[ESpringer]

I'm not *sure* what the credit card companies do, but there's certainly a *fact* of the matter about whether a non-renewed card stops working on the first of the month or the last. My guess would be that it's the last, or even the day after the last: it's more of a "Valid Through..." than an "expiration" date, when the day is missing. But don't take my word for it! Whatever it is, once you find it out, it's surely standard across all the cards that omit it.

By the way, in data entry, you may indeed want separate entry fields for Month, Year, Day (where day is often not specified). Then the real date field can auto-enter a calc based on these values.

You might also consider data entry with a set of global fields (delaying creation of new records to hold the values until user confirms, and not cluttering up each record with info about the numeric values of months and years specified), you should have a single date field set according to the separate numeric values in the globals. Use the Date calc function to generate a real date value from separate numerical entries for Month, Day, and Year.

[Vaughan]

I was talking this issue over a couple of days ago with another client. (In her case the date was for an archive of documents, where the full date of creation is not always known but they may know the year, or month and year it was made.)

She had suggested having a separate field each for day, month and year, and building the date up from the three fields, substituting a default value for day or month where necessary.

The more I think about her suggestion the more I like it. I cannot see any disadvantages.

It makes it easy for the data entry people, since they don't have to "make up" date information. Each entry field can be validated individually, and the calculation can trap for stupid entries like day and year entry but no month: a valid date only appears when the year field is entered, yet they can enter day and month information if they have it.

Why don't you implement the system and let us know how it works!

[PiedPiper]

I'll let you know! I'm more concerned struggling through the conversion piece but I've just about got it - at least in calcs. Now do I turn around and use the calculations in the script as my test (and just run Set Field to calculation), or do I put the calculations in the script using If/Else? The first way seems easier but I'm unsure.

[ESpringer]

You could also just quickly "freeze" your calc field data now, by changing the field type to Date. Then proceed from here on with this field rather than the previous one, which you'll eventually discard (after archiving)...

Or were you after a different question?

What you still need to figure out is what your data entry setup will look like, and how a script will convert the one, two, or three numeric values into one date field using the Date(Month;Day;Year) calculation, presumably (I now think) replacing all blanks with the *highest* possible value for that month/year...

[PiedPiper]

Actually, I used one Case to flag each field with 1, 2, 3, 4 or 5 (blank) depending on its contents. Vaughan is right - even I got it. This was probly wrong because I could have just put each flags calculation in the script itself, I think. But I have fields for month, day and year ready and I'm listing the calculations again. Too bad they won't transfer but I copy/paste well.

Besides, I'll be deleting this stuff once it's right.

[ESpringer]

On second thought, maybe you want to keep the date as a calc based on numeric entry fields for each record, because it might be useful to leave intact the data entry traces (that is, to see whether or not something had been left blank). In fact, no data entry user ever needs to see the date fields behind the scenes doing the work...

BTW, all my cards really do have printed on them not "EXP DATE" but "VALID THRU" -- which leaves little room for doubt about how to construe the date precisely.

[ESpringer]

What do you mean by "they won't transfer"??

[PiedPiper]

I just mean that I have to recreate the calc in script using set field, depending upon the flag. Flag just tells me HOW to convert it. The calc INSIDE the case resulting in the flag, is what I need in my script now.

Did that make as little sense to you as it did me?