Skip to Content (Press Enter)
UserImage.jpg
Greg Adams
77Views
4Comments
Solved
Download data from client side (not from server) after SQL query
Question
SQL
Reactive
Best Practices
Import/Export Data
Application Type
Reactive
Platform Version
11.54.6 (Build 62311)

After an SQL query, the output result has been assigned to a client variable called QueryResult.

This has been displayed in a table on a screen.

If the user wants to keep a copy of this data, there is a button to click to download it from the browser to a local file.

What method would you suggest to convert the QueryResult variable into something that can be stored on the user's device?


With the convert to Excel widget calling to me, I had assumed a server action of converting to Excel, but that was wrong because the data which had been in the server logic has ceased to exist. (The convert to Excel instructions all seem to use a get table from the database, which would be impossible anyway as the SQL query is bespoke, under control by the user.)

The data is on the client device, just not in a format that can be easily saved and used.  

(The data is a structure with rows. There are about 10 headings. Often there would only be 10 to 20 rows, but there could be 3,000)


I have read & searched in the forum & on YouTube & found what I think may be the way to do this.

It uses an extension  CSVUtil which can convert lists to comma separated value files. 

What is your opinion? 

Is there a better way?

If you have used CSVUtil to download a list, any advice? 


Links to

CSVUtil documentation

CSVUtil YouTube how to

0
0
22 Apr 2023
Copy Link
2021-09-06 15-09-53
Dorine Boudry
 
MVP
Solution

Hi @Greg Adams  ,

yes, the sql output only is available for you to use inside the server action, and in the call chain down from that as long as you pass it to the output, and so finally back on the client side.  

Any bits of logic along the way (like server actions) that have ended, have no longer the date that was inside their scope.

And yes, the 'RecordListToExcel' tool is available on server side, so that's where you'll have to use it.  

But one big difference that you have to think about, is that in your UI you are probably paging the results, so in your client, you would not have available all records, only the ones currently shown.  In the excel however, you do want all records that fit current filter and sort etc.  So it does not really make much sense to turn into an excel whatever you have on the client side at any given time, you'll have to go back to the server for the full set anyway.

I would aim at using the same SQL widget to service both things, avoiding double maintenance in case things like filters and joins change.  (assuming of course, your end users want to get a result set in the excel matching the filters and sorting and so on they are currently using for the list in the screen)

So encapsulate it in a separate server action, taking an IsForDownload boolean, and make the "OFFSET ... FETCH NEXT ... ROWS" bit of your excel a separate input parameter to the SQL, leaving it empty in case of Download.

The output of that server action is both the list, and the binary.  In the server action, only convert the sql result to an excel binary if IsForDownload.  Vice versa, I would not bother filling the output list if it is for download.

In your data action for populating the ui list, pass in IsForDownload false, and pass in startindex and maxrecords of the screen.  In your screen action for download, pass in IsForDownload true.

Dorine

See solution in context
0
0
23 Apr 2023
Copy Link
UserImage.jpg
Greg Adams

Thank you @Dorine Boudry for the detailed response which was exactly to the point.

About 12 hours ago I had done enough to get the download running using a method similar, but not as complete as your suggestion.

I created two buttons, one for Display which sets IsDownload to false, and another which currently only appears after display called Download which sets IsDownload to true.

The logic, just after the SQL query has been assigned to the output variable, now has an IF to check whether to send the variable back for display or go to a new branch to convert to Excel.

I was very pleased when the download window opened on the PC and the data was actually correct too.

