Creating Relational Data Entry Forms for SQLite with Read-Only Lookup Fields on Linux

Relational Data Entry Form Requirements for FilmPhotoRecords Database

Issue Overview
The FilmPhotoRecords database schema consists of three tables: FilmTypes, FilmRolls, and Frames. The FilmTypes table stores film brands (e.g., Kodak Gold), while FilmRolls references FilmTypes to associate rolls (e.g., R0021) with specific film types. The Frames table links to FilmRolls to record individual frames. The core challenge involves creating a data entry form for the Frames table that dynamically displays the film type (from FilmTypes via FilmRolls) when a film roll is selected, while preventing edits to the film type field. This requires a form interface that can resolve relational data across tables, enforce referential integrity, and restrict write access to specific fields.

The user seeks a solution analogous to MS Access’s form builder, which natively handles relational lookups and read-only displays. However, SQLite lacks built-in GUI tools for form creation, necessitating third-party applications or custom development. The Linux Mint environment further restricts options compared to Windows-centric tools. Key technical hurdles include:

1. Relational Data Binding: The Frames form must query FilmRolls and FilmTypes to populate the film type field without allowing direct edits.
2. Cross-Platform Compatibility: The solution must work seamlessly on Linux Mint.
3. Minimal Coding Overhead: Preference for low-code or no-code tools to avoid extensive programming.

Limitations in SQLite GUI Tools and Framework Integration

Possible Causes

1. Insufficient Form-Building Capabilities in SQLite Browsers: Tools like DB Browser for SQLite (DB4S) provide basic table editing but lack advanced form controls for relational data. While DB4S supports foreign key navigation, it does not allow dynamic display of related fields (e.g., showing FilmType when selecting a FilmRoll).
2. Mismatched Expectations for Read-Only Lookups: Most SQLite GUI tools prioritize raw data manipulation over user-friendly forms with conditional field behavior.
3. Framework-Specific Constraints: Solutions like LibreOffice Base or web frameworks (e.g., Django, Flask) require configuration steps that may not be immediately obvious, such as setting up relational forms or handling SQLite connections correctly.
4. Schema Design Oversights: If foreign key constraints are not explicitly enforced in SQLite (PRAGMA foreign_keys = ON;), tools may fail to recognize table relationships, complicating form development.

Implementing Relational Forms with LibreOffice Base and Custom Validation

Troubleshooting Steps, Solutions & Fixes

Step 1: Validate Database Schema and Enable Foreign Keys

Before building forms, ensure the database schema enforces referential integrity. The FilmRolls table should include a foreign key to FilmTypes, and Frames should reference FilmRolls:

CREATE TABLE FilmTypes (
    FilmTypeID INTEGER PRIMARY KEY,
    Name TEXT NOT NULL UNIQUE
);

CREATE TABLE FilmRolls (
    RollID TEXT PRIMARY KEY,
    FilmTypeID INTEGER,
    FOREIGN KEY (FilmTypeID) REFERENCES FilmTypes(FilmTypeID)
);

CREATE TABLE Frames (
    FrameID INTEGER PRIMARY KEY,
    RollID TEXT,
    -- Additional fields...
    FOREIGN KEY (RollID) REFERENCES FilmRolls(RollID)
);

Enable foreign key enforcement in SQLite:

PRAGMA foreign_keys = ON;

Step 2: Build Relational Forms Using LibreOffice Base

LibreOffice Base provides form-building capabilities similar to MS Access. Follow these steps:

1. Connect to SQLite Database:

   • Launch LibreOffice Base.
   • Select “Connect to an existing database” > “SQLite” and browse to your .sqlite file.
   • Save the Base configuration as an .odb file.

2. Design the Frames Form:

   • Navigate to “Forms” > “Create Form in Design View”.
   • Add a grid or list box for the Frames table fields (e.g., FrameID, RollID).

3. Add a Combo Box for FilmRolls Lookup:

   • Drag a combo box onto the form.
   • Set “Data Field” to RollID (the foreign key in Frames).
   • Under “List Content”, select “SQL” and enter:

     SELECT RollID FROM FilmRolls ORDER BY RollID;
     

   • This populates the combo box with valid RollIDs.

