Many to One Relationship Search

General support questions.
Posts: 7
Joined: Mon Mar 06, 2006 1:50 pm
PostPosted: Mon Mar 06, 2006 1:52 pm
I have a many to one relationship between two of my tables. Specifically, I have one person that can have multiple letters associated with them. I have a script that sends that person a letter as long as they meet the following: they have not been sent this particular letter before. As I loop through my people table I want to run this check. How do I handle this? To I do an if statement like:


Code: Select all
if(not(Letters::ID = Contact Info::ID and Letters::Description = "Initial Letter"))



I have been unable to get this to work as it only looks at the first Letter and not any subsequential ones. Thanks for the help.
PerryL7
FMP: 8
OS: Mac OS X Tiger
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Mon Mar 06, 2006 3:39 pm
Well, if you want to use a relationship to test for this, you'd create a separate relationship between your contacts table and your letters table just for this test. You'd then add a global field to contacts; this would contain the description (name) of the letter you'd like to send. Then the relationship to test if you'd already sent the letter would have two predicates:

Contacts Letters
ContactID = ContactID
GlobalDescriptionField = Description

This relationship would only be true if this contact had been sent a letter matching the description in GlobalDescriptionField.

Alternately, you can do it without a special relationship to letters, just using the relationship that you already have to all the letters send to the contact. In this case you'd build a value list from the context of Contact, showing the description field of the letters sent to that contact. If this value list was called "ContactsLettersSent" then your test would look like

Patterncount ( ValueListItems ( get ( FileName ) ; "ContactsLettersSent" ) ; "Initial Letter" ) = 0
John Sindelar
SeedCode
Posts: 7
Joined: Mon Mar 06, 2006 1:50 pm
PostPosted: Fri Mar 10, 2006 9:04 am
John,
I tried the first option but it only checks the global description against the first record listed in the portal. It does not cycle through all of the letters tied to lthe contact through the contactID. I know I have to be doing something wrong.
PerryL7
FMP: 8
OS: Mac OS X Tiger
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Fri Mar 10, 2006 9:19 am
perryl7 wrote:John,
I tried the first option but it only checks the global description against the first record listed in the portal. It does not cycle through all of the letters tied to lthe contact through the contactID. I know I have to be doing something wrong.


If the relationship matches BOTH the contact ID and the description, then the only records in that match are those fitting the global description; that's the trick. This is not the same relationship as that just based on the contact ID. Does that make sense?
John Sindelar
SeedCode
Posts: 7
Joined: Mon Mar 06, 2006 1:50 pm
PostPosted: Fri Mar 10, 2006 9:42 am
Here is the script that I am using. Is this correct?

Code: Select all
if[not((Contact Info::Recruit ID = Recruit Contact::Recruit ID)  and (Recruit Contact::Description = "Initial Letter"))]


Do I have to make a global description field to hold "Initial Letter"? The problem I am having is that it just checks the first record listed in the portal containing all of the prior contacts.

Thanks for all of your help.
PerryL7
FMP: 8
OS: Mac OS X Tiger
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Fri Mar 10, 2006 9:52 am
perryl7 wrote:Do I have to make a global description field to hold "Initial Letter"?


Yes. Check the second post in this thread. You create a global field to hold the letter description you want to test, then build a Second relationship to your letters based on not only the contact ID, but this global field as well. If there are any records at the end of that relationship, then this contact has already been sent a letter with the same description as the one in your global field. (BTW, this global field is in the contact table.)
John Sindelar
SeedCode
Posts: 7
Joined: Mon Mar 06, 2006 1:50 pm
PostPosted: Fri Mar 10, 2006 3:09 pm
Thanks John, I got it working.
PerryL7
FMP: 8
OS: Mac OS X Tiger

Return to General Support

Who is online

Users browsing this forum: No registered users and 3 guests

cron
(855) SEEDCODE
[email protected]
Follow us: