Choose Field by Text - Merge Question

[Savery]

I have several email merges to send, using 360Works FYI, but that part is fine. What I'm having trouble with is making it childsplay to use tags within the body to replace with field values.

Here's what I'd ideally like to do:

Substitution_Tag field contains the tag names to be searched within the body, each separated by a carriage return.

Substitution_Fields field containing the names of the table fields to be used as the value to replace the tag, again separated by carriage returns to match the tags in above field.

I can do the loop, using GetValue and could use GetLayoutObjectAttribute to identify each field of the database, but that'd take forever and would need amending whenever fields are added or changed. It also wouldn't account for all the related fields.

If I could set the Substitution_Fields text field (Global, BTW) with the names as you would in, say, PHP 'Customers::First_Name' then I'd be over the moon.

Am I going to go insane trying to achieve this or is there a trick way of doing this? I could use AppleScript to fetch the value, of course, by having a calculation AppleScript get the value of the field based on the GetValue for the field name but this would mean only Macs could run this merge feature.

[comment]

Perhaps you could adapt this custom function to your needs:

http://www.briandunning.com/cf/851

[Savery]

I was afraid I hadn't explained myself clearly enough...

I can do the substitution (360Works comes with a BodySubstitution function which works fine). What I am trying, and failing, to do is use a text field to contain the names of the fields I'd like to reference as the 'replace with' values.

So, say I have a text field called 'ReplaceWithValueFrom' which contains the value 'Customer_Number'. What I want to do is use a Substitution something like this:

Substitute ( GetValue ( $tags ; $tag_row ) ; GetValue ( $replacefield ; $replace_row ) )

But I can't see any way of getting the value of a field by being able to reference it in this way. Seems I have to use GetLayoutObjectAttribute which would mean having to name every field object. I could do this, of course, but it wouldn't be as flexible as I'd like the solution to be (ie we would be restricted to use only fields which have been named as objects).

[Savery]

Maybe the AllFields (start ) function will be a workaround. I could add the fields we're likely to need to a new layout and make sure people know to order the tag name in the correct order (using false tags where an non-required field falls).

[comment]

I am still not sure I follow this. I have the feeling you want something like:

Substitute ( text ; searchString ; replaceString )

where:

searchString = GetValue ( searchValues ; 1 ) ;

replaceString = GetField ( GetValue ( replaceFields ; 1 ) )

which is what I meant by adapting the SubstituteValues() function.

[Savery]

Thanks for persevering with me.

I have two fields with lists within them (simplified)

Substitute_Tags:

[##NAME##]

[##EMAIL##]

Substitute_FieldNames:

Full_Name

Email

Full_Name:

Steve Savery

Email:

[email protected]

Ignore the first field for a moment. What I want to do is look at row n of Substitute_FieldNames and get the value of the name of the that field.

So, in this example if I wanted row 1 of Substitute_FieldNames I'd get "Steve Savery" as the result.

My loop encompassing all that I want to achieve would go something like this:

Set var $count = 1

Loop

Substitute ( GetValue ( Substitute_Tags ; $count ) ; GetValue ( Substitute_FieldNames ; $count ) )

Exit Loop If $count > PatternCount ( $count ; "returnsymbol" ) + 1

End Loop

But, the flaw in this Substitution is that I don't want to replace [##NAME##] with "Full_Name" but with the contents of the field with the name of "Full_Name".

Hope that is clearer. :)

[comment]

Isn't that what the GetField() function does in my code above?

[Savery]

I read your reply far too quickly, clearly. Thank you! I think Spent far too long on this before asking for help.