8 Replies Latest reply on Feb 28, 2012 9:27 AM by philmodjunk

    some new user questions: check boxes, date conversion, date range



      some new user questions: check boxes, date conversion, date range


      I am currently teaching myself FileMaker to manage a database of historical figures for my dissertation and am hoping that this forum might help me achieve that goal.  As I am a new user, please excuse any improper terminology I might use.

      #1: Managing checkboxes.  I want to show relationships between the various individuals in my database (more specifically, a field should indicate all of the students of a given individual from the same table).  I set up a field to be "check boxes" so that a given teacher could have as many students listed as I wish, all drawn from the same table of individuals.  However, there are so many individuals the check box system quickly becomes practically unmanageable.  What is the best way to set up a field for making multiple simultaneous selections pulling from the same or another table?

      #2: Date conversion.  It is useful for me to have two date columns, one using Christian dates, one using Islamic ones.  It seems like a waste of time to convert them manually; how would I set up the fields such that if I enter one kind of date, it converts to the other one, and if enter the other, it converts to the former?

      #3: Date ranges.  I do not always have precise dates; sometimes I am approximating a date or guessing within a range of years.  How do I set up date ranges such that the records will come up for any given search referring to those years?  (E.g. if I ask for all records from 1934, I want a record marked 1930-1940 to be pulled as well.)

      Many thanks in advance.

        • 1. Re: some new user questions: check boxes, date conversion, date range

          The best way is to create related records in a table instead of trying to populate a check box field. Take a look at the invoicing starter solution and note how a portal to a table called lineitems is used to list items purchased on a given invoice record.


          Teacher::TeacherID = Class::TeacherID
          Students::StudentID = Class::StudentID

          And you then can use a portal to Class on a teacher layout and use a drop down list of studentID's (With their name listed in "field 2") to link a student record to a teacher record.

          #2: Since FileMaker stores Christian datas, it should be possible to define a calculation that converts the given date into one from the muslim calendar. I don't have the time this moment to research the muslim calendar to find a conversion formula, but would expect that you can find one to use fairly easily. FileMaker stores dates as the number of days elapsed since 12/31/0000 so you can treat a date in  fields of type date as an integer for purposes of producing such a calculation.

          #3 One method is to use two date fields in each record to define the date range. For cases where you have an exact date, put the same date in both fields. For your above example, you'd use 1/1/1930 in the first field and 12/31/1940 as the second date. Then you can search your records for all records from 1934 by entering find mode and specifying < 1/1/1934 in the first date field and > 12/31/1934 in the second date field.

          • 2. Re: some new user questions: check boxes, date conversion, date range

            PhilModJunk, thank you very much for your help.


            #1: Very helpful.  But in the Invoice - <Line Items> - Product example, Invoices and Products are separate things living in separate tables. A given teacher can be another individual's student, hence the "student" field pulls from the same table as the teacher.  Moreover, I also want to show other kinds of relationships between the individuals: father-son, etc.  Will an Individual ---- <Class> ---- Individual setup work just as well?


            #2: So basically I need to figure out the conversion algorithm.  Once I have it, is there a way to have it so entering a date in the one field calculates a value for the other or vice versa?  I.e. for one record I might have a Christian date and for another I might have a Muslim one.


            #3: I think that solves my problem, thank you.  Basically, I need two fields for any date field for which I wish to be able to "approximate" when I do not have exact information.

            • 3. Re: some new user questions: check boxes, date conversion, date range

              1) the relationship shown can use the same data source table for both students and teachers. The "boxes" used to define a relationship are called "table occurrences" each refers to a specific table on the table's tab, but you can create multiple occurrences that all refer to the same data source table. See this thread to learn more:  Tutorial: What are Table Occurrences?

              2) Yes, you can define fields of type calculation that take data from other fields can compute a value

              • 4. Re: some new user questions: check boxes, date conversion, date range

                Thank you very much!  I think I am getting very close to solving my problem.  As you suggested, I set up a join table called "Classes" with three columns: ID, Teacher, Student.  "Teacher" keys to the first occurance of the Individual ID number, and "Student" to the second.  However, I have some new questions related to this system:

                (1) When I bring up a given individual's records is there a way for me to have portals both to all of his students and all of his teachers?  So far I was only able to accomplish this by setting up separate layouts, one based on each of the two "Individuals" table instances.

                (2) How do I display collaterally related information in the portal?  For instance, I can get the portal to display information from within the keyed table itself, but I have another "Locations" table keyed to the Individuals table for geographic data.  How do I get that information to show up?  E.g. a portal listiing Name::Individuals, City::Locations (both of which are keyed together).

                (3) How do you set it up such that you can edit the Classes table via the portal and have the program automatically infer that if I am entering information through a portal from a given individual's record, that individual is the teacher (i.e. not unlike the invoicing system you referenced)?

                Finally, a very simple new user question: What is the best way to set up a field for a simple "yes or no" binary?

                Again, I really appreciate your support.

                • 5. Re: some new user questions: check boxes, date conversion, date range

                  Oh, one other related question: Do I understand correctly if I have a field for which there will only ever be a single value (e.g. Father) there is no need to set up a join table and I can simply enter the ID from the same individual table via a dropdown list to illustrate the relationship?

                  • 6. Re: some new user questions: check boxes, date conversion, date range



                    Teacher::TeacherID = Class::TeacherID
                    Students::StudentID = Class::StudentID

                    With additional tables and table occurrences you can add:

                    Teacher::PersonID = ClassesAttended::StudentID
                    TeachersOfTeacher::PersonID = ClassesAttended::TeacherID

                    Now, on a layout based on "Teacher" you can use a portal to Class to list the teacher's students and a portal to classesAttended to list the teacher's teachers.

                    2) It depends on the relationship. If there is only one related record, fields from the related table can be placed inside the row of the portal. In the above example, a portal to Class can include a name field from Students to list the name of the student. If there are many related records we'd need to use either a calculation field that lists all the records, or another layout approach such as a second portal that displays the data whe a record in the first portal is clicked.

                    3) not sure that I understand the question. If you enable "allow creation of records via this relationship" you can add new portal records by entering data in the bottom blank portal row and the needed match value from the layout's parent record will be entered for you to automatically link it to the current record shown on the layout.

                    4) A value list with the values "yes" and "no" can be set up and you can format the field to use this value list with the radio button option. YOu can also set up a single custom value of "yes" or just the number one and set it up with a check box format so that clicking the box specifies "yes" and clearing it with a second click specifies "no". If you use the number option, you can resize the field so that only the box--not the value is visible and this is often used as a way to "toggle" a flag type value.

                    • 7. Re: some new user questions: check boxes, date conversion, date range

                      PhilModJunk: Thanks largely to your help, I am well on my way to setting up a database sufficiently robust for my modest needs.

                      (1) Thanks, I think this solves my problem. By setting up a second instance of the "Classes" table, i,e, "Classes Attended," I was able to pull portal records from the other direction.

                      (2) How does one set up "a second portal that displays the data whe a record in the first portal is clicked"? Basically, I have a field in the Individuals table called "Birthplace" which is a key number that links to another Locations table with more information. When I bring up the record for a given teacher, it would be useful to be able to see the point of origin for his students, and eventually even filter based on that data.

                      (3) Thanks, this solves my problem.

                      (4) Thanks, this solves my problem.

                      Other queries:

                      (1) When I am editing the layout, is there a way to edit the stile of multiple objects at once? FileMaker does not seem to allow me to control select multiple objects. Same goes for the table mode if I am trying to, for instance, delete multiple entries.

                      (2) What is the best way to set up familial relationships? I was thinking about setting up a join table not so different from the Classes table with four columns: "Relationshiop ID", "Individual 1," "Individual 2," and "Nature of Relationship." The last column would be a check box field with "Father, Brother, Grandfather, Cousin, etc." options. Then by using a second instance of the "Individuals" table I could illustrate those relationships going both directions. Or is there a better way to do this? The major problem here I see is that it would work well for father-son relationships with in-built directionality (i.e. two separate portals, one listing children, one showing father) but poorly for directionless relationships like siblings. Is there a better way to deal with illustrating familial relationships?

                      • 8. Re: some new user questions: check boxes, date conversion, date range

                        2A) This takes scripting and either an added relationship or a filtered portal. Clicking a button in the portal row (or all the fields in the row may be set up as this button), runs a script that sets a value used in the relationship to match to a specific set of records for display in the portal or which updates a portal filter to display the same set of records by filtering out the others.

                        It doesn't sound like what you describe needs that solution, however. If you needed to show multiple locations for each student, you'd click a portal row in the portal of students and see all the location records for that student. Since you can only be born in one location, such an added portal isn't needed.

                        If you have this relationship: (Could be more tables involved than just these)


                        Then a portal to students on the Teachers layout can include fields from the locations table to show their birthplace. If you have a join table between Teachers and Students, this same approach should still work.

                        1B) you can select multiple objects by Shift clicking them or by dragging a selection box around them. Then you can apply the same options to them as a group.

                        2B) I'd use the method you are describing. This is a many to many self join relationship and you'll find the links are one way. Linking two people in a relationship so that both are shown as related to the other will require two such records in the join table. The two join records will store the same ID's, but if you compare them, you'll find that the ID's are stored in the two foreign key fields in a different order.


                        To link in "George" as the Son of "John" requires a linking record and "Father" as the relationship type, George's ID in FK field 2 and John's ID in FK field 1. To link in "John" as the father of "George" requires it's own linking record with "Son" as the relationship type, John's ID in FK field 2 and George's ID in FK field 1. The same is true of siblings, but you will enter "Sibling" as the relationship type in both cases.