Jump to content

HELP!!! Date conversion?


This topic is 7328 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Someone please help me! I've got a simple database with a date field. Unfortunately, the person who designed the database ahead of me originally created the date field as Text. I realized this error when I tried to do a search. When I went to convert the field to a Date field, everything converted to 1900s (i.e.B) if the date entered in the Text field was 9/1/03, it now comes up as 9/1/1903 instead of 9/1/2003). What can I do to "mass" fix this. The DB has about 1000 records with an average of seven dates fields per record!!!!! HELP!!!!!!!! confused.gif

Link to comment
Share on other sites

Peg:

I just helped someone with a similar problem. Look under the ScriptMaker forum; there's a thread called "Date Calculation" which has instructions on how to script a quick solution.

-Stanley

Link to comment
Share on other sites

Stanley,

I tried the script that you described in the Script Maker Forum, as you suggested and it didn't work. I'm not very familiar with scripts, so I'm sure I'm missing something. It may be as simple as a comma or a quotation mark! I have dates that range anywhere from 1991 to 2004, so I tried the <1905 first, but it just spun and spun.

There are 1000 records with an average of five or six dates per record so, then I tried deleting all but one record which has several dates which need to be converted (in a copied database, of course). It still spun and spun. So I changed the "bad date" to just 1900, knowing that my one record had a 1900 date. Still didn't work. Didn't spin and spin, but nothing converted.

I failed to mention before that my DATE field is a repeating field. Does this matter?

Here is exactly what is showing in my dialog box (my field name is DATE)B)

Show All Records

Go to Record/Request/Page [First]

Loop

If["Year(DATE)=1900"]

Insert Calculated Result [select,"DATE","DATE+36525"]

End If

Go to Record/Request/Page [Exit after last, Next]

End Loop

Link to comment
Share on other sites

Peg:

That looks fine (in fact, exactly how I have it in the test I did for Dave the other day) so I wonder if the repeating field doesn't have something to do with it. Repeating fields are notoriously difficult, a legacy from the old days that most of us avoid at all costs.

I'll do an experiment and let you know what I come up with.

-Stanley

Link to comment
Share on other sites

Peg:

I just tested that out with repeating fields, and it still works (but only for the first repetition of the field). So, it's probably something else. You may want to make sure you're in a layout where the date is editable (like the layout where you enter the date) at the start of the script. Also, add an "Enter Browse Mode" step at the beginning of the script.

Let us know if it works.

-Stanley

Link to comment
Share on other sites

Thanks Stanley ...

If you highlight the "Insert Calculated Result" line, the box down below gives some options.

On the left is a "Select entire contents" check box, which is checked.

On the right are two boxes. The first one is "Specify Field" and it is checked. If you click on that box, the listing of all my possible fields appears. Below the listing is "Repetition" and a box with "1" in it. This can, obviously, be changed. I was guessing that if you leave it at "1", it would only run the script on the first of the 40 repetitions. If you change it to 7 it would run the script on the first 7 of the 40 repetitions. I experimented with this, but there is still something wrong.

Thanks for your time. Please let me know what you find out!!!!!

Link to comment
Share on other sites

Peg:

I thought I saw something unusual out of the corner of my eye when I was doing my experiment. Yes, you're right, it asks for the repetition number. Unfortunately, that's the particular repetition (i.e., number 7 of 40, not 1 through 7 of forty) which means that, if you've got 40 repetitions (please tell me you don't have 40 repetitions) then we're in for some work.

Try this. Set it to "1" and put a date with a "1900" date in it (like you tried before) in the first repetition in a repeating field. It should change it. to a "2000" date.

If that works, and you want it to work on all 40 repetitions, then you're going to have to put 40 different loops in a row, each with an "If" statement... let me know if this is the case, and I'll lay it out for you.

-Stanley

Link to comment
Share on other sites

Stanley,

I ran through it again, adding the Enter Browse Mode step as you suggested, just in case. It still doesn't work.

I double-checked the repetition thing-y ... in the one record I am using as an experiment, the 2nd repetition is showing as 1900, rather than 2000, so I set the repitiion to 1 and the DATE in the script to 1900. It still doesn't work.

Correct me if I'm wrong ... I don't have to have anything highlighted or selected or anything like that, right? I just have the database open and let it rip? If I had 100 records, it should go through all 100 and convert all 100 that need to be converted?

Also, I do have 40 repititions, but no record is actually using all 40; I think the most is about 25 or so.

Would it be possible for me to e-mail you my one-record-experiment for you to look at? I'm betting I'm missing something so obvious it's not even occuring to you. I'd really appreciate it if you have the time.

Let me know.

Link to comment
Share on other sites

Peg:

