Relationship working only 1 way

[simoncpage]

I have a pref file called constants which I am using to store email templates etc. The constants file contains a ID field which is a calculation = 1 and this is linked to a calculation field = 1 in the contacts file as a relationship so that all the constants appear for each of the contacts.

However I want to add to the email either a formal or informal greeting Dear Mr Smith, Dear Simon. So I added a field to the contacts called ToEmail which is a calculation based on a drop down (formal or informal) which is fine.

However when I want to send the email I want to send a text field which is the combination of formal/informal greeting, body of the email and a signature. Now I could add this field to contacts but i really don't want to add this 12,000 for each entry and ideally I want it to just refernece to a field in the constants file (but I can't use the same relationship backwards)

Is there anyway to do this without having to add a massive load of calculations fields to the contacts file?

Thanks

[QuinTech]

Hi Simon. You're right in saying you can't use the same relationship backwards in FM6. However, you can create an identical relationship in Contacts, no?

Jerry

[simoncpage]

What do you mean?

I have the relationship created in the contacts file

i.e

ID_Contact (-calc field = 1) link to:

ID_Constants (-calc field = 1)

ID_contact::ID_constants

One work around I thought of is to add a global field in the constants file and have the name part copied over to that in a script when the send is clicked. Not idea but I guess it will work

- unless someone has a more elegant way??

[QuinTech]

You're aware that you can access fields from a related file directly, without calculations? On any layout, drop a field in there. When asked to choose the field to display, change the "Current File" at the top to the name of the relationship you described above. Then select the field you want to display.

Alternatively, you could calc this all out. Lets say in Contacts you have fields called Greeting and Signature, and in Constants you have a field called Body. The relationship from Contacts to Constants is called Constants1. To calculate the entire email, make a new field in Contacts that is equal to:

Greeting & " " & Constants1::Body & " " & Signature

This field will be different for each record, depending on the contents of Greeting and Signature.

J

[simoncpage]

i know all this as i had said this in my posts - - what i am trying to achieve is:

Greeting & " " & Constants1::Body & " " & Signature

all in the constants file without have to needlessly create a whole field for each contact and making the solution nice and compact in one constants file

[QuinTech]

I think i'm getting it... you want to display a composed email in the Constants file, with the greeting and signature calculated based on something you've selected in Contacts?

If this is the case, create a new field in Contacts called "Selected"; then make a script that, when run, sets Selected for the current record to 1 and, for all other records, sets Selected to 0. (You could do this with a radio button and no script, but an issue arises when you have more than one record so marked.)

Back in Constants, duplicate your relationship based on the calc 1 field but modify it so that it is looking to Contacts::Selected for its foreign key. Now the calc

NewRelationship::Greeting & " " & Body & " " & NewRelationship::Signature

should give you what you're looking for. If i understand what you're looking for. Sorry for not understanding your request at first, but i still don't think it was all that clear.

J

[simoncpage]

Okay I see where you coming from although not sure about the script that changes selected for current to 1 and other to 0. Not sure how good this is performance wise on 12,000 contacts which will more than likely be double that in a few months. I would be happier with a calculation field I'll have to ponder......thanks for the ideas though, appreciated!

[ESpringer]

I'd suggest making a global field in the email templates file which holds the ID number (or name if you don't use ID#s) of the contact record to which you want to relate. Since it can hold only one value, you don't have to worry about "fixing" the radio button on the previously-used contact record.

Then, set up a relation between the global ContactID and the matching record in the contacts DB... Then your concatenated calc, pulling things by way of that relation, should work fine..

It sounds like you might have been contemplating such a move, several posts back...