Pulling client details in as a merge variable

[sbphoto01]

Hi all, apologies if this is the wrong place to post...

 

Here's my problem. I have four tables in my relationship... a 'Contacts' table, an 'Events' table and an 'Email Correspondence' table and an 'email templates' table.

 

In my email templates table, i have merge variables which are replaced in a 'merged view' calc field using a pattern count find & replace, which works great.

 

So for example if <<event_venue>> appears in the template creation field it is replaced by the actual 'event venue address' in the related events table... all good.

 

What i want to be able to do is automatically replace another <<primary_contact_1>> with the first related contact in the contacts table...

 

is this something that's possible?

 

In previous carnations of my solution, all the contacts lived in separate static fields in the events table, so i was able to pull them in easily as merge variables but now there are multiple related contact in a separate table, I'm not sure how to do this.

 

Any help would be much appreciated!

 

thanks, Steven

[comment]

What i want to be able to do is automatically replace another <<primary_contact_1>> with the first related contact in the contacts table...

 

You haven't described the relationships between the tables. In general, a reference to a field in another table gets the data from the first related record in the referenced table. So that if you are in the Email Correspondence table, and you apply =

Substitute ( Message ; "<<primary_contact_1>>" ; Contacts::FullName )

you will insert the name of the first related contact (in the sort order of the relationship) wherever the placeholder "<<primary_contact_1>>" appears in the Message field.

 

 

 

using a pattern count find & replace

 

What exactly is that?

[sbphoto01]

Hi, Thanks for your reply, and your suggestion, the relationship between email correspondence and contacts table is through the events table and the contacts are related to events via a join table.

 

How would i pull in the second record in the contacts table as a merge variable?

 

Ignore the pattern count find & replace reference...  I meant substitute function... I'm using pattern count for another thing and got mixed up :0)

[comment]

How would i pull in the second record in the contacts table

 

Try:

GetNthRecord ( Contacts::FullName ; 2 ) 

[sbphoto01]

Awesome! Thank you so much! that works great.

[sbphoto01]

Hi, just one other thing, how do I determine the sort order of a table as it's returning the first and second contacts details but not in the order I want them....

 

 

I have a sort order auto enter serial in the contacts table which i use to sort and order the portal.

I think I've found it... you can do it in the relationship.

[sbphoto01]

I'm having real problems getting the getNth part of my merge variable script to pull in the correct record...

 

The Email Correspondence table 'sees' the contacts records, but not in the correct order...

 

The relationship is Contacts - Contact|Events|Join - Events - Email Correspondence

 

I thought I had it working bu when i add in a third linked contact to the event it then pulls in the third contact rather than the second.

 

Bit stumped!

[comment]

What determines the "correct" order of Contacts?

[sbphoto01]

Hi I have a sort field (auto enter serial) in the contact table and I sort via the relationship using this field, but when pulling in via this method there doesn't appear to be any order... it's all a bit randon no matter which way I sort the relationship.

 

Should I script a sort order step in the merge calc somehow?

[Lee Smith]

Hi all, apologies if this is the wrong place to post...

 

Not if you are using version 13?

 

If so, please update your profile here

[comment]

There's nothing random about software behavior... Make sure that the relationship between Contacts and Contact|Events|Join is sorting the records on the Contacts side by the field you want.

 

As an aside, it seems a little strange that an auto-entered serial does not follow the creation order - which is the default sort order.

[sbphoto01]

The related contact records are in a portal with a sorting script attached which switches the sort numbers... could it be that although they appear in order, the merge variable substitute script is putting them in creation order by default then?

 

As the order I see them in the Contact|Events|Join table (sort field sort order) is certainly not the order it is pulling them in as variables... I'll create some new contact records and refrain from adjusting the sort numbers via my portal script and see if i get a different result.

 

Any other ideas? I'll try to post an example file to show how i have things setup.

 

Thanks for all your help so far and Lee... I've updated my app version details.

[comment]

You speak of portals, yet the entire thing here works at data level and ignores any portals you may have on any layout and the sort order you have set up for them. The only thing that matters here is the sort order of the relationship and - since there are several relationships connecting the two tables in question - it is only the "last" relationship that counts.

[Lee Smith]

 I'll try to post an example file to show how i have things setup.

 

This link will tell you the steps. 

 

How to add an attachment to a reply