Creating Relationships on eMail addresses

[kiwipaul]

Two Questions here, 1) eMails addresses as Match Fields 2) Best way to store and reuse special characters such as Quotation Marks

I'm hoping use to eMail addresses as the Match Fields to create a Relationship between 2 tables, STAFF and EMAILS. When an EMAIL is received I want to automatically make it a Child of the Parent STAFF. However Filemaker doesn't seem to like the @ (ampersand) and period (dot) in the eMail addresses and the Match Fields don't join. Does anyone have a more elegant solution than my work around described below?

I already have a Relationship between KEY FIELDS with unique values in both Tables. When an eMail comes in I extract the eMail address, pass it to the STAFF table, Find the Staff Record based on the eMail address, and then use a Script to create the Join based on the KEY FIELDS. Now comes my second issue.

A simple Find on an eMail address doesn't work because of the ampersand and dots. I have to wrap the eMail address in Quotation Marks before doing the Find.

However I don't know any way to use Quotation Marks in a Calculation (Set Field) other than store the Quotation Mark in a Global Field or in Table with only One Record. I then retrieve the Quotation Mark as a Variable to use each time I want to make the Find.

I'm not keen on using Globals because they must be created for each user. If I use a Table with only One Record I'm locking that one record every time I access the record (this is a high volume application). Is there a better way such as being able to use the ascii code for special characters in caculations?

Thanks in Advance

Kiwipaul

[LaRetta]

However Filemaker doesn't seem to like the @ (ampersand) and period (dot) in the eMail addresses and the Match Fields don't join.

They do for me.

[LaRetta]

As an aside ... you are making many assumptions which aren't necessarily true nor are they the best way to proceed. It is a bit confusing. If you already have a key between Staff and eMails then just place the eMail table's email field directly on the staff layout. If you have 'allow creation on between the tables', it will create a new email. If there may be more than one email address, put it in a portal.

I believe that, what confuses me, is that you are mixing the ui needs with the structural needs.

[comment]

Filemaker doesn't seem to like the @ (ampersand) and period (dot) in the eMail addresses and the Match Fields don't join.

There shouldn't be any problems with either the "@" character (which BTW is not ampersand), or the period - provided both matchfields are Text fields, of course.

I have to wrap the eMail address in Quotation Marks before doing the Find.

Either that or escape the "@" character as "@".

I don't know any way to use Quotation Marks in a Calculation (Set Field)

Same way: """

See Help > Creating a database > Working with formulas and functions > Identifying text constants and special characters in formulas.

You can also use the Quote() function to enclose field contents in quotation marks.

[LaRetta]

Well, I made some assumptions based upon your self rating that you understand relationships. That might not have been wise so let me take this just a bit further and see if we can't get on the same page:

I already have a Relationship between KEY FIELDS with unique values in both Tables.

Unique values in both tables does NOT make a relationship. You need the SAME key value in both tables. Staff should have a StaffID. Emails should have an eMailID AND a StaffID. They should be joined on StaffID. Typing into an eMail::eMail field placed on staff layout will create a new email record and automatically insert the StaffID into the eMail::StaffID field.

Are we on same page here? I do not believe you would EVER want the eMail to be the key field. Ever.

LaRetta

[comment]

I think it would be OK to match on e-mail address, so that an incoming e-mail record can lookup the recipient's StaffID. In fact, I don't see any other way to assign an e-mail record to the correct staff parent.

[LaRetta]

When an eMail comes in I extract the eMail address, pass it to the STAFF table, Find the Staff Record based on the eMail address, and then use a Script to create the Join based on the KEY FIELDS.

In our part of the country, people change emails quite often and it is not trustworthy to hold relationsips together but sometimes we do what we have to do. It would depend upon perspective and use and, upon reread, if records are being imported from an online ordering system (for example) and the only means of identifying the staff is the email then yes, it will need to be used as a lookup.

Overall, I would perform a find on the email and show the result as a list for staff selection. I believe that's what threw me ... performing a find is a User-based activity and handled more one-at-a-time. In which case, I do not like depending upon email for anything other than narrowing down results.

[kiwipaul]

Thanks for replies, I've taken another look and see there was an extra (invisible) character in all the incoming eMails addresses I'm importing, so yes once that is cleaned up the Match does happily work between two eMail addresses in STAFF and EMAILS.