ktalley

Hello, I have been able to remotely access a FileMaker Pro 12 Server via a reverse SSH tunnel with a FileMaker Pro 12 client since version 12 was released. I based the SSH connection off the instructions in this very old post: http://fmforums.com/forum/topic/27976-tunnel-filemaker-over-ssh-through-nat/ I have not been able to connect the same way with a FileMaker Pro 13 client (either regular version or advanced) but still can connect with version 12. Does anyone know if something has changed to disallow this type of access in 13? It works fine with version 12, so I don't think it's the SSH connection that is the issue. Below is how I'm connecting. In FileMaker I connect through a remote host at 127.0.0.2 which is an alias so there's not a conflict with the local host address. Remote Computer (using a cygwin terminal on Windows 7) ssh -R 10000:127.0.0.1:22 username@homeipaddress This sets up an SSH tunnel from the remote computer to my home computer. Local Computer (running OSX 10.9.3) sudo ifconfig lo0 127.0.0.2 alias up sudo ssh -NL 127.0.0.2:5003:remote_computer_ip_address:5003 remote_username@127.0.0.1 -p 10000 This connects to the remote computer via SSH over the existing SSH connection - this bypasses any router issues of connecting remotely. This works great with version 12, but no databases show up in version 13. Could this be a conflict with having 12 and 13 installed on the same computer? Thanks for any insights. I'm not an expert in SSH by any means. kt

Hello, I recently migrated a FM11 database to FM12. I haven't noticed any hiccups the past few months until I tried to create a container field that has interactive content. I'm wanting to view PDFs in the container field. When I mark the container field to "optimize for interactive content" in layout mode, the Insert Menu does not display PDF or Audio/Video after selecting the container field in browse mode. However, if I create a new database in FM12, the interactive nature of a container field works as expected. Could this be a problem with the conversion? I have tried creating new container fields in a few different tables with the same results. The file is hosted by FMS, but it still does not work when I try on a local copy. I have the same issue on Mac OS 10.8 and Windows 7. Thank you for your suggestions. kt

Hello, I have a FileMaker 11 database for my music department that I'm having trouble with duplicate records after a few imports from an Excel document. In my student table I have auto-entered, serialized, unique IDs that link to other parts of the database. I download an .XLS document from the university's mainframe that lists students by their student ID number. I have to convert it to .XLXS format since I'm running Mac OS 10.8 before the import. For all of the students in my database, I have a student ID number field that matches the student ID number field in the .XLXS file (different from the FileMaker studentID). The information changes in the .XLXS (GPA, current semester hours, etc.) depending on when I download from the mainframe, but the student number remains the same. On my import, I use "Update matching records in found set" and have checked the box of "Add remaining data as new records." I import the fields I need using only the "Import this field" option and match records based on the university's Student ID Number. I have First Name, Last Name, Major Code, Address, etc. set to "Import this field" in case there are students in the university's system that I don't yet have in my database. The first few imports went fine and it added new students that weren't already in the database and updated the existing records. But after the 2nd or 3rd import, it starts creating another record for existing students. The new record has only the imported fields. Am I doing something incorrectly? Could there be something wrong with the database? I do have a reoccurring import that imports data in another table for class enrollments. That one also matches on the university's student number and deletes all records before it imports. It works fine. Perhaps I need to setup the same thing for this import? I'd rather be able to have some of that data as a part of the student's record since eventually these students will become alumni that I will want to follow after graduation. Thanks for your help kt

Hello, I'm adding in a Purchase Order module to my existing music department database and need some guidance. Our department has 15 accounts from which purchases are made. The account numbers on these accounts are static except for 2 digits that change based upon the fiscal year (from July 1 to June 30). I would like to have a way when creating a PO to select from a drop-down list that shows accounts only for the current fiscal year. I will need to keep a record of the POs and their year-specific accounts from past years. I can create a calc field that calculates the correct account number at any time of year for each account, but I don't know where best to store the account numbers for the list. Do the account numbers need to be in their own table? In the PO table? I have a preferences table with one record that I use to get lots of other information, but that won't give me a value list. Thank you for your assistance kt

That did it. Thanks for your help!

Thank you gentlemen. Having the layout in the StudentEvents table was what I was missing. It would be nice to have the total number of participants per event on the same report - even though it's not hard to add two numbers in my head. How complicated are we talking? Thanks

