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.
TDM Portal
Test Data Manager
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.