1 2 Previous Next 18 Replies Latest reply on Sep 19, 2011 9:36 AM by philmodjunk

    Handling semester and last year - is this a structure problem?

    DanielPackman

      Title

      Handling semester and last year - is this a structure problem?

      Post

      I am porting a database from 4D to FileMaker which manages a school. In the original database, information for the current semester was entered which included private lessons, semester charges, payments received, and payments made to teachers. At the end of each semester, the database was copied and these semester-specific entries were deleted. It would be straightforward to continue this approach, but I think it might be nicer to keep these entries, but include a __kf_semesterID field and somehow filter the approriate records based on this. The structure seems complicated enough now, but any time I add this field to an existing table, I generate another table occurrence. Is there another way that I can approach this, perhaps with searches? Ideally, I'd like to be able to set the semester to any value in the past or leave it as the current one and have the appropriate set of records selected. And even better, it would be nice to be able to have two semesters selected at the same time to get certain information out on a given year.

        • 1. Re: Handling semester and last year - is this a structure problem?
          philmodjunk

          any time I add this field to an existing table, I generate another table occurrence.

          That doesn't actually happen. You can add hundreds of new fields to a table without getting a single new table occurrence. Can you describe the steps you used and what you saw that produced the impression on your part that this was the case?

          You haven't said much about what tables currently exist, but adding an additional table for semesters where you have one record for each semester sounds like a logical next step that will make working with records from multiple semesters easier.

          This isn't the only option, however. Whether you add a _kf_SemesterID field to each of yoru existing tables or just a value list controlled field for specifying the semester ID, you'll need to add at least one field to your existing tables. You can also add a separate year field to identify the year. This is a field you can define in the related semester table, or if there is already a date field in the table, you might be able to use a calculation field that extracts the year from that date field. The more information such as year, start date, end date, etc that you need to record in your database, the more useful the additional table will become so that you can enter this data once for each semester and then use SemesterID to link records from each related table to the correct record in the Semester table.

          • 2. Re: Handling semester and last year - is this a structure problem?
            DanielPackman

            Sorry, I was very sloppy in explaining things. Of course just adding fields doesn't add new table occurrences. I have a table that is indexed by semester and it holds things such as beginning/end dates of the semester, semester fees, and other default values that are fixed for each semester but may change from one semester to the next. I'd like to somehow select the "current semester" [global variable?] that defines which record is selected in this table. Your point on generating a "previous year" by calculation is very clear and might be just the thing for some further calculations. Every time I create a relationship from a table with a semester dependency, I need to create a new table occurrence for the "defaults" table whose primary key is _kp_semesterID. Now I am up to 6 table occurrences of "defaults". So I either need to continue down this path dealing with the multiple occurrences of the defaults table, redefine the structure to have table occurrences of different tables, or change to some other solution (perhaps with searches). Here is the current structure:

            Structure of the database

            • 3. Re: Handling semester and last year - is this a structure problem?
              philmodjunk

              I don't see the structure of your database.

              You can enter this as simple text if you want:

              Semester::__pk_SemesterID = PaymentsReceived::_fk_SemesterID

              Is an example.

              It's often the case that you need to create many occurrences in order to build the relationships needed for your database to function. Sometimes, you can get a much simpler structure by carefully selecting which occurrence to duplicate and which to keep as is.

              Here's method on organising TableOccurrences to match your layouts to your occurrences to keep a more coherent and easy arrangment of occurrences in the relationships map:  http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

              • 4. Re: Handling semester and last year - is this a structure problem?
                DanielPackman

                Perhaps the structure as a gif would show the relationships for you.

                structure of database

                • 5. Re: Handling semester and last year - is this a structure problem?
                  philmodjunk

                  That's quite a "spider web"

                  What problems does the above set of relationships and table occurrences create for you? (What you have could work just fine or you may need to create new occurrences of the other tables to link to your "defaults" table.)

                  Frankly, this looks like a made to order case for using Anchor Bouy as it's a way to organize all this so that get "tree" arrangements of table occurrences with each "root" occurrence corresponding with a different layout or group of layouts.

                  • 6. Re: Handling semester and last year - is this a structure problem?
                    DanielPackman

                    In this design, every time I have a table with a field connected to the semester, I have a new table occurrence of the "Defaults". I could revisit this keeping the "Defaults" table a single occurrence and then have multiple instances of TeacherPay, TeacherFixedPay, PrivateLessons, StudentsToGroup, Charges and Payments.

                    In any case, I need to have a mechanism to select (do a find) off of a specific semester. As you mentioned in another post, "Perform Find from Drop Down List", one could "Define a text field and specify global storage in field options for it" with a drop down list. In my case, this would be an integer field of the semesterID with the drop down list being these semesterIDS with a suitable name associated with them (eg, "Spring 2012"). Then you mentioned a trigger to get to a script that would do the appropriate find:

                    Enter Find Mode[] // Clear pause check box
                    Set Field [YourTable::yourContactGroupField ; YourTable::gContactGroup ] //gContactGroup must have global storage or this will not work.
                    Set Error Capture [on]
                    Perform Find []

                    A few questions. This new field with global storage can be on any table including, in my case, the "Defaults" table? With global storage, does this new field work independently of other users? With a single table occurrence, I assume you can set any number of fields within a single table to then pass on to the find. In my case, would I set the same field in all six of my table occurrences and then do the find?

                    • 7. Re: Handling semester and last year - is this a structure problem?
                      philmodjunk

                      You'd use a global field as this is a convenient way to store criteria (your semester ID) to be used in the find. If you selected this value in a field that isn't a global field, the script can't access the value while in find mode. (Try putting a global and non global field on a layout. Enter data into both, then enter find mode and notice how the global field still shows the data but the other is now blank.)

                      Yes, you can place your global field on any layout and you can define it in any table as long as it is not used as a key field in a relationship. I often put all such fields in a dedicated "globals" table as a way to keep better track of them.

                      What you will need to be careful here, is that you need to match the script to the correct layout and via that layout to the correct table occurrence.

                      While the script steps are basically the same:

                      Enter Find Mode[] // Clear pause check box
                      Set Field [PrivateLessons::_kf_SemesterID ; Globals::gSemesterID ]
                      Set Error Capture [on]
                      Perform Find []

                      Finds PrivateLessons Records with the specified semester ID and should be performed from a layout based on the PrivateLessons table occurrence.

                      Enter Find Mode[] // Clear pause check box
                      Set Field [TeacherFixedPay::_kf_SemesterID ; Globals::gSemesterID ]
                      Set Error Capture [on]
                      Perform Find []

                      Finds TeacherFixedPay records and needs to be performed from a layout based on TeacherFixedPay.

                      You can use Set Field By Name and pass the specified table occurence::field name by script parameter if you want to use one script for multiple such searches on different layouts and to search different tables.

                      And please note that neither of these scripts refer to any occurrence of the Defaults table. The only place where you need an occurrence of that table for this is in the definition of the value list of Semester ID's and only one occurrence is needed for that.

                      The only reason for linking in all of these occurrences of this table is if you either plan to display data from that table on a layout based on the related table occurrence or you plan to use Go To Related Records instead of performind a find to pull up records.

                      • 8. Re: Handling semester and last year - is this a structure problem?
                        DanielPackman

                        Very helpful, as usual. Thanks.

                        I'm still not entirely clear how these finds need to work together. Ideally, I'd like to have a single global "semester" field on the main page. Each user can then select this (or more often just leave it alone as the current semester). This would ensure that all records being viewed or used for calculations correspond to the correct semester.

                        On the teacher layout, I have several calculations:

                        TotalPaid = sum (TeacherPay::Amount)   // the TeacherPay::Amount is dependent on semester

                        TotaltoPayfromGroups = sum(GroupLessons::TeacherPay) // the GroupLessons::TeacherPay is dependent on semester

                        TotaltoPayFromPrivateLessons = sum(PrivateLessons::TeacherPay

                        TotaltoPay = Sum( TeacherFixedPay::Amount) + TotaltoPayFromGroups + TotaltoPayFromPrivateLessons  // TeacherFixedPay::Amount depends on semester

                         

                        Find sets are separate for each user, so this should work fine in a multiuser environment, right?

                        If as a user you only have permission to read/write some of the records in the database, do finds of various sorts just never return these proscribed records?

                        • 9. Re: Handling semester and last year - is this a structure problem?
                          rjlevesque

                          my 2 cents - I would just use a flag to convey payment status without deleting any records.

                          i.e. flags either Past Due or Current for payment records to teachers

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

                          Not going to comment on anything else, Phil has everything under control =)

                          • 10. Re: Handling semester and last year - is this a structure problem?
                            philmodjunk

                            Ideally, I'd like to have a single global "semester" field on the main page. Each user can then select this (or more often just leave it alone as the current semester). This would ensure that all records being viewed or used for calculations correspond to the correct semester.

                            You can define a global field and put it on your main page for selecting a semester. A script that is specified in File Options to run when the file opens can initialize this field with the current semester ID. Then this field can be used through out your system to controll what records are currently visible and accessible to the user.

                            TotalPaid = sum (TeacherPay::Amount)   // the TeacherPay::Amount is dependent on semester

                            Just to use this one case as an example, if amount is dependent on semester, then the relationship should be changed to be:

                            Teachers::__kp_TeacherID = TeacherPay::_kf_TeacherID AND
                            Teachers::_kf_SemesterID = TeacherPay::_kf_SemesterID

                            Each teacher would have a new Teacher record each new semester. With this relationship, you need only find the correct set of teacher records to see the correct pay results. This is not the only approach. This is drawn directly from the above screen shot of your relationships, but it requires new teacher records every semester.

                            Find sets are separate for each user, so this should work fine in a multiuser environment, right?

                            Found sets are separate for each user. This is also true for the values entered in global fields so the global semester Id field can be used by different users to select different semesters. Note that layouts can have a script trigger that performs a scripted find that uses the Global Semester ID field to find the records for the specified Semester.

                            If as a user you only have permission to read/write some of the records in the database, do finds of various sorts just never return these proscribed records?

                            That is correct. Show All Records and Show Omitted Only, however, can still bring up these proscribed records. If this is done, the records that for which access is not permitted are hidden behind "Access Denied" tags. Some developers use Advanced to set up a custom menu where these two options perform scripts that still show all or show omitted, but then use constrain found set to drop out the access denied records.

                            • 11. Re: Handling semester and last year - is this a structure problem?
                              DanielPackman

                              I can't imagine where else I could get this detailed information. Thanks again. You are truly the missing-missing manual.

                              One other question occurs to me now: If I include a new teacher record for each semester as you suggest, it nicely solves the problem of active vs inactive teachers as well. A simple "create new semester" script could copy previous records for both teachers, defaults and any other set of semester dependent records that would be well served as starting out as a duplicate of the previous semester. So for a number of reasons, this might be an attractive solution. However, is there a way to set up a relationship with a field in another table? In the relationship pane between teachers and teachersPay, the two tables are fixed and cannot be changed. You mentioned that this is not the only approach.

                              • 12. Re: Handling semester and last year - is this a structure problem?
                                philmodjunk

                                the two tables are fixed and cannot be changed.

                                Please explain what that means.

                                I thought that this was a solution consisting solely of tables defined in FileMaker. Is this not the case?

                                The alternatives also require changes to your relationship graph.

                                • 13. Re: Handling semester and last year - is this a structure problem?
                                  DanielPackman

                                  If I look at the relationship between two tables, then the options available to me seem to be relationships of fields with each of these two tables and no others. That is, if I select the relationship in the structure above between Teachers and TeacherPay, then I can create many relationships between the two tables, but on one side I can only select fields from the table Teachers and the other side I can only select fields from the table TeacherPay.

                                  • 14. Re: Handling semester and last year - is this a structure problem?
                                    philmodjunk

                                    That's exactly how this has to work. Even in SQL database systems you'll find that this is the case when defining relationships (not queries) between tables.

                                    What I suggested uses fields already present in both tables so I do not see where this is an issue.

                                    Teachers::__kp_TeacherID

                                    TeacherPay::_kf_TeacherID

                                    Teachers::_kf_SemesterID

                                    TeacherPay::_kf_SemesterID

                                    are all fields that I can see in your screen shot.

                                    You could replace teh __kf_SemesterID field in Teachers with a global field that a script updates by copying the current value of your global semester ID in the defaults table. That would avoid the need to create duplicate teacher records each new semester.

                                    You can also define a Join table that links Teachers to your Semesters (Default) table which then links by Teacher and Semester IDs to the Teacherpay table. This approach means that you do not duplicate teacher records, but just create the needed Join table records for the new semester for each teacher in Teachers that will be employed for that semester.

                                    1 2 Previous Next