Error using form on database view

Classic Servoy
1

Hi everyone,

I’ve got an interesting issue here: I like to use a form with a database view as datasource.
The view is created by this sql:

SELECT c.contact_id, c.first_name || ' ' || c.last_name As Name, 1 AS contact_chk FROM contacts c
UNION
SELECT p.company_id, p.name, 0 AS contact_chk FROM companies p

Servoy 5.1.2 shows the view correctly as a table and I can select the view as datasource for the form, but this error occurs when showing the form:

Form 'addrbk_addressbook_view_tbl' is based on table 'addressbook_view' which doesn't have a primary key.

How to make this work since the view data is retrieved from multiple tables?

Servoy 5.1.2
MacOS X 10.6.3

2

You need to mark a column or combination of columns in the view are row identifiers, so Servoy knows how to uniquely identify each row

3

I think you should (for your own sake) create some logic, that allows you distinguish one record from the other. For example, you could do something like this

create view karel_view as 
select 'companies' as table_name, company_id as id, company_name as name FROM companies
UNION 
select 'contacts' as table_name, contact_id as id, name_last as name FROM contacts

Then you use “table_name” and “company_id” as PK.

4

Thanks guys! I didn’t know about the row_ident option in the datasource properties, but now I know! :D