Hope this is the right subforum for this

[Aaron Nutter]

Hi everyone. New here, just trying to see if what I am trying to do in filemaker is possible, as I am pretty novice with the software. 

 

So I have been working on a banking database for a student deposit program for a financial literacy program for schools. The database stores student information such as the school year and the corresponding grade, homeroom, and teacher for that school year. This is all identified by a bank ID # given unique to each student, and I use that as my primary key. 

 

We are coming up on the second year of the program, and I need to update these records for the new school year, grade, teacher, and homeroom for these students, as many of them are returning to the program. The problem is that we don't want to get rid of the old information. I need to be able to somehow make a note of the previous information that was stored in these fields, perhaps in another field somewhere. Is there an easier way than manually entering the previous information somewhere? Is it possible to set these changing fields in such a way that when they are modified, the previous values are stored and/or posted into another field? This new storage would need to keep track of multiple values of this field, i.e. for year 3, it would have 2 previous values for each of these fields. 

 

I know that I can make a dropdown list to select school year and have it display the corresponding values for the remaining fields, but I really just want a note somewhere of what the previous values were. Thanks in advance for the help!

[David Jondreau]

You need more tables. You need to split the current table into student information and year information. A student's info, name, student ID, bank ID, etc is in one table. The info about a program-year: teacher, homeroom, grade, goes in another table. Each student will have more than one record in the program-year table.

[Matthew F]

... and if you follow David's suggestion you can then display the data from previous years in a portal on the students page.  This is preferable to appending additional data onto a single field. 

 

If you don't like the look or functionality of portals, then you could consider using a repeating field.  One advantage here is that you can display the information horizontally, not just vertically.  Repeating fields have less built-in functionality but they might be handy if your repeating units are well defined (e.g. year 1, year 2, year 3, year 4) and don't need to be used in relationships or other complicated ways.

[LaRetta]

I wouldn't recommend using repeating fields unless the User were advanced and even then I would not recommend it for data and this is particularly true if the reason is simply because it 'goes horizontal'.  Data structure should not be decided based upon UI requirements.  If you meant only for display, Matthew, then let's clarify for Aaron so they stay on a solid track of relational structure.  

Edited August 20, 2013 by LaRetta

[Fitch]

Definitely use a related table.

 

By the way, since you brought it up... yep, it's the wrong forum topic -- Relationships might be the "correct" one, but it's no big deal.

 

I would recommend though, that for future topics you pick a more descriptive title. It will help you get more responses, more quickly.

 

Welcome to the forums, Aaron!

[Matthew F]

I agree repeated fields are lousy in most situations, and most certainly here too.   

 

A portal is the way to go.  However, it may or may not be necessary to create a second table.  The alternative is to create a self-join relationship and show prior years data for the same individual in a portal. The portal could be filtered in order to not show the current year's data, if it is already displayed elsewhere on the layout.  

 

The statement about inputing data, and having the data in the other fields shift in location, is probably not the best way to make it work.  Instead one would make a new record (i.e. for the current year) and then the prior years data would automatically be displayed in the portal as a reference.  

[Fitch]

It's generally a Good Idea to use one table for Person, and other tables for other stuff.