I have a FM7 database with two tables, one for Teacher Info, one for Service Choices that teacher might sign up for.

I would like the user of the database (eventually to be a web-based tool to allow teachers to request/track multiple services) to be able to type in their email address, have it compare to existing email address records (in the Teacher Info table), find the matching one and open that r teacher's record. If it doesn't have a match, open a new record with their address aready in the correct field.

Any ideas for me?

Thanks,

Create a global field "g_mailaddress" and create a table occurence with a relationship between the global field and the field "mailaddress" in Teacherinfo (here: "Teacherinfo by g_mailaddress"). Put a button next to the global field and attach a script like this (which the user clicks after having entered his/her mailaddress into the global field)

If (Is valid (Teacherinfo by g_mailaddress::mailaddress))

I have some more questions to help me clarify. This may take some time for me to understand - thanks so much for your help.

"Create a global field "g_mailaddress" and create a table occurence with a relationship between the global field and the field "mailaddress" in Teacherinfo (here: "Teacherinfo by g_mailaddress"). "

Right now, these two fields are in the same table, TeacherInfo. How do I create a relationship within a table? I thought (perhaps naively) that was only used between tables.

Thanks...

A relationship between two fields of the same table is called a self join. For this you need a second instance of the table (called a table occurence in Filemaker). Then join the two fields, as if they were from two different tables.

OK. A little earlier today I figured out the self-join - interesting. Now onto the next issue. Attaced is my script. I can't seem to make it look exactly like what you showed and I am at a very literal stage using this. Any suggestions?

Picture 1.pdf

DJuans,

I hate to complicate things -- and I'm not answering your current question -- but looking at your post it seems to me that your file structure really wants a third table (which, luckily, is easy in FM7). Basically, you have a record for each teacher, and a record for each service option. But it seems the purpose of your database is to track *which* teachers do *which* Service Choices, right?

If so, the real work would take place in a JOIN table, one where one field is TeacherID (or name), another is SvcID. You might call this table "SignUps" or something like that. That way, you have a many-many relationship. Each record in the middle table shows exactly one connection between a teacher and a Service Choice. (You could have an additional field indicating facts particular to this signup, such as date signed up, whetehr payment is required, whatever.)

Although I understand your interest began with a script, I suspect that most of what you want to "go to related record" for is stuff that you could display right there on the Signups layout, using related fields. Teacher phone number or school affiliation, whatever, can be placed on the layout for your signups table, and you can even *create* a new teacher record right there from the signups table if there isn't a corresponding teacher already in your teachers table... Do you know about setting up a relation with "Allow creation of related records" enabled?

That said, perhaps Detlev or others would follow through on your question... I hope I haven't confused things too much.

Regarding your script, Djuans

In the second row, the function "IsValid" just needs one parameter, a related field. The relationship for this related field has to be a self join between the global field, in which the user enters his/her mail address, and the actual E-Mail field in the table (the name of the relationship you used, is "Name": Does it join g_mailaddress and mailaddress?) Choose first the relationship/table occurence from the pop up menu at the top and then double click the field "mailaddress" in the list below. That's all.

In the third row you have to use the same relationship again as argument for the "Go To Related Record" script step (just choose it from the pop up menu below).

But in the fifth row, you don't need any relationship for the "Set Field" command. You just choose first the field, you want to put the value in (select "mailaddress") and then below the field, the value comes form (select "g_mailaddress"). Both times the pop up menu should read "currrent table".