8 Replies Latest reply on Jul 10, 2015 1:27 PM by Paper_Queen

    Search multiple fields in multiple tables from one layout



      Search multiple fields in multiple tables from one layout



      Four tables, each representing a different US Federal Census report (ex: one for 1870, one for 1880, etc.), with the census form used as the layout "base" behind fields. A single record might contain 8 family members; each family member has its own name, gender, birth year, marital status, etc. Those fields are labeled Name 1, Name 2, Name 3, Gender 1, Gender 2, Gender 3, Birth Year 1, Birth Year 2, Birth Year 3, and so on.

      The goal:

      To create a single "universal search" layout that allows me to type in a person's name, click a button, then have it show each census year (layout) that person appears in (ideally, along with their age, marital status, and location). Basically, searching across all layouts/tables simultaneously, looking for James Smith, then displaying "here's where the name occurs" in the results.

      - - - - - - - - - - - - - - - 

      Hope this makes sense. If not, please ask questions. In the end, the database will contain a few hundred records spanning 20+ layouts, each with a corresponding Table (since no two census reports are alike).

      Help, please. Am I nuts for trying this? If not, how do I accomplish it? 


        • 1. Re: Search multiple fields in multiple tables from one layout

          It's not an ideal design. Identical format data is best put into a single table, not 3. Multiple fields i the same records is also a poor design that will complicate your process 15 fold.

          And "a few hundred records" is a trivial number of records so a restructuring would simplify your solution and make it many times easier to work with.

          That said, a scripted search could be set up to search even these tables, but each layout would show separate results of the search on that table. Combining results in a single layout would also take additional work--work not necessary if you put all of this data into a single table and spit up the data so that 15 sets of fields became 15 individual records in a related table.

          The method that could be gotten to work after a lot of scripting would be to enter your search criteria into a global field and then a script would enter find mode and generate 15 find requests in order to search just one table. It would then repeat this on the next table and so forth...

          See this thread for examples of scripted finds using global fields: Scripted Find Examples

          • 2. Re: Search multiple fields in multiple tables from one layout

            Thanks for the feedback---the tricky part of this project is that each decade's census asks for different data. One year might include 12 questions, the next might include 20, only a handful of which overlap. Each FM record includes multiple family members grouped together. A 9-year-old child on the 1850 census can later appear as a 69 widowed father-in-law living with his married daughter's family on the 1910 census. 

            To complicate things further, a woman's name will change over time, as she marries (and in later generations, divorces, then remarries).

            So..if I understand your suggestion (putting "identical format data" in a single table), that would only apply to males, and only for a small number of data points. Regardless of gender, every person's age would change with each passing decade. 

            Here are three examples of completed forms (remember, the grids/forms themselves are PDFs dragged in as "base layers"), giving an idea of what I'd dealing with. The forms themselves are simplified versions of the original census reports, used throughout the genealogical community (i.e., provides a standard that can be printed and shared with fellow historians):


            • 3. Re: Search multiple fields in multiple tables from one layout

              Sorry, but you don't understand my suggestion fully.

              Identical format does not mean identical data.

              You need one table with one record for each census record, but a related table to list the members of that family. Those questions that differ with each census would be put in another table--you might need one table for each census there but if you make each question and response a related record, you may be able to use a single table there also. (The differences then become different lists of records associated with each decade's census.)

              But I see that you are showing data from a much advertised commercial web site for researching family trees. Aren't you re-inventing capabilities already built into their system?

              • 4. Re: Search multiple fields in multiple tables from one layout

                To address your last question first:

                Aren't you re-inventing capabilities already built into their system?

                Not really. There's no transcribed version that provides all info in a single screen, across the entire family, in a way that's quick and easy to reference. Also, using the site requires internet access, which isn't available in many (most) of the old courthouses and other historic structures, be it wifi or getting a clear mobile signal.

                Now...working backward to your suggestions (please forgive me in advance if I'm not quite grasping this). Is this what you describe?

                Family Names Table: Only one field, titled "Name," would be used in all census form layouts, for every "name" line on the form < < < Never mind. Just realized that would make all names on a census form identical. No clue what fields would appear in "a related table to list the members of that family."

                Several Year-Specific Tables: One table per census year, including fields for every question on the corresponding census forms

                - "One table with one record for each census record" (quoting your earlier post): This one has me flummoxed. Again, forgive me if I'm being dense, but all I've every put into tables are fields---how would one put a record into a table?  


                Also, wouldn't this create a slew of "many-to-many" relationships? 



                • 5. Re: Search multiple fields in multiple tables from one layout

                  to answer your last question first. There's nothing wrong with many to many relationship if properly implemented with correct relationship structures, but no, I don't think this would create multiple many to many relationships. You get exactly one many to many relationship between censusRecords and Individuals with a correctly designed join table used to link them in that many to many relationship.

                  how would one put a record into a table? 

                  Better read that one again. ALL database tables consist of records that are then broken down into fields. To put a record in to a table requires simply adding a new record--a standard data entry task.

                  Here's a sample of what I have in mind, The key is to try to avoid different tables for different censuses if at all possible--this leads to very difficult to work with designs.


                  CensusRecord::__pkCensusID = FamilyMembers::_fkCensusID
                  Individuals::__pkIndividualID = FamilyMembers::_fkIndividualID
                  CensusRecord::__pkCensusID = CensusQuestions::_fkCensusID

                  The indivdiual names you show in a single Census record would be stored as individual related records in FamilyMembers. As you "cross connect" and determine that Mary Smith in one record is Mary Jones in another, you create a single record with her birth or earliest known name in Individuals that then links to both records in Family members.

                  • 6. Re: Search multiple fields in multiple tables from one layout

                    ALL database tables consist of records that are then broken down into fields.

                    (Insert: Palm plant on forehead) Knew that. My bad for turning things inside out earlier. 

                    I appreciate your explanation and example of how the tables would relate to one another. A question, though---wouldn't the resulting database require me to first enter all family members on their respective Individual records, then backtrack to re-enter them into the census form-formatted records (table: Census Records)? Many times, the first indication there's a new family member is when they show up as a small child on a census. Would mean lots of back-and-forth if I need to set them up on an Individual record first.

                    Watched the first five chapters of Relational Database Design in FileMaker Pro on Lynda.com, in hopes of helping sort this out in my head. Something somewhere just...isn't...clicking. Am going to start with a clean slate, strip things down, and start over, in hopes of figuring out what eludes me.


                    • 7. Re: Search multiple fields in multiple tables from one layout

                      PhilModJunk, I simply can't get this to click with how I need to use the database. The goal is pretty simple---to search for one person, then see all the layouts they appear on. Logically (at least on the surface), seems the layouts (as opposed to tables) would work this way:

                      Census Report for year XXXX: Looks like what I shared in an earlier post. Names, dates, details would be entered on this layout. Makes sense to use dropdown menus for each name line, but wouldn't that require me to pre-enter all family names on a different layout first? Lots of extra work.

                      Individual: A layout that includes the individual person's name, plus (I'm guessing) a portal that lists all Census Reports that name appears on. In a perfect world, that same layout would include a button or other method of jumping to a selected census layout to review the form "in tact."

                      Which brings me to fields. Where would the Name field reside? Of the four tables you used in the example (which is deeply appreciated, in spite of my confusion), would it be the Individuals Table, or the Census Questions Table? Knowing there would be up to 15 names on a single Census layout, does that mean 15 Names fields, or only one on the Individuals Table? 

                      What would go on the Census Record Table, other than the serial numbered primary key field? 

                      For the life of me, the logic escapes me, on all this. What seemed like a simple idea is beginning to feel far, far more complex than expected. 

                      (Thanks for hanging in there though this, by the way. Tripped over an old 2009 post of yours, explaining the name PhilModJunk. I have even more respect for the sheer volume of assistance and never-ending patience you contribute---not because you have to, but because you want to. What a superhero!)


                      • 8. Re: Search multiple fields in multiple tables from one layout

                        Have been banging away on this for a week, and have decided my mental block ties to figuring out how the relationships function in layouts. I can more or less see the flow...until hitting the actual census report layouts. Would there be 15 "Name" fields (one on each line) per layout? And if so, wouldn't that make it virtually impossible to do a name search later on without having to script a Find that touched 15 name fields x 50 census forms each time? 

                        I've sketched this out twenty different ways, but keep hitting a pothole on the actual census form record layout. Any hints?