combining two functions

[dysong3]

I have a number of text fields where I use the following auto-enter calc to replace existing data to eliminate unwanted leading spaces.

 

 

 

Let (

 

// Remove up to multiple returns, tabs, and spaces

[

tempText = Substitute (nom; 

["¶¶"; "¶"]; ["¶¶"; "¶"]; ["¶¶"; "¶"]; ["¶¶"; "¶"]; ["¶¶"; "¶"];[", ¶"; "¶"];[" ¶"; "¶"];[",¶"; "¶"];["¶"; ""];

[" "; " "]; [" "; " "]; [" "; " "]; [" "; " "]; [" "; " "]; // tab characters

["  "; " "];["  "; " "];["  "; " "];["  "; " "];["  "; " "] );

 

// Remove return from beginning if there to prevent blanking out

tempText1 = If(Left (tempText; 1) ="¶"; Right(tempText; Length(tempText)-1) ; tempText)

];

 

// Remove all text formatting and make one line without leading spaces

Evaluate (

Quote ( 

Trim (

If(Position(tempText1;"¶";1;1)>0;

Left(tempText1; Position ( tempText1 ; "¶" ; 1 ; 1 )-1); tempText1)

))))

 

But one of the text fields needs to have another auto-enter calc to render addressing coherent and consistent.

 

 

Substitute(rue; 

["ch. "; "chemin "]

;["ch "; "chemin "]

;["Ch. "; "chemin "]

;["Ch "; "chemin "]

; ["av "; "avenue "]

; ["av. "; "avenue "]

; ["Av. "; "avenue "]

; ["Av "; "avenue "]

; ["ave. "; "avenue "]

; ["ave. "; "avenue "]

; ["Ave. "; "avenue "]

; ["Ave "; "avenue "]

;["pl. ";"place "]

;["Pl. ";"place "]

;["bd ";"boulevard "]

;["bd. ";"boulevard "]

;["Bd ";"boulevard "]

;["Bd. ";"boulevard "]

;["Bvd. ";"boulevard "]

;["Bvd ";"boulevard "]

;[" cp ";" — Case Postale "]

;[" CP ";" — Case Postale "]

;[" Cp ";" — Case Postale "]

;["CP ";"Case Postale "]

;["Cp ";"Case Postale "]

;["cp ";"Case Postale "]

;["c.p. ";"Case Postale "]

;["De ";"de "]

;["Du ";"du "]

;["Des ";"des "]

;["r ";"rue "]

;["rte ";"route "]

;["Rte ";"route "]

;["sent. ";"sentier "]

;["str. ";"strasse "]

;["str. ";"strasse "]

;["st. ";"Street "]

;["St. ";"Street "]

;["¶";" "]

;[" ";" "]

;[" L’";" l’"]

;[" L'";" l’"]

;[" D’";" d’"]

;[" D'";" d’"])

 

I am at a bit of a loss as to how to combine the two.  Could someone give me a pointer?

 

 

[eos]

Chain them by using Let () and passing each intermediate result to the next. For easier handling I'd pack them into CFs first, though - especially the second one (and the first could be done recursively …).

[dysong3]

eos, thanks for your input, but it's the construction of the combination that's got me beat.  I am an enthusiast but not a wiz and need a little more help and I don't really master the "Let function".  The first auto-enter calc comes from an example somewhere

[eos]

eos, thanks for your input, but it's the construction of the combination that's got me beat.  I am an enthusiast but not a wiz and need a little more help and I don't really master the "Let function".  The first auto-enter calc comes from an example somewhere

 

See if this helps you along on your road to wizziness. Not sure if it's exactly what you want.

SuccessiveReplace_eos.fmp12.zip

[dysong3]

Yes that works perfectly.  Thanks a million.  I just have one further question though.  I'm using these functions to auto enter calcs in a text field.  I would have thought that the calc result would automatically override the field's contents, but it only does that if I manually enter the field, make some change or other and then commit.  Isn't there a way to have this automatically commit without intervening?

[eos]

Those calculations can be a bit sluggish. Try Evaluate and define the fields that the calc depends on as trigger fields.

SuccessiveReplace_trigger_eos.fmp12.zip

[LaRetta]

Hi Dysong3,

 

I believe it can be much simpler.  A few observations first …

• Evaluate() is not needed here and is only necessary when you do not know the formula in advance.

• Your calc breaks on the carriage returns and runs your words together.

• Your calculation removes all carriage returns so your calc can be fine-tuned.

