Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×
The Claris Museum: The Vault of FileMaker Antiquities at Claris Engage 2025! ×

Self Join to find Duplicates, but trouble with Indexing Match field


This topic is 6012 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

Hi Everyone,

I've got two tables, "People" and "Campaigns" which are joined by a join table, "Camp_Join", to make a many to many relationship, via People_ID and Campaign_ID fields. The "Campaign" table gets the person's email address from the "People" file, and i want to check that there aren't duplicate email addresses. I've already got a self join of the "Camp_Join" table working to check whether the same person is in the "Campaign" file twice, but when i try to do the same for the Email Field in "Camp_Join" table, i can't because that self join key is a calculation, ie Campaign_ID & "_" & People_Campaign::Email, and as you know you can't base a relationship on a match field that can't be indexed. Is there a way that i can do this? (Tried GetAsText, and a calc field as a replacement, but to no avail.)

Thanks for any help you can offer,

Slater

Posted

I've got two tables, "People" and "Campaigns" which are joined by a join table, "Camp_Join", to make a many to many relationship, via People_ID and Campaign_ID fields. The "Campaign" table gets the person's email address from the "People" file,

How can the Campaign table get a person's email address directly from people since you do not have a _kF_PeopleID in your Campaign table? I believe you need to tunnel back thru your join table to get a person's email address. Or did you mean the Camp_Join table gets a person's emai address from People, which makes sense.

Anyhow, aren't you asking how to make the email address in People unique? That could be easily done with field validation set to unique only.

As for

I've already got a self join of the "Camp_Join" table working to check whether the same person is in the "Campaign" file twice

People aren't in the Campaign file at all, so I don't understand. They can be in the join table more than once (that's why there is a join table), so can you restate what you meant here?

Posted

i can't because that self join key is a calculation, ie Campaign_ID & "_" & People_Campaign::Email

You don't need a self-join for this. Make the field a text field with auto-entered calculation (replacing) =

Let ( trigger = People_ID ; Campaign_ID & "_" & People_Campaign::Email )

and set its validation to unique.

You can do the same thing with the Campaign_ID & "_" & People_ID combo - no trigger is required here, since both are local fields.

Posted

Hi Comment - thanks for your help.

Very neat method! - but because i want to identify these records rather than stop them being entered, i'm still working on the problem. I had a go (thanks to seeing your method) using another field which calculates with IsValid (Test) when Test = Campaign_ID & "_" & People_Campaign::Email, and have Test set up as auto enter, unique with user override but, as i'm sure you know, that field never works out to be invalid because of the override.

Is there another direction you could point me in?

Cheers,

Slater

You don't need a self-join for this. Make the field a text field with auto-entered calculation (replacing) =

Let ( trigger = People_ID ; Campaign_ID & "_" & People_Campaign::Email )

and set its validation to unique.

Posted

It would be nice to know if my questions were totally off the mark, because I really didn't understand your data model.

Posted (edited)

How can the Campaign table get a person's email address directly from people

Or did you mean the Camp_Join table gets a person's emai address from People

yes, that's how i've done it

Anyhow, aren't you asking how to make the email address in People unique?

No, what i want to do is be in the Campaign table, see a portal to the Camp_Join table which includes on that row the person's email pulled from the People table, and then have a way on the same portal to identify any records that have the same email address (though they may be for different people). It's only this last bit that i'm having difficulty with. I had tried to do it with a self join, but as i've written it doesn't work out.

Hope that clarifies, and thanks for taking the time to answer my post,

Slater

Edit: Our mails missed each other while posting

Edited by Guest
Posted

I'm still confused. How can two different people have the same email address? Why wouldn't a unique validation in the People table on the email address help?

Posted

I'm still confused. How can two different people have the same email address? Why wouldn't a unique validation in the People table on the email address help?

For example, two people at the same company might use the same email "[email protected]" and i might have them both down for a campaign. This being the case, i would want to delete one of them from the campaign so that "[email protected]" only gets the one email, and the easiest way to identify this situation may be to identify any email addresses that are represented twice in any campaign.

(BTW the reason why i just "can't be more careful" when entering the people that get the campaign, is that they're automatically imported from a filter of the People table, so i won't see them until they're in the campaign and displayed in the portal. There are too many of them for me to scroll thru checking visually - though if you add up the time i've spent trying to automate the process, it probably comes to more than i'd spend visually checking in a half year!. [i've got the identification of duplicated people in the portal via a self join as i've written before, so that problem (the same person getting more than one email) is sorted.]

Cheers

Posted

To identify instead of validating, you can use the same auto-entered field as the match field for a self-join (the same field on both sides). The duplicates are the ones where:

Count ( selfjoin::SerialID ) > 1

Another option is to do a find for duplicates in the auto-entered field.

One caveat applies here:

If you select another person for the join record, the auto-enter will trigger and update with the new person's e-mail. However, if the selected person changes their e-mail, nothing will happen in the join record. If that's a real possibility for you, you need to make sure that join records are updated after changing a person's e-mail address.

Posted

To identify instead of validating, you can use the same auto-entered field as the match field for a self-join (the same field on both sides). The duplicates are the ones where:

Count ( selfjoin::SerialID ) > 1

That's great! i had been using

SerialID = selfjoin::SerialID

but that only identified one of the duplicates ... your method is much better for me.

One caveat applies here:

If you select another person for the join record, the auto-enter will trigger and update with the new person's e-mail. However, if the selected person changes their e-mail, nothing will happen in the join record. If that's a real possibility for you, you need to make sure that join records are updated after changing a person's e-mail address.

And i guess i would do this by having a script "Replace Field Contents" before i'd ever do a search?

Thanks, you've helped me out no end!

Slater

Posted

And i guess i would do this by having a script "Replace Field Contents" before i'd ever do a search?

It depends on your work flow. You may not want to change anything in past campaign records. It's also possible that the problem will never come up. It's only an issue when:

1. You enter Adam into a campaign;

(You continue entering more people into the same campaign);

2. You change Adam's e-mail.

At this point, the campaign's join record are still checking for duplicates using Adam's original e-mail. So if that could be an issue, you need to follow the change of address with:

- Go to Adam's related join records;

- Replace People_ID with itself.

Posted

Thanks for a very clear explanation. I'll get that sorted.

All the best,

Slater

This topic is 6012 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.