Dynamic Layouts using a calculated field

[StooJ]

I'd like to set up three layouts to print as letters, to be sent out when a payment hasn't been received. However, I'd like to give end-users the opportunity to rephrase the letters if they'd like, rather than stick with the default I provide. So, some kind of setup screen, where you fill in a global field that contains the message body.

However, the message will probably be something along the lines of:

"On referring to our records we note that your account remains unpaid. The amount outstanding is <<patient bill>> We would very much appreciate if

you could make arrangements to settle this account as soon as possible."

Is there a way to set this up from another layout via scripts, or calculated fields or something, since Filemaker sees <<patient bill>> as text, rather than a field when it's contained in a field.

I'm sorry this isn't clearer, I'm not really 100% sure what I'm trying to do, but basically I'd like to set up a layout where user's can control the text displayed on another layout, but it includes field references.

Thank you for any help

[richMac]

Stool,

Yes you can use several layouts using scripts to determine which layout to go to. Use the IF statement in your script to determine a layout based on a value in the record. For example, you could go to one letter if they are 30 days old, another if 60 and yet a stronger message if over 90. In your Go to Letter script, use IF statements to evaluate the age and then go to the appropriate layout.

You can offer them the option to edit the message but this may not be the actual layout that you print.

This may be an editing layout and when you hit the print button a script takes them to the printable layout.

As far as a field in a field or what is more commonly referred to as mail merge, you can readily do this in FileMaker using the Substitute function. So your output or printed field would actually be a calculated field from the field that is edited. The Substitute function takes care of inserting the value for that record. Check out the Substitute function in FileMaker Help.

Welcome to FileMaker!

Richard McCulloch

[bruceR]

There are lots of example files that do this, including some at www.databasepros.com. But I haven't seen any that take advantage of the new FM7 calculated-format features, etc. FM7 shoud really make this easy and powerful.

[StooJ]

Thanks for your replies, guys, but that's not actually what I meant. Sorry, my bad for my poor explanation.

There will be three layouts, but I'd like end users to be able to build up their own letters. So, in a control screen, I'd have a big global field that users could type in their "you've not paid yet" letters 1, 2 & 3.

The problem is, they will probably want to include field values in the body of these letters somewhere. <<patient bill>> and <<date seven days from now>> might be anywhere in the body of the letter, might occur more than once, etc.

3 layouts for 3 different letters, but I'd like users to be able to write their own when they set up the solution, or use the one I provide. Is there a way for them to do this, <<field values>> included, without going into layout mode?

Or am I taking that too far?

Thanks for the welcome as well!

Stewart

[Ender]

So you want users to be able to dynamically specify a merge field just by typing it in the text field. That's an interesting idea. I'll bet you could use a custom function to replace each occurance of <<field>> with GetField(field) (or evaluate(GetField(field))?)

Sorry I can't help further, I'm not a custom function guru. I'll be curious to see what you come up with.

[MoonShadow]

I do not believe you can insert a field into a field. You would need to break apart the entire letter as: Sentence 1, Sentence 2, etc. Not a pretty sight. However, there are ways to give you what I think you want ... using a script and script parameters.

You can place buttons across the top of the 'letter' labeled DueDate, AmountDue, FirstName, LateFee sentence or whatever. Then create just one script with:

Set Field [ LetterField; LetterField & " " & Get(ScriptParameter) ]

Then on each button, select this script and in the Parameter, insert the correct field to match the button name -- Customer:FirstName, Customer:LastName, Invoice::DueDate etc. The above method would always insert at the end of the existing text. It works quite well for basic letter-writing and is unlimited in the number of fields which can be used.

You can also use Insert Calculated Result [ FirstName ] -- and just use CTRL-1 and the remaining 9 shortcut keys. In this way, the User can click into an existing letter and evoke the shortcut key and it will insert just the field contents at the cursor. This will be limited because User must use shortcut keys (as opposed to clicking a button) because once they click the button, they will lose their place in the letter. This will also work for inserting global text sentences, such as "We appreciate your business" or sentences about Late Charges, etc. Simply populate a global text with repetitions with your various sentences and refer to the repetition number in your scripts.

Using script WILL work while in Browse mode. And using the shortcut keys WILL work for inserting field contents at a cursor point. A combination of buttons (set to 'Do not print selected objects') across the top of a letter will give Users prefabricated letter parts and field data in which they can build their own letters. Since shortcut keys for scripts are limited to 10, you would be limited; however, there are also ways of 'marking' a Users place and inserting the text at that exact spot after they click a button. You would use an unusual character (such as ^) and then use Find/Replace to re-locate it for insertion after a button has been clicked.

After the letter has been created in a global text field, it can be saved to a Letter table and cleared for the next User.

[Ender]

Well, this problem got me digging into custom functions, and I think I got it working. Attached is a sample using my very first custom function:

FieldSubsitute (text):



Let([

l = Length(text);

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

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

];

Left(text;p1-1) &

Case(p1*p2; 

GetField(Middle(text;p1+2;p2-(p1+2)))

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

)//case

)//let

It seems to work for the data I threw at it. Give it a try and let me know what you think.

FieldSubstitute.fp7.zip

[MoonShadow]

I love being proven things aren't impossible. And now I see it so clearly - HTML tags, right? Of course!! Very pretty, Ender.

[Ender]

I'm just glad I'm starting to *get* custom functions. The recursion is where the power is.