Parsing a field and then what...?

[wendykdt]

I know what I want to do... I think. I just don't know where to put it!

I have a field called Dimensions. It contains either 2 dimensions or 3, of varying size, e.g.

11 x 10.5625 x 0 or

0 x 0 x 0 or

17.125 x 12 or

1 x 3 etc.

I'm sure there are many ways to parse this string. I have figured out I want to remove spaces (to get, for example: 11x10.5625x0), then substitute spaces for the X's (to get 11 10.5625 0), then use WordCount to get the number of "words", and then use Leftwords, Rightwords, and perhaps Middlewords to get the first, last and (if there is one) middle value, and place these 3 values, into three existing (currently empty) fields: Length, Width, and (if there is a middle value) Depth.

I have a set of 6,000 records. I know I can create a script to loop through the records. I also know I don't want to destroy the original string of data in the original Dimensions field. I just want to get the parsed numbers into the three separate fields. What I can't figure out... and this sounds really dumb... where and how do I DO all this calculating, including getting "If Wordcount = 2" and "If Wordcount = 3" statements in there somewhere? I tried creating a field called DimensionsCalc, but as I tried to first Substitute, then started thinking about using "If"... I ended up feeling like I was getting...circular.

Using FMPro8 Developer, served file using FMS 8.

[sbg2]

See if the attached gets you started.

user = admin, no password

Dims.zip

[wendykdt]

Thanks! This looks familiar! I used this technique for something else quite a while back. But I didn't think to use it here! One problem: The existing Length, Width and Depth fields may need to be edited manually by users in the future, so they cannot be calculation fields. Sorry I didn't mention that earlier.

[mr_vodka]

Make them auto-entered calculated results then. You can uncheck 'Do not replace..." if you want the field values to update after the main dimension field is updated.

[LaRetta]

… and place these 3 values, into three existing (currently empty) fields

Script is required originally.

Length, Width, and (if there is a middle value) Depth.

The solution posted always puts the second value (if there are only two) in the Depth and also leaves errant spaces. Wendy, I might use script as …

Show All Records

Replace Field Contents [ No dialog ; table::NewLength ; [color:green]LeftWords ( table::Dims ; 1 ) ]

Replace Field Contents [ No dialog ; table::NewDepth ; [color:green]Let ( list = Substitute ( table::Dims ; "x" ; ¶ ) ; Trim ( Case ( ValueCount ( list ) = 3 ; GetValue ( list ; 2 ) ; 0 ) ) ) ]

Replace Field Contents [ No dialog ; table::NewWidth ; [color:green]Let ( list = Substitute ( table::Dims ; "x" ; ¶ ) ; Trim ( Case ( ValueCount ( list ) = 3 ; GetValue ( list ; 3 ) ; GetValue ( list ; 2 ) ) ) ) ]

The new fields should be number. Just a different approach ...

LaRetta :wink2:

Edited March 6, 2007 by Guest
Added trim on Width (blush)

[sbg2]

The solution posted always puts the second value (if there are only two) in the Depth and also leaves errant spaces.

There are no errant spaces. GetAsNumber removes any spaces from the list.

[LaRetta]

Ah yes, sorry about that ... I was looking at the multiline.

But your process would still need to be changed to address the sometimes-missing Depth. There are many ways to approach it; mine was just the first to come to my mind.

L

[wendykdt]

I tried out the auto-enter calc idea; cool. I will keep that idea in mind. (I also didn't realize the flexibility of using just "MiddleValues" for all three dimensions; thanks sbg2). I switched the calcs around so if there are only two dimensions, they land in the Length and Width fields, and Depth is empty. Neat.

I think I will investigate the script also because actually what is happening is we are switching to importing data from a new corporate system. The new system will provide dimensions in 3 separate fields instead of a string like the previous sysytem. The new import sequence will enter data in the LWD fields, ignoring the Dimensions field.

The night before we all switch to the new corporate system, I want to get existing data out of the "string" dimension field into the LWD fields. From then on new data will be written directly into the LWD fields, which might be edited manually too, and the Dimensions field may no longer be valid. So it seems like a script that can be run the night before the switch, severing any "relationship" between the Dimensions field and the LWD fields, might be the way to go. (I haven't used "Let" yet, so it will be good experience for me to understand LaRetta's script.)

Sorry I didn't explain all that earlier! I was trying to keep it brief (and obviously I didn't succeed).

