Data conversion

[LaRetta]

I have a set of data to convert. It originated from an old DOS-based accounting program and is currently in a text field. I have new number field ready to set, if I can create a calc. They are minus numbers (but displayed as: 1236.21- or 85- (which should be 85.00)

From using RussCalcs from OZ, I've figured out how to convert the other set of data (JUN 03 02) by using D_DateTextConverted! Thanks so much, but I can't figure out how to move the minus sign to the beginning and make them all - ###.## formats. Any help would be appreciated!

[SteveB]

You can remove the '-' using Substitute(field,"-",""). Then convert the remaining text to a number: TextToNum(field) and multiply by -1.

[CobaltSky]

Since the data is text, I suggest you put it in a text field (at least initially) and run a script through it with a loop and a couple of text functions, along the lines of:

Loop

Set Field ["YourTextField", " Case("Right(YourTextField, 1) = "-", "-" & Left(YourTextField, Length(YourTextField) - 1), YourTextField) "]

Set Field ["YourTextField", " Case(not PatternCount(YourTextField, "."), YourTextField & ".00", Left(YourTextField & "00", Position(YourTextField, ".", 1, 1) + 2) "]

EndLoop

After you run the script above and check it has had the desired effect throughout the whole data set, you can re-define the field as a number field if you want (then use the number format 'display as entered').

[LaRetta]

I applied Cobalt Sky's suggestion as it also took into account converting to dollars. I had a bit of trouble with the 'loop' portion. I don't understand how that works through a set of records. So, I finally changed it to Set & Set, then go to next record (exit if last) and perform script again (when I was on first record). That worked perfectly through the entire set (the minus is in front on negative numbers, and they are 2-digit decimal). I'll add understanding loops to my very (VERY) long 'need-to-learn' list! Thanks Cobalt Sky!

Steve, I loved your example of using Substitute to 'strip' the minus off the end. I will now be using that to strip text off the end of many data fields that I'm importing. And, using * -1 is brilliant! Thanks much. You've both taught me another piece of this FM puzzle.

[CobaltSky]

Hi LaRetta,

My oversight, I'm afraid - I forgot to include the Go to Record/Request/Page [Exit after last, Next] step. Must have been late - sorry!

Glad you were able to figure it out and get something to work despite my omission. What you came up with was a slightly different way of achieving the same thing.

The way it goes is that if you use the Go to Record/Request/Page [Exit after last, Next] inside a loop, it exits the loop after the last record is reached. If you use it outside a loop, it exits the script instead.

What I should have written but didn't, was:

Show all Records

Go to Record/Request/Page [First]

Loop

Set Field ["YourTextField", " Case("Right(YourTextField, 1) = "-", "-" & Left(YourTextField, Length(YourTextField) - 1), YourTextField) "]

Set Field ["YourTextField", " Case(not PatternCount(YourTextField, "."), YourTextField & ".00", Left(YourTextField & "00", Position(YourTextField, ".", 1, 1) + 2) "]

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

EndLoop

That is pretty typical of the kind of thing loops are good for, so if the logic of the above seems clear, you should now be able to take them off your 'need to learn' list...

[LaRetta]

Ahhh, so if I use a script step within a loop, I can continue the script (or then run another one) after I sequence through the set, instead of exiting!?! I've been reading posts in which even some advanced FM'ers are confused on sequencing and loops within scripts. I'm glad you explained this 'easier' example. It helps me get the jist, but I'm sure I'll be re-reading all those posts as my scripts become more complex ... I'll maybe even have to post for help again about it. But, I'll try to do my homework first -- it's just that, sometimes I run out of time trying to figure things out, before 'the powers that be' want solutions! Thank God you're all here!

[CobaltSky]

Ahhh, so if I use a script step within a loop, I can continue the script (or then run another one) after I sequence through the set, instead of exiting!"!

Yes, that's about the size of it - loops provide a means to accomplish a repetative task in a controlled way, repeating any sequence of events until a desired outcome is achieved. But of course, they are not confined to looping through records.

For instance, to locate a layout (if there is one in the current file) which has a field on it with a particular name, one might construct something along the lines of:

Set Field ["gLayoutNumber", "1"]

Go to Layout ["gLayoutNumber"]

Loop

Exit Loop If [PatternCount(FieldNames(Status(CurrentFileName), Status(CurrentLayoutName)), gLocateField) or Status(CurrentLayoutNumber) = PatternCount(LayoutNames(Status(CurrentFileName)), "

[CobaltSky]

Please read all the question marks in my previous post as quote marks (for some mysterious reason, the forum did not permit me to correct the problem by editing the post in the usual way...). :

[LaRetta]

I had designed some layouts with fields to play with this, but I hadn't gotten to the point of writing the scrips. When I looked at it orignally, I thought "well, here's something else I know nothing about (?'s)." The script didn't make sense, but very few do at first.

Then I read your post to the FM guys about the web problem, re-read the script and understood it! Thanks for all the time you devote to helping us on this forum.