7 Replies Latest reply on May 27, 2011 8:21 AM by philmodjunk

    Multiple Survey Database for a Beginner

    BG_1

      Title

      Multiple Survey Database for a Beginner

      Post

      Hello all,

      I have recently created several separate databases, each centered on a particular survey/test.  However, what I really need is a multiple survey database, but I'm not really sure how to go about it.  I have done some planning/research before starting, and here is what I've come up with:

      Surveys <Survey ID> Questions < Question ID> Responses <User ID> Subjects

      Would this be a logical way to go about it?  I found a "How to" article for creating a survey database, but it was for Filemaker 3.X and didn't use a structure that made sense to me.  

      The intent, in the end, would be for me to be able to input a User ID, and I will see all the tests they have done.  Also, I would like to be able to analyze averages scores and the like. 

      Also, am I correct in assuming that you would only have 4 records in the Survey table, assuming there were 4 different surveys that could be taken?

      Furthermore, what will actually go into the Survey table when all the questions will be in the Questions table (1 record for every question/survey combination)?

      Once again, thanks for the help!

      P.S.  Although I could attempt to merge the databases I have already made, I don't think it would be worth it.  Each database I've made has been different, as I have been learning more logical ways to structure my database and analyze my data.

        • 1. Re: Multiple Survey Database for a Beginner
          philmodjunk

          Your table structure looks reasonable and matches what you would likely find if you searched this forum for threads using "Survey" as your keyword in the searh. Not sure the purpose for Subjects here, but it doesn't look wrong.

          You are correct that 4 different surveys would require exactly 4 records in the survey table. The typical data you would record in this table would be:

          SurveyID: A serial number field that uniquely identifies each surver. This allows you to group your questions records by survey so that you can see which questions were asked on a given survey.

          SurveyName: A text field that allows users to tell one survey from another since a survey ID will be to cryptic for that.

          You can then add as many other fields as you need to document a given survey in its entirety. You might add a date field, for example, to record the date the survey was administered or a text field that summarizes the overall purpose of a given survey in several sentences. You could also add fields here that compute and/or store aggregate values that summarize data from all the responses to this survey.

          • 2. Re: Multiple Survey Database for a Beginner
            BG_1

            The "Subjects" table is intended to store particular information about the test-takers (academic major, gpa, SAT and ACT scores, etc.).  Would there be a better place to put that information?

            As far as I can tell, this database will only be used to analyze, not administer, the surveys.  Most, if not all, the data has already been collected and put into excel spreadsheets (much of the testing/grading had been done before I started this job). 

            I guess my problem is that I usually equate table and form (since they are usually very similar, if not the same).   So, I was unsure what form/table I would go to to put in a Universtiy ID (User ID was a mis-type) to view their surveys taken, or where I would put my calculation fields.  I'll just need to think about it more.

            • 3. Re: Multiple Survey Database for a Beginner
              philmodjunk

              Univerisity ID could be a mistake here for your relationship to the Subject Table. You need an ID that uniquely identifies each test taker if you are going to use a Subject table to record Academic major, etc. for each person and if you need to examine the responses for a specific person taking your survey. You might find it useful to rename this table "Person" or "Respondant" and use it to record all data specific to one person who took the survey.

              If you have multiple univeristies involved and you need to record data about each university, you'd add one more table for that data and link to it by a UniversityID.

              • 4. Re: Multiple Survey Database for a Beginner
                BG_1

                One persistant problem I have been having is importing from Excel.  I think this is because most databases are separated into different tables.  This makes it so that when I want to import data, I can only import to one table at a time.  Do I need to just upload my data piecewise, or is there a way for me to import the Excel data into the entire databse instead of a just a table?  I already have to upload the data piecewise anyway (1 upload per question), so it adds up to a lot of extra time. 

                I hope that makes sense. 

                • 5. Re: Multiple Survey Database for a Beginner
                  philmodjunk

                  It's frequently necessary to upload from such a "flat file" structure repeatedly when importing into a relational system such as fileMaker in order to get the right data into the right tables. Uploading once for every question seems like major overkill and suggests that you need to consider structural changes to your system to avoid such a major timewaster in your import process.

                  One option you may want to consider is to set up a table in FileMaker that exactly matches the structure of your Excel file. You'd then import into Excel and use a script to loop through the data moving data from this "import only" table into the individual tables that make up your FileMaker system.

                  • 6. Re: Multiple Survey Database for a Beginner
                    BG_1

                    Alright, I finally got back to working on this database.  I really like the looping idea, but I find scripts pretty confusing.  I've searched for some "Loop Script" guides and I think I know how they work, but I'm not sure what commands I need to actually use.  I'll continue looking for more examples and experimenting.

                    • 7. Re: Multiple Survey Database for a Beginner
                      philmodjunk

                      Let's use a simple example that you can then "scale up" to suit your actual file.

                      Let's say you have this data in the columns of your spreadsheet: Student Name; Value1, value2; Value3. And you want to populate two tables: Table 1: Students, with the student names and Table 2: Values with one related record for each of the three values.

                      Import the data into your temp table with the fieldnames: Student; Val1; Val2; Val3 so that the entire file is imported in one Import.

                      After Import, Run a script like this:

                      #This script should only be run from the Temp layout immediately after Importing records
                      Go To Record/Request/Page [first]
                      Loop
                         Set Variable [$Name ; Value: Temp::Student]
                         Go To Layout [Students]
                         New Record/Request
                         Set Field [Students::Name ; $Name]
                         Set Variable [$StudentID ; Value: Students::StudentID ] //Students::StudentID should be defined as an auto-entered serial number field.
                         Go To Layout [Temp]
                         Go To Field [Temp::Val1]
                         Loop
                             Set Field [$Val ; Value: Get ( ActiveFieldContents ) ]
                             If [Not IsEmpty ( $Val ) ]
                                 Go To Layout [Values]
                                New Record/Request
                                Set Field [Values::StudentID ; $StudentID ]
                                Set Field [Values::Value ; $Val ]
                                Go To Layout [Temp]
                             End If
                             Exit Loop If [ Get ( ActiveFieldName ) = "Val3"]
                             Go To Field [next]
                         End Loop
                         Go To Record/Request/Page [next ; Exit after last ]
                      End Loop

                      If you then use Manage | Database | Relationships to link Students and Values by StudentID, you can now place a portal to Values on the Students layout and you'll see the values from the three Value columns displayed in the portal.