How to dynamically build database field name

[volkyl]

I have columns in my DB as such:

price_tier1
price_tier2
price_tier3
etc...

I have a for loop wherein I'd like to iteratively pull back the value in each column:

for i := 1 to 10 do
(
tierColumn = "table.price_tier" + Cstr(i);
tier := {tierColumn}
);

Unfortunately this is giving me the "field name is not known" error. I'm guessing you cannot use variables as names for database fields.

Any suggestions on how to overcome?

Thanks!

 

[IdoMillet]

Please explain what you are trying to achieve.

Knowing the objective would make it easier to suggest a solution.

- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[volkyl]

The objective is to calculate a total cost based upon the different prices in each price_tier (and incidentally, quantities in another group of columns: qty_tier1, qty_tier2, qty_tier3, etc.).

The formula will eventually do a comparison between the current tier, table.price_tier(i), and the previous tier, table.price_tier(i-1). It then does an arithmetic calculation using table.price_tier(i) and table.qty_tier(i) (e.g. table.price3 * table.quantity3 = cost)

basically I just want to reference a database field name with a String variable.

 

[IdoMillet]

Sounds like you could simply use the Choose function:
----------------------------------------------------------
Choose (i, {price_tier1}, {price_tier2}, {price_tier3}, ...)
----------------------------------------------------------

hth,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[volkyl]

Thanks IDO!

You are more helpful than Crystal's support!

That approach did indeed work.

When looking at the documentation for the Choose function, it says that all of the arguments following the first must be of the same type. What type is a database field such as {price_tier1} or {price_tier2}? They are certainly not strings, otherwise my approach would have worked.

I ask because if I knew the type, perhaps I could access a function or field that would allow me to set the "name" of the field dynamically.

Thanks again!