Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Recommended Posts

Hi All


Hopefully someone here can assist me, and I hope I'm pasting this in the right place. I’m very, very, new to FileMaker coming from a long background of Microsoft Access design and use. Currently I am trying to use FileMaker to create a database that will be used by 5-6 tutors to record student attendance using FileMaker Go on iPads. This database will in turn interrogate and update data tables held on an SQL server. I have evaluative versions of FileMaker Pro and FileMaker Go installed, and my test database is talking to the SQL tables. So far so good!


In developing a front end I want to filter choices for tutors by, in order:


1. Course name

2. Academic year

3. Date of session

4. Tutorial session name


These filter choices would ideally be presented by either drop down boxes or lists of possible options and each choice would further narrow options of choices in each step. Once fully filtered this should then bring up a list of attendees of that session which the tutor can then complete.


In principle I can see exactly how this will work in FileMaker, but I am falling at the very first hurdle in that I cannot seem to understand the method behind filtering, or referencing a “choice” made in a drop down box. I am really liking the ease of use of the database design principles and the methods I have so far encountered, but the step from Access to FileMaker is a lot harder than I had anticipated in terms of terminology and method.


For instance, in trying to create a drop down box that references a simple table of data referring to the academic year, (which contains two fields: “academicYrID” which is a simple numeric field that I want to base further filters on, and “academicYrDesc” which is a text description of the academic year for ease of user reference), as soon as I make a selection from the drop down, (which displays the academicYrDesc correctly), the drop down box then displays the academicYrID numeric field associated with that selection and also changes the academicYrDesc field in the data tables to now store the numeric value instead of the description! In Access I can do this in the blink of an eye, but I am missing something in the underlying methodology used in FileMaker


So my first two questions would be:


1. How do I create a simple drop down box that references an ID data field, but displays a descriptive field when selected, (and doesn’t then overwrite that description with the data from the ID field!) and

2. Then filter further choices based on the selection in question 1).


And any references to useful resources that might help me further would be really appreciated. Also any downloadable examples of how this method works would be very useful. (I am a dab hand at reverse engineering!). I just feel if I can get this step sorted then I can really progress.


Sorry for the long post! Thanks all, looking forward to getting started in FileMaker!


Link to comment
Share on other sites

What you're seeking is known in FileMaker-land as "conditional value lists".



The most common approach is to have two fields in Table B (let's say Category and SubCategory) and a main table, Table A, that also has Category and SubCategory fields; the tables are related by Category = Category. From the perspective of Table A you create a value list of Table B::SubCategory based on related values only.


In Table B your records might look like this:


Cuisines..........Main Courses


Italian..........Chicken Parmesan

Mexican..........Chiles Rellenos


Italian..........Scaloppine Alla Paesana

Mexican..........Carne Adovada

French..........Canard Aux Champignon


In Table A, you pick the main Category (cuisine) first, and the value list that drops down from the SubCategory (main course) field, you see only the related values: if you pick "Mexican" you see "Chiles Rellenos" and "Carne Adovada".



In your case, you want to extend this to a third and a fourth field, where Table C contains three fields and another relationship to Table C gives you a conditional dropdown value list dependent on the TWO prior fields (CourseName AND Academic Year) and gives the user a choice of Date of Session; and then a fourth table, Table D, with four fields and another relationship giving you a conditional dropdown value list dependent on the THREE prior fields (CourseName AND Academic Year AND Date of Session) to give the user a choice of Tutorial Session Names (whatever they may be?). Finally, a fifth table, Table E, with Tutors in conjunction with their CourseName, Academic Year, Date of Session and Tutorial Session Name — a relationship based on all four of the latter fields lets you set up a conditional value list of appropriate tutors.



In each of your tables you are probably going to use serial numbers and use those in your relationships; and therefore your dropdown value lists are actually dropdown value lists of (for example) CourseName ID Number. So you were asking (also) how to make the dropdown display the descriptive text string in each such situation.


When you define Value Lists, you have the option to choose a second field, like so:




(In your case, unlike in the screen shot, you would select "Include only related values starting from" the table occurrence of the layout containing these dropdown values; but you would select Date of Session ID then check "Also display values from second field" and pick Date of Session. and so on.


The end user sees something like "32 February 3 2016" and selects it and 32 is what goes into the field. Then you use that 32 in conjunction with the ID number of the selected Course Name to create your next relationship. It's just a cascade of relationships, each anchored by the choices made by the end user thus far.

Link to comment
Share on other sites

Thanks AHunter3, that's given me a head start.


Thinking this through a little more over the weekend I think I was over-complicating things for myself. I can actually get the desired results from approaching the sort using Tutorial Session name as the first choice, and then date of session. A session on a particular date will already be related to a course and academic year, so half my battle is already sorted.


In experimenting with the drop down I have noticed that using a Pop-up menu seems to give me the desired result of showing the user their filtered selection, whilst still hiding the ID reference field, (which is much more user friendly). Is their any other reason for choosing this method, apart from aesthetics?


Thanks again.


Link to comment
Share on other sites

Popup menus also do not allow entries except from the value list. Drop down VLs will let you click twice and then freehand type a custom value. In both cases there are workarounds but that's the default behavior.

Link to comment
Share on other sites


  • Create New...

Important Information

Terms of Use