Reformat Phone number

[Mats]

I had some help before to format the phone numbers I put in the field. This is the code I got:

Let([

Step1 = If(Left(WorkPhone;1) = "0"; "Y"; "N");

Step2 = If(Step1 = "N"; "0" & WorkPhone; WorkPhone);

Step3 = TextStyleRemove (Step2 ; AllStyles );

Step4 = TextFont(Step3; "Helvetica");

Step5 = TextSize (Step4; 12)

];

Step5)

But now i also need a string in the code that takes away spaces from any number. i.e. 08-10 22 33 to 08-102233.

As I don't know how to do that I ask here for some help, and thank you in advance!

[David Jondreau]

Try adding this:

Step6 = Substitute(Step5; " "; "") and use Step6 as the result.

[Mats]

Sorry I missed you reply as I forgot to subscribe to the thread : But thanks!

Could you please help me to show me how the whole code would look like as I tried but only got errors. FM Coding is not my field :

Is it possible to strip spaces from phone numbers already inserted?

Edited October 27, 2008 by Guest

[Lee Smith]

I would use a Filter for this, that way you can add other characters if they become necessary.

I believe that DJ was suggesting for you to modify your code by adding his step in your Let Calc.

Filter ( (Let([

Step1 = If(Left(Text;1) = "0"; "Y"; "N");

Step2 = If(Step1 = "N"; "0" & Text; Text);

Step3 = TextStyleRemove (Step2 ; AllStyles );

Step4 = TextFont(Step3; "Helvetica");

Step5 = TextSize (Step4; 12)

];

Step5) ) ; "0123456789-" )

HTH

Lee

[Mats]

Super I got it to work after some studying. Thank you very much!

Now it's just the question if I can some how take away the spaces on the phone numbers I already have in old records?

Edited October 27, 2008 by Guest

[117Alan]

To remove spaces in old records-

First make a back up (File save copy as as….)

Using SciptMaker how about something like:

Go to Record/Request/Page[First]

Loop

Set Field[ Specify your telephone field] tick the box Calculated result and Specify the calculation

Filter ( your telephone field ;"0987654321" )

Go to Record/Request/Page [Next; Exit after last] there is a check box to do the exit after last bit

End Loop

Or try:

Go to Record/Request/Page[First]

Loop

Set Field[ Specify your telephone field]

Perform Find/ Replace [" ";"";Replace All]

Go to Record/Request/Page [your telephone field] tick the Specify By Calculation box and then insert your telephone field

End Loop

Alan

[Mats]

Thanks,

I just tried the second script, and it don't work. Very strange results.

first:

occurrences find 0

occurrences replaced 0

and the a window:

Go to record

?

I don't know how to copy the script and show you how I done it : I will try the first script later though.

[mr_vodka]

Show all records. Click into your field, then under the records menu--> Replace field Contents. Specify the following calc:

Substitute(YourPhoneField; " "; "")

[Mats]

Worked like a charm : Thanks vodka!

[mr_vodka]

Also, you can combine your steps 1 and 2 into one let variable. I know that you have each one seperated out so that its easier for you to follow, but the whole Y,N thing can really go away.

Let([

Step1 = Filter ( WorkPhone; "0123456789-" );

Step2 = Case ( Left(Step1;1); 0 & WorkPhone; WorkPhone );

Step3 = TextStyleRemove (Step2 ; AllStyles );

Step4 = TextFont(Step3; "Helvetica");

Step5 = TextSize (Step4; 12)

]; 



Step5



)

[Mats]

Thanks,

But your suggestion does not strip the spaces in the phone number?

[Lee Smith]

You need to post a copy of the file, so that we can see what is happening.

Lee

[Mats]

Well I don't think it's necessary to post the whole database just to see that vodkas suggestion do not strip the spaces from the phone number as the old script does. I.e. 212- 12 34 56 to 212-123456.

Please don't miss understand me as disrespectful because I truly appreciate the help and new friends I get here. But I think you ge my point.

If you really need it I will prepare one and attache it :

[Lee Smith]

You are missing my point. Providing a file with the data, can speed up the process of helping you. Often when we see the files, and the actural data, we can spot the problem.

You don't have to post the original file, either make a clone of it and remove any confidential information, or recreate the problem in a demo file, and attach that.

Lee

[Mats]

Got it.A sample is attached.

mediahelp_sample.fp7.zip

[Lee Smith]

without the phone data history, I am still guess at the solution.

New data pasted into the Work Phone number field, seems to do what you want so I'm guessing the problem lies with the old data. Mr Vodka suggested a Replace to remove the space, and you replied "Worked like a charm"

So, if this didn't work, you can also do this another way.

Change the Field from Text, to a Calculation field. Save the Change. Verify that the action fixed the problem, then go back in and change the field back to Text, and go back into Options, and recheck the box for the Auto Calculation.