4. Display FilmType as a Read-Only Field:

   • Add a text box to the form.
   • Set its “Data Field” to a new SQL query that joins FilmRolls and FilmTypes:

     SELECT FilmTypes.Name 
     FROM FilmRolls 
     INNER JOIN FilmTypes ON FilmRolls.FilmTypeID = FilmTypes.FilmTypeID 
     WHERE FilmRolls.RollID = :RollID;
     

   • In Base, bind :RollID to the combo box’s current value using a macro or event handler (see Step 3).
   • Set the text box’s “Read-only” property to Yes.

Step 3: Automate Field Updates with LibreOffice Macros

To dynamically update the FilmType field when a RollID is selected:

1. Create a Macro for the Combo Box’s “Item Changed” Event:

   • Right-click the RollID combo box > “Control Properties” > “Events” > “After record change”.
   • Assign a new macro written in LibreOffice Basic:

   Sub UpdateFilmType(oEvent As Object)
       Dim oForm As Object
       Dim oCombo As Object
       Dim oTextField As Object
       Dim sRollID As String
       Dim sSQL As String
   
       oForm = oEvent.Source.Model.Parent
       oCombo = oForm.getByName("RollID_Combo")
       oTextField = oForm.getByName("FilmType_Text")
   
       sRollID = oCombo.Text
       sSQL = "SELECT FilmTypes.Name FROM FilmRolls " & _
              "INNER JOIN FilmTypes ON FilmRolls.FilmTypeID = FilmTypes.FilmTypeID " & _
              "WHERE FilmRolls.RollID = '" & sRollID & "';"
   
       Dim oStatement As Object
       oStatement = oForm.ActiveConnection.createStatement()
       Dim oResultSet As Object
       oResultSet = oStatement.executeQuery(sSQL)
       If oResultSet.next() Then
           oTextField.Text = oResultSet.getString(1)
       Else
           oTextField.Text = ""
       End If
   End Sub
   

2. Assign the Macro to the Combo Box:

   • In the “After record change” event property, select the UpdateFilmType macro.

Step 4: Alternative Solutions for Advanced Customization

If LibreOffice Base proves limiting, consider these alternatives:

1. Web Application with Flask/Django:

   • Use Flask-SQLAlchemy to define model relationships:

   class FilmType(db.Model):
       id = db.Column(db.Integer, primary_key=True)
       name = db.Column(db.String(80))
   
   class FilmRoll(db.Model):
       id = db.Column(db.String(10), primary_key=True)
       film_type_id = db.Column(db.Integer, db.ForeignKey('film_type.id'))
       film_type = db.relationship('FilmType')
   
   class Frame(db.Model):
       id = db.Column(db.Integer, primary_key=True)
       roll_id = db.Column(db.String(10), db.ForeignKey('film_roll.id'))
       roll = db.relationship('FilmRoll')
   

   • Create a form template with a dropdown for roll_id and a read-only field for roll.film_type.name.

2. Desktop GUI with Python and Tkinter:

   • Use sqlite3 for database access and tkinter.ttk.Combobox for RollID selection:

   import sqlite3
   import tkinter as tk
   from tkinter import ttk
   
   conn = sqlite3.connect('FilmPhotoRecords.sqlite')
   
   def update_film_type(event):
       roll_id = roll_combobox.get()
       cursor = conn.execute('''
           SELECT FilmTypes.Name 
           FROM FilmRolls 
           JOIN FilmTypes ON FilmRolls.FilmTypeID = FilmTypes.FilmTypeID 
           WHERE FilmRolls.RollID = ?
       ''', (roll_id,))
       result = cursor.fetchone()
       film_type_var.set(result[0] if result else "")
   
   root = tk.Tk()
   roll_combobox = ttk.Combobox(root, values=get_roll_ids())  # Implement get_roll_ids()
   roll_combobox.bind('<<ComboboxSelected>>', update_film_type)
   film_type_var = tk.StringVar()
   film_type_entry = tk.Entry(root, textvariable=film_type_var, state='readonly')
   

Step 5: Validate and Test Data Entry Workflow

After implementation:

1. Verify that selecting a RollID in the Frames form correctly displays the associated FilmType.
2. Ensure the FilmType field cannot be modified directly.
3. Test edge cases (e.g., deleting a FilmType referenced by FilmRolls) to confirm foreign key constraints are enforced.

This guide provides a comprehensive path to achieving MS Access-like forms for SQLite on Linux, balancing no-code tools (LibreOffice Base) with customizable code-based solutions.