adding data from lookup-table

Question

Dear OF-team

How can I add data from a previous inventory using lookup?

I have a survey that is structured as follows: /reserve/plot/tree. Each plot has 1-3 marking points to identify the plot in the next inventory. Therefore, I would like to preset the coordinates from the previous inventory in the followup inventory.

However, I cannot use the sampling point data to get the preset data since all key-columns are used (level1_code=reserve, level2_code=plot, level3_code=tree).

The only solution I therefore see, is using idm:lookup.

I'm aware of this post and did the following.

add a table lu_test to the collect.db database on collect desktop

create following table in collect desktop:

reserve	plot	marking_id	value
9999	2	1	3.3
9999	2	2	10.1
9999	2	3	7.4

export the survey, unpack the .collect-mobile file and add this table to the collect.db database in the export and pack the archive again
import survey to OF collect-mobile

I have tried to create a calculated field to get values from this table with this code:

idm:lookup('lu_test', 'value', 'reserve',9999, 'plot',2, 'marking_id',1)

Note that I had to quote table- and column-names. Otherwise I get errors of the following kind:

Invalid expression: Node 'lu_test' not found
Invalid expression: Node 'value' not found
...

However, neither collect desktop nor collect mobile were able to retrieve any of these values. It seems that the lookup-table was not found.

Where am I wrong?

Thanks for your help,
Christoph

Answer 1 · 2023-01-23T15:41:48

1

Accepted answer

Dear Christoph,
The idm:lookup function is now supported by the latest version of Collect Mobile (1.7.9 build 206).
Many thanks,
Open Foris Team

Dear OF team
Thanks, it works like a charm, now!

chris_1123 24 Jan '23, 07:52

Answer 2 · 2023-01-17T00:23:57

Dear Cristoph,
Usually we don't suggest to use the idm:lookup function because it has been used only for very specific purposes and in most of the times is not needed... but in your case it could be quite useful.
You were almost there... the only thing missing is a column "id" (primary key) because it's required by Collect, and the column types must be INTEGER, CHAR or VARCHAR. The structure of the table in your case should be something like this:

CREATE TABLE "lu_test" (  
    "id"        INTEGER,
    "reserve"   INTEGER,
    "plot"      INTEGER,
    "marking_id"    INTEGER,
    "value"     VARCHAR,
    PRIMARY KEY("id")
);

Please let us know if it works with this change, many thanks.
Open Foris Team

Answer 3 · 2023-01-17T14:43:31

Dear OF team

Thank you for your advice, this brought me one step further. In OF Collect this works now as it should.

Unfortunately, it didn't work in OF Collect Mobile. I tested a couple of things, but neither helped, I always get the following error message

Invalid expression for calculated attribute /plot/lookup

where plot/lookup is the attribute fetching the lookup-value.

I did the following without success:
- use a minimised setup to exclude any other problems
- used fixed numeric lookup-values
- tested single or double quotes for the attribute expression
- excluded underscores from the table and column names

In any instance I double-checked that id is the primary key of the table.

Finally, I also tested using a simple expression (string("hello world!")) to ensure that the calculated field works (it does...)

I'm using the latest Collect Mobile version.

Best, Christoph

Answer 4 · 2023-01-17T22:48:13

0

Accepted answer

Dear Christoph,
This feature is not yet implemented in Collect Mobile.
It will be available soon in the next release, by the end of the month.
Many thanks,
Open Foris Team

Great, then I will wait until then. In OF collect it already works pretty nice.

chris_1123 18 Jan '23, 07:02

Answer 5 · 2023-03-09T17:03:04

0

Accepted answer

Dear Christopher,
The new version of Collect Mobile fully supports the idm:lookup function.
Please update it and let us know, many thanks.
Open Foris Team

Dear OF Team,
Thank you for the notification, I have tested the functionality and it works well.
Christoph

chris_1123 20 Mar '23, 07:25

adding data from lookup-table

5 Answers

Your answer

Insert a link

Insert an image

Follow this question

By Email

By RSS

Open Foris

Question tags

Related questions

Keyboard shortcuts

Anywhere

Lists of questions

Question pages