Skip to main content

Data Schema (Zoho Creator)

1. Streams​

Strategic & Operational Portfolio Entity.

  • Name: name (Single Line, Mandatory) [e.g. "Roll-out of new website", "IT Operations"]
  • Code: code (Single Line) [e.g. "1.25.6"]
  • Description: description (Multi Line)
  • Owner(s): owners (List/Multi-select Users)
  • Status: status (Picking: Planning, Active, Closed)
  • Type: stream_type (Picklist: Strategic, Operational)
  • Display Label: display_label (Formula)
    • Logic: if(code != null && code != "", code + " - " + name, name)
    • Usage: Main display field for lookups.

2. Providers​

Central Entity for "just-in-time" provider management.

  • Name: provider_name (Text - Unique)
  • Notes: notes (Textarea)

3. Fiscal_Years​

Configuration Table (e.g., Oct 1st - Sep 30th).

  • Name: fy_name (String, PK) [e.g., "FY25"]
  • Start Date: start_date (Date)
  • End Date: end_date (Date)
  • Status: status (Picklist: "Planning", "Open", "Closed")

4. Budget_Buckets​

Budget Container (Header).

  • Name: bucket_name (Text, Mandatory) [e.g., "Website - Devel", "Cyber Security Tool"]
  • Stream: stream (Lookup → Streams)
    • Binding: References display_label from Streams
  • Domain: domain (Picklist)
    • Values: "IT", "AI", "Website", "M&A", "Trainings" (Sorted Ascending, Others Allowed)
  • Owners: owners (List: maurizio, margherita, chiara, alice, roberta)
  • Description: description (Textarea)
  • Display Label: display_label (Formula)
    • Logic: bucket_name + if(stream != null," - " + stream.name,"") + if(domain != null && domain != ""," (" + domain + ")","")
    • Usage: Main display field for lookups.

5. Budget_Versions​

Financial Snapshots (Transactional). Note: Financial metrics are calculated via workflow, not live formulas.

  • Bucket Link: budget_bucket (Lookup: Budget_Buckets)
  • Snapshot Month: snapshot_month (Date)
  • Fiscal Year: fiscal_year (Lookup: Fiscal_Years)
  • Expense Type: expense_type (Picklist: "Capex", "Opex")
  • Proposed Amount: proposed_amount (Currency - Manual Input, updated quarterly by budget manager)
  • Approved Amount: approved_amount (Currency - Manual Input, updated quarterly by budget manager)
  • Review Status: review_status (Picklist: Draft, In Review, Approved, Rejected, Archived)
  • Display Label: display_label (Formula)
    • Logic: budget_bucket.bucket_name + " - " + snapshot_month.toString("MMM yyyy") + " (" + fiscal_year.fy_name + " " + expense_type + ")"

Financial Metrics (Calculated)​

  • Total Contracts: total_contracts_value (EUR, Default 0)
  • Committed: committed_amount (EUR, Default 0, Derived) -> Sum of Approved/In Delivery/Closed contracts.
  • Remaining Estimate: remaining_estimate (EUR, Default 0, Derived) -> approved - committed.
  • Spent Amount: spent_amount (EUR, Default 0, Derived) -> Calculated based on Invoices.
  • Remaining Payments: remaining_payments (EUR, Default 0, Derived) -> committed - spent.
  • Residual: residual (EUR, Default 0, Derived) -> approved - spent.
  • Closing Forecast: closing_forecast (EUR, Default 0, Derived) -> max(approved, committed).

System Fields​

  • Last Recalculated: last_recalculated (DateTime, Private) -> Used for sync logic.

6. Contracts​

