case insensitive relational link problem

[Dr. Zathras]

I want to create a relational link to a field in a table hosted on an Oracle server. I have read-only ODBC access to the Oracle database so cannot make a calculated lower-case field at that end to connect to, but the key field I'm using is an email address with unpredictably mixed-case characters. The relational link only works if the case matches for both fields so I appear to be stuck. Any helpful suggestions would be much appreciated.

Colin

[comment]

How many records in total will there be in the external table?

[Dr. Zathras]

Around 20,000.

[comment]

Ouch. Still, I don't see that you have too many choices here. Try defining another relationship to the external table, using the x relational operator. This will enable you to retrieve a list of all the e-mail values in the table. Then you can get the correct version of your key value by:

Let ( [

listOfValues = List ( AllRecords::eMail ) ;

v = ValueCount ( Left ( listOfValues ; Position ( ¶ & listOfValues & ¶ ; ¶ & KeyValue & ¶ ; 1 ; 1 ) ) )

] ;

GetValue ( listOfValues ; v )

)

[comment]

Actually, come to think of it:

FilterValues ( List ( AllRecords::eMail ) ; KeyValue )

is both simpler and (in case the eMail values are not unique) will return a multi-key of all permutations of KeyValue occurring in the table.

[Dr. Zathras]

Yes, that should work. Many thanks for the help.