Dynamic allocation of Header columns

[miszczykr]

Hi,

My problem looks like dynamic allocation of rows and columns for each record using a portal. Is that possible? It is easy to dynamically add new records (in a portal) just by having a relation to the other file. Fine. But can I avoid defining static number of header fields?

This is my design. I have a Size Specification database where users will put some numbers for specific points of measurements. Each point of measurement will be in different line of my portal. Fine. Of course these measurements are phisically in second related file (relation one to many).

Now there is my problem. There are different ranges of sizes for their measurements, example of portal:

________________3|6|9

point1,description1|1|2|3

point2,description2|3|1|8

point3,description3|4|2|3

Second record may have the other Size Range (ex. 8,10,12,14)

________________8|10|12|14

point1,description1|4|6|3|1

point2,description2|2|5|2|4

So as you can see the number of Sizes (headers for a portal) is different. How it was designed before, portal had defined static number of Sizes (10). In my opinion it is not a good approach in programming. What happens if lets say in a feature ther will be a need for more sizes in one range? Or if usually there are just 3 or 4 sizes, there is a waste od space, which slows down the database.

I hope you understand my problem. Using basic or c++ I would create a dynamic 2 dimensional table. I know ralational databases work different. I spent some hours today trying to figure something out using different relations, but I can't. Is there any solution for that? Maybe I am just looking into one direction not seeing something really easy...

[CobaltSky]

Hello miszczykr,

I understand the concept you are wrestling with, though I confess that you lost me on some of the detail.

However in broad terms, what you are trying to achieve should be possible by using calculations within the parent file (which reference the portal contents via aggregating functions and text functions etc) to dynamically adjust the column headers as appropriate for the range of values which is within the related record set at any time.

To extend this approach further, you may also wish to add some calculations within the related file which can provide a richer base of information to drive the calculations in the main file.

So for example, to adjust the column heading to correctly reflect the maximum number of values in a particular related field, you might create a calculation in the related file called 'NoOfValues' which has the formula:

WordCount(ValueField)

and then create a calculation in the main file with a formula along the lines of:

LeftWords("Value One, Value Two, Value Three, Value Four", (Max(RelatedFile::NoOfValues) * 2)

By variations on this kind of technique, you will be able to have the column headings change according to the number of values, range of values, or - by the use of PatternCount( ) on the ValueListItems( ) function, referencing the specific values present in the current group of related records which are listed in the portal.

[miszczykr]

I had to be too tired today. I was so close. I created in related file calculation to count number of colums already and even a calculation having a whole list of values separated with comas (for other reasons). What misled me was that for some reason I wanted the headers being fields in main file. Obviously having them as a text is enough.

Thank you for your help.

[miszczykr]

Alright. The problem is not over yet. Let me try to explain to you what users want.

There is main file called Size Specifications. User chooses size range (ex. 7,9,11 or XS,S,M,L,XL etc) and then for selected size range he defines lines with measurements for these sizes. User wants to see a "table" in the database where columns are Sizes and rows are measurements for these sizes.

My first thought was creating a portal with measurements, so I am done with rows (I defined related database with - relation one-to-many).

The problem is that number of sizes changes. That means number of colums may differ in a portal. After last Size column they want to have a column called Adjustment. If there are lets say 3 sizes (7,9,11) Adjustment will be in 4th column, but for 5 sizes (XS,S,M,L,XL) Adjustment will be as 6th column. And this is my problem. How can I build such a portal?

I was thinking of defining a calculation field (RecordID & Number of colums) to create separate relations for each number of sizes (usually they vary from 3 to 10) and create separate related databases for each size. Then I could have different layouts in main file with separate portals where first would have 3 columns, next 4, etc. But my concern is that if there will be a need to use a size range with more than 10 sizes I will be stuck.

Any tips?

[CobaltSky]

Well, miszczykr,

I don't think that you can make it open ended - it will be necessary to set a maximum number of measurements that you are going to cater for, be it ten or twelve or whatever.

Once you've decided on a reasonable upper limit, I suggest that you set up a series of unstored calculations in your related file (one for each column), to produce the required heading.

To make this work, you will also need a field in the related file in which users define the measurement range for each record. From this field you will then have to derive the code logic for each of your ten or twelve header calculations.

Once the calculations are in place and are producing headings which match to the selected measurement range on each record, you will be able to add the related fields on your layout directly above your portal, to provide the dynamic header labels (which will be taken from the first related record displayed in the portal.

[miszczykr]

ok, that's about dynamic headers, but how can I go around with Adjustment Column problem. They want it to be right after last Size column. That means position of that column would have to change based on number of sizes. Do you think my idea with creating each related file based on number of columns and then creating as many portals on different layouts in main file is correct? Could that be done in easier way?

P.S. Im done with the part when user chooses range of sizes. There is a file (Range Sizes) having defined different size ranges.

[CobaltSky]

Is there any reason why you need to always place the adjustment value in the same field?

After all, all the other fields in the column will have a different meaning/value depending on what measurement range is chosen. Logically, the adjustment can always be found in the first field that doesn't have a measurement range value in it?!

If you need to reference the adjustment in calculations etc, then that could be implemented using the same logic that has already been discussed above.

[miszczykr]

yes, you are right. Let me play with this today and I will let you know, thanx