Operational Core.

  • Code: contract_code (Text - Mandatory Unique)
  • Object: object_name (Text - Mandatory)
  • Provider: provider (Lookup: Providers, Allow New Entries)
  • Budget Bucket: budget_bucket (Lookup: Budget_Buckets - Mandatory)
  • Status: status (Picklist: Proposed, Approved, In Delivery, Closed)
  • Expense Type: expense_type (Picklist: Capex, Opex - Mandatory)
  • Priority: priority (Picklist: High, Medium, Low)
  • Service Type: service_type (Picklist: Service Agreement, Software Licensing, Consulting, Equipment Rental - Others Allowed)
  • Invoice Type: invoice_type (Picklist: Monthly, Quarterly, Yearly, Milestone)
  • Start Date: start_date (Date)
  • End Date: end_date (Date)
  • Total Contract Value: total_contract_value (EUR - Mandatory)
  • Total Invoiced: total_invoiced (EUR - Read Only, Calculated)
  • Notes: notes (Textarea, Scope details and import context)
  • Contract Document: contract_doc (URL)
  • Renewal Option: renewal_option (Picklist: Automatic, Optional, Conditional, None)
  • Termination Notice: termination_notice_period (Number)
  • Erosion Applied: erosion_applied (Checkboxes, Private) -> Used for FIFO logic.
  • Original Currency Data: original_currency_data (Multi Line Text, Optional) -> YAML format: original_amount, currency, date, rate.
  • Display Label: display_label (Formula)
    • Logic: if(contract_code != null && contract_code != "",input.object_name + " - " + input.contract_code,input.object_name)
  • Allocation: fy_allocation (Subform/Grid)
    • Fiscal Year: fiscal_year (Lookup: Fiscal_Years)
    • Amount Due: amount_due (EUR)

7. Invoices​

Accounts Payable.

  • Number: invoice_number (Text - Mandatory)
  • Date: invoice_date (Date - Mandatory)
  • Service Start: service_start_date (Date, Optional - for allocation)
  • Service End: service_end_date (Date, Optional - for allocation)
  • Amount: amount (EUR - Mandatory)
  • Import ID: imported_id (Text - Unique)
  • Contract Link: contract_id (Lookup: Contracts - Mandatory)
  • Status: status (Picklist: To Review, To Pay, Paid, Disputed, Association Error)
  • Invoice File: invoice_file (URL)
  • Original Currency Data: original_currency_data (Multi Line Text, Optional) -> YAML format: original_amount, currency, date, rate.
  • Allocation: invoice_fy_allocation (Subform/Grid)
    • Fiscal Year: fiscal_year (Lookup: Fiscal_Years)
    • Amount Allocated: amount_allocated (EUR)

Automation & Logic​

The system relies on specific Deluge scripts to maintain data integrity (see .ds file):

  1. Centralised Recalculation (Recalculate_Budget_Version):

    • Updates Commitment, Spent, and other derived metrics on Budget Version.
    • Triggered by Schedule (Daily) or Manual Action ("Recalculate Selected Versions").

  2. FIFO Erosion (Run_FIFO_Erosion):

    • Manually triggered on Contracts.
    • Reduces "Proposed" placeholder contracts when a new "Approved" contract is entered, maintaining the budget balance.

  3. FY Allocation (Calculate_FY_Allocation):

    • Automatically splits Contract Value across Fiscal Years based on Start/End dates.
    • Triggered on success of Contract add/edit.

  4. Invoice Allocation (Calculate_Invoice_FY_Allocation):

    • Automatically splits Invoice Amount across Fiscal Years based on Service Dates.
    • Fallback: Allocates 100% to Invoice Date FY.

  5. Approve & Roll (Custom Action):

    • Trigger: Manual button on Budget_Versions.
    • Purpose: Locks current quarter and generates the next one.
    • Inheritance Logic:
      • Same FY: Copies approved_amount and proposed_amount.
      • New FY: Resets approved_amount and proposed_amount to 0.
    • Locking: Sets current version to "Approved".
    • Clean-up: Sets previous quarter version to "Archived" (Waterfall Logic).

  6. Strict Date Snapping (On Input):

    • Forces snapshot_month to be 1st of Oct, Jan, Apr, Jul.
    • If user enters random date, snaps to next valid quarter start.