Your description includes pagination that I had not considered. Yes, I can see how that would be a problem (I am not yet paging the output, so hadn't thought about that.)

At this point the app has some utility, although all it is doing is acting like an easy to use front end pivot table for a complicated spreadsheet. 

Thank you for giving such a clear, detailed and to the point reply.


0
0
23 Apr 2023
Copy Link
2024-07-05 14-16-55
Daniël Kuhlmann
 
MVP

Hi,

I don't really get why you cannot export to Excel. This is a very common practise and easy to do.
Even though the data is displayed in a screen client side on your browser, from what I understand you retrieved it from the database. 

The Record List to Excel widget can take your structure as input.

https://success.outsystems.com/documentation/how_to_guides/data/how_to_export_entity_data_to_excel/

Regards,

Daniel

0
0
22 Apr 2023
Copy Link
UserImage.jpg
Greg Adams

I started by trying to use the Excel widget, but could not find anything in the data tab that held the Query result. 

The data tab has access to the database, but it knows nothing about the Query result, as far as I can see.

The SQL result stayed on the server until the logic reached END. It only exists client side, doesn't it, or have I missed something?

To convert the data to Excel on the server wouldn't that mean running the query again, converting to excel in the logic immediately after the query and then downloading?

Is that what you are thinking?

I thought it would be more efficient doing it client side as that is where the result is. I thought I had seen a video on how to do that, but I am not sure it is practical.

Looks like it is necessary to change the logic of the SQL process & to run it again to create the file.




Here is more background information:

All the instructions on convert to Excel that I have read say roughly the same thing as the instruction page you posted:  

1. Go to the Data tab a drag the User entity to your flow creating a GetUsers aggregate.

Reminder: Using bespoke SQL query, not an aggregate.

Here is the datatab, which shows the structure for the table. (The actual database is in another module). If I wanted to download table 2 through an aggregate I could use the Excel tool, but that isn't what I am doing. 

 


The SQL query was constructed ad hoc by the user. That was sent to the client where it was displayed. Now it resides in the SQLResult local variable


 

0
0
22 Apr 2023
Copy Link
2021-09-06 15-09-53
Dorine Boudry
 
MVP
Solution

Hi @Greg Adams  ,

yes, the sql output only is available for you to use inside the server action, and in the call chain down from that as long as you pass it to the output, and so finally back on the client side.  

Any bits of logic along the way (like server actions) that have ended, have no longer the date that was inside their scope.

And yes, the 'RecordListToExcel' tool is available on server side, so that's where you'll have to use it.  

But one big difference that you have to think about, is that in your UI you are probably paging the results, so in your client, you would not have available all records, only the ones currently shown.  In the excel however, you do want all records that fit current filter and sort etc.  So it does not really make much sense to turn into an excel whatever you have on the client side at any given time, you'll have to go back to the server for the full set anyway.

I would aim at using the same SQL widget to service both things, avoiding double maintenance in case things like filters and joins change.  (assuming of course, your end users want to get a result set in the excel matching the filters and sorting and so on they are currently using for the list in the screen)

So encapsulate it in a separate server action, taking an IsForDownload boolean, and make the "OFFSET ... FETCH NEXT ... ROWS" bit of your excel a separate input parameter to the SQL, leaving it empty in case of Download.

The output of that server action is both the list, and the binary.  In the server action, only convert the sql result to an excel binary if IsForDownload.  Vice versa, I would not bother filling the output list if it is for download.

In your data action for populating the ui list, pass in IsForDownload false, and pass in startindex and maxrecords of the screen.  In your screen action for download, pass in IsForDownload true.

Dorine

0
0
23 Apr 2023
Copy Link
UserImage.jpg
Greg Adams

Thank you @Dorine Boudry for the detailed response which was exactly to the point.

About 12 hours ago I had done enough to get the download running using a method similar, but not as complete as your suggestion.

I created two buttons, one for Display which sets IsDownload to false, and another which currently only appears after display called Download which sets IsDownload to true.

The logic, just after the SQL query has been assigned to the output variable, now has an IF to check whether to send the variable back for display or go to a new branch to convert to Excel.

I was very pleased when the download window opened on the PC and the data was actually correct too.

Your description includes pagination that I had not considered. Yes, I can see how that would be a problem (I am not yet paging the output, so hadn't thought about that.)

At this point the app has some utility, although all it is doing is acting like an easy to use front end pivot table for a complicated spreadsheet. 

Thank you for giving such a clear, detailed and to the point reply.


0
0
23 Apr 2023
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.