Thanks for your reply. I had tried that, but was getting numbers that didn't add up. Then I tried using the EventID from the Events table and not the EventID from the StudentEvents table and it's closer, but there is still a problem. Edit: When I have a student who is attending more than one event, it only counts their gender in once for all the totals and not for each event they are in. If a student is in Event A and B it only count them in event A and not in Event B. Any thoughts on that?

Hello, I am wanting to count the number of male and female participants in a particular event and display that number breakdown for all events. Here are the relevant tables: Students -< StudentEvents >- Events I have created a report that shows correctly the number of male and female per event. The layout I have in the Students Table is: Body: EventID (using a pop-up menu to display the real name of the event) Sub summary: Gender and Gender_Count (just a count of Gender) Trailing Grand Summary: Gender_Count When I do a find for a particular event and then sort by Gender it works great - but for one event at a time. Is there anyway that I can display on one report the breakdown of Gender and total for each event? Thanks for your assistance kt

Hello Ron, Very true. I've been bitten by that dog several times and am trying my best to avoid it in the future (-; It's not like these classes are for any type of school credit - the camps last 4-6 days and we only need class rosters in order to take roll. So keeping track from year to year really isn't important. All the needed information to track is in other tables. I currently have a Year calc field in the enrollment table so in the class portal I have it filtered to display the classes of the current year. Since I've made some poor decisions in the past, I keep trying to get it 'right.' But if it does what I need it to do and doesn't require a lot of extra steps, I should be content. Thanks for the suggestions comment. I'll try them out and see which one seems to fit what we do

Thanks for your reply. Your observation is correct (as usual). Since the classes themselves rarely change, and are unique to each type of camp, and happen every year, I have a "Camp Name" field in the Classes table instead of linking a specific EventID to it. I currently enroll students into a class using a conditional value list that selects the camp name (not tied to an event in the even table), type of class, and the class itself. I create a new 'event' for each camp every year since I hire different faculty and have different students attend each year. I set it up that way so that I don't have to choose the same 60 classes for each camp every year for the new 'event.' Perhaps there is better way of doing that?

Hello, I need some advice on if this is possible or a good approach. Part of my database solution includes enrolling students into a summer music camp (an event) and then enrolling in classes in that particular camp. Here are the relevant tables I have: Students Contacts (faculty who teach at the camps and others) Classes Events (includes camps and other departmental events) Student-Events (join table to events) StudentEnrollment (join table to classes) I currently have these relationships: Students --< Student-Events >-- Events Students --< StudentEnrollment >-- Classes I also have a similar setup for the Contacts who are hired for each camp and assigned to teach each class. Currently, the StudentEnrollment table does not contain an EventID or any link to a specific event; only a ClassID so I can get a roster of students in the same class. This works fine, but I'm concerned about next year's enrollment. I could just delete all the entries in the StudentEnrollment table each year, but I wouldn't be able to track what students took each year. Would it be a good approach to have the StudentEnrollment table as a child table of the StudentEvent table? Something like: Students --< Student-Events --< StudentEnrollment with Student-Events >-- Events StudentEnrollment >-- Classes I've never seen a join-table linked with another join table, and I am probably making this too complicated. There's probably an easy solution staring me in the face and I just can't see it. Thanks for your assistance kt

Thanks for your reply. I think that would work. Some schools have multiple directors and not all directors from each school would be directing bands at the jazz festival. But if I had a child table from a Schools-Events join as you suggest, I could still get a set of participating directors by assigning them to a band from a school. Thanks for your suggestion

