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??
Yeah you can use the edit list or you can just create a grid and put it on any interface: docs.appian.com/.../read-only-grid-configuration.html. You can pull back related data as well by just specifying the related fields you want to use.
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.