Separating text from numbers in a field

[shellas]

I have a field that holds the retail price of products. Unfortunately the database was poorly designed. The price as well as the unit of measure got entered into the same field, for example, "3.95 EA." Now, I have something like 18,000 records that I need to separate the price from the UM. Is there a script that can be written to automatically separate the number (3.95) from the UM (EA)?

[swf]

try calculated fields instead.

old_price=3.95 EA

c_new_price=leftwords(old_price;1)

c_new_units=rightwords(old_price;1)

[shellas]

Thanks, swf. I don't know what I have been thinking, or not thinking! for that matter. I have used this exact same method in other calculations, and yet I haven't thought of that. I guess my skills are not that "intermediate" after all.

Next thing, some of these retail price fields (btw, they are set as text fields) have more than one "set" of data in them, meaning the field entries look something like below:

3.95 EA

9.50 PK

In that case, the above solution will not work properly, as the result would be "3.95" & "PK". Can anyone help me out with this one?

[swf]

If the bottom number is bad data and can be discarded I have a solution.

c_new_units=case(wordcount(old_price)>2;leftwords(old_price;2);rightwords(old_price;1)

anyway something along those lines.

Now if the botom number is data that you need. Then I would probably try to flag that record, find a set with flag and write a script to copy out left and right into new records. If successful then you could delete the flagged records.

eg:

flag=case(wordcount(old_price)>2;*,"")

perform find of flag=*

now set up a loop script

set up two variables left and right

$left=leftwords(old_price;2) $right=rightwords(old_price;2)

then create new record and set old_price=left

then create new record and set old_price=right

I would have to tinker with it but that is the basic idea.

[dwins]

The problem with the calc fields is that they depend on the contined use of the original problem field. Why don't you make separate text or number fields for the data in that price field that you want to keep, then set up a script that loops through all the records and puts the data where it belongs.

Based on your sample field content:

3:95 EA

9.50 PK

These formulas extract each word:

LeftWords(LeftValues(pricefield;1);1)

RightWords(LeftValues(pricefield;1);1)

LeftWords(MiddleValues(pricefield;2;1);1)

RightWords(MiddleValues(pricefield;2;1);1)

David

Edited November 9, 2005 by Guest

[LaRetta]

Whoa shellas. Are you getting into 1:n stuff? Have you thought about HOW you're going to fix this - other than split the field? Like WHERE these pieces will go? I'm sure you have. Do you have FIELDS called RetailPakPrice and RetailEaPrice? Or TWO UM fields? Are your WholeSale and Retail Prices fields also? What do you do with Special Pricing?

How you split this text-mess will depend upon what you have (and want). I agree ... clean it up now and keep it from entering your normalized data ever again. You may just as soon want this as related records. I think it would help to understand what you're going to do with the data before we suggest how you get it there.

LaRetta

[shellas]

You've struck the nail on the head, LaRetta. I've been trying to come up with a solution from a looonng time ago, but just couldn't figure out how I'm going to "disintegrate" the pricing levels for certain items. I've had to create a different file for wholesale prices and do calculations to split the prices from the UM's. Moreover, I've had to set those wholesale prices field as repeating fields because for some items different quantities purchased mean different prices. I have wholesale price and wholesale UM fields. One of the more complicated entries look something like this (all wholesale price information in one text field and all retail price information in another text field):

Description:

Candles by Pack

Wholesale Price:

Credit

26.00 dozen

78.00 case (40 pks per case)

720.00 10 cases

Cash:

75.00 case

700.00 10 cases

1575.00 25 cases

Retail price:

2.75 pk

2.25 pk taking 2 pks

For the wholesale part, I've created another file that breaks down the pricing structure as below:

(Layout Split in 2 sides - credit & cash)

Under credit side in the following order as 3 repeating fields:

Unit Price, Readable Price (readable so we can see the amount that we usually refer to)& Qty Range

2.1667 26.00 12

1.9500 78.00 40

1.8000 720.00 400

Similar calculations are done for the cash side as well. And by the way, I've had to do all this manually. These are necessary as wholesale orders are entered in another file called the invoice file, where I've created a layout to choose the item by words or item numbers, and then selecting the one desired, at which time the appropriate price is pulled up due to the quantity chosen.

For the retail part, I've created a layout within the same all-in-one file that I originally have, extracting and separating the price value from the retail UM value. The only thing is that I've written a script for it and was running the script everytime we add new entries to the database, which sometimes can be as much as 500 items. (The solution swf had suggested was readily available but somehow I didn't even consider that method.) In the retail price layout, I have a retprice field that holds the number part of the price, and a ret UM field that holds the UM part of the price. The information in this layout is called upon from another file called the "Retail Price Menu", where when the barcode is scanned in, the information is retrieved from the original price file, and the price is set in the price field. I can then choose how many the customer wants, override the price if so desired, and give a discount in either percent or dollar amount.

With this method, however, only the first retail price would be pulled up. I would like to get some ideas on how to pull up the other parts as well. I also have the problem of not being able to identify each item by a unique number or upc, as not all items have item numbers or upc's. And the people in charge here are not interested in putting a unique identifier on everything. Instead, I was asked to create solution where, if more than one item have the exact same item number (mine you, it's the 4 or 5 digit item number and not the upc), all items with that number would be shown, and the user can choose the item desired, and the correct pricing structure would be pulled up. I still haven't been able to get this done yet. The solution I've created can only pull up the first available item that matches the number, but not the second or third one. Likewise, if the item does not have an item number or upc, it will need to be looked up according to keywords, and chosen from the resulting data shown.

The project and the solution are complicated. I hope I haven't confused anyone with what I'm trying to get across.

[LaRetta]

Someone else will need to make good suggestions here. I've changed my pricing structure twice and I'm still unhappy with it. We need FLEXIBILITY! We have (buy two get one free) and all kinds of special pricings. We have 'buy this product and get these three other products (package sales) for half price.' Between pricing and inventory and piece vs. case pricing (as well as wholesale & retail), it makes me feel nuts. Some of it is lookup; some scripted. No real consistency to process.

I would prefer Owner (from Browse) be able to group & specify this stuff. But I simply get twisted in it. I will read and learn suggestions made here just as eagerly as you!!

LaRetta

[sbg2]

I would prefer Owner (from Browse) be able to group & specify this stuff. But I simply get twisted in it. I will read and learn suggestions made here just as eagerly as you!!

LaRetta

I'm not sure the attached file is the type of structure you are after but I gathered what I could from the OP's description. Its a little akward as you our ordering QTY's of different pack types but thats the way I thought the OP wanted it.

I had intended it for the OP but unfortunately I do not have access to FMP version 6, maybe you will find it useful or someone more knowledgable could revise it to work better.

Order.zip

[shellas]

Thank you swf, dwins, and LaRetta. Thank you sbg2 for showing us your method. I have a trial version of FMP8 so I was able to open up your file. However, I have not had a chance to actually analyze it. We had an accident at work that has 3 of our most valuable workers on sick leave, so I'm extremely busy filling in. But I want to let you all know that I appreciate your interest in helping.