Save the change, and all should be well.

If not, give us Feedback.

Lee

[Mats]

No. "Worked like a charm" was a respons på vodkas solution how to take away spaces on OLD records :

The he gave me the new code without "Y" and "N" and that one did not work.

[LaRetta]

The calculations you have on both the Cel and Mobile phones aren't even the ones Mr. Vodka gave you. Regardless, in the sample file you provided, all spaces are removed from both phone fields. I miss your point.

Mr. Vodka's calcs work just fine as well in your file - I just tried them - all spaces removed.

Edited November 1, 2008 by Guest
Added sentence

[Lee Smith]

I understand what you were saying now, as I didn't test his calculation, in fact I ignored it.

So forget about his calculation, and tell us what you are still needing.

Here is DJ's suggested modification of your calculation

 

Let([

Step1 = If(Left(WorkPhone;1) = "0"; "Y"; "N");

Step2 = If(Step1 = "N"; "0" & WorkPhone; WorkPhone);

Step3 = TextStyleRemove (Step2 ; AllStyles );

Step4 = TextFont(Step3; "Helvetica");

Step5 = TextSize (Step4; 12) ;

Step6 = Substitute(Step5; " "; "") 

];

Step6)

Here is my modification of your calculation

Filter ( (Let([

Step1 = If(Left(WorkPhone;1) = "0"; "Y"; "N");

Step2 = If(Step1 = "N"; "0" & WorkPhone; WorkPhone);

Step3 = TextStyleRemove (Step2 ; AllStyles );

Step4 = TextFont(Step3; "Helvetica");

Step5 = TextSize (Step4; 12)

];

Step5) )  ; "0123456789-"  )

You have the Replace calculation by Mr Vodka

Substitute(WorkPhone ; " "; "")

and my suggestion that will do the same thing by changing the field type.

What am I missing now that you are still needing help with?

Lee

[Mats]

Hey, please do not shoot me guys.

1. I was asking for a way to take away spaces when inserting phone numbers and Lee Smith gave me a solution.

2. Then I was asking for a way to deal with the old files who already had the spaces, and mr vodka gave me a perfect and simple solution.

3. My understanding after that was that mr vodka gave me another calculation formating the numbers then the first I have to make it simpler and as he said "but the whole Y,N thing can really go away."

I tried it and maybe me, but it did not work for me, so I keep the old one as it's working. So for me things is good and I thanks every body for their help and efforts :

[LaRetta]

Whoah up here a minute ...

No. "Worked like a charm" was in response to vodkas solution how to take away spaces on OLD records :

The he gave me the new code without "Y" and "N" and that one did not work.

This was your last post - and it indicated you still had the problem that spaces weren't being removed. You also said Mr. Vodka's calculation didn't work. I took the time to thoroughly test your file, both calculations (including the one Mr. Vodka provided) and clarified that they all worked as I thought you wanted them to.

And I'm shooting you because I responded? If you say someone's calculation doesn't work and I test it and it does, I plan to respond. Period. First to clarify I tested it and second to ask what we are still missing in what you need! How you react to my evaluation of the situation is not my problem but I certainly wasn't shooting.

Edited November 1, 2008 by Guest

[Lee Smith]

I was not taking shots at you, I was scrtching my head over what you were still needing help with.

Your last post was excellent. It really clarified what the problem was.

Lee

[Lee Smith]

Hi LaRetta,

Are you sure that John's calculation worked for you, I just dropped into a test file, and didn't work for me either.

Lee

[LaRetta]

Of course I am sure it works for me. Am I sure it works for you or Mats - no. I copy/pasted John's calc directly into a fresh unzipped copy of Mats file, replacing existing calculation in Cel Phone; saying OK, then OK, then OK to return to the layout.

I type (space) 344 (space) - (space) 12 (space) 22 into the Arbets Telefon field and exit. I end up with: 0344-1266.

I posted because I tested it at work on Windows box and then again here on home system and get the same results. I posted because I'm unsure if that is the result being asked for but it appears to be! I ask what I am missing and it is interpreted that I'm shooting! Inquiring minds want to know - that's all!

Thanks for asking, Lee. :wink2:

Edited November 1, 2008 by Guest

[LaRetta]

Actually, I had tried it on work phone. But cel phone is broken! Same calc works on one but not the other and they are identical! That's the problem!

[comment]

Maybe it has something to do with "Workphone" being referenced in Step2.

[LaRetta]

Precisely! Step 2 should reference Step 1 and then the spaces will be eliminated! Thank you, Michael!

[mr_vodka]

Been away a few days. My apologies for not checking the calc more thoroughly. As Michael already pointed out, I didnt properly modify it after the copy and paste. It should be.

Step2 = Case ( Left ( Step1;1 ); 0 & Step1; Step1 );

[Mats]

Thanks vodka,

now this one also works like a charm

Subject closed........