Related Table Configuration

These are tables that are related to an entity in a one to many relationship.

The related table editor allows for the editing of rows in a table that is related in a one-to-many relationship to a selected entity.

An example of a related table is where multiple site visits can be made by persons to a single piece of graffiti.

Defining a Related Table Editor

Define a 1:M table.

The database object that we will use as a related table is public.graffiti_visit which is defined as follows.

DROP TABLE IF EXISTS public.graffiti_visit;

CREATE TABLE public.graffiti_visit (
  id          serial primary key,
  graffiti_id int not null,
  visitor     int not null,
  visit_date  date not null,
  purpose     int not null
);

ALTER TABLE public.graffiti_visit 
  ADD CONSTRAINT GRAFFITI_VISIT_ONE_MANY_FK 
     FOREIGN KEY (graffiti_id) 
     REFERENCES public.graffiti (id);

ALTER TABLE public.graffiti_visit
  ADD CONSTRAINT GRAFFITI_VISIT_STAFF_FK 
     FOREIGN KEY (visitor) 
     REFERENCES public.staff (id);

ALTER TABLE public.graffiti_visit
  ADD CONSTRAINT GRAFFITI_VISIT_PURPOSE_FK 
     FOREIGN KEY (purpose) 
     REFERENCES public.visit_purpose (id);

This relationship between the graffiti entity and the public.graffiti_visittable can be visualised using a database relationship where, for the purpose of this example, the entity graffiti is associated with the table public.graffiti in some way.

Related Table Visualised Using a Database Data Model

The public.graffiti_visit table’s purpose attribute, which determines the types of site visits that can be made, has its values defined by the public.visit_purpose lookup table. This table has already been defined but is repeated here.

DROP TABLE IF EXISTS public.visit_purpose;

CREATE TABLE public.visit_purpose (
  id      serial primary key,
  purpose varchar(10) not null
);

INSERT INTO public.visit_purpose (purpose) values 
('Initial'),
('Repair'),
('Confirm');

A data definition for this lookup table which can be used in a pulldown menu is defined as follows.

<data:datadefinition id='lu.visit_purpose'>
	<datasourcedataconnection datasource='ds.postgresql' table='public.visit_purpose' key='id'  prefix="DISTINCT">
		<parameter name='id' label='Id' column='id' type='int'/>
		<parameter name='purpose' label='Purpose' column='purpose'/>
	</datasourcedataconnection>
</data:datadefinition>

The public.graffiti_visit table’s visitorattribute, which determines the staff member who made the visit, has its values defined by the public.stafflookup table. This table has already been defined but is repeated here.

A data definition for this lookup table which can be used in a pulldown menu is defined as follows.

A related table editor r.graffiti_visit can be defined for the public.graffiti_visittable as follows. (Note the use of listboxes for the pulldown menus for the purpose and visitorparameters.)

Notes:

Each table in Weave has always has to have its own primary key.
For public.graffiti_visit the <key> attribute value isid.
This attribute is optional and will be set if there's a single column primary key on the table.
The <entitykey> attribute whose underlying column graffiti_id will contain the value that links a site visit record to a parent entity. It is insufficient to just have the <entitykey> attribute set to graffiti_id. A parameter (id='graffiti_id') needs to be defined for the entitykeywith its value attribute set to entitykey(). This function will extract from the selected entity its idvalue and write it to the associated column.

To check that the related table editor has been created, we can use the Config application in the Weave Admin tool.

Related Table Editor Successfully Created.

To interact with this related table editor, we add the following to the client XML.

Creating Visitation Records

The following steps show how to create a visitation record for a new piece of graffiti.

Create a new graffiti object (using a spatial editor).

Select the new graffiti entity.

Note: The new graffiti entity could be selected using an attribute search (as shown in the documentation on Entity Tables).

Switch to Graffiti Visits tab and Create new visitation record.

Create two more visitation records for this piece of graffiti showing that many records can be created for a single piece of graffiti.

Now check public.graffiti_visit table in the database.

<< Previous

Next >>