TDM: Dropdown list not working on TDM Portal but working on Datamaker.

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

We are using a list formula to create a dropdown with some data we have on a database table.
Is working correctly in Datamaker but when we try to use it in the TDM Portal is failing.

The formula that we are using is:
@aslist(@list(Any,@execsql(R,SELECT from REF_USER_ELEVATED_PRIV WHERE DATA_TYPE='ESN' and USER_ID='~USER~')@)@
***************************************************************************************************************
@aslist(@sqllist(R,
with temp as  (
       select ESN_APP_ACCESS AS APPID from REF_USER_ELEVATED_PRIV WHERE DATA_TYPE='ESN' and USER_ID='~USER~'
)
SELECT 'Any' from dual
UNION ALL
SELECT trim(regexp_substr(APPID, '[^,]+', 1, level))
  FROM temp
CONNECT BY instr(APPID, ',', 1, level - 1) > 0)@)@

We have tried with both options and both work on datamaker but as soon as we try them on the portal they fail.

The field we are bringing is a comma separated values which should be part of the dropdown one at a time.

Environment

TDM Portal 
Test Data Manager

Resolution

The issue is due to the @[email protected] function being called.
The "aslist(@sqllist9connection,sql)@)" function will cause the data to be stored in separate rows, instead of a single row. 

Our testing and analysis showed that Portal’s behavior regarding this defect is flawless and working as it is expected to.

Example:
Let’s imagine two scenarios where we need to return values from a database as a list: 
a) The user has 3 values delimited by a comma “,”. We have a single row having: 
1. “A1, A2, A3” 
b) The user has 3 values in 3 separate rows. Row#3 has values separated by a comma (a legitimate character) with no delimitation intended. So, we have multiple rows having 
1. A 
2. B 
3. C, D 


If we applied Data Maker’s behavior to scenario (a), then we will have a drop down list with: 
• A1 
• A2 
• A3 

And if we implemented Data Maker’s behavior to scenario (b), then we will end up with: 
• A 
• B 
• C 
• D 

However, here in scenario (b), the user expects the drop-down list to be: 
• A 
• B 
• C, D 


With all that explained, we suggest you extract the values you need from the database while having them stored in separate rows rather than being delimited with a comma.