1 Reply Latest reply on Sep 12, 2014 8:55 AM by philmodjunk

    Value list that pulls from two unrelated fields



      Value list that pulls from two unrelated fields



      I have database that is tracking IT assets (computers, printers, etc.) I have another database that tracks personnel (employee names, address, etc.). Inside my database for IT assets I have imported the table from the personnel database as a data source, I also have created a table in the IT assets database called "Generic Users". In the assets table I have a drop-down value list on the "assigned to" field that pulls all employee names from the personnel database, however I want to add all the users in the "Generic Users" table to that value list so that the value list show everything from Personnel and everything from Generic Users.

      The purpose of Generic Users is to hold "users" that aren't real people (for example "network printer", "inventory" etc.) I do not want add these faux users to the personnel database, that just seems sloppy to me. I don't want to have to hide these "users" from others that might use the personnel database.

      The other "solution" i can think of doesn't seem much better to me, which would  be to add a global "constant" field to the the Generic Users and to the personnel table and relate the two tables, then use the "also display values from a second (related) field" option. This seems like a band-aid hack to me.

      Please tell me there is a better way!

        • 1. Re: Value list that pulls from two unrelated fields

          Do it this way (this is a method adapted from "Adventures in FileMaking #2")

          Define a calculation field in Personnel as:

          List ( PersonnelName ; " Network Printer" ; " inventory" ; "-" ) you can add as many "generic user" entries as you need in any order.

          You'll also need to "invert" the standard beginner level practice of using an ID field for "field 1" and the name field for "field 2" in your value list, You'll need to use PersonnelName as Field 1 and look up the ID number with scripted support to handle duplicate names. This is also demonstrated in AIF #2. (and using special values in combination with such a set up is also demonstrated.)

          This value list will list the special users first, then a dividing line (the "-" value does that) , then personnel. The extra space at the start of each special value sorts it ahead of the value divider in the list.

          Your script that checks for duplicate names also checks for these special values and does what is needed to assign the object to the specified generic user. I don't know the structure of your database, but I'd probably put either a special value in the ID field or leave it blank and then put the specified special user name in a text field left blank when the asset is assigned to a person.

          I would not link assets to personnel by their name. Names are not unique, people change their names (and not just due to marital status changes) and it easy to mistype a name, link records by it and then discover that you need to make a change. All of those issues can create problems that foul up the connections between assets and personnel.

          Here's a download link to the free AIF#2 file: https://www.dropbox.com/s/9q0iia6lw34jbtd/Adventure%202%20EVS.fmp12?dl=0

          Caulkins Consulting, Home of Adventures In FileMaking