I have a table X which does not have a primary key. the format of data is something like this,
I have to create a record to view this data.
Notes:
The record list breaks with following error: Expression evaluation error at function a!gridField [line 27]: A grid component [label=“”] has an invalid value for “totalCount”. “totalCount” must not be null or less than the number of items in any of the “data” arrays, but “totalCount” was XX and the largest column data array had XXXXX items.Why record: I would like to leverage the filters and export functionality from records for this table. I know this can be achieved using query entity and export to excel plugin however I would like to know If this can be achieved in records which has all these functionalities inbuilt.
Discussion posts and replies are publicly visible
Hi Bhushan,
What is preventing you from generating a standard primary key field that you just use internally and do not expose to the end user? The primary key needs to be a unique identifier of a record. If your example is real, you are not using a field that is unique since the first 2 rows have the same identifier.
Hi Pedro Simões
The table is not managed within Appian and is not part of Appian db, it is part of different software suite. hence I cannot add a primary key. the table resides in shared database space. changes to the table is not permitted as it will break other software that is utilizing it.the requirement is just to display the data in grid with export functionality from the table.
To add to Pedro's answer - part of the reason a record requires a primary key is for export. To export data from a grid, the export will generate the data in batches to add to the grid, and the batching process needs to ensure that the data isn't duplicated in the export file.
Is it possible to create a database view that would add a column that uses a row count or calculated column as a unique field?
Do you have any way of creating a View within Appian that references the external DB table? I'm not sure the feasibility of such an approach but if something like this were possible (or if you could create a view in that other system specifically to be consumed by Appian), then there would be easier approaches to overcoming your issue I expect.
Thanks Mike Schmitt and Peter Lewis, I guess I have to ask if I can get a view with a row count in the source db.can you guys tell me the performance impact of the view with in Appian here before I propose this, cause there is lot of traffic on the table all the time for example: around 500000 rows are Inserted/updated/deleted every day on this specific table.
My (vague) understanding of views is that they have no performance impact until they're run, i.e. there is no impact for the view just existing, until you try to query it. At that point the performance impact is essentially the same as a query into it.
yeah, I understand, the problem is we have fairly complicated query entity already with lot of querylogicalexpressions nested together.how much performance impact I am looking at when querying directly from table vs querying from View?Edit: to add, currently a basic query with already defined filters take almost 10 seconds from table. [yeah, that is a lot.]
If your view is simple and mainly establishes a unique column by some means (either by row index or by concatenating some of the other column values in such a way that it's guaranteed to be unique), i wouldn't expect the query from that view to be particularly worse than querying from the original table. It wouldn't be *better*, but also might not actually be *worse*.
Have you tried to do some performance tuning on that particular database table? Are you able to check the execution plan of your query? A 10s query jeopardizes any attempt of having a good user experience
unfortunately I cannot make edits to the table directly. I have proposed view creation. I will update once I receive any updates.I have also requested to add index to few of the filter based columns in table for the ease of loading data faster. Can you suggest any other options that I can propose to make things faster?