(PS I am also a big fan of David Kachel's White Paper for FM Novices!)

[wendykdt]

Question for LaRetta: I'm trying to, um, mentally parse your script so I understand it...! As I am fairly new at calculations I'm struggling...

Why does the case statement in both lines, as far as I can tell, test for a ValueCount of 3 dimensions? Shouldn't the first Case test against "=2", not "=3"?

And another clarification please: FM Help says that the Replace Field Contents script step "uses the value in a specified field in the current record {or uses a calculation} to replace the value in every record in the current found set..

"

Doesn't that seem to be saying that every record would end up with the same values in the 3 fields? There's obviously something I'm not getting.

Whatever "walkthrough" you'd care to provide would be appreciated! (Oh, and I AM using a COPY of our dbase for dinkin' around, so I won't ruin anything...)

[LaRetta]

Hi Wendy,

Replace Field Contents works via calculation when in a script (and you enter a calculation) … and calculations ALWAYS apply to records independently. So, as Replace Field Contents is running (whether via script or manually via the Replace Field Contents dialog box via calculation), it will look (and evaluate) each record individually. So if you Replace Field Contents with numberField * .06, it will apply .06 to each record's numberField independently.

ValueCount – If there are three values (meaning if there is a length, depth and width), then that counts as 3 values. If there is only length and width, it counts as 2 values. The depth calculation says, if there are three values, grab the second; otherwise produce ‘0’ and the width calculation says, if there are 3 values, grab the 3rd otherwise grab the 2nd.

I always test my suggestions before making them - even if I know the answers. Because, sometimes while testing, I will think of other GOTCHAS which you might need to know about. And sometimes *I* get gotcha'd as well. So testing is mandatory in my book. Good questions!!

LaRetta

[wendykdt]

OK, I'm starting to get it. But I think I need to make a change. The first value is always length. But I double-checked with corporate minds ( is that an oxymoron?), and we think this is how it goes: The second value is a width. There is a depth value only if there is a THIRD value. Some of our products, like plastic wrap, don't really have a "depth". (There is a MIL thickness, but that's a totally different field.) I don't THINK I have any records where there is just ONE value, but I suppose I should plan for that too.

My first posting was wrong on this. Apologies.

I also have some records where the Dimensions field is entirely blank because someone didn't know, or didn't fill in, the dimensions. I don't want to substitute zeros in that case, because that implies that a particular dimension IS actually zero, when in reality the Dimensions field was bypassed entirely when data was entered. Meaning that all the dimensions are missing, not zero.

So following your explanation, I'm going to take a stab at this:

Length: If the list is empty, produce ""; otherwise grab the first value.

Width: If the list is empty, produce ""; if there are at least ( >= ?) 2 values, grab the second; otherwise produce 0.

Depth: If the list is empty, produce ""; if there are 3 values, grab the third, otherwise produce 0.

If my logic sounds OK, I'll see if I can pull the actual syntax out of my skull, following your example, and I'll see what I actually get. (And from your explanation it sounds like there is no need for a loop in the script, right? Cool.)

[LaRetta]

Script:

Show All Records

Go to Record/Request/Page [ First ]

Loop

Set Field [ Length ; [color:green]Case ( not IsEmpty ( dimension ) ; Let ( list = Substitute ( dimension ; "x" ; ¶ ) ; Trim ( GetValue ( list ; 1 ) ) ) )

Set Field [ Width ; [color:green]Case ( not IsEmpty ( dimension ) ; Let ( list = Substitute ( dimension ; "x" ; ¶ ) ; Trim ( Case ( ValueCount ( list ) ≥ 2 ; GetValue ( list ; 2 ) ; 0 ) ) ) )

Set Field [ Depth ; [color:green]Case ( not IsEmpty ( dimension ) ; Let ( list = Substitute ( dimension ; "x" ; ¶ ) ; Trim ( Case ( ValueCount ( list ) = 3 ; GetValue ( list ; 3 ) ; 0 ) ) ) )

Go To Record/Request/Page [ Exit after Last ; Next ]

End Loop

... now, I wanted to keep all the formulae the same for simplicity (it was a bit of overkill on Length). And I considered using PatternCount() instead and probably should have ... but you had already familiarized yourself with this concept. You can use these calculations in Replace Field Contents (follow original example). What is the difference? A loop requires a few more script lines; a loop is a smidgeon slower; with a loop, you can add multi-user protections while Replace Field Contents can't be error-trapped. A loop will be a powerful part of your arsenal so I thought I'd introduce it now.

This appears to perfectly match your needs, Wendy. If you find out that your needs shift again, let us know. And yes, your logic is sound.

LaRetta

Edited March 7, 2007 by Guest
Reversed depth syntax for consistency

[wendykdt]

Ah! You did the work for me! (A lot more quickly, I'm sure.)

Ok, this makes sense. I've worked with a few loops already so I'm somewhat familiar with them. Since this is a one-time script that we'll run the night before GoLive, as opposed to a user script, it doesn't matter if it's a bit slower.

My problem, when I first posted, seems now to appear to be that I had a mental block of looking at the problem from the point of view of the existing Dimensions field, how to parse it, and then where (and how) to put the resulting calculation(s), instead of starting from the perspective of the 3 fields (LWD) and what I wanted in each one.

I wondered about PatternCount (count the X's?) when I first started working on this, but I'm glad you instead introduced me to several concepts and functions (Let, ValueCount and GetValue), and the Replace Field Content script step--speaking of adding tools to my arsenal. If I can find the time I might try it both ways (Replace Field Contents and the loop) just to prove to myself that I can do it...

Hmmm. My logic is sound? Can I use that quote in, um, domestic situations? ...!

[wendykdt]

Shame on me, I almost forgot: Thank you!

:thankyou: