How to arrange data for display as a simple spreadsheet format.

[bloke]

I hope I have the right place - I consider it to be layout issue.

My mind has gone blank - I normally figure these thing out my self given tiime.

I run a filemaker database, tables include Students, Subjects and Results. (obviously tables contain relevant fields). All related and it works. Heaps of scripting and display options etc.

Management wants me to use a simple spreadsheet with check boxes so they can view results etc. (Hey, that is the extent of their computer literacy)

Table view in filemaker does not help me that I can see. It displays all my subjects vertically and horizontally from the far left column is the data for each student as a line item.

They simply want a list of students vertically in column view and across the top horizontally the subject, descriptor and below that a column to place an x for the theory result and another column to place an x for practical result along side.

I actually did produce this sometime back but it was a flat database with a million separate fields and was clunky and slow.

Attached is a copy of the xls spreadsheet they want.

Appreciate any pointers on this greatly.

atte

BlokeDiesel Fitting Skill Assessment Tracking.pdf

[comment]

If they want to use a spreadsheet, perhaps they should be using a spreadsheet application. You could export your data to Excel for viewing and import it back if they do modifications.

To do this in Filemaker, you will need a portal for each subject. You can either filter the portal by subject, or create a dedicated relationship for each one.

[RodSierra]

Not a direct answer to your question, but I've heard this so many times. The issue is most likely that your dealing with users that are used to dealing with flat spread sheet type data formating. In the instance you describe, instead of recreating this request you should expand on it and take advantage of relational data, and portals. I'd have a portal on the left side of the layout with the ability to filter students by whatever is important to the user. Clicking on a student record would bring up significant student details and a photo above the portal. To the right a second protal with the class records for that student and their results. You can then extend this further by adding attendance history, or grade history from the same table. This is as far from the original flat table as you can get, but will most likely provide a better view of what the user wants to see and do. You may find as we have that after implementing this type of layout you will soon get requests that are more directed to showing related data on the same layout, and making your users more productive and database savy than one dimensional spreadsheets.

[bloke]

Thank you for your input. It is difficult to get people to shift from their "simple way" of thinking and for them to understand by moving towards databases, even though the data may not be displayed in a manner to which they are accustomed, it is a more powerful mediam in which to capture and analyse information. They tend to used spreadsheets for even less than the calculus for which they are intended but that is the mindset of MS Office oriented people. In regards to importing and exporting to excel I will walk 100 miles before I go there - I try not to be too dictated to by others who have little idea about what it is about. I generate all my reports as is within filemaker with a click and script of a single button. There is no heartache. And yes, I have considered the use of portals (and will in fact have a play), but again whilst I can see the benefits I am not going to achieve the simple output sort by management. (I am the odd one out - others make do with speadsheets and more spreadsheets whilst I consolidate all my activities into filemaker related databases which is beyond their understanding and comfort level) Your input has given me some inspiration to no roll over.

[comment]

I have considered the use of portals (and will in fact have a play), but again whilst I can see the benefits I am not going to achieve the simple output sort by management.

I don't see why not.

I try not to be too dictated to by others who have little idea about what it is about.

Is that directed at me?

[bloke]

No, the quote "regarding being dictated to" is directed to those in my workplace who urge you to use only what they use and nothing more. They don't like rebels. Go outside their comfort zone and they erect a major barrier. It is also for that, I make the point unless I present the data in the format they consider "appropriate" I am on a hiding to nothing. I prefer to keep it a 100% filemaker solution. I am currently the only user of filemaker (I have been using FM since around 1990) in an office of MS office eccentric people. I have maintained my student data in a filemaker solution for quite some time. Whilst the manner in which I display data and make reports is acceptable and meaningful to me, unless it is in a sheet in their format with coloured boxes and crosses it does not cut the mustard for them. I quite often feel they would be better off with a box of crayons and a sheet of butchers paper. I am going to have another look at it but trying to make a layout to keep them happy is going to be an untidy affair.

[comment]

See if this helps:

FilteredColumns11.zip

[David Jondreau]

Spreadsheets are an excellent way of presenting information in an intuitive manner. All that data in a single glance? Priceless.

The problem isn't with your users. The problem is that Filemaker does not make cross-tab reports easy for developers to construct.

Search this Forum for "cross tab". Google "cross tab Edoshin".

[comment]

This doesn't seem (to me) like a cross-tab issue.

[David Jondreau]

True, it's not summarizing data, but it is presenting data from a table in a way that looks like a cross tab, at least to me. I think the technique of a repeating calculation field to present data in a table view would work well.

[comment]

Maybe, provided they only want to present it - not modify it. I am not sure how to read this:

They simply want a list of students vertically in column view and across the top horizontally the subject, descriptor and below that a column to place an x for the theory result and another column to place an x for practical result along side.

[David Jondreau]

If it's just an 'x' in the field that's easy enough with a field entry trigger.

[comment]

True - I just think that providing direct access to the field is even easier.