Cashflow items
Purpose
Unified ledger that normalises Hours, Projects, and Expenses into a single table for monthly cash‑flow and P&L dashboards. Each record references exactly one underlying source (billable hours entry, fixed‑price project, or expense) and converts it to a common sign (+ revenue / – cost) and currency field.
Fields
| Type | Field | Key Options / Formula | Notes |
|---|---|---|---|
| Name | FormulaIF(Billable hours, Billable hours, IF(Billed projects, Billed projects, Expense)) | Mirrors the linked record’s name for quick recognition. | |
| Type | FormulaIF(Billable hours, 'Hours', IF(Billed projects, 'Project', 'Expense')) | Evaluates which link field is populated; drives colour coding. | |
| Created By | — | Audit trail for manual adds. | |
| Billable hours | — | Positive revenue inflow. | |
| Billed projects | — | Positive lump-sum inflow. | |
| Expense | — | Negative outflow. | |
| Date | FormulaIF({Date (from Hours)}, {Date (from Hours)}, IF({Date (from Project)}, {Date (from Project)}, {Date (from Expense)})) | Picks whichever linked record provides a date. | |
| Currency | FormulaIF({Currency (from Hours)}, {Currency (from Hours)}, IF({Currency (from Project)}, {Currency (from Project)}, 'USD')) | Chooses currency from Hours or Project link; defaults USD. | |
| Amount | FormulaIF(Expense, {Amount (from Expense)} * -1, IF({Billable hours}, {Billable (USD)}, {Price (USD)})) | Ensures expenses show as negative; revenue as positive (USD). | |
| Months ago | FormulaFLOOR(DATETIME_DIFF(TODAY(), Date, 'month')) | Facilitates cohort / trailing-12-month charts. | |
| Client | FormulaIF({Client (from Hours)}, {Client (from Hours)}, IF({Client (from Project)}, {Client (from Project)}, {Client (from Expense)})) | Cascades client name from whichever link field is present. | |
| Date (from Hours) | — | Hidden lookup feeding canonical Date formula. | |
| Date (from Project) | — | Hidden lookup from Billed projects → Delivery Date. | |
| Date (from Expense) | — | Hidden lookup from Expense → Date. | |
| Billable (USD) | — | Hidden lookup feeding canonical Amount formula. | |
| Price (USD) | — | Hidden lookup feeding canonical Amount formula. | |
| Amount (from Expense) | — | Hidden lookup feeding canonical Amount formula. | |
| Currency (from Hours) | — | Hidden lookup feeding canonical Currency formula. | |
| Currency (from Project) | — | Hidden lookup feeding canonical Currency formula. | |
| Client (from Hours) | — | Hidden lookup feeding canonical Client formula. | |
| Client (from Project) | — | Hidden lookup feeding canonical Client formula. | |
| Client (from Expense) | — | Hidden lookup feeding canonical Client formula. |
Relationships
- Billable hours (linked via Billable hours)
- Billed projects (linked via Billed projects)
- Expenses (linked via Expense)
Gotchas
- Exactly one link: hours or project or expense—never more than one. Use an automation/script to enforce.
- Sign convention: Revenue is positive, expenses negative. Do not manually edit Amount; edit the source record instead.
- Currency consistency: Source formulas convert to USD for unified reporting; adjust FX rates periodically.
- Months‑ago field powers rolling‑window charts; value refreshes daily at midnight.
- Deleting a source record breaks the link and will set Amount to zero—dashboards may dip unexpectedly.
- Manual additions discouraged: Create Cashflow items via the dedicated script so formulas populate correctly.
Calculated & AI fields
The Name, Type, Date, Currency, Amount, Months ago, and Client fields are all formula-driven, automatically pulling data from whichever source record is linked. The Amount field applies proper sign convention (positive for revenue, negative for expenses) and normalizes all values to USD for consistent financial reporting. Hidden lookup fields feed these formulas but are typically not displayed in the interface.