Hello, I am in process of building a database that will cover most of our university's music department activities and I need some assistance with one area. I have tables for: Contacts Schools Events Contact-Event Join Table The problem I'm having is scheduling the bands for our annual jazz festival. Most schools bring only one band and have only one director, which makes everything simple - I add the director to the Contact-Event Join table and get a list of only the directors participating in this year's festival (and can keep track of that year to year). However, some schools bring multiple bands with the same or multiple directors. Would it be best to add a child table related to the Schools Table where each band would have it's own record related to their respective school. Then each band would be linked to a particular director. I would then add a band to the event instead of the director (and need a band-event join table instead). Is that the best way of going about it? For example: Enid High School Jazz Band I - Class 6A - Director Eric P. Jazz Band II - Class 6A-E - Director Bob S. Jazz Band III - Class 6A-EE - Director Robert A. Mustang High School Jazz Band I - Class 6A Steven S. Jazz Band II - Class 6A-E Steven S. Enid High School and Mustang would still get the total bill for entry fees (itemized by band) and I would still be able to have a list of all the directors. Other parts of the database are more director focused instead of bands, so this is the only case where multiple groups from the same school are an issue (that I've run into so far). It seems like this would work, but I wanted to run it by the experts first. Thanks for your assistance kt

Good point about the payments - don't know why I didn't see that before. Thanks for the direction. I'll give it a try kt

Hello, I need some advice on formatting a portal that has a lot of fields. I am enrolling students into a summer music camp (i.e., Event) and have to be able to keep different joined records for every year and keep separate records if the same student enrolls in multiple camps during the same year. Students -< StudentEvents >- Events Enrollment works fine, but the trouble is that I have a lot of data that needs to be in most StudentEvents records such as: Heath Form (just a yes/no box); First Payment, Type, Receipt; Second Payment, Type, Receipt; Final Payment, Type, Receipt; Roommate Preference; Roommate School; Dorm room; Additional Information; T-Shirt date ordered, etc. Really too many fields to put on a horizontal row (with what I know now). Would it be easier for data entry (around 1000 new entries over the period of 45 days) if I use the Students Table for data entry of the Student's information (the information that won't change from camp to camp), then use a popup window whose layout would be based on the StudentEvents table to enter the relevant information in the join table for one particular event? Or is there a portal display trick I don't know about? On my current Students Table layout I only need to view a few fields regularly from the StudentEvents table after that information is entered, so only the data entry part is where I can't figure out how to easily present the data. Thanks for your assistance! kt

For example, this year we have a set of students that will graduate in 2012. We send them a certain set of letters/emails. The students who graduate in 2013, get a different set of mailings. Next year, the 2013 set will get the mailings that the 2012 group received and so on. So I guess the individual student's graduating year never changes, but our sorting does - easily done with a find though. I just was curious if this was similar to a class/semester situation, but I see now that it isn't. High school students, especially those that audition for us, will have ACT Score, GPA, Parent's name, Music Director's name, High School, etc. Things that are completely unimportant once they get out of high school and into the university. The university students will have a set of music requirements and some will be student-workers at our camps or for the department. Once that student graduates from college, none of that is important anymore and where they end up teaching and living is what we track. Perhaps I'm making it too complicated. I just didn't want a bunch of empty fields in every record that only apply to people at one, and only one, stage. I've been through the filemaker self-paced training and have learned a lot. But since I can't see down the road what problems I might encounter by making a poor choice up front, I want to set it up right the best I can. Especially since my day job is being a musician

Thanks for your reply. We get information about students in a variety of ways. Many are associated with a summer camp (which I have an enrollment table for), some are just names and addresses given to us from band directors or guidance counselors, some are in an honor band and some come to a clinic. Many students attend several things. One goal is to get all the students who have the same graduating date (which, of course changes every year) from all our sources the easiest. Ideally, I would have a layout that I could use a drop-down menu to select the graduating year and have all students we have contact information for be listed. Then I could break that list down further to instrument or voice type and send emails/letters to that subset. I can do this now through finds, but am curious if having the relationship do the sorting would be more efficient. I currently have an "Event" table that associates a high school student with a particular music camp or honor band. I have this primarily so I can run roll sheets and put students in elective classes for each camp they attend. Should I just make something like a "General Contact" record in the Event table to catch the students that don't attend any real event we have but we still have information about and want to contact? Does the distinction of being a high school student, a current university student or a contact (i.e., band director or alumni) become irrelevant and the information that is different about them goes in a separate table (or their "status")? Thanks kt

