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 4.5

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. Let us explain. 

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. 

Additional Information

Video URL on how to open a Support Case - https://communities.ca.com/videos/5898-demo-how-to-open-a-support-case

You can download the latest version of TDM by following the directions in this document: https://support.ca.com/us/knowledge-base-articles.TEC1903942.html.   

To contact support, go to https://www.ca.com/us/services-support/ca-support/contact-support.html.