Vertical table database design

[GregDC]

I know I must just have a "designers block" on this one.  I have done similar things in DB2, Oracle, and MSSQL.  But in all these cases  the language I was working in had multidimensional Arrays I could use to solve the problem. 

Problem:  Create a vertical table with the flexibility of growing the width or depth of the table over time.   Displayed table is something like this:

                 |2010 | 2011 |2012  |2013 | Net Change between last two Years

Measurer  |Intern|Extern|Extern|Intern|

Factor 1    |3.5    |3.6     |3.4     |3.5    | 0.1

Factor 2    |2.7    |2.6     |2.6     |2.6    | 0.0

......

Factor 10  |4.0   |3.9      |4.3     |4.1    |-0.2

Weighted----------------------------------------

    Avg       |  3.5 | 2.9     | 3.8    | 3.5   |-0.3

 

Table can expand in the number of Factors and will expand in the number of Years

 

Needed solution:  what is the best normalized table structure for FM 13 that will deliver display (last year figures are editable)?

  1.  Yr, Measurer, Score 1, Score 2, .... Score 10, WgtAvg

  2.  Yr, Factor#, Score, Measurer  (If Factor# = 0, Score is blank, and Measurer if filled in)

  3.  Yr, Factor#, Score  and  Yr, Measurer  (2 tables)

 

With any of these proposed table layouts how do you get everything in the right display slots without a two dimensional array?

 

Thanks

   

[Kris M]

Perhaps your solution can be found by Googling FileMaker Cross Tab reports?

or virtual list techniques such as ...

 

http://filemakerhacks.com/tag/virtual-list/

[comment]

I don't follow your description that well, but in general, Filemaker has no mechanism to dynamically increase the number of displayed columns beyond what has been preset at the time the layout was designed.

 

 

Some alternatives you may wish to consider:

 

1. Set up a large number of columns to begin with and leave some of them unused until you need them;

 

2. Display your results in a text field, organized as a tabular array (tab separated columns, return-separated rows);

 

Note that fields cannot be scrolled vertically, so neither of the above methods can be expanded without limitation.

 

 

3. Use a fixed number of (calculated) columns with "pseudo-scrolling" (for example, 10 columns that show 10 years starting from a given year that can be bumped up or down using a script).