How do I match one table to several tables?

[RichardAustin]

I'm creating a Task table to store all the tasks I need to do for several areas. I want one place to go to see what I need to do.

I have the tables: People, Organizations, Events, Projects, and Opportunities, all of which I want to create tasks for. I want to be able to view the tasks I need to do for all or any of these areas. I also want to jump from any Task back to the record that it is matched to within any of the originating tables.

The problem is I can't link more than one table to the Task table. If I try to use extra occurances of the Task table, I get threaded links back to organizations for which I have people matched (I'm matching people and organizations together, events and people, etc., so it looks like the task is for the organization when it isn't.

How can I have one task table to serve these different areas without causing links back to other tables or requiring ID's from every linked table to be input so I can even create a task.

Relational databases are new to me and what I thought would be easy is turning out to be hard. Any ideas, especially for keeping it simple?

[ESpringer]

Richard,

I hope this isn't too hurried to be helpful:

Given where you're struggling, it seems you're not using the power of multiple TABLE OCCURRENCES. From your "TaskIsRelatedTo..." field (whatever your name for that is), you can have multiple relationships connected. The only thing is that they can't be otherwise connected to each other. So, add a new table occurrence for each using the add-table tool in the Define Relationships diagramming area. (You can further connect tables "out" from this connection, so that people can belong to organizations *as seen from* tasks, but the organizations table here will not be the same occurrence of the organizations table from which you were doing data entry before.)

Simple version: Each task needs a field in which it is assigned to only one of four categories (Person, Organization, Project or Opportunity).

Then another field shows *which* person/organization/project/opportunity it is. You can use a value list for data entry if you actually superimpose FOUR versions of teh same field, and assign a different value list to each.

