Comparing text between fields: a script?

[genostar]

Hi all, new to FMP. I'm creating a database that contains information on a group of research publications, each of which has various authors. Among these authors are some researchers in our local group.

What I've done is create two fields:

- an Authors field, which contains ALL the authors for each publication, whether the authors are in our local group or not. This is simply a editable text field.

- a Local Researchers field, which is a pop-up list whose value list contains all the names of our local researchers (which are stored in another table altogether).

Here's what I want to do. Currently, when one enters data for a publication, one types in the authors into the Authors field, and then one picks in a pop-up list (which contains all the names of our local researchers) who is in the Authors field. This is tedious but, more importantly, there's a huge room for error: what if the data entry-person simply overlooks one of our local researchers while glancing at the Authors field?

Is there a way to use a script of some sort to pop up an alert window that says, "Hey, you just typed in an author that is local to your group. Wanna add him/her into the Local Researchers field?" All I need is a notification; I don't need FMP to pick the local researchers for me.

I've been successful in ScriptMaker with coming up with two carriage-return-delineated lists, one which contains all the words in the Authors field and another which is a list of all the last names' of local researchers. What I'm having trouble figuring out is, is there a way to have ScriptMaker compare these two lists and then see if any of the words in both lists MATCH UP, and then tells me whether or not there's a match?

E.g.,

Say I have some publication titled "How to eat cheese and G4 stars." The authors of this publication are: "Mickey Mouse, Donald Duck, Bill Clinton, Charles Manson, and Tony Blair." Let's also say that we have five researchers in our local group: "John Doe, Donald Duck, Carol Jones, Mickey Mouse, and Mark Smith." So, among the authors of the publication, two are local to our group.

After I'm done typing into the Authors field the text "Mickey Mouse, Donald Duck, Bill Clinton, Charles Manson, and Tony Blair," I want FMP to come up with an alert window that says,

"This publication was authored by the following Local Researchers:

Donald Duck

Mickey Mouse,"

or to simply know that any local researchers authored the publication.

(N.B., To make this automatic, i.e. to come up with alert window immediately after I enter the text, I'll probably be utilizing one of those plug-ins out there that can do this, so that won't be a problem.)

Thanks for the help!

[Fitch]

Try the Position or PatternCount function, but the only way I can think of is to loop through and test on each of your local authors. I guess that won't be too slow if you don't have more than ten or twenty.

[ESpringer]

Uy -- I would structure things entirely differently.

Especially when you have multiple authors for each article, I think you want not an authors' field, but an authors' TABLE, and a join file between authors and articles: it's a many-many relationship. This seems awkward at first, but FM7 makes it easy. You'll be able to TAG authors as local in that authors' table, as well as include all sorts of other stable or cumulative info on authors (such as how many articles authored, institutional affiliation, whatever). The join table is also a useful place to note, say, that someone was a LEAD author for this piece, or that someone was a translator here, whatever.

The end user need not know that there's a complex table structure here. The authors' section of the articles data entry layout is a simple portal into the join file, uses a pop-up of available authors, and of cours you have "allow creation of related records" enabled in the relationship definition.

Your relationship diagram has these 4 table-occurrences:

authors1

[ESpringer]

Looking at your message again, it seems you're a relative beginner, and perhaps haven't played with relationships yet. If so, don't be intimidated! They're here to make your life easier! I should clarify that in the structure I suggest, the tables Articles (or Publications) and Auth_Artic_Join are the crucial ones.

Probably you want to have a ArticleID for the "key field" connecting articles and "authorings" (that's my way of keeping the topic of the join file straight). If "allow creation of related records" is on, and the key match between Articles and Auth_Artic_Join is ArticleID, then when you type a name into the portal from a record in the Articles table, FileMaker will know you want a record connecting THAT name to THAT article. (The reason I recommend an articleID -- which can be any unique serial number which works "behind the scenes" -- is that this way you can fix a typo in the publication structure without losing the link between the article and any information related to it -- who the authors are, etc.)

The join table, once you've done all the data entry (or importing of data) will include one record for every *connection* between an author and a publication. So, if you have 10 articles, with an average of 4 authors each, there will be 40 join records. But, as I said before, you generally never look directly at the join table. It does the work behind the scenes.

The Authors table need not include every author, but only those for which you want to store additioal details -- such as a "Local" tag.

If you're not clear on what I mean by a "tag": it's a numeric field designed to hold 1 or 0, where 1 means "yes". It's useful in many ways. In FM6 folks always used to make a field called Constant for various relations, and it's less urgent now, but still useful in connected ANY join record (where the global constant is a numeric field with 1 entered) to EACH author whose Local? tag is set to 1.

I'll stop now, and let you ask questions...

[cellmaker]

Geno -

As I understand it, you've got a table with publications (pubs) that holds information about the articles, including the authors (pubs:authorname). You've also got a second table (researchers) that holds information about your local researchers, including their names (researchers:lname, researchers:fname), from which you can create a composite field c_fullname (lname & fname), for example..

In FM, you can set up what is called a multikey relationship, which means that you can relate a list of values in one field with another field. If anything in the list matches what's in the second field, the fields will "link."

So, under your current scenario, you would relate pubs:authorname to researchers:c_fullname.

You can create a calculation field in pubs that says, If(count(researchers:c_fullname)>0,"Hey, there's a match","") and then put that calc field on your pubs layout.

This set-up may be less than ideal, however, because the relationship is going to look for exact matches. If someone puts in an author as "Bobby O'Hara" and c_fullname in your researchers file is "O'Hara Bobby", they aren't going to match. This is a bit of a problem, so you'll need some strict conventions on how name informatios is encoded.

One possible solution is to make a related authors table for pubs, and split up the author names in the same way you split up your researcher names. Then you have much more control over how the fields are related.

I'm attaching an example file that shows the two different methods for your case.

Pubs.fp7.zip

[genostar]

Okay, ESpringer, I don't know if you're still reading this post, but after having tried a number of other methods I'd like to return to yours. The reason why I strayed away at the beginning is because I am a novice and have little experience with relationships between tables.

I'm still confused. Can you maybe break it down for me a bit more? What tables am I creating exactly? What fields are in each table? How am I relating each of these tables: which fields to which fields? How am I supposed to connect all of this to the articles/publications table?

Thank'e much!

Geno

[-Queue-]

If you want a real challenge someday, you could use the logic from this file and incorporate it into your solution.

Okay, okay, so it's a shameless plug. But it took me a long time and I'm quite proud of it, so enh!

[ESpringer]

You need three tables. I'll call them Articles, Authors, and Join. The fields and relations might go as follows:

Articles: