peg Posted March 4, 2004 Posted March 4, 2004 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. 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!!!!!
stanley Posted March 4, 2004 Posted March 4, 2004 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
peg Posted March 4, 2004 Author Posted March 4, 2004 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) 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
stanley Posted March 4, 2004 Posted March 4, 2004 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
stanley Posted March 4, 2004 Posted March 4, 2004 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
peg Posted March 4, 2004 Author Posted March 4, 2004 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!!
stanley Posted March 4, 2004 Posted March 4, 2004 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
peg Posted March 4, 2004 Author Posted March 4, 2004 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.
stanley Posted March 4, 2004 Posted March 4, 2004 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
peg Posted March 4, 2004 Author Posted March 4, 2004 OK, I tried to post my attachment, but it has a .fp5 extension and I got an error message when I tried to attach it. Recommendation?
stanley Posted March 4, 2004 Posted March 4, 2004 Peg: Compress your attachment using Stuffit or WinZip or whatever compression tool you've got, then post it up... -Stanley
peg Posted March 4, 2004 Author Posted March 4, 2004 I tried to send the attachment with a .fp5 extension and it said I couldn't do that? What should I do?
peg Posted March 4, 2004 Author Posted March 4, 2004 Sorry ... didn't mean to post that twice. I got distracted and forgot that I'd already sent it ... And I don't have any compression tools ... and it's only 104K ...
stanley Posted March 4, 2004 Posted March 4, 2004 Peg: You can try to email it to me directly at [email protected] -Stanley
Oldfogey Posted March 5, 2004 Posted March 5, 2004 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.
peg Posted March 5, 2004 Author Posted March 5, 2004 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.
Damocles Posted March 5, 2004 Posted March 5, 2004 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.... Paul Date conversion mechanics.pdf
stanley Posted March 5, 2004 Posted March 5, 2004 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
peg Posted March 5, 2004 Author Posted March 5, 2004 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)&"
peg Posted March 5, 2004 Author Posted March 5, 2004 Oh, also, Stanley ... did you see Paul's suggestion about the rolling of the dates? Did it make sense to you. Do you think it might help?
stanley Posted March 5, 2004 Posted March 5, 2004 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
stanley Posted March 5, 2004 Posted March 5, 2004 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
peg Posted March 5, 2004 Author Posted March 5, 2004 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.
stanley Posted March 5, 2004 Posted March 5, 2004 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
Recommended Posts
This topic is 7637 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 accountSign in
Already have an account? Sign in here.
Sign In Now