Use Case: Create spatial records with a form and fetching table

By the end of this tutorial, you will be able to:

• Create Studio queries for loading and inserting records
• Build a validated data-entry form
• Display query results in a fetching table
• Refresh the table after a successful insertion
• Add the complete application to the sidebar

Before you start: Create the layer

Before building the application, create the layer (Data, Legend, Map View, Browser app)

Once the layer is available, you can start building the analysis application.

Step 1: Understand the application structure

Before writing code, it is useful to understand what the application will contain.

The sidebar panel consists of two main components:

Data Entry Panel
│
├── Recent Records Table
└── Insert Record Form

When the user submits the form, the application follows this sequence:

Submit form
    ↓
Execute insert query
    ↓
Reload table data
    ↓
Reset form fields

The table and form are independent UI components, but they work together as one workflow

Step 2: Add the sidebar

async function main(): Promise<void> {
    const panelRoot = document.createDocumentFragment();
    window.map.stores.sidebarMenu.addMenuItem( { 
    id: 'use-case-form', 
    icon: { 
      svgIcon: { 
        path: 'M22.764 20.476l-4.24-4.24a.81.81 0 0 0-1.144 0l-.218.219-1.456-1.456a8.32 8.32 0 1 0-.708.707l1.457 1.456-.219.218a.81.81 0 0 0 0 1.145l4.24 4.238a.808.808 0 0 0 1.143 0l1.145-1.143a.811.811 0 0 0 0-1.144zM2.2 9.5a7.3 7.3 0 1 1 7.3 7.3 7.308 7.308 0 0 1-7.3-7.3zm18.848 12.421l-3.97-3.968.874-.873 3.97 3.968zM15 12.25v-5.5a.751.751 0 0 0-.75-.75h-1.5a.751.751 0 0 0-.75.75v5.5a.751.751 0 0 0 .75.75h1.5a.751.751 0 0 0 .75-.75zM14 12h-1V7h1zm-3.75-8h-1.5a.751.751 0 0 0-.75.75v7.5a.751.751 0 0 0 .75.75h1.5a.751.751 0 0 0 .75-.75v-7.5a.751.751 0 0 0-.75-.75zM10 12H9V5h1zM6.25 8h-1.5a.751.751 0 0 0-.75.75v3.5a.751.751 0 0 0 .75.75h1.5a.751.751 0 0 0 .75-.75v-3.5A.751.751 0 0 0 6.25 8zM6 12H5V9h1z', 
        viewWidth: 24, 
        viewHeight: 24
      }, 
      tooltip: 'Use Case' 
    }, 
    panel: { 
      domContent: panelRoot, 
      title: 'Use Case' 
    } 
  }, true ); 
} 

main();

Step 3: Create the table

Unlike a data-supplied table, a fetching table retrieves its rows through an asynchronous query. This allows the application to refresh its contents automatically after new records are inserted.

Lets make the table visible by:

Defining the types

Outside the main function add the table types

type RecordTableValues = { id: number; external_id: number }; 

let table: AppEngineDataFetchingTable;

These describe:

• each row displayed in the table.

Think of these definitions as a blueprint shared by the entire application.

Using explicit types makes the code easier to understand and helps prevent mistakes while building the application.

Creating the DOM Container and the UI component

Inside the main function

const tableRoot = document.createElement('div'); 
panelRoot.appendChild(tableRoot); 

//tableconfig

table = TableInstance.create(tableRoot, tableConfig) as AppEngineDataFetchingTable<RecordTableValues, 'id'>; 

Creating the tableConfig

