Skip to main content

Detail Tables

Managing multi-line tables in forms.

Overview​

Detail tables allow you to manage sets of data rows related to a main document. Each row can contain multiple columns with different field types.

Examples​

  • Order lines (Item, Quantity, Price)
  • Participant list (Name, Email, Role)
  • Event history (Date, Action, User)

Architecture​

Database Structure​

For a FRM_Commande form with a Lignes table:

-- Table principale
FRM_Commande
  - id (PK)
  - reference
  - date_commande
  - client

-- Table de détails
FRM_Commande_Lignes
  - id (PK)
  - id_document (FK vers FRM_Commande)
  - article
  - quantite
  - prix_unitaire
  - total

Nomenclature​

Detail table: FRM_{FormulaireName}_{TableauName}
Primary key: id
Foreign key: id_document

Creating a Detail Table​

In Process Studio​

  1. Open the form
  2. Fields tab
  3. Create a Detail table type field
  4. Configure the columns

Column Configuration​

For each column:

  • Name: Column identifier
  • Type: Text, Number, Date, List, etc.
  • Width: Display width
  • Required: Required or optional
  • Default value: Initial value

Column Types​

Standard Columns​

// Texte
Column: article
Type: Text
MaxLength: 255

// Nombre
Column: quantite
Type: Number
Decimals: 0

// Décimal
Column: prix_unitaire
Type: Number
Decimals: 2

// Date
Column: date_livraison
Type: Date
Format: dd/MM/yyyy

Calculated Columns​

// Calcul du total de ligne
function calculateTotal(row) {
    var quantite = parseFloat(row.quantite) || 0;
    var prix = parseFloat(row.prix_unitaire) || 0;
    row.total = (quantite * prix).toFixed(2);
}

Columns with Value Lists​

// Liste déroulante
Column: categorie
Type: List
ListSource: Categories_Produits

Data Management​

Add a Row​

// SQL pour insérer une ligne
INSERT INTO FRM_Commande_Lignes 
  (id, id_document, article, quantite, prix_unitaire, total)
VALUES 
  (NEWID(), @idDocument, @article, @quantite, @prixUnitaire, @total)

Modify a Row​

// SQL pour mettre à jour une ligne
UPDATE FRM_Commande_Lignes
SET 
  article = @article,
  quantite = @quantite,
  prix_unitaire = @prixUnitaire,
  total = @total
WHERE id = @idLigne

Delete a Row​

// SQL pour supprimer une ligne
DELETE FROM FRM_Commande_Lignes
WHERE id = @idLigne

Load Rows​

// SQL pour récupérer toutes les lignes
SELECT *
FROM FRM_Commande_Lignes
WHERE id_document = @idDocument
ORDER BY ordre, date_creation

Advanced Features​

Table Validation​

Make a table required (at least one row):

function validateTable() {
    var rowCount = detailsTable.GetRowCount();
    if (rowCount === 0) {
        alert("Le tableau doit contenir au moins une ligne");
        return false;
    }
    return true;
}

Total Calculations​

// Calcul du total du document
function calculateDocumentTotal() {
    var total = 0;
    var rows = detailsTable.GetAllRows();
    
    rows.forEach(function(row) {
        total += parseFloat(row.total) || 0;
    });
    
    document.SetValue("montant_total", total.toFixed(2));
}

Import from Excel​

// Importer des lignes depuis Excel
public void ImportFromExcel(string filePath, string idDocument)
{
    var excelData = ReadExcelFile(filePath);
    
    foreach (var row in excelData)
    {
        SQL_ExecuteNonQuery(
            "INSERT INTO FRM_Commande_Lignes ...",
            new KeyPair("idDocument", idDocument),
            new KeyPair("article", row.Article),
            new KeyPair("quantite", row.Quantite)
        );
    }
}

Export to Excel​

// Exporter les lignes vers Excel
SELECT 
    article AS Article,
    quantite AS Quantité,
    prix_unitaire AS [Prix Unitaire],
    total AS Total
FROM FRM_Commande_Lignes
WHERE id_document = @idDocument

Copying Tables​

During Field Inheritance​

// Copier les lignes d'un document source vers cible
string sqlCopyLines = @"
    INSERT INTO FRM_Commande_Lignes
    SELECT 
        NEWID() as id,
        @IdNewDoc as id_document,
        article,
        quantite,
        prix_unitaire,
        total
    FROM FRM_Commande_Lignes
    WHERE id_document = @IdSourceDoc";

docMng.SQL_ExecuteNonQuery(
    sqlCopyLines,
    new KeyPair("IdSourceDoc", docSource.IdDocument),
    new KeyPair("IdNewDoc", docTarget.IdDocument)
);

Use Cases​

Purchase Order​

Table: Order lines
Columns:
  - Item reference (Text)
  - Description (Text)
  - Quantity (Number)
  - Unit price (Number, 2 decimals)
  - Line total (Number, 2 decimals, calculated)

Timesheet​

Table: Activities
Columns:
  - Date (Date)
  - Project (List)
  - Description (Text)
  - Hours (Number, 1 decimal)
  - Billable (Checkbox)

Participant List​

Table: Participants
Columns:
  - Name (Directory resource)
  - Email (Text)
  - Role (List)
  - Present (Checkbox)
  - Comment (Text)

Best Practices​

Design​

  • Limit the number of columns: Max 10-12 visible columns
  • Clear naming: Explicit column names
  • Logical order: Columns in a consistent order
  • Appropriate widths: According to content

Performance​

  • Pagination: For tables with many rows
  • Indexes: Index id_document for joins
  • Lazy loading: Load rows on demand
  • Limit calculations: Optimize JavaScript formulas

Maintenance​

  • Data migration: Plan for structure changes
  • Validation: Verify data integrity
  • Audit: Track row modifications
  • Archiving: Manage rows of archived documents

References​

SQL Structure​

-- Exemple de table de détails
CREATE TABLE FRM_Formulaire_Details (
    id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    id_document UNIQUEIDENTIFIER NOT NULL,
    colonne1 NVARCHAR(255),
    colonne2 DECIMAL(18,2),
    colonne3 DATETIME,
    ordre INT,
    CONSTRAINT FK_Details_Document 
        FOREIGN KEY (id_document) 
        REFERENCES Documents(id)
        ON DELETE CASCADE
)

CREATE INDEX IX_Details_Document ON FRM_Formulaire_Details(id_document)