Using field Alias for search criteria comparison.

rbuser

Hi,

Our application allows the user to define some custom fields in the
database. We reserve 5 text fields in our database for these custom fields.
They are called "UserTextn", where (n) is 1 to 5. When the user defines a
custom field, they enter a prompt for the custom field. We use that prompt
to form field "Alias" in the ReportBuilder dictionary. We update the RB
dictionary every time the user redfines these custom fields. Note that the
database field name always remains UserText(n). We only define the RB field
Alias with the user-defined prompt. This allows the field to appear in the
query designer using the user's prompt as the field name instead of the
ambiguous UserText(n).

The problem: My customer would like to set up a report that has search
criteria such that it compares one field to his custom field. If he enters
a search criteria of:

Field Operator Value
------- ----------- --------
Field_A < Table.FieldAlias

RB creates SQL that reads: WHERE (FieldA < 'FieldAlias')
so it doesn't work right.

But if the user enters criteria of:

Field Operator Value
------- ----------- --------
Field_A < Table.UserText5 (the
physical field name)

RB creates SQL that reads: WHERE (FieldA < Table.UserText5)
and that works (due to no quotes around the Value expression).

Is there any way to get RB to *see* that Table.FieldAlias really refers to
Table.UserText5?

Keep in mind this is the end-user designer, so I can't hard-code anything
for this particular report. It would need to be a generic translation if
anything.

Thanks,
Vinnie Murdico
Software with Brains, Inc.
SWBTracker - Professional Bug Tracking Software
http://www.softwarewithbrains.com

digitalmetaphors

No, when you are using the query designer, it is assumed that you don't need
an alias to configure the query. You need to use the tablename.fieldname in
this case.

An alternative is to make this an autosearch criteria and set
ShowAutosearchDialog to false. Then use the RAP OnGetAutosearchValues event
to set the criteria value. Please see the autosearch demos for an example of
assigning the autosearch value for an end user autosearch criteria created
in DADE.


Cheers,

Jim Bennett
Digital Metaphors