9 Replies Latest reply on May 15, 2013 8:55 AM by philmodjunk

# Matching characteristics

### Title

Matching characteristics

### Post

I am trying to build a FM12 database for a dating agency. Each person on the database is stored in a table called "Contacts". Each contact has two fields called "Selected Personal goals 1" and  "Selected Personal Goals 2". The values for these fields are selecxted from a dropdown and contain values such as :-

* Honesty

* Certainty

* Intimacy and Love

* Significance

* Contribution

I am trying to count the number of matches on these values. The match is stored in a field called "Match on Personal Goals".

So if Person 1 says that his personal goals are "Honesty"  and "Certainty" and Person 2 says that their personal goals are "Significance" and "Certainty" then there is one match. So the field containing "Match on Personal Goals" has the value 2.

So if Person 1 says that his personal goals are "Honesty"  and "Certainty" and Person 2 says that their personal goals are "Significance" and "Honesty" then there is one match. So the field containing "Match on Personal Goals" has the value 1.

Likewise if Person 1 says his personal goals are "Honesty"  and "Certainty" and Person 2 says that their personal goals are "Significance" and "Contribution" then there are no matches. So the field containing "Match on Personal Goals" has the value 0.

I am computing the field "Match on Personal goals" in two steps:-

match on personal goal one =

If((second Contact::selected personal goal two=selected personal goal one) or (second Contact::selected personal goal one=selected personal goal one);1;0)

match on personal goal two =

If((second Contact::selected personal goal one=selected personal goal two) or (second Contact::selected personal goal two=selected personal goal two);1;0)

and then :-

match on personal goals = Sum(match on personal goal one;match on personal goal two)

I am finding that I get correct results one way, but not the other. So with the reference to the screen shot, Paula Price has one match with Mark Anthony - which is correct. However, if I look at Mark Anthony's profile it shows no matches with Paula Price.

The portal is a self join with the contacts table and is called Second Contact.

I also need to do this with "Selected values" but that has 5 comparisons. Thanks

regards

Kaps

• ###### 1. Re: Matching characteristics

I suggest descriging the details of that "self join", I don't really see how that approach would work here for finding the number of matching individuals of a specified gender that match one one or both values--but perhaps those details will clear that up.

• ###### 2. Re: Matching characteristics

The self join is :-

Contacts Table            Relationship                      Second  Contacts Table                         Comment

Contact id                    NOT EQUAL to                    Contact id                                                   (i.e match on all records except onself)

Active                            EQUAL to                             Active                                                           (only look at members who are active)

SEX                              NOT EQUAL to                   SEX                                                              Only interested in Heterosexual partners

thanks

Kaps

• ###### 3. Re: Matching characteristics

I don't see any way for that to work with this expression:

If((second Contact::selected personal goal two=selected personal goal one) or
(second Contact::selected personal goal one=selected personal goal one);1;0)

When your refer to the field of a related record in an expression like this, it only refers to the "First" such related record and does not access the value in this field for any other records in the related table. The "first" related record will be the oldest related record if the relationship is not sorted.

I suggest a relationship that includes your two fields are part of the matching fields:

Contacts::Contact id ≠  SecondContacts::Contact id AND
Contacts::Active = SecondContacts::Active AND
Contacts::SEX ≠  SecondContacts::SEX  AND
Contacts::cGoalList = SecondContacts::cGoalList

SecondContacts and Contacts are table occurrences that refer to the same data source table.

cGoalList would be defined as:

List ( PersonalGoal1 ; PersonalGoal2 )

This will match to all contacts that have at least one matching personal goal.

Define gGoalList as a global text field that you set to the value of Contacts::cGoalList via a script trigger performed script.

You can then define this calculation field, cMatchCount in contacts:

ValueCount ( FilterValues ( gGoalList ; cGoalList ) )

And then you can sort your portal by cMatchCount in descending order to get the contacts that have two matches listed before those that have just 1 match.

Note that this can be adapted to work with more than two personal goal values.

• ###### 4. Re: Matching characteristics

Thanks. Must be missing something:-

1. I have amended the relationship to include

Contacts:cGoalList = Second Contacts:cGoalList

where :-

cGoalList = List (PersonalGoal1;Personal Goal2)

2. gGoalList is a Global text field (in Contacts table) that under a modify script trigger is set to Contacts::cGoaList. The script fires whenever PersonalGoal1 or PersonalGoal2 changes.

3. cMatchcount is defined as :-  ValueCount(FilterValues(gGoalList;cGoallist))

So if I have three people :-

Mark Anthony with values                        Intimacy  and Love,                             Significance

Paula Price with values                           Vareity ,                                                  Significance

James Smith with values                         Vareity                                                     Certainty

I would expect

Mark Anthony and Paula Price to have one match (Significance). Under Mark Anthony I get one match (correct). However under Paula Price I get two (these should agree)

James Smith and Paula Price to have one match (Vareity). Under James Smith I get one match (correct) but looking on Paula Price for James I get zero.

So it appears that the relationship is correct from one side - but not the other. Any thoughts ? Thanks

Kaps

• ###### 5. Re: Matching characteristics

I don't know what you mean by from "one side and not the other" as these should all be evaluated from the same side of the relationship.

As I look at your data, Paula has two matches not one. A match to Mark via Significance and a match to James via the "vareity" goal that they have in common.

I'd also use the OnRecordLoad trigger to update the global field in addition to the triggers you already have in place here.

• ###### 6. Re: Matching characteristics

I would expect symmetry - i.e when I look at Paula Price from Mark Antony's page I should get the same result as when looking at Mark Anthony's page from Paula Price. But I dont.

• ###### 7. Re: Matching characteristics

That makes no sense to me because Paula and Mark do not specify the same list of goals.

• ###### 8. Re: Matching characteristics

I think I have confused you. A match can occur on EITHER (i.e it is an OR type relationship) rather than BOTH.

Mark and Paula have a match on ONE of their goals - Significance.

Likewise Paula and James have a match on ONE of their goals VAREITY.

Hence it is that you compare the first person's first personal goal with either of the second personal goals as one test. You then compare the first person's second goal with either of the second personal goals as a second test.

• ###### 9. Re: Matching characteristics

What I describe is an OR type relationship. But notice that Mark matches to Paula, but Paula matches to both Mark AND James. Thus, it makes no sense to me that Paula would need to show the same matches as Mark when viewed from the context of Mark's record.

The use of the global field--needed to count the number of matches so that people with the most matches are listed first, does make this a one way relationship, but I don't understand why that is a problem.