Dynamic Tables (again..)

[eibrahim]

I asked this earlier but the answer was not very clear. So I am rephrasing the questions.

Setup:
A PROCEDURE has ATTRIBUTES AND COMPONENTS.
An ATTRIBUTE has ELEMENTS
A COMPONENT has ATTRIBUTES and these ATTRIBUTES have ELEMENTS too.

PROCEDURES are stored in the table TB_PROC
PROCEDURE's ATTRIBUTES are stored in the table TB_ATTR
COMPONENTS are stored in the table TB_COMP
COMPONENTS' ATTRIBUTES are stored in TB_COMP_ATTR

Here is the twist:
The elements for each attribute is stored as a column in the attributes' tables. These columns are created dynamically as needed. So if a new attribute is inserted with elements "temperature", "cook time", "hardness", 3 columns will be added to the TB_ATTR temperature, cook time and hardness. Now if another attibute is added with element "Time", again a new column (Time) is added to TB_ATTR.

I want to write a report that will report on a procedure and display all its attributes and elements. How do I do that If I don't know what the columns are going to be? How do I skip over null elements too? Because for the second attribute that contains "time" it does not contain "temperature", "cook time" etc.. and I don't want to display them if they are null.

Thanks for any help.

 

[kenhamady]

The columns are created on the fly? That will give CR a serious problem, because (as you suspected) CR needs to know the columns in advance.

What happens to the previous data when the columns are replaced?

You may have to read up on unbound fields and how bind them at runtime. That might help in a situation where the columns are changing. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[eibrahim]

If you have columns temperature and time. Then later column strength is added nothing happens to temperature and time or their data. So columns are added but no columns are removed.

 

[kenhamady]

And there are no limits on the columns that can be added? You can't just say that these are the fifteen columns that are possible, add them all up front, and then fill them as needed?

This is an unusual approach. In most environemtns like this I have seen two columns, one for the value, and one for the TYPE of value. So instead of adding 3 columns, you add three records, one record for each type. So you have a one to many relationship with the parent record.

In your scenario, I think you will need to use unbound fields. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[eibrahim]

Well we know the dynamic columns will go up to a max of 30 and maybe not. We are debating the possibility that we add all 30 up front and then add them all to the report up front.

But how do I only display the ones that are filled out. So if attribute1 only needs column1 and column13 while attribute2 on the same report needs only column4 and column8.

Remember this is going to be all the attributes for a certain procedure.

Can I hide/show fields and labels dynamically? I know how to hide a field if it is null but how do I hide the column header if all attributes didn't use that column?

NOTE: I can not use unbound fields because I am using the web reporting component of Crystal reports which doesn't support unbound fields.

 

[kenhamady]

I don't see a practical way to do what you want given that table structure.

If you were using the more common table strucrure described above, a simple cross-tab could be used to give you dynamic columns in the report. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.