6 Replies Latest reply on Jun 2, 2012 7:45 AM by GuyStevens

    Indexing

    tays01s

      Title

      Indexing

      Post

      Struggling a bit (lot!) grasping relationships. I need a drop-down list to pull up people listed by a calculation field. So I link an serial ID to the calc field. However, how do I index those people automatically so that each time a new person (calc as: lastname&firstname&dob&sex) has their own index number, without a user having to enter it.

        • 1. Re: Indexing
          GuyStevens

          how do I index those people automatically so that each time a new person (calc as: lastname&firstname&dob&sex) has their own index number, without a user having to enter it.

          Indexing is really something you don't need to worry about. It's something that Filemaker does to make finds happen faster.

          What I think you need to do is create a serial number for every person that is created.

          So I link an serial ID to the calc field

          Whoops, you shouldn't do that.

          Here's what you want:

          I assume you have two tables, the first table being a table of people. And on the second table you want to select one person from a dropdown list that shows the Lastname firstname dob and sex.

          This is what you want for your first table:

          TblPeople
          ID  -  A number field with auto enter serial number enabled
          FirstName  -  text field
          LastName  -  text field
          DOB  -  Date field
          Sex  -  text field
          c_FullNameDOBSex   -  A calculation field set to result in text. The calculation is: FirstName & " " & LastName & " " & DOB & " " & Sex

          The & " " & makes it so that there is a space between the values.

          SecondTable
          ID  -  A number field with auto enter serial number enabled
          PeopleIdFk  - A number field
          ...

          The PeopleIdFk field wil store the ID of the people table you want to link this record to. This is where the selected persons ID will be stored.

          Now you create a relationship from the ID in the People table to the PeopleIdFk in the SecondTable

          TblPeople::ID-----SecondTable::PeopleIdFk

          Now in a layout based on the SecondTable you set up the PeopleIdFk field to be a dropdown.

          In the Valuelist settings you base this dropdown on the People table and for the first field you select the ID from the people table, for the second field you select the Calculation field ( c_FullNameDOBSex )

          Then at the bottom you select "show only values from second field"

          Now your dropdown list should show the calculated names of everyone in the People table. And when you select one this persons ID will show up in the PeopleIdFk field.

          The annoying thing is that, once you selected a person you only see this persons ID, that might be a bit annoying, but you can fix that by putting the "c_FullNameDOBSex" field from TblPeople on the layout based on "SecondTable". Once you select a person, this persons name will show up.

          If you have any problems let me know!

          • 2. Re: Indexing
            GuyStevens

            This is actually a demo file that might be helpfull:

            http://dl.dropbox.com/u/18099008/Demo_Files/TwoTablesLinked.fp7

            It also shows a technique to hide the ID number.

            • 3. Re: Indexing
              tays01s

              Thanks,

              Probably daft Qs but:

              1. What does 'PatientIDFk mean?

              2. How did you get he drop-down (that appears as if an edit box) on the right.

               

              Any good teaching manuals out yet? Missing manual seems expected July.

              • 4. Re: Indexing
                GuyStevens

                Probably daft Qs but:

                There are no daft questions, only daft answers. Smile

                1. What does 'PatientIDFk mean?

                That's actually a good question, because I didn't really explain that.
                I actually hate it myself when people use abbreviations without explaining them Embarassed

                Fk is the "Foreign Key"

                The ID field in the People table is that persons "Primary Key" of PK.

                Then if you want to refer to that person in another table you need to create a field in the second table where you can store that persons ID. But because you are storing an ID that is generated (auto enter serial number) in another table you call this the Foreign key.

                And you connect these two fields using a relationship.

                2. How did you get he drop-down (that appears as if an edit box) on the right.

                That's a little trick. They are actually two fields on top of each other.

                First you have the PeopleIdFk field from the second table.This is set up as a dropdown.

                On top of that you have the c_FullNameDOBSex field from the first table. This is set up as an edit box.

                The trick is to disable field entry in browse mode for the top field (c_FullNameDOBSex)
                And to disable field entry in find mode for the other field (PeopleIdFk)

                You can do this in the inspector under the "data" tab under "Behavior" - "Field Entry"

                That way when you click on the top field the dropdown floops out. But in Find mode you van just enter a name in the field and perform a find on the actual name instead of the ID.

                If you have a lot of names in your dropdown list you can just start typing with the dropdownlist open and as you type the corresponding person will get selected.

                • 5. Re: Indexing
                  tays01s

                  That is v. helpful and the trick of the overlying fields works neatly.

                  1. Is there a particular reason that the layout should be based on the Admissions table and not the other way around?

                  2. If Admission was Current or Discharge, how do you get the patient drop-down populated dependent on this initial choice?

                  3. Any good teaching manuals out yet? Missing manual seems expected July.

                   

                  Thanks,

                  Stephen

                  • 6. Re: Indexing
                    GuyStevens

                    Is there a particular reason that the layout should be based on the Admissions table and not the other way around?

                    That really depends on what you are trying to achieve.

                    In the case of my example you have a table with patient information.
                    And on the second table "Admissions" you create an admission for a patient. When you create an admission you also select a patient. Because it's always a patient that gets admitted.

                    Because you where talking about a second table I assumed you wanted to so something like this.

                    But I don't really know anything about your particular situation, so I can't make any more precise suggestions.

                    Unanswered Q: Any good teaching manuals out yet? Missing manual seems expected July.

                    I myself never read any Filemaker books. I learned from Lynda video's which is really handy because you see what they are doing on a screencast while they are explaining it.
                    http://www.lynda.com/FileMaker-Pro-training-tutorials/199-0.html

                    The only real way to learn is to dive in and to try and find solutions for the problems you run into.

                    Also look at starter solution files and examine them. Plus there are many example files to be found on this forum you can dissect.

                    Another unmissable resource are the filemaker product documentation pdf's:

                    http://www.filemaker.com/support/product/documentation.html

                    And then especially:

                    And ofcourse: