Hello, I need your help. I have two tables, Table A and table B. Table A has this unique ID as a variable called PID with value 1234(sample). Table B has a search functionality in the ODK-X tables. The purpose of this filter is to search the PID in Table A. When i search the PID from table B it displays none. My question is, is it possible to search a variable from another table when you are in another table? For instance, search value 1234 from table A using the search filter in table B?
Hi @James_Nyanga! I think you can get close using some of the async_assign features. I am not quite sure about search (not sure how you have that set up), but in general you can at least assign a value from one table into another table using async_assign: Using ODK-X XLSX Converter — ODK-X Docs. To do that as a search, though, you might have to enter a variable as an integer (1234) and then go to the next screen to see if it is listed by the query on the next screen.
Thank you @elmps2018 here is how i have set up. Table A has IDS already inserted. Table B has a search filter like the one implemented in HOPE table in ODK-X application designer. The filter is querying data from Table A instead of table B. Basically, I want to search id 1234 which is already existing in table A using the search filter in Table B. I am open to suggestions and also solutions, thanks.
Can you explain a bit what you want to do with id 1234 after you search it? Do you want to pull in other data about id 1234? Go to id 1234 in the other table? That would help think through options…
Basically, I want to pull data of id 1234 from table A using the search filter of table B.
Note: table A has id 1234 which comes with some data. Table B has other variable names that will be filled only if the searched ID is found(1234). Table B also has PID field which is in table A(foreign key), plus other variables that will be filled .
Thanks for these details! I think then what I suggested earlier with async_assign would work. You could either (1) enter in Table B into a variable the 1234 and then use async_assign with a query to pull in the other variables you need to fill in or (2) use the foreign key in the query (if it maps the data together uniquely) to do likewise. You’ll need to make sure the variables are in the model statement and the same type in both tables for this to work. The customizing prompt types section in Using ODK-X XLSX Converter — ODK-X Docs has more details and examples.
This post may be helpful:
1 Like
Hi @James_Nyanga,
You could use an arbitrary query in ODK-X Tables to bring in data from your 2 tables. See app-designer/app/system/js/odkData.js at 7abf14b16d940850cedfa66e2f9c2afd79553b3e · odk-x/app-designer · GitHub
It sounds like you might want to use an arbitrary query to write a JOIN SQL query to fetch data from your 2 tables. The underlying database is a SQLite database, consult SQLite documentation for the exact syntax.
1 Like