Crystal Reports: Dynamically accessing Database Fields

Question

I'm working with Crystal Reports 2016 (Version 14.2.7) and I need to dynamically access database fields with generated names. My current formula looks like this:

Local NumberVar i;
Local StringVar fieldName;
stringvar array tmp_arr := [];

For i := 6 To 45 Do (
    varFieldName := "databaseName.fieldName" + ToText(i);
    tmp_arr := tmp_arr + split ({@varFieldName}, "@");

    // do some stuff with the splitted array
);

However, I'm encountering the issue that the field name is unknown. I want to access a database field while dynamically generating the field name. The field name is part of the curly-braced references and it's not recognized.

How can I achieve this dynamic access to database fields in Crystal Reports? Any guidance or insights would be greatly appreciated! Thank you.

Answer 1

afaik, you would need to use a UFL (User Function Library) for this because Crystal doesn't support dynamic SQL (except partially via Commands and embedded parameters -- but your case can't be handled with parameters).

Ken Hamady maintains a listing of 3rd-party Crystal Reports UFLs here. At least one of these UFLs allows Crystal formulas to construct and execute dynamic SQL.

Answer 2

Dynamically Access: In Crystal Reports, it's not possible to access database fields when the field name is dynamically generated, as Crystal Reports needs to know the data source structure at design time. If the requirements are complex and you have to dynamically access different fields, an programmatic solution outside of Crystal Reports is necessary.

Static access with Array: In this example we only want to count up the fields and do the same stuff with it. This was our solution:

1. Save all Database-Fields in an Array so they are static.
2. Iterate Array.

Local stringvar array fields := [
  {databaseName.fieldName1},
  {databaseName.fieldName2},
  ...
];
Local NumberVar i;
Local StringVar output := "";

For i := 1 To count(fields) Do (
  stringvar array tmp_arr := split (fields[i], "@");

  // do some stuff with the splitted array
);

output;