The configuration is stored inside tableConfig inside the main function right before the UI components creation (table = TableInstance.create(ta...)

const tableConfig: AppEngineTableConfig<RecordTableValues, 'id'> = { 
  ui: {
    elevation: 2, 
    padding: 2, 
    backgroundColor: 'transparent' 
  },  
  id: 'use-case-table', 
  keyField: 'id', 
  selectionMode: 'none',  
  type: "data", 
  data: {
    data: (() => {
      return [];
    })()
  }, 
  toolbar:{ 
    actionsLeft: [QuickSearchAction], 
    actionsRight: [FilterAction, SettingsAction] 
  }, 
  columns: [
    { field: 'id', headerName: 'ID' }, 
    { field: 'external_id', headerName: 'External' } 
  ], 
  onRowClick: () => {}, 
};

UI configuration

The form and table also contain a small UI configuration.

 ui: {
    elevation: 2,
    padding: 2,
    backgroundColor: 'transparent'
  },

You can find more information in the UI documentation

Step 4: Create the form

Lets make the form visible by:

Defining the types

Outside the main function add the forms types

type RecordFormValues = { external_id: number, copy_arpt_id: string, wkt: string };

let form: AppEngineDataSuppliedForm<RecordFormValues>; 

These describe:

• the values stored by the form.

Creating the DOM Container and the UI component

Inside the main function

const formRoot = document.createElement('div'); 
panelRoot.appendChild(formRoot); 

//tableconfig
//formConfig

form = FormInstance.create(formRoot, formConfig) as AppEngineDataSuppliedForm<RecordFormValues>;

Creating the formConfig

The configuration is stored inside formConfig inside the main function right before the UI component creation (form = FormInstance.create(...).

const formConfig: AppEngineFormConfig<RecordFormValues> = { 
  ui: {
    displaySubmitButton: true, 
    formBackgroundColor: 'transparent', 
    sectionElevation: 0, 
    sectionPadding: 1, 
    formPadding: 1, 
  }, 
  config: { 
    sections: [
      { 
        section: '', 
        fields: [ 
          { 
            name: 'external_id', 
            type: 'number', 
            label: 'External ID', 
            placeholder: '174', 
            showRequiredAsterisk: true, 
            validation: [{ required: true, message: 'External ID is required' }] 
          }, 
          { 
            name: 'copy_arpt_id', 
            type: 'text', 
            label: 'Airport ID', 
            placeholder: 'NJ38', 
            showRequiredAsterisk: true, 
            validation: [{ required: true, message: 'Airport ID is required' }] 
          }, 
          { 
            name: 'wkt', 
            type: 'text', 
            label: 'WKT', 
            placeholder: 'POINT(-74.006 40.7128)', 
            showRequiredAsterisk: true, 
            validation: [{ required: true, message: 'WKT is required' }] 
          } 
        ] 
      } 
    ] 
  }, 
  handleSubmit: async () => {}
}

UI configuration

The form and table also contain a small UI configuration.

ui: {
    displaySubmitButton: true,
    formBackgroundColor: 'transparent',
    sectionElevation: 0,
    sectionPadding: 0,
    formPadding: 2,
    },

You can find more information in the UI documentation

Step 5: Create the queries

The application retrieves and inserts data through two Studio queries.

Go to Studio → Content → Queries and create two new queries named load_record and insert_record with the following SQL:

load_record

SELECT
  id AS "id",
  external_id AS "external_id"
FROM your_table_name
WHERE id IS NOT NULL
  AND id > 0
ORDER BY id DESC
LIMIT 10;

insert_record

INSERT INTO your_table_name (
    external_id,
    copy_arpt_id,
    geom
)
VALUES (
    {ENTITY.external_id},
    {ENTITY.copy_arpt_id},
    ST_Transform(
        ST_GeomFromText({ENTITY.wkt}, 4326),
        26918
    )
)
RETURNING id, external_id;

Step 6: Define the shared state

Lets add another type need it for the app to works fine

type Records = { id: number; external_id: number; }; 

These describe:

• a record returned by the load query.

Now, right after all the types definitions create the shared variables.

const { map } = window;  

let record: Records[] = []; 

const queryName = 'load_record'; 
const insertQueryName = 'insert_record'; 

These variables are used by multiple parts of the application to:

• Allow the application to call component methods later.
• Prevent query names from being repeated throughout the application.

Step 7: Create the helper functions

Instead of placing all the application logic inside main(), the tutorial uses several small helper functions. Each helper performs a single task.

This makes the application easier to understand and maintain.

Now, right after all the types and variables add the functions.

Loading and inserting Data

async function loadRecord(): Promise<Records[]> {
  const result = await executeQuery(queryName, {
    format: 'json',
    srid: 4326,
    parameters: {}
  });
  const rows = result.rows
    .map((row: any) => ({
      id: Number(row.id),
      external_id: Number(row.external_id)
    }))
    .filter((row) => !Number.isNaN(row.id));
  return rows;
}

async function insertRecord(
  values:RecordFormValues
): Promise<void> {
  await executeQuery(insertQueryName, {
    format: 'json',
    srid: 4326,
    parameters: {
      external_id: Number(values.external_id),
      copy_arpt_id: values.copy_arpt_id,
      wkt: values.wkt,
      ENTITY: {
        external_id: Number(values.external_id),
        copy_arpt_id: values.copy_arpt_id,
        wkt: values.wkt
      }
    }
  });
}

For more information about query execution read the following tutorial.

Prepare the table rows

This helper converts the loaded records into table rows.

async function loadRecordTableRows(): Promise<RecordTableValues[]> {
  const rows = await loadRecord();
  return rows.map((record) => ({
    id: record.id,
    external_id: record.external_id
  }));
}

Table refresh

After inserting a record, the table must reload its query.

async function refreshTable(): Promise<void> {
  await table.refetch();
}

Step 8: Assemble the application

Lets make this app work by calling the functions that we already built.

Loading the data before creating the table ensures that every component has immediate access to the complete dataset. So lets call the loading function before the creation of the form- and tableConfig

record = await loadRecord();

Go to tableConfig and replace data	Go to tableConfig and replace onRowClick
data: record.map((s) => ({   id: s.id,   external_id: s.external_id }))	onRowClick: (row) => {   map.stores.mapState.apiFeatureSelect([String(row.id)], 'replace'); },

insertRecord and refreshTable

Go to formConfig and replace the handleSubmit

handleSubmit: async (values) => { 
  await insertRecord({
    external_id: Number(values.external_id), 
    copy_arpt_id: values.copy_arpt_id, 
    wkt: values.wkt 
  });
  await refreshTable(); 
  form.reset({
    external_id: undefined,
    copy_arpt_id: '', 
    wkt: '' 
  }); 
}  

You have built a complete data entry workflow using Studio queries, forms, and fetching tables.