Hi All,
I am getting around 2000 ids in one local and then passing that ids to one of column in queryfilter using IN operator. But when i test the rule I get this error as:
An error occurred while retrieving the data.
So we split the ids in batches by taking 1000 ids in a set and then pass these id to queryfilter through looping. But still it failed with above error.
We have already optimized the view also and same query when we run in DB side it took just 0.170sec to bring the data. But same is not working in Appian queryentity.
Can anyone please suggest what could be the reason behind this and what all steps we can try to get this working.
Thanks in Advance..
Discussion posts and replies are publicly visible
This error is generated because either you are passing too long of an input to the DB or the DB is returning too much data to Appian (1 MB of data is the default limitation for Appian queries).
Is this an Oracle DB? There are some known limitations which could be related such as ORA-01795 maximum number of expressions in a list is 1000.
I would ask to see if there is any further information in the logs as well.
Have you tried looping with something lower such as 500 values at a time?
Generally this method you are using is not scalable when the list of IDs can grow this large and I would look to other designs to accomplish the outcome.
Yes we have Oracle DB and we also found this ORA-01795 maximum number of expressions in a list is 1000. And that is why we used that looping thing.
And we are querying data from DB with batchsize as 50 only to limit the data size and row.
Yes as per your last comment this id will surely increase in PROD so do you have any suggestion to change this design.
Just at a guess, your list of 2000 items has to come from somewhere, probably the database? If that's the case, would it make sense to create a database view, for this comparison, and then pull into Appian from that view? I would think this kind of heavy data processing should be moved to the database side, if possible.
I suppose it might be hitting some kind of time-out limit in Appian, you might want to check the logs to see if there was an error message that would give a hint as to what actually happened.
Nilesh Raman said:Yes as per your last comment this id will surely increase in PROD so do you have any suggestion to change this design.
For this we will need to know a little about your current data setup and process. Can you describe generally what is being accomplished here and how the system works?
We are passing the loggedinuser name to queryfilter to get these 2000 ids from DB. We cannot do this operation in DB side because of this dynamic value of filter we have to pass from Appian side itself. Once we have these ids we are passing it to another view which is getting failed.
Also one thing we observed that when we are doing this whole operation like getting the ids and passing to another queryentity then that rule just take 500ms but same rule when we are calling on parent Interface in gridfield then we are getting the error.
Querying two views using a parameter from Appian ..... that might be a case for a stored procedure.
Or, did you consider switching to syncronized Records?
Our current setup is as follow:
1. We have two separate groups which upload data in our system to create issues.
2. On the basis of loggedInUser group, we are getting the ids associated and then passing it to queryentity to get only those set of data from view to display in Read-Only grid. In View we have just 25 columns.
3. As soon as user select a filter in grid, it is failing with above mentioned error.
So do you suggest anything here which can help me to overcome this issue.
Oh yes! Use record level security!
docs.appian.com/.../record-level-security.html
we can't have record type as those are VIEW.
Our Use case involves 3-4 filters applied as per logged in member entitlements and some additional business filter which can be passed via Appian.
Here Query entity expression is not taking much time and giving result very quickly but same Query entity expression is causing mentioned error while using it via Interface.
Then either recreate the view using records, or implement record level security the old way.
community.appian.com/.../record-level-security-for-entity-backed-records
Are you suggesting the following steps-
1- Instead of using query entity on DB view we should create Record based on that View and use that via query record
Still we will be passing 1000> ids to this record. Will that have any difference though at the end record will still go to DB to fetch and it is not SYNC enables record( Database related entity is VIEW not Table).
I do not understand why you bring up the 1000 IDs again. If you want to implement something similar to record level security, then please follow the guide I linked.
If this is a misunderstanding, please excuse me. But that pattern you try to implement will not work. The last idea I have is a stored procedure.
I am out of my depth, but I think what Stefan is saying, is that you have 2 options:
That is, with RecordType relationships, or as they are calling it, the "Data Fabric", you don't NEED Views, more often than not.
It sounds like you have at least 3 tables you are dealing with. You constructed a view with at least 2, and then are trying to get the rows from the view, using the Id from the first. With the "Data Fabric", you could just index into the relationship, of the first, and then index into the relationship field of the next, and so on.
For Example, if a user has a Personnel File, and that Personnel File points to an officeLocation, and that officeLocation has an officeNumber field, you can get the User's office number like so:
User.personnelFile.officeLocation.officeNumber
Using these relationships, you can populate an interface with data from widely separated tables, using a single a!queryRecordType, and in so doing negate the need for many uses of Views, which I understand (and you note), do not play well with RecordTypes.
Again, I'm not exactly sure what you are doing, but moving such a large amount of data from the database, through Appian, and then back again, does not seem ideal.