3 Replies Latest reply on Dec 11, 2012 12:42 PM by philmodjunk

    Help with Data Entry info



      Help with Data Entry info


           I have a database of schools (173) within school districts (24).  Two schools have the same name but are in two different districts.  They have different ID #'s, addresses and districts.  I have a data entry form to record enrollment by year for each school.  So when I click the Entry button I have created, I cannot get the second school to appear so that I can choose it.  The field for the school name is drawn from Table "Counts" , Field "School Name", which stores the enrollment numbers by school and year.  I have figured that the reason the second school does not appear is that no "Counts" have been recorded previously but since the School Name field is a drop down list tied to School Name in the SCHOOLS table, it should show all schools.  I cannot find what part is wrong.  When I do enter the enrollment, it appears on both school enrollment pages, the correct one and the incorrect one.  Can you help me?

        • 1. Re: Help with Data Entry info

               I would guess that your value list either hides the first field (which refers to the ID #) or your value list is sorted on the second field--which lists the school name. If so, schools with duplicate names are automatically omitted from he values displayed in your value list. Before you complain too loudly, though, consider the fact that if all you saw were either the name of school only or the ID number and name of the school, there's no information present in the value list to tell you whether "234 Jefferson High School" or "345 Jefferson High School" is the correct school to select.

               You'll need to rethink a bit. One simple option is to define a field that uses an auto-enter calculation to combine both school and district name in a single field. You can set a "unique values" validation on this field to make sure that this combination is always unique. Then use this field in place of the school's name field in your value list.

          • 2. Re: Help with Data Entry info

                 Well, not exactly.  The Field School Name I have as a drop down box that refers to Schools which shows School Name and ID from Table Schools.  I have it sorted by School Name which I have as field 1 and ID as field 2.

                 On this entry form, I also have a field for ID and School District which populates from the Table Schools once I select the School name.  That is how I know that it is pulling the wrong one. 

                 In your reply, I am not sure how to define an auto-enter calculation field that combines the school and district as a unique values Validation. 

                 In other areas of my database, both schools show up so I thought this was weird in this area.  I don't understand why it is one way in one area and not the same in other.


            • 3. Re: Help with Data Entry info

                        Well, not exactly.  The Field School Name I have as a drop down box that refers to Schools which shows School Name and ID from Table Schools.  I have it sorted by School Name which I have as field 1 and ID as field 2.

                   In which case, you have a second problem, this field is entering the name when it should--in most cases--enter the ID number. Why use a drop down list that includes ID numbers to enter the name instead of the ID? (I can think of some, but they wouldn't use a value list that also includes the ID number.)

                   That suggested auto-enter calculation assumes that you have both school and district name fields in the same table--where you may actually have just a DistrictID number that links to a table of districts. Such an auto-enter calculation might require a field that looks up the district name or your auto-enter calculation can reference the district name field from the related Districts table.

                   Thus, you might have one of these two auto-enter calculations:

                   SchoolNameField & ", " & DistrictNameField


                   SchoolNameField & ", " & Districts::DistrictNameField 

                   For both calculations, make sure that "do not replace existing value" checkbox is NOT selected. But also recognize that both options will create an update issue should you modify the DistrictName for a district after there are records in the Schools table that refer to that District record.

                   You might also be interested in this script supported method for selecting a name in order to enter an ID number. It enables using auto-complete and the script handles cases where the same name appears in the value table more than once.

                   FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                   Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7