Concactonate calculations

[dysong3]

Hi,

 

I have 3 calculations that I would ideally like to concatenate into one, but can't work out how to do it.  How can it be done?

TextFormatRemove ( MiddleWords ( Self ; 1 ; Length ( Self ) ) )

Substitute(Home_Address_1
; ["ch. "; "chemin "]
; ["  "; " "]
; ["-Au-"; "-au-"]
; ["Ch. "; "chemin "] 
; ["ch. "; "chemin "] 
; ["Chateau"; "Château"]
; ["chateau"; "Château"]
; ["Ch "; "chemin "]
; ["av "; "avenue "]
; ["av. "; "avenue "]
; ["Av. "; "avenue "]
; ["Av "; "avenue "]
; ["ave. "; "avenue "]
; ["ave. "; "avenue "]
; ["Ave. "; "avenue "]
; ["Ave "; "avenue "]
; ["pl. ";"place "]
; ["Pl. ";"place "]
; ["BIS ";"Bis"]
; ["bd ";"boulevard "]
; ["bd. ";"boulevard "]
; ["Bd ";"boulevard "]
; ["Bd. ";"boulevard "]
; ["Bvd. ";"boulevard "]
; ["Bvd ";"boulevard "] 
; ["Bp ";"Boîte Postale "]
; ["BP ";"Boîte Postale "]
; [" cp ";" — Case Postale "]
; [" CP ";" — Case Postale "]
; [" Cp ";" — Case Postale "]
; ["CP ";"Case Postale "]
; ["Cp ";"Case Postale "]
; ["cp ";"Case Postale "]
; ["c.p. ";"Case Postale "]
; ["C.p. ";"Case Postale "]
; ["De ";"de "]
; ["-De-";"-de-"]
; ["-Du-";"-du-"]
; ["Du ";"du "]
; [" d'a";" d’A"]
; [" l'a";" l’A"]
; [" d’a";" d’A"]
; [" l’a";" l’A"]
; [" d'e";" d’E"]
; [" l'e";" l’E"]
; [" d’e";" d’E"]
; [" l’e";" l’E"]
; [" l'h";" l’H"]
; [" l’h";" l’H"]
; [" d'h";" d’H"]
; [" d’h";" d’H"]
; [" d'i";" d’I"]
; [" l’i";" l’I"]
; [" d’i";" d’I"]
; [" l’i";" l’I"]
; [" d'o";" d’O"]  
; [" l’o";" l’O"]
; [" d’o";" d’O"]  
; [" l'o";" l’O"]
; ["-d’o";"-d’O"]  
; ["-l'o";"-l’O"]
; ["-d’o";"-d’O"]  
; ["-l’o";"-l’O"]
; [" d'u";" d’U"]
; [" l'u";" l’U"]
; [" d’u";" d’U"]
; [" l’u";" l’U"]
; ["Des ";"des "]
; ["rte ";"route "]
; ["Rte ";"route "]
; ["Rue ";"rue "]
; ["Ruelle ";"ruelle "]
; ["sent. ";"sentier "]
; ["str. ";"strasse "]
; ["st-";"St-"]
; ["¶";" "]
; ["	";" "]
; [" L’";" l’"]
; [" L'";" l’"]
; [" D’";" d’"]
; [" D'";" d’"]
 )

Let (
pos = Position ( Home_Address_1 ; " " ; Length ( Home_Address_1 ) ; - 1 )
;
Case ( pos ; Replace ( Home_Address_1 ; pos ; 1 ; Char ( 160 ) ) ; Home_Address_1 ))

 

[comment]

Can you explain briefly the purpose of these calculations? I think I understand the Substitute part (although I wonder why some of the strings start with a space). And I think you want to replace the last space with a non-breaking space. But this part makes no sense to me at all:

TextFormatRemove ( MiddleWords ( Self ; 1 ; Length ( Self ) ) )

 

[dysong3]

On 3/31/2019 at 1:00 PM, comment said:

 

TextFormatRemove ( MiddleWords ( Self ; 1 ; Length ( Self ) ) )

 

TextFormatRemove is to remove any formatting in the field.  The "Let" function is to make sure that the last word of an address does not appear as an orphan at the beginning of a new line, whatever the context. And the Substitute function is to correct any of the following suite of characters to make them conform to my specific needs for address formatting.  Ideally I would like to subject any entry into this address field to the whole three of these calculations.  I hope that's clear

Edited April 3, 2019 by dysong3

[comment]

Well, I know what TextFormatRemove() does. I just can't figure out what is the purpose of doing =

MiddleWords ( Self ; 1 ; Length ( Self ) ) 

For one thing, the count of characters will always be equal to or larger than the count of words. For another, do you really want to remove all leading and trailing punctuation from the input? Because that's what this will do.

 

 

Edited April 3, 2019 by comment

[mr_vodka]

 

8 hours ago, dysong3 said:

 Ideally I would like to subject any entry into this address field to the whole three of these calculations.  I hope that's clear

 

To me this would be better served with a onIObjectValidate script trigger.  You can store the patterns and the corrections in its own table.

[dysong3]

Yes, I do want to remove all leading and trailing punctuation from the input.

Thanks for the idea of using script triggers. However after a little toying, the only script I can get to work with that method is the Let function. TextFormatRemove() apparently is not available under script calculations. And I can't seem to get the Substitute one to work, but I can't fathom why.

 

If I can just get the Substitute to work as a script calculation or concatenate it with the TextFormatRemove. I'will have got to where I want to be.

[comment]

Well, then it is a rather simple matter of nesting (not concatenating) the operations. The Let() function is a convenient tool to do this while retaining readability. Consider something like:

Let ( [
// REMOVE FORMATS
text = TextFormatRemove ( Self ) ; 

// REMOVE LEADING & TRAILING PUNCTUATION
text = LeftWords ( text ; WordCount ( text ) ) ;

// EXPAND ABBREVIATIONS
text = Substitute ( text ; 
  [ "av " ; "avenue " ] ;
  [ "av. " ; "avenue " ] ;
  [ "ave. " ; "avenue " ] ;
  ...
  [ "str. " ; "strasse " ] 
  ) ;

// CORRECT CAPITALIZATIONS 
text = Substitute ( text ; 
  [ " d'a" ; " d’A" ] ;
  [ " d'e" ; " d’E" ] ;
  [ " d'h" ; " d’H" ] ;
  ...
  [ "st-" ; "St-" ]
  ) ;

// HANDLE WHITE SPACES
text = Substitute ( text ; [ ¶ ; " " ] ; [ Char (9) ; " " ] ) ;
text = TrimAll ( text ; 0 ; 0 ) ;
lastSpace = Position ( text ; " " ; Length ( text ) ; - 1 ) ;
text = If ( lastSpace ; Replace ( text ; lastSpace ; 1 ; Char (160) ) ; text ) 
] ;
text
)

You can do any calculation inside a script; however, I can see no advantage to scripting this.