Newbie Question - Foreign Keys / Lookup Fields

[jasongriffing]

Hi all,

I am brand new to Filemaker, and this is my first post on the forum. I am trying to wrap my head around what is most likely a simple concept. In brief, I run a subcontracting firm and I am working on a simple Work Order solution. The tables in question follow:

Clients

Projects

DWO (aka Daily Work Orders)

Each client can have 1 or Many projects

Each Project can have 1 or Many DWO's

I have created a Foreign Key in the Projects table (fk_Client_ID)

I have also created a Foreign Key in the DWO Table (fk_Project_ID)

I have drawn the corresponding relationships.

Now I am trying to create a Layout on which the user can create a new project. On the so-called "Project Detail" layout, I have created a drop-down value list which is populating with data from the ClientLastName field (obviously in the client table). I also have a field on the "Project Detail" layout for the Foreign Key, which in this case is fk_Client_ID, mostly for testing purposes. When I manually enter a Client_ID into that field, I see the value list select the corresponding client automatically. However, I actually need this to work the other way around. So, for example, when my receptionist is creating a new project, she can simply type the client's last name in the field, and the record will be created and associated with the proper Client_ID.

This is where I'm a bit stuck, and am trying to wrap my head around how to approach it. Any advice? I've scoured around a bit, but being so new to the concept, I'm not really sure what to search for in order to find the answer.

Thanks so much,

JG

[bcooney]

How about a portal of projects on the client form with allow create allowed on the relationship? each portal row could have a "detail" button that gtrr project using the project detail layout.

[jasongriffing]

Thanks so much for the reply, sorry it has taken me so long to get back to you. I have actually been researching portals recently for another application within this same database. I haven't delved much into scripting, but I understand the basics of what GTRR does. However, from my understanding of it at least, this would seem to apply more aptly to the layout once the project has already been created and associated with the proper clientID. So for example, I could open up my client layout and see all records of projects related to that client, right?

What I am trying to accomplish is creating the actual layout on which the project record itself will be created. This is no doubt a VERY simple task, and I have found an approach that seems to work, I just wonder if I'm doing it "right".

Here's where I'm a little confused. When I have two tables that are related (i.e. Clients and Projects), I draw the necessary relationship between the ClientTable::PK_Client_ID and the ProjectTable::FK_Client_ID. Simple enough. But the ID itself is just a numerical value. Let's say I have 100 clients, and my receptionist sits down to create a new project. Looking at a list of 100 different Client_ID numbers is not very useful. I need here to be able to assign the Project to the correct Client_ID in a manner that is more user friendly.

So,

On the Project Detail layout, I have created a field for the FK_ClientID. I have formatted that field as a drop down menu which uses a value list that I am calling "ClientID_WithLastName". The fields specified for this drop down list are:

Field One: ClientTable::PK_ClientID

Field Two: ClientTable::ClientLastName

I have then checked the box for "Show Values only from second field"

Now, from Browse mode, I can create a new record, click on my drop down list, and I see a list of client last names. This seems to work fine, but is there an easier way? Am I just over thinking is?

Also, I've been wondering about a situation where I may have more than one client with the same last name. So I thought I could insert a calculated value in the second field of my drop down, with the Calculated value being a simple concatenation of Client First Name and Last Name. This however is causing funny behaviour. Can I use calculated values in a drop down menu?

I hope these questions make sense. I really appreciate any advice you can offer.

Thanks again,

JG

[bcooney]

Wow, you're doing really well on your own.

If you create the project starting from the client form, you "know" the clientID and can avoid the need to select it from a popup menu or drop down list. That is why I suggest you create the project in a portal on the client form set to allow create. This will populate the foreign ClientID for you. Then a button on the portal row with gtrr will take the user to the project form in order to fill in more details.

It's helpful to understand some key facts about popup menus and drop down lists and two-field value lists:

1. Popup menus display the second field and hide the key field. Nice, but longer than 20 choices and they're fairly unusable. I move on to popup window selection techniques at this point. The forum has many demos I've posted. Search popup selector. here's one

2. Drop down lists display nicely, but show the key field after selection. Actually, I never use this. I always store keys, not text values.

3. You can use a concantenated value as your second field in a two field value list, but the value must be capable of being indexed, that is, it cannot be an unstored value. What odd behavior are you seeing? Name_Full (=Name_First & " " & Name_Last) is a common concatenated value for use as a second field in a value list defined to have an ID as the first value. However, as you point out, two Jim Smiths will be a problem. You can add another value to the second field's definition, but at this point a popup selector is probably better (a nice list view with a find, etc.)

Another way to create a child starting from the parent is scripted like this:

Set ($parentID, _kP_ParentID)

Go to Layout (ChildLayout)

New Record

Set (_kF_ParentID; $parentID)

[jasongriffing]

Thanks again for your assistance! Sorry it has taken me so long to respond to this. I took a week of vacation, which was followed by a week of madness getting caught up at the office. I'm trying to get back in gear here.

In response to point 3 above (regarding "funny behavior when using a concatenated value) here's what happens:

- First, I have created a value in my Client table called ClientFullName (=ClientFirstName & " " & ClientLastName

- Next, I have created a drop down list on my Project Layout for FK_Client_ID, which populates using a Value List called ClientID_WithFullName

- Values for this drop list:

- Fleld 1 - PK_ClientID

- Field 2 - ClientFullName

However, when I test it in browse mode it only displays the first record in the drop-down list (?). However, if I go back into "specify fields" in the value list, and switch from the concatenated value (ClientFullName), to a "normal" value (simply ClientLastName), the drop down list works fine (i.e. displays all clients in the list when I click it in browse mode)

From your response thus far, it sounds like I may be better off pursuing this a little differently. However, I am still curious as to why I can't get my drop list to display a concatenated value properly.

Any thoughts?