Defalut font & add a "0"

[Mats]

How do I set a field so that no matter what I paste the font and settings will be default Helvetica, 10px?

And also I have a lot of telefonnumbers that are missing the first 0 in the area code. I need a simple calculation or some thing that "if first value don't have "0" insert "0".

Thanks for any help!

[Kent Searight]

Make the field an auto-enter calculation as follows:

TextSize ( TextFont ( formattedField ; "Helvetica" ) ; 10 )

Make sure you uncheck "Do not replace existing..." in the auto-enter dialog.

Your other request to add "0" to the telephone number uses an auto-enter calc that refers to its own field as well. Do a search on the forums for a phone-formatting calculation...there are a good number of examples to try.

Hope this helps!

[coconutt2000]

Generally, area code, phone number, and zip code fields should be text types. If you ever need to evaluate the contents as a number, the GetAsNumber(text) function will work. You can reference a field inside the GetAsNumber function.

(Number type fields will generally strip the leading 0's.)

[Ender]

Hi Kent,

Make the field an auto-enter calculation as follows:

TextSize ( TextFont ( formattedField ; "Helvetica" ) ; 10 )

Make sure you uncheck "Do not replace existing..." in the auto-enter dialog.

I haven't tested this lately, but my recollection was that these text formatting functions only change the font, size, and style, if each of those functions is explicitly used. IOW: I don't think your function as written would remove style formatting. Also, this explicit application of text styles actually changes the text in the field to have those attributes. This may be fine for the data-entry layout, but when it comes time to print, a different font or size may be desired, and the font settings embedded within the field data would override the layout's fonts.

I would suggest removing any text formatting instead, then you can use the font settings that are selected for each layout. To remove all text formatting: in FM8, you can use TextFormatRemove(field) as the auto-enter calc, or for FM7 compatibility, you can use Evaluate(Quote(field)) as the auto-enter calc.

[Kent Searight]

Hi Ender,

Yes, you're absolutely right about the style...I should have included that also.

To remove Text Formattng in FM 7 you use "Evaluate(Quote(field))"? I didn't know that. Good to know!

And good points about removing formatting vs. adding it, especially with regards to printing.

Thanks!

[Mats]

Evaluate(Quote(field)) is Working fine for me! Thanks

What I don't understand is the "GetAsNumber(text) function" and how to include it in the string I have now:

"HemTelefon = Evaluate(Quote(HemTelefon))"

[Mats]

I made a search for "phone-formatting calculation", and did not find any thing? The problem is that I am importing 6-700 recoreds from Excel sent to me by a Company. For some reason some of the phone numbers are missing the leading 0 before the number. So I want to add that after. (Recalculate)

As I also now after importing the records find out that I want to have a serial number for each record. I know how to create a serial number field, BUT not how to make it active / inserted in existing records?

[Kent Searight]

Try:

If ( Left (phoneField; 1) = 0; phoneField ; 0 & phoneField)

as your calculation if you're certain that the missing "0" is the only issue to fix in the field.

Edited April 18, 2006 by Guest

[-Queue-]

Right( "0" & phoneField; x ), where x is the number of digits for the field, should work also.

[Mats]

Thanks -Queue-,

but the number of digits varies from different numbers.

Kent_S,

Will this work on already inserted numbers as well?

(Still looking for the serial number solution)

Thanks guys! :D

[Kent Searight]

Will this work on already inserted numbers as well?

No, not as an auto-enter calc. If you want to convert existing data, you can write the calc in a separate field and then have a looping script go through your records and set the phone field to the corrected value in the calc field.

[LaRetta]

(Still looking for the serial number solution). I know how to create a serial number field, BUT not how to make it active / inserted in existing records?

So you've created your serial field and set the Auto-Enter to serial increment by 1? Place that field on a layout (form is fastest) and Show All Records. Put your cursor in the serial field and ... stop ... back up first (smile) ... then:

Select Records > Replace Field Contents (be sure cursor is in the serial field). Select the middle radio and set it to increment by 1 starting at 1 (or 00001 or whatever). Then be SURE to click the box 'Update Serial Number in Auto-Entry.' Wham! Done! And set to continue serializing your import where the last record leaves off.

NOTE: Do NOT be in multi-user when you run this.

LaRetta :wink2:

Edited April 18, 2006 by Guest
Added NOTE

[Kent Searight]

...but the number of digits varies from different numbers.

That's why I suggested a phone formatting calculation. It will format your phone numbers uniformly, i.e. they'll all be the same length, hyphens or other characters will always appear in the same place, etc.

Here's a thread I found for you when I did a search on the forums for "phone".

[Mats]

Kent_S>>

I tried the following calculation:

Evaluate(Quote(ArbetsTelefon))

Right( "0" & ArbetsTelefon; 11 )

But I get error on the "Right( "0" & ArbetsTelefon; 11 )"

Did I miss something?

_______________________________________________________________

LaRetta>> Wham bam, working fine. Thanks :D

Edited April 18, 2006 by Guest

[Kent Searight]

I tried the following calculation:

Evaluate(Quote(ArbetsTelefon))

Right( "0" & ArbetsTelefon; 11 )

But I get error on the "Right( "0" & ArbetsTelefon; 11 )"

Did I miss something?

Try this:

Evaluate(Quote( Right( "0" & ArbetsTelefon; 11 )))

[Mats]

Thanks,

Just to make sure. This will add a "0" only if the telephone number don't start with a "0"? i.e. "18-123245" will be "018-123245".

[Søren Dyhr]

No as the calc is in Kent_S latests will it just put an extra 0 on regardless..

Well I'll like to give it a stab as well:

Evaluate(Quote(

Left(Replace ( "0" & GetAsNumber ( Filter(ArbetsTelefon;"0123456789")) ; 4 ; 0 ; "-" );12)))

Let's RPN it...

1) Filters off anything not numbers.

