Select Related by Pop-up List

[tpoyner]

This may be simple but I can't seem to figure it out. If I have two files, say "user" and "service". I've created a relationship on "userid"--a field that I've placed in both files. I'd like to be able to associate (relate) a particular user in the user database file with a specific service record. Rather than having to type the user id in the service file to relate the two files, I'd rather have a pop-up list in the service file that allows you to select a user from a pop-up list. Even though the selection is made on a user name field, I want the relationship to be set on userid so that, if the user name changes, the relationship between this particular service record and the user id stays linked. I'm just not sure how to create a pop-up list to select the correct user from within the service file/layout--and to make sure that the userid field in the service record gets set automatically to match the userid in the user file based upon the selection in the pop-up list.

[BobWeaver]

Don't base the value list on the relationship. You can set up your value list to display values from any field (either in your current file or any other file). Select the UserID field from whichever file has both the UserID and the UserName, then where it gives you the option to display values from a second field, select the UserName. When you do this both the UserID and UserName will appear together in the popup menu.

[tpoyner]

Thanks for your help. I got the first part of this to work. In the User file I created a value list and used "Values from Field" where I took your advise and DID NOT use the "only related values" option, but instead used the "all values from" the "user" file and selected userid in first section (left), selected the "also display values from" checkbox and selected usernme in the second section (right). Then I went into the Service file, went into Layout mode, selected the userid field in this file, selected Field Format from the Format menu and told FileMaker to use a Pop-up list and selected the User List that I created in the User file to be the source of the value list. I test it out and it works. In Browse mode, when I click on the Userid field in the Service file I get a pop list that shows both the user name and userid from the User file's value list. I can then select the user that I want and it inserts the correct userid value in the userid field. GREAT! Works like a charm. NOW, I need one more thing. I also have a "usernme" (user name) field in both files. I'd like the username field in the Service file to automatically insert the correct user based upon the value in the userid field. I'm assuming that I need some sort of script to do this? I'm also assuming that its the "set field()" script command that is at the center of this? (am I on the right track so far?). I set this up but the usernme field doesn't update after I select something in the userid pop-up (in the Services file). I'm guessing that what I'm missing is the logic to tell the script when to run? I'm not sure that best way to do this.

[BobWeaver]

You can do it two ways which have slightly different results.

1. You can simply display the related user name field on the service layout without ever having to create a user name field in the service file. To do this, (in layout mode) place a field onto the layout and when the dialog comes up to select the field name, pick the userid relationship from the popup menu at the top and then the user name field. Or..

2. You can set the options of the user name field (in the define field dialog) to auto-enter value looked up from another file. You select the userid relationship, and then the user name field.

The difference between the two methods is what happens if you ever change the user name for a particular userID in the user file. The change will be immediately reflected in the service file if you used method 1. If you used method 2, the user name in existing records will not change, but the new name will be used in subsequently created records.

Method 1 is recommended unless you need to ensure that existing records aren't modified.

Method 2 is usually used only for things like looking up prices from a price list to put in a sales order. That way, changes to the price list will not affect old orders, only new ones.

[tpoyner]

Again, thanks for your reply. After I posted the last reply I actually figured out the first option you presented (insert a related field into the layout) and it worked great. The main reason that I might want an actual usernme field in the Service file that auto-updates if the usernme field changes in the User file, is based upon what I understand to be a limitation with FileMaker as compared to something like 4D. Let's say that I have another file that is related to the Service file and I want the usernme info to show up in this file as well. In order to do this, my understanding is that I would need an actual usernme field in the Service file (I don't think that I can use the usernme field that is two relationships/files away from the User file, right?). I'm assuming that I can probably trick this all into working by using calculation fields?

[BobWeaver]

That's true, related field values only work across one relationship. Once you have the usernme field in the service file, you can directly access it from the new related file without any calculations (using the second method I described).

Another option is to have a User ID relationship in the new file back to the user file so that you can display any user info in the new file. This means that the new file needs to have a User ID field that's set to the same value in as the related record in the service file. You can use "Set Field" to do this.

[pedrofp]

To make a relationship work across three files try this. In the middle file create a calculation field where the calculation is simply the desired field in the 1st master file. In the second related file create a relationship to that field. This should work so long as the match fields at both ends are the same type and the intermediate field gives a result of that type.

Another relationship trick is to create in each of two files a calculation field with the calculation being "0" [sans quotes] and create a relationship through those fields. Very usefull for accessing globals in another file and a couple of other things.