Viewing records in columns and NOT in rows

[messadua]

Hello everybody,

 

Reporting from Filemaker.com "When you view records in a table, FileMaker Pro displays data in rows and columns. Each row displays a record, and each column displays a field."

And that is fine in most of the occasions...now what if one would like to invert this order ? In other words...would it be possible to set the layout (or data) so that each row displays a field and each column displays a record ? 

In particular I can very easily obtain something like this:

 

Exam Date   Field1   Field2  Field3

1 Jan            A          A             B

2 Jan            B          C             D

3 Jan            E          F             G

 

But would it be possible to twist the layout so to obtain something like this:

                      1st Jan     2nd Jan    3rd Jan

Field1                etc.         etc..          etc....

Field2

Field3

 

?

 

As always, thanks a lot to everyone !

[brian rich]

I think you are asking for a Cross Tab report, sometimes called a Pivot Table. 

 

You can't build these as a standard report in Filemaker, but there are various ways to achive a similar result.

 

See here: http://fmforums.com/forum/topic/78969-reporting-data-in-a-grid-pivot-type-layout/

 

HTH

 

Brian

[Lee Smith]

This question as come up in the past, a search for "Pivot" 

[messadua]

Here we go again : )

 

I have been doing my homework and the Demo from Comment has been extremely helpful (thank you Brian for pointing me in the right direction, and of coursde thank you Comment to share such a wonderful solution). Now, I have been analyzing the Demo, and I made some progress, but still not there.

Here it is my understanding of comment's work:

- The Cross-Tab or Pivot is obtained by using several 1 cell portals so that a calculation is filling the fields in the portal

- The Demo is obtained using 2 classification criteria, by Year (columns) and by Location (rows)

- The values for "Year" and Locations are populated by a value list, obtained from fields in the Child

- Finally, the computation is so that for each cell, we are able to set the column and the row interaction, defining the final value of the cell (in the example that is "Value")

 

If I am correct, what I am looking for is a little bit simpler. In my envision, I should obtain a Cross-Tab that uses only 1 classification (Exam date), the rows would be populated by the crude numerical value of each test performed in the specific date. Something like 

                                          Jan 2                Jan 3             Jan 4

Syst. Blood Pressure          145                   155              130

Diast Blood Pressure           90                     85                95

Field3

Filed4

....

To accomplish this, I can use the Get (ValueListItems) as shown by comment creating a value list from "Exam Date" (for the columns, and this is the working part) 

Then I should obtain a second value list (for the rows) reporting the different Tests performed (Systolic BP, Diastolic BP etc...)

Finally, I should be able to populate each cell with the value of the test (Syst. BP) for the specific Date.

 

The computation I would use, resemble (of course!) that one used by comment: 

 

Let ( [
col = 1 ;
row = 1 ;

hValues = ValueListItems ( Get (FileName) ; "Years" ) ;
vValues = ValueListItems ( Get (FileName) ; "Locations" ) ;

h = GetValue ( hValues ; col ) ;
v = GetValue ( vValues ; row )
] ;
h = Child::Year
and
v = Child::Location
)

 

but, although I was able to obtain a satisfactory result using the hValue (Years) or in my case Exam_date, I do not know how to modify the vValues to adapt to my setting, since Locations in my case is not a categorical value (i.e. a field which can have only few, fixed, values) but is a true numerical (any value from 0 to infinite...well in theory at least).

 

As usual, thank you so very much for the help in this !

 

Diego

 

 

[comment]

If I am correct, what I am looking for is a little bit simpler.

Possibly it's a lot simpler - although it's more likely I am missing something here. Going by your description above, you could have three side-by-side portals, each showing one row only. Make that one row very tall and place all those fields in it. Filter each portal by the relevant date (this could be done dynamically, using a list of patient's test dates).

In any case, fields are not records and you will never get the same kind of flexibility in displaying fields as you have with records.

[messadua]

Michael !

 

you have been outstanding (as already several times in the recent past : )

As you said, my goal was lot simpler than your Demo, so I did as you had suggested: first I have defined an Exam_Date value list from the field exam_date (to obtain a dynamic filter by date), then I created 3 (o more, actually, it depends on your needings) portals, 1 row only, but tall enough to keep all the fields I need to report (SBP, DBP and so on). I have put the portals side by side, finally filtering each of  them with the following formula (adpated from your Demo):

 

Let ( [
col = 1 ;

hValues = ValueListItems ( Get (FileName) ; "Exam_Dates" ) ;
h = GetValue ( hValues ; col )
] ;
h = ExamTable::exam_date
)

 

Change the calculation so that col = 1 is referred to the first column, col = 2 to the second etc...and Bingo !

 

Again, thank you so very much, I have learned a lot in the last days and that is always a nice experience

[comment]

Actually, it could be even simpler (and consequently, faster): instead of filtering the portals, set each portal to show a different initial row.

You see, the big difference between that demo and your situation is that in my file each "cell" summarizes many records; in your case it's merely a matter of pointing to the correct single record.