4 Replies Latest reply on Oct 15, 2012 1:08 PM by ChrisWelsh_1

    Question about creating Query of multiple fields



      Question about creating Query of multiple fields


      I am very happy that I have never needed to ask for help yet (not been using FMpro long) but I have a criteria that I need to fulfill and I cant figure out the best way to accomplish it and I was hoping some people here might have some ideas.

      What I am making is a query of a database that stores the results of online classes that are made up of modules (up to ten modules each class) 

      so when I import information into this database, there are cells that store a score for each module (m1s, m2s, m3s, etc...) and a cell that stores a timestamp (m1t, m2t, m3t, etc...) that denotes when a specific module score cell was filled using an If statement. (10 module score cells with ten cells with timestamps)

      So I need to make a query that will search all ten of these dates cells and only show those records which received a score (thus a timestamp) during a certain time period.

      I have a date search set with a button script that triggers the action using set fields and two date criteria to user puts in.

      This works fine when I am trying to just search one date field but when I add additional set fields (my other date fields for the other 9 modules) then nothing comes up and I am pretty sure why this is happening...it is now going to only report records in which every module field conforms to the date requirement...which will almost never be the case so it shows nothing.

      How would one go about, on one button push, creating a query of 10 different columns with cells for dates and bringing up ANY record that has any of the 10 cells on the rows with a date within the date search parameter? Instead of having it search all the cells and only bringing up records that have the time in all 10 records... the difference between any and all...

      I hope I have made myself clear....Any help would be greatly appreciated...


        • 1. Re: Question about creating Query of multiple fields

               I think you should restructure your table definitions to add a new related table for your list of modules and time stamps. This is a much more flexible approach and coincidentally will make your query work quite simply where getting what you want with this list of individual fields becomes quite complex.

               With a related table where each record has one module field and one TimeStamp field, you can put a portal to this new table on your layout and then you can enter search criteria in the timestamp field and it will bring up any parent record with at least one related record that matches to your criteria.

               To do the same with your separate fields takes one of two rather tedious approaches:

               1) enter find mode and specify the criteria in m1t. Select New Request from the requests menu and enter the same criteria into m2t. Repeat for each additional field. Perform your find.

               2) Perform the find using criteria in m1t. Return to find mode, specify the same criteria in m2t. Select Extend Found set. Repeat for each additional timestamp field.

          • 2. Re: Question about creating Query of multiple fields

                 Thanks for the quick response!

                 I think I am a bit fuzzy about how I would set up the related table and tie that into my parent table search.

                 This database has a form layout front end which allows people to do canned queries and this would be one of them. So the user clicks on 'search by module and date' button which takes them to another layout with search criteria being 'start date' and 'end date'...the two dates the results should be confined within....then this takes them to a results page that sorts the results by test title subheader with (name) (over all score) (module1) (module 2), etc.....

                 If I were to start a new table and put just one Module feild and one Time Stamp field...how wold I be able to relate the results to the parent record or differentiate between modules? Are you saying that this second table should have a row for every module, for every class? thus 10 rows entries for every course and for every person?



                 student name | Course title   |   Module |  Time Stamp

                 Bob Smith       | course name|         m1   |     m1t

                 Bob Smith       | course name|        m2   |     m2t

                 Then the search would search 'Time Stamp' column?

                 Something like this? Sorry, I am relatively new to this :(

            • 3. Re: Question about creating Query of multiple fields

                   You are on the right track, but you should not have a column for the student name. Student names are not unique and subject to change. They can also be entered incorrectly and then fixing the error can create problems if you have related records that match to the incorrect name.

                   Replace the name field here with a number field. Add a serial number field to your original table. (And use Replace Field Contents to assign a seril number ot any existing records in your original table.) LInk it to the number field in a relationship like this:

                   Maintable::StudentID = Modules::StudentID

                   IF the same student record can be linke to multiple courses, a different relationship would be needed in order to match by course and student.

                   You'll want to look up "portal" in FileMaker help and any training materials that you have if you are unfamiliar with them.

              • 4. Re: Question about creating Query of multiple fields

                     okay..Thanks for the help...we give a student ID number which will probably suffice and is already attached to each student record. I will look what you say and see if I can't figure this out. Again, thanks for taking the time.