I have a calculation field cal_simple_search_result that generates and executes an SQL statement. The result is a list of record keys from a table called 'innovations' separated by ¶. This calculation field is the key for a relationship to the same table.
When the calculation generates a query like this:
SELECT pk_record FROM innovations WHERE UPPER(firm_name) LIKE '%TOLERANS AB%'
The result is this 5875
and the portal showing the relationship shows this record as it should.
When the calculation generates a query like this
SELECT pk_record FROM innovations WHERE pk_record= NUMVAL('5875')
The result is also 5875 but the portal shows nothing. My question is: why does the portal show the result in the first case but not in the second?
I checked/tried the following without result:
- the result-type of the calculation field is 'number' as is the type of pk_record
- pk_record and the calculation field have 'unicode' language setting
- I read somewhere that the ExecuteSQL function is a bit picky when it comes to field types, I added NUMVAL since the field that contains the search value is a text field.
- I tried queries of the second type that resulted in more than one value 'pk_record>NUMVAL('5875')'