I have 3 different Records and respective tables where SYNC is enabled.
Now, i want to create a view on top of these 3 tables and export to excel or use it any other workflow.
I am not planning to query view using queryentity or queryrecord etc but i use SQL Statement to get required data and populate on excel in PM.
If not view, I am left with stored procedure which is the least option in the list.
Question: Can i create view on top of synced records/tables in first place? All the resources says that i cant (if i query on it using queryentity or queryrecord) or in general etc..
If not what is the work around?
Discussion posts and replies are publicly visible
Can you clarify why you even need a database view? You could create a grid that combines data from each of those sources and you would be able to export to excel without needing to do anything in the database.
I have about 100 columns need to export, and data is present in multiple tables which need to be exported. I also contains many-to-many relationships on these, which i never tried on modern/sync records.
When you mean Grid, is it this one??
If so, we add relationships between all tables and get data from one??
1. I have many-many relations.
2. I am already displaying 15fields on Ui from this grid list, however there are additional 70 which are not added under edit list and not displayed/used by user on Ui. Suppose i add all of them under the list and not display on UI. Everytime there is load/change/update all 100 columns gets queried/synced which would impact the performance of Ui all together.
Which is not ideal unless it does not impact the UIs performance
I also missed important part, excel file is a template and there are different versions of it that cant be achieved by Grid list export at all OOB.
Ah okay that last part is important context - currently grids can export record data but there isn't a node in a process model that can export data from records. So, I think your best approach is still to use custom data types and then query the data from those.
I still am not convinced you need a database view - you can query nested CDT data in the Export to DSE node.
Our application is completely build on Modern Records and we stick to not using CDTs at all. However I see we have challenges with records here as CDTs are easy to use compared to records.
++ Export to DSE has a limit of 50 columns and we need 100 columns
Hi Malleswari,You can try following options:For generating grid: Create a SP instead of view as indexes doesn't work on view which makes them underperformer. Create a CDT to hold the SP data in the AppianFor generating excel: 50 columns is a limitation for the current set of smart services available. You can create two excels with 50 columns each and then use a third part system to merge these two excel into one single excel.
generating excel:it is in our list, want to explore something we can do it in single go instead of multiple steps.
between is there any easy workaround for Export to CSV with same number of columns and data, we have to generate same excel file and csv file in other scenario.
We had a similar request, the first thing what we did was asking, what is the business reason to have such a huge download? :-)Regarding merging the Excel Sheets. We were able to overcome somehow the limit of 50 columns by writing 50 columns to the Excel Sheet and the next 50 columns we were writing to the second tab in the Excel sheet. Doing so, everything can be done in Appian and no need for multiple extra steps and involvement of a third party tool.
1. Business use all this data for reporting purposes.
2. They are expecting the data to be in 1 sheet and in a template where they(or their processes) run some macros on it and looking for exact format etc.. They are not willing to change anything on their end.
But would give it a try and see if they are willing for slight changes which they are not.
Thanks for your response!