Modify query of ODK X Services

Hi All

I’m working in a project with a large dataset (more than 250K records ion one table) for the post i see is not possible to only get the records in a specific group, in the project we have more that 250 groups already set.

I like your help to see if is possible to modify ODK X services to add a where clause to the query that get the rows from the tables.

Or if anybody have a alternative solution, thanks in advance.

Hi @jgomezr!

With permission filters you can ensure folks only access some records: Data Permission Filters — ODK-X Docs
but the syncing does occur against the entire database.

In the odkData object there is an arbitrary query function that allows you to write more complex queries using a select statement.

WARNING: arbitrary query does not enforce the data permission filter (because of figuring out the complexity of the query would require new functionality to modify a random arbitrary query that already has a where clause with the additional where clauses needed for data permission filters).

https://docs.odk-x.org/injected-interfaces/#odkdata-js

Is possible to have like a local table, a table that don´t sync with the server?

Yes, this is supported. In odkData.js there are a few methods with LocalOnlyTable in the name, these methods interact with SQL tables that exist only on the device.

@linl33 this means in the properties tab of my form i can mark it as localonly?

No. You have to create these tables using odkData.js.

@linl33 and this tables can be used as Linked Tables to use their data in other tables?

Not using the built-in linked_table prompt type. But you can create a custom prompt type that queries your local tables. The async_assign prompt types would be a great starting point to build on for your custom prompt type.

https://docs.odk-x.org/xlsx-converter-using/#customizing-prompts

2 Likes

If anyone need more details on how to implement it i find a example in the ODK X github repository made by Clarlars, i hope this can help the implementations that manage huge amounts of data. :grinning:.

Thanks to @linl33 and all that replay this post.

2 Likes