Excel Type View in FM

[TechRangerBrad]

I am attempting to create a layout that looks like an excel

spreadsheet. My problem is that where as a row in a spreadsheet is

considered one record, I would really like to have each cell as a

record. Does anyone know of a way to accomplish this? Thanks

[bcooney]

Could you elaborate? I can't think of a data model that would need this.

[TechRangerBrad]

Basically, I have membership figures that have to be shown monthly by teacher for a school district. Attached is a screenshot of what I am trying to replicate. I am pulling this data in through a SQL query. This is just one month of data for a specific school. I am thinking I need one record for every date so 1/1/2007 would be one record for each teacher and class, then 1/2/2007 would be another record.

Can you think of a better way?

[Lee Smith]

Have you looked at the Table View?

[bcooney]

Why switch from Excel?

[TechRangerBrad]

There are various reasons to put it into FMP - the biggest being that I don't think I can specify which Excel tab I need the data to go into. You see one tab with one school. They have 19 other schools - I don't want them to have to go into each tab and run a separate query. Also, I can lock down FMP a whole lot tighter then I can Excel. Web based would be another reason. :

Brad

[TechRangerBrad]

To my understanding the table view only allows for one row per record. I need to put multiple records on one row. I hope that makes sense.

[Lee Smith]

Do you mean like related records, that you could show in a portal?

[TechRangerBrad]

Yes, but I can only display on record per portal row correct? So I would need at least 31 portals? Sorry, I am just having a tough time wrapping my brain around it. Not that I have a large brain :

Edited August 17, 2007 by Guest

[AudioFreak]

Yes, but I can only display on record per portal row correct? So I would need at least 31 portals? Sorry, I am just having a tough time wrapping my brain around it. Not that I have a large brain :

You would have 1 record in the main file that can be related to as many records as your relationship matches in the child file. I think you aren't getting the idea of relationships yet.

I would really like to have each cell as a

record

Not sure I totally understand why you would want that or see how it could be useful in any way.

Michael

[Fenton]

This is called a "cross-tab" report. There are a few ways to do it, none of them easy. Take a look at this site:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html

There is also a relational way to do it, which is possibly easier to understand (but maybe not); it requires even more structure.

Basically you are wanting FileMaker to show individual date related data as columns (which are fields in FileMaker). That is not what the data is; there is only 1 field Date, one "column." Spreadsheets can do these things because they are only loosely based on logical data, so they can also have "position on the screen determines relationship to other data", which a database in most ways does not (other than layouts, and portals).

Yeah, when you want to use "position," you'll say, "Why can't FileMaker do this easier?" But not having the limitation of "position" is what gives a database its flexibility and power. It's hard to get it both ways.

[TechRangerBrad]

I think I understand relationships at least with FileMaker (Don't ask my wife about this) - I think I need individual records as they will be coming in daily from a SQL import. Basically, the record with be a teacher, student, and the section they would be enrolled in which would then go to a calc field for a total number of students in the class for that teacher. Let me know if I am over complicating it : Thanks