Hello, I am setting up a single database to manage our university's music department current setup of multiple databases. This is a ground up redesign and I'm needing some confirmation that I'm not doing anything the long way around. FYI, I'm using the FM Starting Point database as a jumping off point (although I am copying it manually so that I learn more about filemaker) for basic setup, design and layouts. The main conceptual trouble I'm having is with my Students table. We have hundreds of potential students every year that we track for recruiting purposes (and for their participation in a summer camp, honor band, etc.). I need a way to keep track of students that are potential recruits (current high school students) by their High School Graduation Year, current university music majors (and their associated requirements) and then graduates (alumni for donation purposes and future potential students they are teaching in public school). Any of these students can take one of two basic paths: HS Student (from one of hundreds of different schools) --> Our University Student --> Graduate (alumni) --> Public School Music Teacher or HS Student --> Other university's student Obviously we want to follow the first student type all the way through while the second student type becomes unimportant to our database. It seems the most logical to have a Student Table for high school students (summer camp participation, honor bands, etc.), a University Student Table for current university students and a Contacts table for everyone else. Some people will need to be moved from table to table, but it wouldn't be that many every year. Is this a good approach? Second Issue: We have a lot of letters/emails that go out when students become juniors and when they become seniors in high school (the form letter exist in our database). I need a way of easily sorting students that are coming up on graduating from high school. I was thinking of creating a separate table that contains the High School Graduation Year so that I can easy get the students we need to reach. Is this the best way of tracking students by graduation year? I could accomplish the same thing with a scripted find, but that doesn't seem as flexible. Is there anything else that should be added to this new table? With this approach, when I add a new student record, I also add a record in the graduation table, correct? I want to get my underlying structure correct so I don't have to redo a lot later. Thank you for your assistance kt

I think I follow you. I'll give this a try. Thank you for all your help kt

I would like to treat these rehearsals just like regular classes (because to filemaker they are the same), except be able to enroll multiple students at once into a single class. Currently I have to enroll one student at a time into the class. All classes and rehearsals are in the same table. Apparently I have a disconnect on what I think is happening and what is actually happening in my join tables and therefore doing a poor job explaining what I would like. I may be making things too complicated. Here is my table layout: Students -< Enrollment_Join > - Classes_Rehearsals -< Faculty_Class_Join >- Faculty Currently, I can select a single student and enroll them into a single class. Then assign a faculty member to that class and get a list of students in the class via the assigned faculty member (or from the student side, list the faculty member's name). I would like to do exactly the same thing except take a set of found students and put them in a single class - or have a script loop through the found set and enroll them. I'm probably going about this all wrong... Does this help explain a little better? Thanks for your patience with my inexperience kt

That would give me the list, but it wouldn't be tied to the faculty member teaching the rehearsal. I would like to get rosters for all classes and rehearsals to which a particular faculty member is assigned.

Hello, I am wanting to create a script that will "enroll" a found set of students in a particular class. I have a camp database hosted on FileMaker Server 11 with clients running FileMaker Pro 9. This pertains to my summer music camp database that I've been working through some of here: http://fmforums.com/forum/topic/78174-value-list-of-current-employees/ I have a students table, an enrollment join table, and a table of classes/rehearsals. I also have a faculty table and join table to link faculty who are teaching each of the classes/rehearsals. The problem I will encounter is enrolling the students into a rehearsal. This is a band camp, so students audition on the first day to be placed in a band based upon skill level. We don't know what band and section rehearsal they will be in until they audition - and we have to process 500 students in a short period of time. In order for me to easily create roster sheets for the faculty, I would like a way to automate enrollment into a certain rehearsal/class. For example: Students Johnny - Trumpet Sue - Trumpet Bob - Trumpet Billy - Saxophone Stacey - Saxophone Jill - Saxophone etc. Bands Blue Band Red Band White Band Gold Band Based upon auditions, we enter the following manually into each students' record: Johnny - Trumpet - Blue Band - Chair 1 Sue - Trumpet - Blue Band - Chair 2 Bob - Trumpet - Red Band - Char 1 Billy - Saxophone - Blue Band - Chair 1 Stacey - Saxophone - Blue Band - Chair 2 Jill - Saxophone - Red Band - Chair 1 etc. For the band rehearsals, I can easily create a roster by just searching for "Band Name" and sorting by section and chair. But each section in every band has different section rehearsals with a different faculty member teaching each section (around 48 different section rehearsals). There are also elective classes (that are different from the band and section rehearsals) that the students enroll ahead of time. I have a script already that loops through the teaching faculty and prints a roster for each of their classes. I would like to tie into this eventually so a single faculty member will be able to get all their rosters at one time. So, in this example, I need a script that will search for "trumpet" and "blue band" and enroll the found students into the "Blue Band Trumpet Section Rehearsal" Class and the "Blue Band Rehearsal" classes then do the same for each instrument and band combination ("trumpet" and "red band", "saxophone" and "blue band", etc.) - which will be about 48 different rosters. I can do this manually when entering in band and chair information, as we enroll them in the elective classes they pick out ahead of time, but is a little tedious and time consuming. Any thoughts on how to build this script? Thank you for your assistance. kt

Sorry, I'm not explaining very well. For example, at last year's biggest camp, we had 71 students who played flute, 105 students who played clarinet, 69 who played saxophone, 92 who played trumpet, etc. In order to get a balanced instrumentation for each band, we have to audition students to get those with similar ability level in the same group. The top band last year had 12 flutes, 17 clarinets, 11 saxophones, 14 trumpets, etc. There is never a hard and fast X number of flute players in band 1 and Y number of flute players in band 2. The amount of students in each band should be similar in ability level to each other so we don't have beginners in the same group as advanced players. We don't know their ability level until the first day of the camp. After the auditions, we add information to each students' record in the database - the band they made (essentially band 1 through band 5) and the placement within each section. Since different faculty members rehearse different sets of students (Mamie rehearses the top band's flutes, Dianne rehearses flutes from the 2nd band....), this allows us to give a roll sheet to an assigned faculty member for all the flutes in auditioned order (aka "chair order") for band 1 and all the other sections too. It also allows us to list students, broken down by section, in the final program for each band. Each camp has a completely different set of students, classes and daily schedule from each other. We have a choral camp and guitar camp that don't even have auditions. That's why I had initially setup each camp of students with their own table because they don't relate to one another at all for our purposes. Some camps do have similar faculty members, but they teach different classes and rehearsals at the different camps. Is that making any sense? Thanks for all your help. Seems like I need to do more reading and planning to avoid problems in the future. What I've got now works fine and is so much better than what has been done in years past - but it's definitely a work in process. Thanks kt

