Hi All,
How we can fetch list of record data from sql. i have created structure and write query in sql to fetch record but it is fetching single record only not multiple record in secondlevel no. please help. please find below attachment.(traditional web App)
thanks
Hi @ritu kushwaha,
Please check that you're storing the child records in a List and not overwriting a single record in each iteration.
If possible, please share your OML file so I can review it in more detail.
Hope it helps.
Thank you.
Hi @Nilesh Trivedi ,
Thankyou for giving response. not possible to share oml file.
Thanks
Hi @ritu kushwaha ,
Are you able to replicate your use case in a sample personal OML and share it?
To display a list within a list, you’ll need to place the sub-list inside a separate block. That block should have an input parameter for the main record’s ID, which you can use to fetch and display the related data.
Hi @Mihai Melencu,
no we are fetching record through sql query and we don't need to use block our requirement is show data in expression. not able to recreate sample oml.
Hi Ritu,
Mihai's answer is correct. If you do not understand a certain solution, or why it is a solution, you should not just dismiss it.
It looks like your SQL is returning only one record instead of a list — this typically happens when either:
You are assigning the output to a structure or a single record, instead of a list.
Your SQL query is correct, but you're using ExecuteScalar or assigning the result incorrectly in the logic.
Regards,
Pankaj
I fear you do not understand how SQL (or an Aggregate) works. By definition, a SQL query will return one or more rows (records) of data, each row containing one or more columns (attributes). It is impossible to have SQL return a row where one column has multiple values.
There are basically two solutions to your requirement:
I believe your SQL only retrieves a single entry, but you need to compile a list of SecondLevelNo values. Below is a solution for you (for Traditional Web App): ✅ Steps:Step 1: Go to Domain and Structure and create a structure with name Example:SecondLevelData - SecondLevelNo (Integer or Text)Step 2: Select SQL widget and write the following SQL Query:SELECT SecondLevelNo FROM YourTable WHERE SomeCondition = @InputValue Set Output StructureMake sure to set the Output structure parameter for the SQL widgetNext, navigate to the output tab of the SQL widget. Then, add SecondLevelData as output. Do not forget to check ‘Is List’ = Yes ✅ Output List via Local Variable Set Output to a local variable of type List of SecondLevelDataDisplay in Table (Optional) Bind this variable to a Table Records widget to display the data, if needed.This way you will be able to return and view all SecondLevelNo values from the data as needed. Do reach out if you need an OML sample from me.
Hi @Sharath Kumaar K K,
thankyou that is the solution i want. now my issue was resolved thankyou so much. i used this query in my sql it is working for me.
STUFF((
SELECT ' , ' + CAST([SecondLevelNumber] AS VARCHAR)
FROM {FIS}
WHERE {FIS}.[FormInspectionID] = {FI}.[Id]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS ConcatenatedSecondLevelNumbers,