5 Replies Latest reply on Feb 13, 2012 9:39 PM by philmodjunk

    Newb question and some matter of jargon

    V

      Title

      Newb question and some matter of jargon

      Post

      Heyas all,

      I've been tasked with building an inventory database and I'm having many challenges.  I've muddled my way through most of them with my book and 'Net searches, but this one is being a pest.  I think the problem is largely one of jargon, i.e. I do not know how to phrase what I'm trying to do.  To the point, I'm asking for help on two fronts with this email; I'm asking for some help in getting down what I need to do, and I"m asking for some input on the correct way to describe my problem so I don't have to resort to bugging you folks when I have a similar challenge in the future.  That said:

      I have two SQL tables connected to Filemaker via ODBC, for the sake of brevity, lets call them Computer and People.  I have a third table in Filemaker called Inventory.  I can create a new record in Computer and assign it to a Person via a lookup dropdown menu field I have named Computer::computer_assignee which pulls the values of Person::FullName.

      What I need to do is create a report that will do the following:

      When a Person::FullName is selected from a dropdown, the fields computer_make, computer_model, computer_type will automagically populate with the vaules of the computer assigned to them.

       

      I've been working on this particular aspect for hours, and I just can't seem to figure it out.  Any help is appreciated.

      Thanks, very much.

        • 1. Re: Newb question and some matter of jargon
          V

          Just as a point of clarity, I tried the sample database with Filemaker11, and I thought it was fine, but it was not satisfactory for my supervisors, they wanted different functionality.  I tried modifying the starter database, to no avail, so I've had to start from scratch.

          Thanks again.

          • 2. Re: Newb question and some matter of jargon
            philmodjunk

            First issue is that you appear to be matching records by name. You can easily get two people with the same name and people also change their names.

            You need a field in People that uniquely identifies each person and will never change. In a FileMaker table, a serial number field is the usual choice for this. Since this comes from an external source, you may have additional options for defining such a primary key. I'll call that field People::PrimaryKey.

            This allows you to define these relationships:

            Inventory::_fk_PeopleID = People::PrimaryKey

            People::_fk_ComputerID = Computers::__pk_ComputerID

            This assumes that a people record can be assigned to only one computer. If a person can be assigned to multiple records in computer, a different relationship is needed.

            Now your fields can use looked up value settings in field options. (Double click field definition in Manage | Database | Fields and then click the auto-enter tab.) This copies the data into the fields. You can also discard these fields and just add the corresponding fields from Computer to your layout and they will also display this data.

            You then set up your value list to list People::PrimaryKey as field1 and People::FullName as field2 so that you can select a person by name, but the primaryKey value is what will be entered into the field.

            • 3. Re: Newb question and some matter of jargon
              V

              Thanks for the response, Phil.  Much appreciated. 

              I've sort of started to figure it out, but I dont' see an 'Auto-Enter' tab under Fields.  

              I think I understand your reference to the different relationships and how you're using the field definitions.  Thanks for taking the time to offer insight.

              • 4. Re: Newb question and some matter of jargon
                V

                Umm.. apparently I'm blind or just dumb.. Referring to the auto-enter tab... Duh.  Sorry 'bout that.

                • 5. Re: Newb question and some matter of jargon
                  philmodjunk

                  Quoting from my last post: (Double click field definition in Manage | Database | Fields and then click the auto-enter tab.)

                  Double clicking the field definition in this window opens the field options dialog. The auto-enter tab is found in this dialog. You can also just click the field's definition and then click the options button found in the bottom right corner of this window.