Jump to content
Sign in to follow this  
habitude

Date/Text Change?

Recommended Posts

[color:blue]I have some records that are in the following format:

--- Wednesday February 23, 2000 17:11:43 By: CW P/M T/Y "CLOSING" LETTER TO CLIENT ; CERTIFIED AND REGULAR MAIL.

[color:blue]They need to be changed, so they appear in this format:

02/23/00 By: CW P/M T/Y "CLOSING" LETTER TO CLIENT ; CERTIFIED AND REGULAR MAIL.

The goal is to remove the 3 dashes, the weekday, and the h/m/s, and only show the date in 01/01/01 format.

I'm working in FM6. Anyone have any tips?

Thanks in advance.

Share this post


Link to post
Share on other sites

Sure. Process the string word-by-word.

Get rid of the three dashes first:

Trim( Right( textfield ; Length( textfield ) - 3 ) )

Then, according to your information, the first word is the day name, the second word is the day, the third word is the month name, and the fourth word is the year. If this isn't 100% consistent then you're screwed.

The first word you can ignor. The processing for the second word will require a Case statement to associate the month name with the month number.

Share this post


Link to post
Share on other sites

Thanks Vaughn.

The trim function worked fine.

My question about the CASE statement -

Converting the day name, day, month & year require a separate CASE statement?

Or is there a way to to say "[color:red]if this date is Tuesday April 2, 2002 13:46:02" - "[color:blue]change it to 04/02/02" and omit the day name & time?

Also, is this a calculation through a new field, or a script?

Share this post


Link to post
Share on other sites

You also posted this question on Cafe' and indicated that you were working in vs. 6. Please clarify because it makes a big difference in the best solution we can offer you.

Share this post


Link to post
Share on other sites

Sorry, I didn't see that in your original post.

I would suggest normalizing the records. This means that you have an ActivityDate, ActivityTime, CreatedBy, Description, and SendMethod. See the picture? In this way, you will never have to restructure the textlines again and you will be able to sort, search and even use the information in relational ways in the future as needed. If this original text line is imported (you haven’t said how it comes in), then you can use auto-enter fields to break out the data properly. You can keep the original 'text' line which is imported as a reference.

But it isn't necessary so here's a quick answer for you ...

Here is an example of breaking out the date (cMonth, cDay and cYear aren't needed but I wanted to make the calculation cDate clearer to understand only for this example file. Thank God for the Let() function in future versions. Then, when you want to display ANY format, you can do so using a combined merge field (in which the date portion can be formatted with 2-digit year) and the remaining cNewLine as per attached. I'm unsure if the remaining text always begins with "By: " so I would check that carefully.

So again, the only calcs you would need is cDate and cNewLine. But I've the feeling you will find yourself manipulating this data over and over and I strongly suggest normalizing it to begin with, importing each datapiece into its own proper field.

LaRetta :wink2:

dateTest2.zip

Share this post


Link to post
Share on other sites

Wow, thanks LaRetta, your example worked great! Only thing is the new date has to preceed the rest of the text (01/01/01 By: ).

Each of these represents a line item of a master record.

Share this post


Link to post
Share on other sites

Huh? Doesn't the red have the date before the rest of the text? Again, I'm unsure why you are horsing the data in such a way. Can you provide more information for me; maybe I'm a bit slow-minded today; it's probable. :wink2:

UPDATE: "each represents a new line of a master record"

Whoa. You are regrouping these records into one record on a master record? Bad boy. Please explain why you're twisting your data instead of leaving them as records and just displaying a portal. :confused:

Edited by Guest

Share this post


Link to post
Share on other sites

I'm sorry, I didn't mean to confuse you with the red. This is the desired outcome:

> Change this <

--- Wednesday February 23, 2000 17:11:43 By: CW P/M T/Y "CLOSING" LETTER TO CLIENT ; CERTIFIED AND REGULAR MAIL.

> To this <

02/23/00 By: CW P/M T/Y "CLOSING" LETTER TO CLIENT ; CERTIFIED AND REGULAR MAIL.

The latter is a complete paragraph in a single field.

(for what it's worth, this data was pulled from an old FoxPro app that became whacked when the year 2000 rolled over, thus changing the way the date was auto-entered into a record, this is a part of the normalization process task at hand)

Edited by Guest

Share this post


Link to post
Share on other sites

Then use only ONE calculation (result is text) of:

Int( 1 + Position( "JanFebMarAprMayJunJulAugSepOctNovDec" , Left( MiddleWords( text , 2 , 1 ) , 3 ) , 1 , 1 ) / 3 ) &

"/" &

MiddleWords( text , 3 , 1 ) &

"/" &

Right( MiddleWords( text , 4 , 1 ) , 2 ) &

" " &

Middle( text , Position ( text , "By: " , 1 , 1 ) , Length ( text ) )

Are we getting closer?

But again, normalization means that each piece resides in its own field. In future, you may find you'll need to 'normalize' it again further. Better to do it now than again later. The date should be separate! Each piece should be separate - that's what databases represent and why they are so powerful - breaking everything to finite!

Hmmmmm, I hear Soren's accent (whatever that may be) :smirk: But I'm quite serious that you'll end up working in this data again.

:wink2:

Edited by Guest

Share this post


Link to post
Share on other sites

It may be difficult to understand this without reference to a previous thread:

http://www.fmforums.com/forum/showtopic.php?tid/188646/

I have modified LaRetta's file very slightly to what I think is what you requested. But the idea to put all dates in a separate field (as true dates, not text) is well worth considering.

dateTest2.fp5.zip

Edited by Guest

Share this post


Link to post
Share on other sites

I believe that, by using Comment's calcs where he uses DateToText(), it will be safer than my method of placing the slash between month/day/year. Did you really need 2-digit year? Again, this is NOT proper methodology. I would use Comment's revised calculations if you can (instead of mine); I think they are safer for all OS date-settings. I dislike horsing dates into text - it is NOT good practice. And his method of pulling the remaining line will save an evaulation (or two) as well.

I again suggest doing it right and having at least the date in a separate field as true date.

Edited by Guest

Share this post


Link to post
Share on other sites

I agree 100%, and I did make the suggestion to my bosses that making the date proper and in its own field (at least for indexing purposes) was a better way to go... I was told to just do what I was told! LOL

Share this post


Link to post
Share on other sites

Comment, I wanted to say thanks to you too for helping me with this issue. I learned alot from it.

I have another question, which probably should be posted in the Scriptmaker forum, but here goes anyway...

Is is possible to write a script that would automatically make all the changes with a click of a button, or do I have to repeat the same process with each calculation field when this type of task comes up in the future?

Share this post


Link to post
Share on other sites

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
Sign in to follow this  

  • Who Viewed the Topic

    1 member has viewed this topic:
    siroos12 
×

Important Information

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