Hello OF Team,

I am building a survey to re-sample a plot network where data already exists for thousands of trees. My use case is very similar to this user, and I believe a lookup table is also the best solution for me. I am having trouble determining the workflow for building a lookup table, inserting it into the .collect file, and importing back into Collect. Are you able to provide detailed documentation with step-by-step instructions for implementing lookup tables in Collect (and Collect Mobile)?

Thank you!

Bryant

asked 10 Mar '23, 00:23

bryant's gravatar image

bryant
134
accept rate: 0%


Dear Bryant,
We don't provide detailed instructions about this feature because it's not supported officially. In the new platform, Arena, it has been replaced by the use of a Category (that corresponds to the Code List in Collect) with additional "extra" properties.
In case you want to use the idm:lookup function, here is what you have to do:
- open the database file (collect.db) with a SQLite database client (for example SQLite browser or use the client you prefer). - add your table to the database with a structure similar to the following one:

CREATE TABLE "my_lookup_table" (  
    "id"        INTEGER,
    "key_1"     INTEGER,
    "key_2"     VARCHAR,
    "value"     VARCHAR,
    PRIMARY KEY("id")
);

It's important that the table has a column "id" and this must be the primary key of the table.
The other columns can be named in the way you want, and there is no limitation to their number, but the type must be only CHAR, VARCHAR, INTEGER or FLOAT.
- once the table is created into the database, fill it with values - test that the lookup function works in the survey preview. In our example the expression you have to use will be something like this: idm:lookup('my_lookup_table', 'value', 'key_1', attribute_1, 'key_2', attribute_2) where attribute_1 and attribute_2 are the attributes defined in your survey. Instead of the "dynamic" value of the attributes in your survey, you can use even constant values, idm:lookup('my_lookup_table', 'value', 'key_1', 10, 'key_2', 'abc')
- if everything works in the preview, export the survey to Collect Mobile format and create the same table into the collect.db file inside the generated collect-mobile file (that is actually a zip file) - test that everything works in Collect Mobile too

Please let us know if you need further support from our side, many thanks.
Open Foris Team

permanent link

answered 10 Mar '23, 07:20

Stefano%20%28OF%29's gravatar image

Stefano (OF) ♦♦
4.6k19
accept rate: 20%

Thank you for this response! Which option should I select when exporting my survey (prior to building the lookup table) in order to generate the correct type of file? The only .db file that I know how to generate is by exporting my survey to the Collect Mobile format, but this does not seem like the right option for when it comes time to import the survey back into Collect for testing.

I understand how to create the structure of the table like you demonstrate with a .sql file and a SQL client, but how do you "fill it with values"? Thanks!

permanent link

answered 17 Mar '23, 22:56

bryant's gravatar image

bryant
134
accept rate: 0%

Dear Bryant,
There is no way from Collect to export a survey including the lookup table.
As we were saying already, this feature is not officially supported.
You will have to "manually" create and fill the table again when you import the survey into a new installation of Collect.
If you need to fill the table, you can do it line by line using SQLite browser for example, or you can prepare an SQL script and execute it.
We will contact you to your private email to help you with this.
If you really need this feature in your survey, please evaluate the use of Arena and Arena Mobile instead.
Many thanks,
Open Foris Team

permanent link

answered 21 Mar '23, 22:02

Stefano%20%28OF%29's gravatar image

Stefano (OF) ♦♦
4.6k19
accept rate: 20%

Thank you for the reply! I was initially confused on how to find/generate the "collect.db" file, but realized that this file is automatically created by Collect and stored in the OpenForis\Collect\data. After locating this file, I was able to follow your instructions to edit and fill a new table. It worked beautifully in Collect desktop! Thank you for your help!

permanent link

answered 21 Mar '23, 22:08

bryant's gravatar image

bryant
134
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×490
×169

question asked: 10 Mar '23, 00:23

question was seen: 566 times

last updated: 21 Mar '23, 22:08