Database field in Formula Editor

[crazyp]

Hi, I am working with Crystal Reports in Visual Studio .NET 2003 and there something that I wanted to ask about database fields.

In example, I have a table named Cost with four colums which are Cost01, Cost02, Cost03 and Cost04.

I want to access to all 4s column with a for a for structure, but I can`t find the right way to access the fields with strings.

This is what I have tried :

dim i as integer
dim TotalCost as integer = 0
for i = 1 to 4
TotalCost += {"Cost.Cost0"+"i"}
next

(Doesn`t work)

This also

dim i as integer
dim TotalCost as integer = 0
for i = 1 to 4
TotalCost += {`Cost.Cost0`+`i`}
next

(doesn`t work)

dim i as integer
dim TotalCost as integer = 0
dim test as string
for i = 1 to 4
test = "Cost.Cost0"+"i"
TotalCost += {test}
next

(doesn`t work either)

Does someone knows how to solve this problem ??

Thanks

 

[synapsevampire]

Crystal won't let you dynamically build column names (annoying, isn't it?), try:

{Cost.Cost01}+{Cost.Cost02}+{Cost.Cost03}+{Cost.Cost04}

Or you can build a SQL Expression to do the same which would be performed on the database, but that's dependent upon the database, which you didn't share.

-k

 

[crazyp]

I know this method and I gave a simple version of my problem (sorry, didn't mention it). Thre problem is that I have more then 4 fields, I have like 50 of them. And for each record, the sequence is not the same, for instance :

First year :
Cost01 to Cost05
Second year :
Cost06 to Cost10
.
.
.

That is why I want to use a for structure, to simplify it :

(example)
dim i as integer
if table.date < now() then
for i = {table.index1} to {table.index2}
formula += {table.cost'i'}
next
end if

It would really simplify it, if I can find the way to combine the index with a "partial" field name.

(Thank you synapsevampire for your reply)

 

[synapsevampire]

Right, well, as I stated, Crystal can't do this.

If you can learn what type of database you're using, you might post it with the intent of doing this on the database, someone can probably supply the code to build a View.

Anyway, it sounds like a poorly designed database.

It might be simpler to round up the dbas, place them all in a burlap sack, place it in a vat of sizzling vinegar and then start over.

-k

 

[crazyp]

Are you sure crystal can't do it ? Or just you don't know how to do it ? (without offence)

Indeed, the person who designed the database have not a done a great job. I have redesign part of the database, but not everything, because it will take too much of my time. It is possible to do it through coding and SQL, but it will more time than the method that I have thought out.

So my first plan is to try to resolve it through crystal report formula, if I really can't find an answer to it, I have no choice to go through plan B.

Hope somebody got the answer to it.

(THK U for the reply)

 

[synapsevampire]

Crystal doesn't allow for this sort of dynamic field or formula creation. I've been writing code for over 20 years, and I'm very familiar with the concept, and have delved relatively deep into Crystal programming for 8+ years.

And no offense intended to you either, but I wouldn't address a bad design in such a kLuDGe fashion anyway. Create a database object that can be more readily maintained, if not address the database architecture in it's entirety.

Consider hiring a consultant well versed in databases and Crystal to assist you in this.

-k