There is a relation between this field in the Tasks table and the field with name of each person/organization/project/opportunity (assuming the same name won't be both a person and an opportunity!). Again: use a NEW table occurence for each link.

Now, the fact that you already have links between people and organizations doesn't need to create any trouble, since you'll be using separate paths to your related tables.

Now, standard relationships can never involve a "decision" about *which* table to look at. Each relationship is to just one table. But a *script* can include a decision tree. If you have a Category field, your script can tell you that IF the category is people, go to the related record from People; otherwise IF the category is Organization, go to the related record from Organizations, etc.

You can show related data for any of the remote tables. As long as only ONE of the relationships at a time is valid, the fields can be superimposed on the layout without ever clashing in practice. (You can't generally overlap *portals*, but fields pointing directly to related data should get along fine.)

Ask more if this isn't clear for you.

[RichardAustin]

I tried this. In the person table I had:

person_ID,

Last Name,

organization=Organization (in org db)

(Organization links to person with just Organization=Org_Name since org_Names are unique)

Two occurrances of Task:

Task has match fields, Link_ID and Link_Name which link to person_ID and Last Name

Now I can create task from the person using a portal into task, and it works. BUT

In the second occurrance of Task (Task2) I had

Organization_ID and Org_Name linked to Link_ID and Link_Name

What happened was that If I created a task for a person, who was related to an organization, the task also showed up also in the task portal for the organization, which I don't want.

If I tried to create a task for an organization which had no person matched to it, it required a Person_ID to create the task and I don't want the task to be linked to a person within and organization, just the org itself. So I've been stumped.

The name of a person and an organization will always be unique, so I thought I didn't need any further fields to make a relationship unique.

[bruceR]

You really really REALLY don't want to base relationships on names. Names change or get spelled wrong.

[ESpringer]

Why don't you take a screenshot of your relationships diagram to post (or post your file)? I'm still suspecting you've somehow strung the paths along so as to force FileMaker to treat one or the other as a "join file"....

[RichardAustin]

I seem to be having a LOT of trouble understanding relationships. I changed the relationship to key on Org_ID which I created rather than using a name.

While I am in a record in the person database I want to view the organizations they are affiliated with. So I created a portal in the person database to look at organization names. When I click in the portal I see the names of possible organizations to select to affiliate this person with.

I have used Person_ID and Org_ID as match fields in both databases to link a person with an organization uniquely.

When I am in the person database and I select an organization in the portal to associate with this person. Instead of linking to the desired organization, it creates a NEW one using the name as the ID instead of using the existing organization.

I don't understand how this matching works and why it doesn't match but creates a new organization. I had to check "allow to create new organization" in the relationship setup in order to even have a box to click into. I thought it would only create a new one if it couldn't find one with the serial number to link to. What am I doing wrong?

[H.P.]

Person_ID cannot match with Org_ID

Assumming that one person can have only one organization (1 to many relationship), you can follow the steps below.... (you will need to create another table Person_Organization if it is multi-to-multi relationship)

In the person table, you should create a field called Org_ID and link the Org_ID field in the person table with the Org_ID field in the Organization table through the Relationships diagram.

Then, in the Person layout, put the Org_ID field from the Person table and put the organization name from the Organization table in that layout. Now create a value list from related table (in this case: organization) with the first field the ORG_ID and the second field Organization name.

Right click on the Org_ID field on the Person layout, format the field as value list (the value list hte list you created above). Now, when you select the organization from the list, it will match the person with that organization.

You should not use the portal to select the organization because you would need to use script to create the match.

[ESpringer]

The problem is that you're not putting the data about the *connection* in either table (each is using a portal to try to throw the data into the other table). *Some* table needs to *contain* the data about the connection between people and organizations.

If each person is always in exactly one (or zero) organizations, then you should just have an organization ID *field* within the people table (not a portal to organizations, but a field to hold info about which organization this person belongs to.

If people can be part of multiple organizations, or you need to show *roles* relative to organizations (or dates of joining, etc.), then you need to have a *join table* (highly recommended) in which there are the following fields: Person_ID; Organization_ID; Role; Date_Begin; Date_End

Then, if a person is a President of one organization, and just got on the board of another, your join table holds such information about the *relation* between the person and the various organizations.

[RichardAustin]

I do have org_ID and Person_ID in both the person table and the org table. People can be associated with more than one organization (with possibly different roles) and, organizations can obviously have more than one person I want to track which is why I want to have portals in both the person table and organization table so I can see all the affiliations. This same problem will arise when I want to work with the Tasks related to several tables probably 4 or 5 tables (including these two).

This join idea sounds like the missing info that I need. I don't know how to attach a file to this message. I don't see how to do that yet.

[RichardAustin]

I created a join table called PerOrgJoin which has Person_ID, Org_ID, and Role.

When I create a record in this table the first name and last name of the person with person_ID shows up in the view (I place a reference field for those in the view). But the organization name doesn't show up. I linked Org_ID to the organization table using a second occurrance of the PerOrgJoin table. It wouldn't let me do it with the first one. So I created another occurrance of the PerOrgJoin table.

I don't see how I can create records in the PerOrgJoin table from within the People or Org tables. I obviously want to create and view these affiliations where they will be most meaningful. Any ideas?

I'll include the database file.

[ESpringer]

I haven't done much to your file apart from change the relationships around to reflect join table structure between people and organizations, and to show how tasks table needs three separate occurrences to connect separately to People, Organizations, and Events.

See whether this works for you...

[ESpringer]

The next thing to do would be to work on your portals: add the Role field (from the Join table) to the Organizations-join portal for each Person record. Note, you *can* have fields all the way from Organizations in the portal that is assigned to the Join table.

Also, the PerOrgJoin table will be much more useful when you turn on "Allow creation of related records" in *both* directions along *both* paths (between it and People; and between it and Organizations). (Double-click the equals sign to modify the relationship criteria.)

Once that's done, you can use the portal to the Join table to add new organizations "on the fly" while you're working on a new person record; you can likewise hang out in the "Super Coop" organization's record (for example) and add lots of people to its portal. In order to make this work, make sure to put the related Name field from "People" directly into the portal from Organizations to the Join table. That way, when you add a new person, a new record appears not only in the join file, but "way over there" in the people table as well. (Meanwhile, if you want to add a person who's already in your people table, you should add it to the Join portal's own Person_ID field, using a value list of people's IDs (but which also displays values from the Name field.)

[RichardAustin]

I tried the database you suggested. It works somewhat. But when I create a task for person ID#3 it now also shows up under organization ID#3, which it should not. I want only the tasks for the different groups to show up where they were created. I added role to the portal for people and included first and last name in the tasks view so I could see who ID#3 is. But really I want a field in the task database to reflect either a person, organization, or other source and then be able to jump to the record that created it no matter which table that record is in.

I don't know how to correct the problem with person tasks spilling over onto organizations tasks. I also can't figure out how I would enter new tasks from within the views of people or organizations where I need to create these tasks.

I've included my modified database with this reply.

[ESpringer]

You asked:

When I look at task created for people I find I can drag the fields first name and last name onto the layout and their names will show up. But what if the task is for an organization?

Now when I look at the organizations layout, the tasks created for ID# 3 in the persons database now shows up under organizations #3. That task is for Person_ID #3 not for Organization_ID #3. So how can I delineate the task database to reflect back to the source where it was created and not stomp on other tables?

I'm not sure I follow the first question... Enter tasks for an organization through the organization's record layout (see below for looking at ALL tasks...)

Second question: you'll need to change the serial numbers for organizations and persons and events, so that they include not only a number but also a table-identifier. So, have the serial number for People be a text (not number) and have it auto-enter a serial number with the next value being P4, and have the next organization be O10, the next Event be O5 (whatever the next serial number would be, but put a letter before it).

Now, FM won't confuse the Link_ID generated from the people_ID with the Link_ID generated from the Organization_ID.

To look at ALL tasks, set up a separate network of relationships: this time with TASKS at the "hub" and three outward paths to the three other tables, each ID connected to the Link_ID. Now you can see what each task is connected to. (You can even see the NAME of the organization/person/event, by overlapping three fields, all with *transparent* background. Since your serial number (like P4) will point to only one such other table, the overlapping fields will always "play nice" and look like just one field.

[RichardAustin]

If you download the file, you'll see that putting the ID# and Name in the pop up menu to select the name of the related organization (in the people file) does not work correctly. The ID get selected in the Name field and a new erroneous record gets created. Not good.

There seems to be a pattern developing here. Whenever I want to match one table to more than one other table, it seems that I need to create a third table that includes a field that uniquely identifies each table I want to refer back to and otherwise to use only fields in that table that connect the tables together in a unique way.

It doesn't seem necessary to create another table to look at Tasks. I can see all the tasks in the existing table. The overlapping fields idea seems like a kind of kludge, but useable if there is no elegant way to place the source creator of the tasks in a single field such as a "source name" field.

[ESpringer]

I still have the file, but I'm not sure follow your first paragraph. You're talking about the People layout: the PerOrgJoin portal: the Org_ID field?

Well, you need to do two things differently, I think: Your value list should show OrgIDs (with names secondarily for data entry ease, but not as the key for the relationships), and your relationship would need to be defined to allow creation of related records (which perhaps you've already done).

Here, I've played with your portals from People and from Organizations...

Indeed, needing to make a join file is a *good* pattern. Whenever the relationships between As and Cs have any kind of depth (they have role names, levels of importance, durations, etc.) then you should have a B-table in which each connection is a separate record. You don't ever have to *look at* the B-table, but it does the many-to-many work neatly behind the scenes. And, indeed, the join table record is formulated to make pivotal use of your key fields, although your *portals* can show whatever fields you like from the file being "bridged" over through the join table.

You *could* create a calc to show the single name of the source of a task, actually. It would include doing a calc with the IsValid(Relationship) function. You could check around these forums for it... must run...

[RichardAustin]

I'm extremely frustrated at this point. When I view Affiliations in the portal of the People layout, any change to the organization doesn't change the affiliation, it changes the name of the organization in the organization table! New entries to roles in this layout creates new organizations with the same name as existing organizations. It TOTALLY screws up the data in the organization table!!! Bad news.

The only fix I can come up with is to never allow entry into the organization name from any other table than organizations itself. To select a new organization for this affiliation I have to do it with an Org_ID selection from the affiliations table, so I set a new value list to show Org_ID's along with the name. This works to create unique records in the affiliations table. BUT only as long as organization names are unique (which they won't be since the same organization can have different locations).

The same is true with selecting people to affiliate with organizations in the organization layout. If I key on ID with secondary last name. People with the same last name but different first names will NEVER show up in my selection pop up menu.

I need to be able to select a role and organization within the people view to create new affiliations in the affiliation table (formerly called PerOrgJoin), not screw up the organizations data. And I need to be able to include all organization locations in the list of selections.

Similarly, I need to be able to select from all people records to create an affiliation in the organization layout.

How can these simple objectives be accomplished?

The FileMaker manual is totally inadequate to demonstrate how to properly use the program.

I'm enclosing my latest incarnation of the file. Any ideas on how to make this work? It seems like it should be a simple process to link the creation of new records in both the People table and the Organizations table with the correct affiliations without unduly creating new records and to be able to select input from a complete list of possibilities.

[Fenton]

ESpringer's latest file has the PerOrgJoin relationships going from Organizations or People. That table is for unique "instances" of a 1 Organization and 1 Person combination. That is what happens when someone joins an organization. They can join more than one organization. An organization will have many people, some of whom also belong to other organizations. That's why a record is needed for each unique combo. These are NOT "people" records or "organization" records (though each is linked to one of them in each other table; that's what a "join" table does). It has only the IDs, it doesn't have or need the names; it can show them relationally.

I agree that FileMaker's documentation is somewhat thin. Their example files are mostly a bit simple. They have a lot of them, but they're mostly the same, structurally; for beginners. Just looking quickly, I see Task Managment has a "join" file example, the Assignments table. No real documentation on it however. It's definitely more than beginner level. It's somewhat similar to what you're doing. It's a good one.

What you are trying to do is really a little beyond what would be in the manual of any program. But you have something better, an example of your own file modified by an expert.

His Task Focus "Table Occurrence Group" is what is needed to easily get back to the other files from Tasks; because there are multiple paths "to" Tasks. The layouts for Tasks should belong to this TO, to give you a central path back to the other files. The "Tasks per Person" layout should be reassigned (incl. its fields), or deleted. Otherwise you'll need a separate set of layouts for each TO, or a global relationship back.

[Fenton]

I said to just have the Task Focus layout (assigned to that Table Occurrence), so you'd have a single central Tasks layout, so you could return reliably to the other files.

But, when coming from one of the other table's layout, you have to use the TO which is linked to it. So, from People you'd use Go To Related Record [ Show, Tasks per Person TO ]. The Task Focus TO is not reachable from the People layout (People TO).

But the Task Focus layout, Tasks List can still be specified as the layout to end up on. They are essentially the same records, so it doesn't matter (at least for this).

Then you can go back to any of the tables, using one of the TOs attached to the Task Focus TO, but specify a layout attached to the targetted base table (TO People 2 has no layout).

Ex.: Go To Related Record ["People 2"], Use layout "People"

You will have to check if the relationship is valid or not. The quickest way would be to check the 1st letter of the ID field (P, O, E).

You'd have to overlay the names (person, org., event); which is not hard, but makes Finds on one of those fields on this layout a bit awkward (though you shouldn't really be doing a lot of "name" Finds in this table anyway). Or give them a dedicated Find layout with all 3 names separate.

If you create separate layouts for each TO coming from the other tables (you only have one Tasks per Person right now), you have a problem. Because it does NOT filter out the Organization or Events Tasks. They're all there. But there is no connection from Tasks per Person to the Organization or Events tables. How will you show the unrelated names? Just leave them blank?

[i see ESpringer has already done the "Go" to Tasks for Organizations using this method. But I think I'd use "Show," and not open a new window; until someone (unspecified) builds window handling so you don't open one unnecessarily.]

ACLSdbFej.zip

[RichardAustin]

I understood most of what you said, but not the last paragraph. I couldn't find "Task Focus" or a "Table Occurrence Group" anywhere. I'm studying the examples provided with the program, but just looking at the tables doesn't explain why they were created or how they are used. It seems much more complicated than it "should" be.

In my last post of the database, I changed the name of the perorg table to Affiliations which is more descriptive.

[RichardAustin]

I guess I'm not looking at the same file now. I don't see a Task Focus layout. I took his file, (I think), and updated it with modifications of my own. So I will have to look again for his file and see if he did something I didn't see before.

[RichardAustin]

I missed one of his uploads and see that the second upload has a Task Focus occurrance in the Relationships definition. I'll have to look at that in detail. I don't understand it yet.

[RichardAustin]

Look at my ACLS.3 version. It's missing Ensmingers Task Table Occurrances, BUT it now links people to organization correctly and creates no false organizations. BUT it disallows a complete selection of people or organizations with the same name! Organizations can have more than one location and obviously people often have similar last names.

While I'm investigating the Task linking problems and suggestions, how can I resolve the person/organization selection problem?

[Fenton]

OK, I looked at yours. It has the relationships to the Task TO's OK. But it's missing the prefix on the IDs, so the relationships to the Tasks table are going to overlap, making its data show up inappropriately in all 3 originating tables at once.

"Table Occurrence Group" (TOG) is just a phrase someone came up with to describe these pretty much self-contained groups of linked Table Occurrences (TO)s, so you can talk about them, like we're trying to :-/

So, yours is also missing the table occurrence group of the Tasks table by itself, going back to the other tables (TO 2s). Without this group you're not going to be able show the names. Because you have 3 possible entities (things: People, Orgs, Events), all coming from different TOs. Only one of those can be directly connected to one of your existing Task TOs, because of the way it's attached to the layout. When you're on the layout attached to the Tasks from People TO, you cannot directly reach the Organization or Events tables.

If you don't care about showing the names, if Tasks is just a sub-table, not used by itself, just shown in portals, then you don't need the names, nor the Task Focus TOG.

The Task Focus TO is assigned to the Task List layout. I imagine ESpringer used the word "Focus" deliberately, saying "this is meant to be used as the table itself, a central focal point going out, not a target of relationships coming in." Right?

It's difficult to talk about version 7 in words, more so than 6.

You're also going to have to eventually face the problem of: Too many Tasks to enter in this "allow creation" portal. The solution is a button and a short script to create Tasks, which are sorted descending by date. Or bigger portals.

Choosing a person for an Organization is kind of backwards, IMHO, since you'll have very many people compared to organizations.

Both can be done, but choosing an organization(s) for a Person is easier and better. The other requires much more work, 'cause a drop-down list for people will probably be inadaquate eventually (but I don't really know how many people you're going to have).

[ESpringer]

Richard,

Perhaps I didn't put in enough time explaining the functionality of what I quickly implemented. My apologies. There are some folks on this forum who are able to dedicate apparently bottomless time and energy for assisting folks, and I wish I could emulate them better. Alas, half an explanation is sometimes worse than none at all, and I had to run off to work. I appreciate that Fenton has also been able to step in and see what I was attempting.

I'll try to follow through later as well. At any rate, the spirit of what I'm doing is not so much to give you a definitive version, but to give you something to poke around with to see how certain configurations work. Many of Fenton's suggestions are right on, and more sophisticated.

[ESpringer]

Richard,

When your join file works by connecting two ID fields (Person_ID and Org_ID), then you should understand that *once* the join record is created, you can use remote fields in your portal to reach all the way *into* the record for the organization/person at the other end of the path. This can be useful (compared to FM6, where you had to make sure your join table had its own "copy-cat" fields for any remote info you might conceivably want to display through portals, such as names), but also confusing if you think you're only editing the Affiliation record.

Suppose you make an Affiliation record to join O1 (named AllStars) and P1 (Pete), and you decide use your portal from Pete's home record to show that he has a shortstop Role in the O1 Organization. The join table itself only includes fields for O1, P1, and Shortstop, but the *portal* for this join table can *show* the name "behind" the O1 ID (namely, "AllStars"). You can have access to this field remotely through the portal. (FM7 programmers knew that people often want the qualitative fields "over there" rather than abstract ID key fields that help the join file do its work.) The point is: if Pete changes affiliation to the Bears, you should NOT edit the "AllStars" text in the portal to read "Bears", because then you're changing the name of the O1 team to which this join record is pointing!

Instead, you have to change the value in the Org_ID field in the Affiliations (join) table. On the version of the file I uploaded, The Org_ID field is *in* the portal but it is hidden until you click on the Org name, which is in front of the ID field, but opaquely covering it. It's a trick for FM developers: the field in front doesn't allow data entry (an option for Field Behavior), but it *shows* what it's most useful to see (the name, rather than the ID). Meanwhile, the field behind it does allow entry, so it "pops through" when the user clicks or tabs into the area. If you click in that area (where the organization name is), you should see a drop-down of Org_IDs from a value list , so that you can choose a different organization for Pete. (Though the value list shows the org names, that's just to help you confirm the Org_ID you want. Since this Org_ID field is the one within the Join table, changing it has no effect on your data in the organizations table; it just makes Pete show up in the Affiliations table as connected to one rather than the other.)

Is this much clear? (I'm worried we're tackling too many things at once...)

[RichardAustin]

You guys are a great help, both ESpringer and Fenton. I'm beginning to see a glimmer of light. What I understand about matching one table to several tables to date is this, to summarize, -- tell me if I'm wrong:

-That each match of one table to another is created through a relationship that must be unique and the relationship can ONLY involve TWO tables. The relationship is defined by matching fields that tie the tables together. Once tied, I have access to the fields in those tables (and to other tables related to them) to view, but I better not change any those fields, unless I want to change the original information where they came from.

-For additional relationships I need to use Table Occurrences which are a kind of copy which creates a different path to the table. I use those to increase the access to one table by many other tables. BUT if I do that, I need some unique marker that shows only what I want. Each linked table appears to need some kind of marker, in addition to it's ID field, to create a unique relationship.

For instance, in my Task table, I need a unique marker such as a character that precedes the serial number in every table that connects to it, or maybe better, a global variable in those tables and a MULTIPLE match that includes the ID plus a global field, such a P for People, or O for Organizations, or E for Events.

Then I can jump back to the originating record for the tasks in any of those tables by a simple script that checks for the correct global table identifier, jumps to that table, and then finds the ID.

Problems I'm still working on---

The pop up menus for selecting ID's have been a bit awkward. I know now that I must select using only ID's that are unique within a given table, BUT since people rarely know a person's or organization's ID's, FileMaker includes the ability to view a second field in the pop up. The problem is that an ID plus a name is also not unique, succeeding occurances of people or organizations with the same name are OMITTED from the pop up. I recently got around this with my People table by creating a calculated name that includes, Last, First and also with Organizations which includes Name, Address. BUT...

There is still a problem with selection. As I view the possible selections, seeing the ID followed by the calculated name, I can't jump to the record I want by typing letters to narrow my search. With 1000's of names of people and orgs, I want to be able to narrow the field and I don't see a simple way to do that.

So this is where I am. I'm still studying the example tables provided by FileMaker and I'm re-reading all the online documentation I can find, as well as re-reading the posts from ESpringer and Fenton. The learning path to be able to utilize this program for real-world applications seems to be not so clear and the methods to overcome inherent limitations seem to be a kind of techno-trickery based upon mind-crunching trial & error, and hard-won experience rather than clear initial conceptual design and documentation. Perhaps that is still true of all programming.

[ESpringer]

Richard,

Concatenating calc fields to show as much info as you need to pick out the organization you want in your value list was a good move (You'll notice I created a concat field for Last,First name in order to do something similar). As for not being able to type into a value list based on the second field: I agree that this is a frustrating limitation and one that FileMaker Inc could, in theory, fix. If/when this limitation becomes decisive for you, there are ways to make data-entry begin with a key word (say, put into a global field) and then using a value-list of IDs which share that key word.

But Oh! I have another idea!!! : The new auto-enter calc (with Replace) function will allow you to solve this problem! Make a concat field in your Organizations table, using your organization name, location, whatever else you need to identify it uniquely, AND END the calc with this: &" "&Org_ID. Now, define the Org_ID field to auto-enter a calculation. Specify that it accept RightWords(Org_ID,1). You'll have to REMOVE the check-box for "Do Not Replace" in the options window. Make your organizations value list work off this concat field. Now you can type into a single-field value list in your portal or wherever and get right to where you want alphabetically, and FM7 will still only keep the ID. (I'm happy because I hadn't even thought of this for my own solutions yet. Auto-enter replace calcs are GREAT!)

In general, I think you're very much on the right track. Now, in most people's situations, there is not so much a direct practical need for each table to have what you call "unique identifiers". You have a relatively unusual application in which multiple *kinds* of things have certainly exactly parallel relationships (You want tasks to work identically for things from different tables). However, it's still good practice to tweak all ID fields so that they carry some information about the table and/or record. You might someday check out some postings here about serial number and ID fields (using search function); simply putting O or P or E before FileMaker's own serial number is a simple approach compared to what the gurus do for big-time commercial databases.

I can see that you might experience some frustration jumping into FM7 with a plan for a complex project. Although there are some quirks to the program, most of what you now see as arbitrary does have some structural logic to it. The power of FM7 demands complexity, and sometimes we have to realize that *of course* there are certain kinds of ESP that FileMaker couldn't and shouldn't have. Often times what feels like a limitation to us is in fact a versatility that makes it useful for other purposes.

Cheers.

[Fenton]

You might want to look at an example file I recently posted, in the Sample files section for selecting from a filtered list. It is, IMHO, better than drop-down value lists, for anything having more than a few hundred entries. It's more intermediate than beginner, but it's not that complex, and you seem to be a quick study (after some initial "resistance" :-)

It is especially useful since the "sort by 2nd field" is broken right now (old-timers will remember it was similarly broken for a while in version 5 also; hopefully it will be fixed soon).

While you're there, look for my scripting Accounts & Privileges sample file. It makes it fairly painless to manage accounts.

I disagree somewhat with this statement:

"However, it's still good practice to tweak all ID fields so that they carry some information about the table and/or record. You might someday check out some postings here about serial number and ID fields (using search function); simply putting O or P or E before FileMaker's own serial number is a simple approach compared to what the gurus do for big-time commercial databases."

I agree with the way it was done, adding the letter (P, O, E), mostly because it works, and it's fairly simple.

However, from what I've learned, the opposite to the statement is true. At the highest levels of database "normalization" it is explicitly stated that the serial ID's should have NO informational content; they should be only an automatically incrementing serial ID, with no possiblilty of user interference.

If something like continuous numbers is needed they can be done in a separate field, which may look like it's the serial ID, but it's not. The real serial ID is hidden; and it is what is used in relationships. There is no problem with this dual method; because the secondary ID is just a data field, for searches, etc..

There have been long discussions about this, on other mailing lists. There are serious timing and record-locking considerations whenever you use any kind of "built-up" serial field (using something like the Max() function). Unless you build a foolproof locking mechanism it has been proved that it is possible to get duplicates, in a high-volume multi-user environment; which would NEVER happen with FileMaker's auto-enter serial number. In your normal office environment you're probably safe, but only probably.

You normally don't need information to know which ID you're dealing with, 'cause it's in its ID field. The Tasks type file is an unusual case. It is also relatively non-critical, somewhat temporary data. So we're not so worried.

The above doesn't apply so much to simply concatenating something onto a serial ID, for secondary uses. But I just had to rant :-|

[DanBrill]

Hey Elise,

This is a great idea! I've posted another workaround to this problem that involves using a lookup. ( http://www.fmforums.com/threads/showflat.php?Cat=0&Number=109852&an=0&page=0#109852 ) But this is much more direct. Well done!

The only problem that I've found with using a large number of the auto-replace fields is that it tends to slow down the creation of a new record. When a new record is created all of these auto-replace calcs have to calculate, and it causes a lag. But nothing too serious. I'm just afraid that imports will slow down as a result.

I've also adopted an entirely different approach to this problem that involves the use of the example plug in. I haven't had time to create an example to post to the forum yet, but I can describe it briefly.

Suppose I have records for people and a field called "employer". The user enters a business name into the employer field from a drop down list (populated with business names from another table), or just enters it directly. When the field changes, a script is launched. If the employer's name exists once and only once in the related database (checked using the Count function), the related id number is used. If more than one instance of the name exists, the user is presented with a popup window that lets them specify which organization they meant. If it doesn't exist at all, they can create a new record on the fly. This really speeds up data entry and makes for a clean user interface.

But your solution is really good for situations where you wouldn't want the user to create records on the fly. Great technique!

Thanks again,

Dan

[RichardAustin]

Fenton, I think you are on the right track. I am uncomfortable with the idea of using calculated (concatenated) fields and pop up menu lists as a selection mechanism for ID's. For one thing, it vastly increases the overhead for storage within a table and seems to undermine the very reason for a relational database i.e. minimal non-repetitious storage of data.

Further, the selection mechanism should proceed from general to particular. In my case of searching for people or organizations, I should start with the State, then Region, then City, and finally the first few characters of the last name and first name and maybe address. I may not need them all. Perhaps Region, Last, First, address would be sufficient. Just like using phone books.

I think generally, the need to find a human readable reference for ID selection (in order to isolate the correct record) must be based upon a find and sort which proceeds from general to particular using multiple keys.

Can this process be triggered by simply tabbing into an ID field? What interface mechanism would be ideally suited to trigger it? Tabbing into field, icon clicking, or something else?

Also,

What about my idea of using a "unique identifier" which is simply a global value within a table, to identify the source table for a task in the Task file - then using a multiple match (both ID and global field) to relate a task back from the Task table to the source table that created it. It seems much more elegant than using a character in the ID field itself. Does a multiple match create lots of processing overhead?

Your sample file was quite enlightening.

[ESpringer]

Dan,

Indeed, your solution is more thorough. I'll be using my version for a bibliographic database whose IDs get used as keys within many many other tables, so there's a *laziness* factor in my having a concat field + value list solution that requires very little setup (a simple auto-replace calc) before other tables can extract the right key ID: no globals, no extra relations for conditional value lists. (Or I *could* use these if list becomes unwieldy, but again get a better type-in based on name but yielding the ID.) But developer-laziness will rarely be the decisive factor for others' solutions. Further, the user of my solution has to know that if the sought-for item isn't in the list, it's necessary to tab-through to the other data fields to input. Since the text values are at the beginning, the user might assume the field is for a text-like value, but the field is only for the ID. I could have user enter field only through "Choose existing" button. Hm...

Thanks for mentioning the import delay issue, too. I hadn't thought of it since any imports I perform will arrive in "clean" condition, with only the ID. But of course you're right that FileMaker doesn't *know* that they're already A-OK! It will still have to "do its thing" and auto-replace calc every ID field with the RightWord(ID,1) that's already there (unless I tell it not to perform automatic operations on import).

[ESpringer]

Richard,

Ah, I see from your reactions that you're designing for a much larger batch of data than I had assumed. Although a contacts/tasks system usually has a relatively limited set of people, you're anticipating many duplicate names, including duplicate names for organizations. Every solution has a priority: responsiveness for data entry balance against storage optimization to some degree.

From a data-entry person's perspective, I think having to start with the state and winnow down to the town and organization or down to last and first name seems awkward; from a data-entry perspective, I'd love to have one global key field where one could put in a last name or a first name and see a value-list with quick visually-scannable lines: much faster than a set of conditional value lists. Often (in my experience) there's only one value anyway...

Just to articulate an extreme (but indeed stored-calc-intensive) version: one can have a global field match to a "Key field" which holds *various* often-unique values (last name, first name, phone, company, zip, whatever) separated by carriage return (functioning as a multi-key) so that the data-entry person can intelligently notice that even though the town name is hard to spell, the first name is a simple and rare one (Zeb), or that even though the organization might be under one of several acronyms (should it be called the Friends' Society, Society of Friends, Quaker Meeting?), I know it's in zip 13820, or if I can't read whether it's "Howard, Dean" or "Howard Dean", or if it's a Mr. Smith in New York City (Egads), but aha, the phone is right on my caller ID You get the idea ... Up pops a value list with clear concatenated strings for ALL items in zip 13820 (or ALL people with that phone number) from which to confirm the one in question or glance and see that it's not there (and the ID is deposited straightaway in the key field if it is there). If *I'm* doing data entry, this makes me happy! Whether this kind of thing is especially useful depends on who's working the system, what they're looking at (or listening to), etc.

(Another application: With books, sometimes one has the ISBN, sometimes not. Sometimes the author is too prolific to bother, sometimes not. Sometimes the title has a hundred editions, but the translator's name is bizarre.)

At any rate, I certainly respect different priorities!

[Fenton]

I agree with ESpringer that using a "browse" across several categories in not the way to quickly filter down to a person whose name you know. I've used my portal filter on a file of 10,000 with no problem at all. Usually only 3 or 4 letters are needed, to filter the list to portal-visible 10-20 possible matches. Some names, like "Jones" or "Smith" can be a nuisance however.

I thought of a way to deal with that (I actually did it somewhere, but it would take me a while to find it). Which is to include an optional First Name filter field also. You'd seldom use it (and not in the Tab Order), but it would be there for those "Jones" names.

It's a little tricky to set up the left side key. You want it to concatenate when both globals have values, but not when they don't; FileMaker 7 doesn't like trailing spaces in keys (6 didn't care). My name calculation (right hand side) would look like this:

J

Jo

Jon

Jone

Jones

J F

Jo F

Jon F

Jone F

Jones F

You could go on. But I bet that just adding that first letter would really cut it down. The left hand key would check for both fields:

_gLast & Case (not IsEmpty(_gFirst), " " & _gFirst, "")

Another little thing with this is that the right hand key is limited to a known number of characters. I think 6 is enough for "_gLast." The optional "_gFirst" is only 1. Then validate it for that, with a message. Because typing more letters is going to make it fail.

With Organizations and Companies (and Books) you have a slightly different problem, which is that you probably need to do the first and second, maybe even third words in the name. Because with many names you just don't know which is the one that identifies it. Use the MiddleWords() function. Lots of Organizations might begin with "Organization of", or "Association of." It's probably a good idea to remove the words like that, and "of" or "The" or "the" (Substitute IS case-sensitive); before setting up the key (enclose the MiddleWords() calc). But don't remove any word that someone might want to look for.

Lastly, you obviously want your filtered portal to be sorted.

[ESpringer]

Fenton,

Could you say more about why your kind of RH key would fail for more than 6 characters? I wonder whether I've been missing something relevant; I haven't noticed any problem with longer strings in my global-to-multikey lists.

Indeed, with Books, though I have the first five words in the key (omitting particles and certain common words), I also include other key strings in case title is commonplace. Having only ONE global that essentially catches values across multiple RH fields is very convenient, since there's no extra mousing or tabbing around associated with changing one's mind about how to pull up the record.

I'm still working with a value-list rather than a portal, though... partly because the quick-list method can be used even in table view this way. On the downside, it requires typing a whole word correctly. So I can appreciate your solution as best for many applications.

Thanks for your insights about IDs, by the way. I certainly didn't mean to imply that the ID ought to carry information from the table's *data*, though I thought I had read convincing posts to the effect that an alphanumeric auto-calc string including some random digits but also some codification of the creation date/time is ideal for sensitive applications. But I was reaching beyond my practical understanding; I do like a table-prefix because it helps me troubleshoot a bit more efficiently... Anyway, for dialogue's sake, it was this CaptKurt post kind of thing I was thinking of:

http://www.fmforums.com/threads/showflat.php?Cat=0&Number=100135&page=0&vc=1#Post100135

But on a less technical note, Fenton, were you not in on the story behind Queue's having acquired the title "Errant Pronoun Propagation Annihilator Extraordinaire" (recently edited back to Pooh-Bah)? ; )