Find and replace text/number within a field?

[Sticks1977]

We have a database that records the time that has been taken to typeset or create a job in our department. We previously used a decimal point system for entering time taken for any work that we had done.

For example - 15 minutes equated to .25 (1/4 of an hour) and 1 hour of work was entered as 1.00.

Recently we have switched over to a new system of charging for artwork and it is on a per minute basis and a cost has been assigned at the rate of $124 per hour.

I have been able to create a field which will automatically calculate the cash rate and enter it into another field using the time reference that is entered by the user (such as 15 minutes instead of .25 as previously done).

The problem I have now is all the old records that remain in the system with an entry of .20, .25, .30, etc. as the cost does not reflect the amount of time spent on the job - since we are now charging in per minute form.

Is there a script or calculation that I can implement that will find and replace text - such as .25 and convert this to a figure of 15 minutes.

There are numerous other fields and denominations that I will have to change to other fields but I figure once I know the calculation or script, I will be able to apply the same procedure to other areas of the database.

I am also unsure as to how limited I am as the copy of FileMaker Pro I have is version 5.5, I know that the Div and Let commands are not available for use in this version...

Regards, Shaun (sticks1977)

[Søren Dyhr]

Well something like a calc'field doing this:

Time(Int(Abs(oldValue));(Abs ( oldValue )-Int(Abs(oldValue)) *60);0)

--sd

[LaRetta]

I do not think a calculation is the way to go, Soren, since it would affect current records as well. And once the older records are corrected, the calc wouldn't be needed at all. At least this is my take on it; I could be wrong.

We previously used a decimal point system

Since it is number, the calc need not be as complex, I don't think. Anyway, I would handle it this way: Find all older records. I don't have 5.5 and I'm unsure even if Replace Field Contents[] existed then (I think maybe not) so to be safe, I would use a looping script:

... with your found set of older records

Go To Record/Request/Page [ first ]

Loop

Set Field [ [color:green]numField ; 60 * ( [color:green]numField / 100 ) * 100 ]

Go To Record/Request/Page [ next ; exit after last ]

End Loop

As always, back up before changing mass data. Soren, do you see any reason this wouldn't work?

LaRetta :wink2:

Edited May 6, 2008 by Guest
corrected green - I renamed the field from time to numField as it was more appropriate

[comment]

1. Replace Field Contents[] is available in older versions, but the name is either Replace Contents[] or just Replace[].

2. 60 * ( numField / 100 ) * 100 = 60 * numField

[LaRetta]

60 * ( numField / 100 ) * 100 = 60 * numField

Oh, of course! I was thinking I'd have to remove the decimal! :crazy2:

[Søren Dyhr]

Soren, do you see any reason this wouldn't work?

I would work provided the field at present is of type "number", then is the format changed into type of "time", before any of the scripting starts. Why does it work? Because the validation is done so the stuff already entered wouldn't beep.

--sd

[LaRetta]

Now see, Soren, you took an entire paragraph to say what Michael said in a few words. He IS concise, isn't he? The field must be a number instead of time because it is decimal. And it will be left as number even under the new entry theory.

UPDATE: You removed what you had said! No fair!! ;-)

Edited May 6, 2008 by Guest
Added sentence and UPDATE

[Søren Dyhr]

Then try and reverse the order of doing things, run your script first and then typecast the field, 12:50 gets you 740.. sorry 750 - which it remains to be!

--sd

Edited May 6, 2008 by Guest

[Lee Smith]

You can do this two different ways in v5. Use a Script, and use the replace Script Step, or isolate the records using a FIND, and then use the Replace in the Main Menu (Main Menu >> Records >> Replace), and select the by Calculation. Caution, neither of these are reversible, so you need to try this on a copy of the file.

Try:

numField * 100 / 60

HTH

Lee

Edited May 6, 2008 by Guest