FieldSubstitute() Custom Function

[Ender]

Here is an updated version of a custom function that replaces <<merge fields>> in a text field with the field data.

Example, if the user entered text field has this (Note the mispelling of the company field):

<<Date>>

Dear <<Name>>,

As a result of recent discussions with our team, we have decided to terminate our contract with <<companie>>.

Thank you for your business.

The result text will be this:

1/5/2004

Dear Greg Anderson,

As a result of recent discussions with our team, we have decided to terminate our contract with <Field Missing: "companie">.

Thank you for your business.

Here is the custom function code:

//FieldSubstitute(text)

//By Mike Hackett

//8-22-2004



//This takes a text field with user entered merge fields in the form <<Field Name>> 

//and replaces the merge fields with the field values.



Let(

[

   l = Length(text);

   p1 = Position(text;"<<";1;1);

   p2 = Position(text;">>";p1;1);

   fieldText = Middle(text;p1+2;p2-(p1+2))

];

If(p1 * p2; //If both sets of brackets exist

   Left(text;p1-1) & 

   If(IsValid(GetField(fieldText)); 

      GetField(fieldText); //field exists, get it's value

      "<Field Missing: "" & fieldText & "">") & FieldSubstitute(Right(text;l-(p2+1))); //field does not exist

   text//no matching brackets

)//if

)//let

This could be used where you want users to make letters using fields without having to come to you to modify the layouts.

[Vaughan]

This is just awesome.

I tried to something similar with custom functions and it's just totally embarassing compared with yours.

[The Shadow]

It seems to me you might be able to support simple calculations in the text by using Evaluate(), so that you could do something like:

<<Get(CurrentDate)>>

Dear <<If( gender="M"; "Mr."; "Mrs.")>> <<Name>>,

[Ender]

Woh. What a great idea.

Here is the revised function that can evaluate inline functions too.

//FieldSubstitute(text)

//By Mike Hackett

//8-23-2004



//This takes a text field with user entered merge fields in the form <<Field Name>> 

//and replaces the merge field names with the field values.  It also evaluates inline functions



Let(

[

   l = Length(text);

   p1 = Position(text;"<<";1;1);

   p2 = Position(text;">>";p1;1);

   fieldText = Middle(text;p1+2;p2-(p1+2))

];

If(p1 * p2; //If both sets of brackets exist

   Left(text;p1-1) & 

   If(IsValid(Evaluate(fieldText));

          Evaluate(fieldText);//Evaluate inline function

          "<Field Missing: "" & fieldText & "">") & //field does not exist

   FieldSubstitute(Right(text;l-(p2+1))); 

   text//no matching brackets

)//if

)//let

Though I'm not sure how comfortable users would be writing functions, it's pretty neat that this can be done. Thanks Shaun!

[Computer Geek]

Ender -- This is great!!!!!! Thank You!!

[Ender]

Thanks. Yeah, these custom functions can be very cool once you discover the power of their recursion.

I've got another interesting one in the works...