Am having difficulty wrapping my brain around this one:

I have 300 newspapers uploading files to a central archive. The archive sends me a manifest of filenames each day telling me what files they received. I import this manifest into my database and through calculation fields pull apart the filename so i can keep track of what paper sent their files, and when.

For example: the Fictionville Fiction uploads a file called "fifi_20050909_A32_32.pdf" and I can pull out the code "fifi" and have a relationship to my newspaper database that says code "fifi" = the Fictionville Fiction

The One-to-Many relationship is where the field "NewspaperCode" in the Newspaper Table = the field "FirstWord" in the Filename Table

However,for complicated reasons beyond my control, some papers have two codes, the same paper can also upload "ficfic_20050909_A32_32.pdf" and still be accepted by the archive.

How can I set up a relationship between the filename table and the newspaper table where the field "FirstWord"(in filename table) could match "NewspaperCode" or "NewspaperCodeNew" (in Newspaper table)?

And what if (mutter, mutter) a newspaper were to change its code. If I were to change the reference in the Newspaper table, I would lose the connection between that paper and all it's previously uploaded files.

There must be some different way to approach this.

If the newspaper table had a serial number assigned to each paper instead of a code .... how can I have the filename table check different codes to make the relationship to the serial number?

should there be some sort of table in the middle? One that has a listing of all the possible codes and the serial numbers that they relate to?

JA

Edited November 5, 200520 yr by Guest

I believe all you need to do is to enter all possible codes for a newspaper into NewspaperCode (in Newspaper table), separated by a carriage return.

Other than that, I don't quite see how assigning serial numbers would help here, when you can't make the Fictionville Fiction stick to a single code. If a newspaper changes its code, you simply add that code to the list in NewspaperCode, and the relationship will see old files as well as new. The crucial point here is to keep the codes unique.

it's beautiful. it works.

is there a name for this... something that I can look up in the book. I'd like to understand more about how/why it works and how else to apply it.

It seems counter-intuitive to put what seems like two values into one cell.

JA

I don't think there's an official name for this technique. According to FMP help (Working with related tables and files > About relationships > About match fields for relationships), it "is sometimes called a multi-key field or complex key field". I have also seen "multi-line key" being used.

Your instinct is very correct to bulk at putting more than one fact into one field. A strictly proper solution would have a joining table in between, where each newspaper-code pair would be a separate record.

But here it would only add unnecessary complexity: your codes are not entity in your solution; you don't manage codes, you manage newspapers and articles. The codes are merely a tool to select the articles belonging to a paper. And since a single article can have only one code, this isn't a true many-to-many relationship anyway.