---
title: "adding data from lookup-table"
url: "https://openforis.support/questions/3531/adding-data-from-lookup-table"
tags: ["collectmobile", "collect"]
status: "answered"
score: 0
answer_count: 5
asked: "2023-01-16T08:34:11"
last_activity: "2023-03-20T07:25:01"
source: "Open Foris Support - Q&A Forum"
license: "Copyright Open Foris, 2026. Some rights reserved under creative commons license."
---

# adding data from lookup-table

Asked by chris_1123 on 2023-01-16

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][1] and did the following.

 1. add a table `lu_test` to the `collect.db` database on collect desktop
 2. create following table in collect desktop:<table>
	<tr>
		<th>reserve</th>
		<th>plot</th>
		<th>marking_id</th>
		<th>value</th>
	</tr>
	<tr>
		<td>9999</td>
		<td>2</td>
		<td>1</td>
		<td>3.3</td>
	</tr>
	<tr>
		<td>9999</td>
		<td>2</td>
		<td>2</td>
		<td>10.1</td>
	</tr>
	<tr>
		<td>9999</td>
		<td>2</td>
		<td>3</td>
		<td>7.4</td>
	</tr>	
</table><br>
 3. 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

 4. 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
  [1]: https://openforis.support/questions/1625/using-lookup-tables-in-collect-mobile


## Answer (score 1, by Stefano (OF) on 2023-01-23)

Link: https://openforis.support/questions/3531/adding-data-from-lookup-table/3549

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)

## Answer (score 0, by Stefano (OF) on 2023-01-17)

Link: https://openforis.support/questions/3531/adding-data-from-lookup-table/3534

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 (score 0, by chris_1123 on 2023-01-17)

Link: https://openforis.support/questions/3531/adding-data-from-lookup-table/3537

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 (score 0, by Stefano (OF) on 2023-01-17)

Link: https://openforis.support/questions/3531/adding-data-from-lookup-table/3540

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)

## Answer (score 0, by Stefano (OF) on 2023-03-09)

Link: https://openforis.support/questions/3531/adding-data-from-lookup-table/3572

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)