• Your tab characters didn't replicate when copy/pasting (?) but same would hold true for tab that it can be simpler:

Calc example ( result is text ) = TrimAll ( Substitute ( nom ; [ ¶ ; " " ] ; [ gTab ; " " ] ) ; 0 ; 0 )

 

However, when I have fields which shouldn't have carriage returns, I set them to Inspector > Data > Behavior > Go To Next Field ( check enter and return ). You can prohibit tabs in same way by checking tab there as well. It would certainly ease the burden on your table.  As for your substitution calc, there are some that will produce incorrect results. For example:

 

no such thing becomes no suchemin thing ( second substitute )

or becomes orue ( thirtieth substitute )

last. becomes laStreet ( thirty-sixth substitute )

If there isn't a space after St. it will not substitute so if you end the field with St. it will break

 

I would be inclined to handle the substitution portion as script trigger on the address field itself and NOT automatically replace the text but rather offer the User the ability to keep what they are entering or to override it with your suggestion. Otherwise there is no guarantee that some code or some word you haven't accounted for will not allow the User to put in the right text and it is very frustrating to the User. If you would like to consider alternative let me know and I'll see if I can put together an example.

 

ADDED:  gTab ... I created a global text field to hold tab character.

 

If you wish to still handle it all via Auto-Enter (replace) then the calc might look like this:

Let ( string = 'put your substitute calc here' ; TrimAll ( Substitute ( string ; [ ¶ ; " " ] ; [ gTab ; " " ] ) ; 0 ; 0 ) )

[dysong3]

Hi LaRetta,

Thanks so much for your astute and clear advice. I take your points. It is much less dangerous to use a script to implement this calc and let the user override if the calc comes across an instance where the resulting calc gives an unwanted result. Thanks also for honing down the leading tab and leading space calc. I have implemented your suggestion and it works like a charm.

I have another question. But maybe I should start a new topic. How could I define a calc so that the letter following an apostrophe is always upper case? i.e. "d'ouchy" becomes "d'Ouchy". Obviously I can do this with the substitute function, but I have to foresee all the different possibilities. Maybe there is a more elegant way around it.

[LaRetta]

There are so many combinations with character on each side of the apostrophe and since there can be many apostrophes within a string, I believe you will need recursion.  This custom function is not very pretty and I still worry about rules being missed but it fixes strings such as d'ortega and l'oreal but ignores patterns such as dad's or I'm.

/*
Apostrophe ( string )
Corrects capitalisation such as d'ortega becomes d'Ortega
Created: 2013-08-16 LaRetta
*/

Let ( [
pos = Position ( string ; "'" ; 1 ; 1 )  ;  // where is apostrophe
chunk = Left ( string ; pos ) ;  // chars through the apostrophe and lets us count the words
target = MiddleWords ( string ; WordCount ( chunk ) ; 1 ) ;  // isolates word with apostrophe
fixChar = Let ( [
  pos = Position ( target ; "'" ; 1 ; 1 ) ;
  char = Middle ( target ; Position ( target ; "'" ; 1 ; 1 ) + 1 ; 1 )   // isolate character 
  ] ;
  Case ( pos ≤ 2 and Left ( target ; 1 ) ≠ "i" ; Upper ( char ) ; char )
  )  // end inner let
] ;
Case ( PatternCount ( string ; "'" ) ; chunk & fixChar &
    Case ( not IsEmpty ( string ) ;
      Apostrophe ( Right ( string ; Length ( string ) - pos - 1  ) ) ) ; string
) // end case
) // end outer let

Let me know if you see any anomalies.  

[comment]

a calc so that the letter following an apostrophe is always upper case

 

Looking at it from a different angle, you could do something like:

Let (
adj = Substitute ( LastName  ; "'" ; "+" )
;
Substitute ( Proper ( adj ) ; [ "D+" ; "d'" ] ; [ "+" ; "'" ] )
)

This will handle correctly (IMHO) names like "d'Artagnan", "O'Toole", "L'Oréal" and even "Muad'Dib". However, it will also spoil a perfectly correct entry of "LaGuardia", for example. See also:

http://fmforums.com/forum/topic/74585-title-case-adjustment/

[LaRetta]

I like that very much!  I assumed it wasn't a single LastName field but rather full paragraphs of text; my bad for not asking for clarification.  In case of other text, your calculation appears to change I'd to I'D and I'll to I'Ll.

 

Welcome back, Michael.