2) Make it a number, which results in stripping off the leading 0 if it's there.

3) Puts a new leading 0 on.

4) Puts in a - in 4th position of the string.

5) Makes sure the length including the - not exceeds 12 chars.

6+7) Uses Enders trick to make deafault format.

--sd

[Mats]

Hi Søren,

And thanks. Now I'm almost there

I just dont want the "-" to be there as the area code can be 1-3 digits long. And the 900 telephone numbers I have in the database can look very different. Here how they can look:

08-22334455

822334455

08 22334455

With your calculation the number will be "082-2334455" which is wrong.

So the only solution is to make the calcolation remove any separators and make the number in obe go.

I tried to take away the "-" part my self but I can't get the calculation right.

[Søren Dyhr]

Just get rid of the Replace part of it - then!

Evaluate(Quote(

Left("0" & GetAsNumber ( Filter(ArbetsTelefon;"0123456789")) ;11)))

--sd

Edited April 20, 2006 by Guest

[sbg2]

Let([

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

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

Step3 = TextStyleRemove (Step2 ; AllStyles );

Step4 = TextFont(Step3; "Helvetica");

Step5 = TextSize (Step4; 10)

];

Step5)

[comment]

I believe GetAsNumber() strips text formatting AND filters out all non-numeric chracters (well, except decimal separator).

[Søren Dyhr]

You're right!

--sd

[Mats]

Ok and if I don't want to take away "-" if it's the. What would I change GetAsNumber() to?

[Søren Dyhr]

No it's te other way round, GetAsNumber( removes the dash, so you would need to get it back again via the Replace( as I did it, what Comment means is that the use of Filter( is overkill + he claims that it zaps the format as well:

Left(Replace ( "0" & GetAsNumber (ArbetsTelefon ; 4 ; 0 ; "-" );12)))

--sd

[Mats]

You forgot:

"Evaluate(Quote("

to be:

Evaluate(Quote(

Left(Replace ( "0" & GetAsNumber ( FilterLeft(Replace ( "0" & GetAsNumber (ArbetsTelefon ; 4 ; 0 ; "-" );12)))

which is not working anyway. I'm getting more confusing now

a correct telephone number would be 08-334455 or 0472-22334

If the number misses the "0" and is 8223344 (the problem the company have is that when they export a number without the "-" the "0" goes away.

The I just need a "0" infront and nothing else as I use an automatic dialer.

There is no way to insert the "-" as there can be a 2 to 4 digit area code.

I want to thank you guys for putting up with me :confused:

[Søren Dyhr]

You forgot:

"Evaluate(Quote("

No I didn't it was the point in Comment's comment. All you need to use is this:

Left(Replace ( "0" & GetAsNumber (ArbetsTelefon ; 4 ; 0 ; "-" );12)))

...forget about Evaluate(Quote(

a correct telephone number would be 08-334455 or 0472-22334

Do you use both, what is the rule for applying each of the two notation forms??

--sd

[Mats]

I have solved it and it's working fine now. Super thanks guys!

I will be back soon with another question I guess :D