You can post your experiment up to the forums as an attachment, and I'll have a look at it.

You said that the 2nd repetition was showing 1900 - in which case you should set the repetition number in the script to "2" rather than "1"... and, yes, you just let the thing rip - that script would go through 100 records in a second, tops.

-Stanley

Link to comment
Share on other sites

Peg:

Compress your attachment using Stuffit or WinZip or whatever compression tool you've got, then post it up...

-Stanley

Link to comment
Share on other sites

Let me but in for a sec.

Peg, I suggest, if it's practical, that you put your converted dates into a separate field, called DATENEW rather than write over your exsting data. That way you do not lose your original data when things go wrong.

I would actually set up another field, called DATE2, and copy DATE to DATE2. I'd then write a little script to roll all the dates in DATE2 up one, so that Repetition 1 gets lost, and Repetition 2 becomes Repetition-1, Rep-3 becomes Rep-2, and so on.

In your main script you then do your work on Repetition-1 only.

In 'plain' (?) English -

loop

exit loop if Rep-1 in DATE2 is empty

Get Rep-1 from DATE2

Fix it

Put it into DATENEW

Perform subscript (not described above) to roll DATENEW down.

Perform subscript Roller to roll the dates up in DATE2

end loop

Of course you need another loop round that one to go from record to record.

This will be much slower that Stanley's but much easier to code and test.

Link to comment
Share on other sites

PAUL,

Thanks for your suggestions. I am working with a dummy-copy of my database right now, so I can mess up all I want right now, without fear of damaging my real database. I think I understand what you are suggesting about the DATE2 field and the rolling, but I'm not sure. I'll get back with you once I get the conversion script working correctly.

STANLEY,

Thanks so much for your help. I will send you my dummy, one record copy right away.

Link to comment
Share on other sites

Here's something else to try....

This script looks at the date that was imported and checks to see if the date is prior to 1991. If so, it goes to the text field, replaces the "19" (from the century) with "20", and then re-converts the text field to a date field with the new numbers. Be careful, though, since this will corrupt your original text fields...

I'm attaching a .pdf file to show the script steps, since I don't have .zip software. Hope this helps.... smile.gif

Paul

Date conversion mechanics.pdf

Link to comment
Share on other sites

Peg:

I got your file - here's the problem. The script works fine as far as the first repetition goes, but the other repetitions are going to be a different story. The reason for this is that the IF statement is checking the first repetition. If you make the first repetition a "1900" and make the Set Field statement go to the first repetition, you'll see that it works. However, the IF can only check the first repetition, so far as I know.

The problem here is that you're using repeating fields - I see why, and it's fine for you to use them, but you're encountering the reason we all tend to avoid them... they're so awkward to deal with when automating.

There is a GetRepetition script command, but I'm not entirely sure what it does. Again, I'll do an experiment, but it may take a while, as the Boston Bruins are playing, and it may have to wait until the intermission.

-Stanley

Link to comment
Share on other sites

Stanley,

While I'm not a hockey fan, I totally understand where you are coming from. If it were the St. Louis Rams, I'd be doing the same thing.

Earlier on in this saga, before I realized that my "dates" were actually "text", I thought that taking the data from the repeating DATE field and putting it into one non-repeating field might help. I got help from someone else in the "Finding and Searching" Forum with the GetRepetition script. Below are his notes:

What you could do is to transfer these 5 repetitions in 2 new fields

cFirst5Dates =

GetRepetition(Date,1)&"

Link to comment
Share on other sites

Peg:

I emailed your database back to you, problem solved.

The "If" loop should look like, this repeated for all the repetitions:

If["Year(GetRepetition(DATE,1))<"1906" and GetRepetition(DATE,1)?""

Insert Calculated Result [select "DATE"-1, "DATE+36525"]

End If

Repeat the IF for all the repetitions (incrementing "1" and "-1" each time, to the max number of repetitions...)

-Stanley

Link to comment
Share on other sites

Ugo:

Peg sent me the file in question. The DATE field is, in fact, a date field, not a text field; forty repetitions.

As usual, you've got a more elegant script - mine is just brute force.

-Stanley

Link to comment
Share on other sites

Well, brute force or not, it WORKED. YEAH!!!!!!!!! And I understood Stanley's scripting, so I guess it's all good. THANKS SO MUCH FOR EVERYONE'S HELP!!! I only had to repeat 20 times. Even though I have the potential for 40 repetitions, no individual record has more than 20 repetitions.

Link to comment
Share on other sites

Peg:

That's why I used that (brute force) method - so you'd be able to understand what was happening, and that way gain a bit more insight into how FileMaker works.

Glad you're all set.

-Stanley

Link to comment
Share on other sites

This topic is 7328 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.