In two of our camps, students audition on the first day. In the biggest camp, there are 15 sections (flutes, clarinets, etc.) and they are filtered into 5 bands by ability. Each student is ranked within each section. Faculty are assigned to audition students by section. Section order and band order is critical as it is used for roll sheets for band rehearsals and section rehearsals as well as producing a program with names of the students in chair order within their section. Chair order is a big thing in the band world. Taking roll and keeping track of 500 or so minors is also pretty important to us too (-; In the database, each student will have an instrument. After auditions, we will enter in the chair and band they made. From that information, we create roll sheets for the band rehearsals and section rehearsals. These are sorted by Band, Instrument and then Chair. It's much faster to take roll in a large band if the roll sheet is in the order in which they are sitting. The most complicated camps have 2 elective classes, 2 band rehearsals and a sectional rehearsal each day - each with it's own roll sheet and faculty member teaching/conducting/rehearsing. The "First Day" "Last Day" fields are mainly for schedule headings in printable layouts. The first day and last day of the camps have different schedules than the middle days. Auditions on the first day, and concerts on the last. There are also extra activities that are different each evening. None of which is really important in the database, but it's one less date that I have to change in 5 different places for printouts. That was probably more than you really wanted/needed to know. It seems very convoluted, but actually works very smoothly. I'm not really familiar with an ERD. Is there a place you would recommend to start? Thanks kt

Thanks. Design is more important at this point, so I'll get back to the value list issue. This is obviously the way to go with the database. <deep breath> I've started another database from scratch with this idea in mind. Would you or someone else mind looking at it and let me know if this is the direction I need to be going with it? Two questions based on tables I currently have that weren't covered so far. - Two of the camps have auditions. Each student auditions (with one to three faculty members) and is placed in a band ranked by chair. That data can easily be entered into each student's record. Should I treat each auditions just like any other class/rehearsal? It only happens once per camp. - I have scholarship applications that I don't know where to put. Students make a separate scholarship application and may or may not apply to the camp. The scholarships are then awarded and need to be applied to a student's camp record - but only if they have applied to the camp. Some students don't attend the camp if they've applied for a scholarship, so they may need a separate record. Do these need a different table? Thank you so much for your assistance. I'm sort of blinded by the way things have been designed, I keep wanting to use that as my template. However, I know most of my weird problems and workarounds will go away with this approach. kt Music_Camps.fp7.zip