| Field | Type | Logic / Description |
|---|---|---|
| Supplier_ID | Formula | PK "SUP-" & RECORD_ID() |
| Business_Name | Single Line | Primary display name |
| Supplier_Category | Single Select | Glamping / Marquee / Both — drives onboarding form branching |
| Referral_Code | Single Line | Unique per supplier. Auto-generated by Make at onboarding (e.g. SMITH24). Customisable via Softr Phase X. Make matches incoming Referral_Code_Used on Events against this field. Decision S-10. |
| Coverage_Type | Single Select | Miles Radius / Named Counties / Named Regions / Counties + Regions / National — determines Match Engine path. Decision S-11. |
| Max_Range_Miles | Number | Required only if Coverage_Type = Miles Radius |
| Coverage_Counties | Multi-Select | Required if Coverage_Type = Named Counties or Counties + Regions. Match Engine checks Event_County ∈ this list. |
| Coverage_Regions | Multi-Select | Required if Coverage_Type = Named Regions or Counties + Regions. Options: North East / North West / Yorkshire and the Humber / East Midlands / West Midlands / East of England / London / South East / South West / Wales / Scotland / Northern Ireland / Ireland. Match Engine ORs across Coverage_Counties and Coverage_Regions. Decision S-11. |
| Is_National | Formula | Formula Coverage_Type = "National" — skips all distance/county/region checks in Match Engine |
| Tent_Types | Multi-Select | Bell Tent / Tipi / Yurt / Safari / Geodome / Shepherd Hut / Other |
| Tent_Sizes | Multi-Select | 2 / 4 / 6 / 8 person / Custom |
| Min_Contract_Tents | Number | Minimum units per booking (e.g. 10). Used in Match Engine filter. |
| Beds_Included | Checkbox | |
| Bed_Type | Single Select | Real / Inflatable / Both |
| Decor_Level | Single Select | Rustic / Mid-Range / Boutique / High-End |
| Structure_Types | Multi-Select | Marquee suppliers: Traditional Pole Tent / Frame Tent / Clear Span / Sailcloth Tent / Stretch Tent / Pagoda / Sperry Tent / Other. Used in Match Engine for marquee enquiries. Decision I-02 fix. |
| Max_Capacity | Number | Marquee suppliers: maximum guest capacity. Matched against Event.Guest_Count in Match Engine. Decision I-02 fix — field confirmed and renamed from Max_Structure_Capacity. |
| Event_Types_Served | Multi-Select | Wedding / Private Party / Festival / Pop-Up Campsite / Public Gathering |
| Event_Types_Excluded | Multi-Select | Explicit exclusions — checked in Match Engine alongside Served |
| Std_Payment_Structure | Single Select | Full Upfront / Deposit+Balance / Three-Stage |
| Std_Deposit_Pct | Number | % shown if Deposit applies |
| Std_Interim_Pct | Number | % shown if Three-Stage |
| Std_Balance_Timing | Single Select | On Booking / 30 Days Before / 14 Days Before / On Event Day / Post-Event 14 days |
| Late_Threshold_Days | Number | e.g. 30 — if event is within this many days, use Late structure |
| Late_Payment_Structure | Single Select | Same options as Std — often defaults to Full Upfront |
| Late_Deposit_Pct | Number | |
| Subscription_Status | Single Select | Pending (default — onboarded, not yet invoiced) / Free Trial / Active / Paused / Cancelled. Decision S-17. |
| Trial_Months_Granted | Number | Number of free introductory months offered. Set at onboarding or manually by Lou. Decision S-17. |
| Trial_End_Date | Date | Set by Make when supplier is activated. Make flips Subscription_Status to Active when TODAY() passes this date. Decision S-17. |
| Avg_Rating | Rollup | AVERAGE(Overall_Rating) from linked Reviews |
| Supplier_Score | Formula | Formula Placeholder: Avg_Rating. Awaiting Lou decision on composite weighting (rating + booking count + response rate). Supplier Hub view sorts by this field. See Open Decision A-01. Audit fix G-06. |
| Quality_Flag | Formula | Formula IF(Avg_Rating < 3, "Review Needed", IF(Avg_Rating < 4, "Monitor", "Good")) |
| Capacity_From | Rollup | SUM(Capacity_From_This_Type) from linked Supplier_Stock records. Total minimum guest capacity across all tent types. Decision S-19. |
| Capacity_To | Rollup | SUM(Capacity_To_This_Type) from linked Supplier_Stock records. Total maximum guest capacity across all tent types. Decision S-19. |
| Offers_Booking_System | Checkbox | Phase X stub only. No automation dependency at launch. Decision S-19. |
| WhatsApp_Number | Phone | Phase X stub. Not used until WhatsApp Business API is activated. Decision M-03. |
| Comms_Preference | Single Select | Email Only / Email + WhatsApp. Phase X stub. Make routes notifications by this field when WhatsApp is live. Decision M-03. |
| WhatsApp_Opted_In | Checkbox | GDPR opt-in for WhatsApp notifications. Captured at onboarding. Required before any WhatsApp messages can be sent. Phase X stub. Decision M-03. Audit fix G-05. |
| Offers_Dry_Hire | Checkbox | Expression of interest in PPN Dry Hire Network (separate future project). Captured at end of onboarding form. Not used in matching v1. Decision S-10 / I-03 context. |
| Dry_Hire_Notes | Long Text | Optional — what equipment would they offer for dry hire? No automation dependency. For Lou/Katie review only. |
| Stripe_Connect_ID | Single Line | Stripe Connect account ID. Populated when supplier completes Stripe onboarding. Used for automated commission payouts. Decision S-12. |
| Bank_Details_Confirmed | Checkbox | Set when supplier has confirmed payment details (Stripe Connect connected or Payment Link confirmed). Required before commission can be paid. Decision S-12. |
| Payout_Method | Single Select | Stripe Connect / Stripe Payment Link / Manual. Lou decision on mechanism — schema consistent across all options. Decision S-12. |
| Referral_Credits_Balance | Currency (£) | Accumulated referral commission credits. Updated by Make when a referred event converts. Applied against next subscription invoice. Decision S-12. |
| Outcode | Formula | Formula Extracts first half of Postcode_Raw. Text value only (not a link). Make uses this to search [Ref] Outcodes and write Outcode_Link. Note: postcodes must include a space for correct extraction of 4-character outcodes (e.g. SW1A). Audit fix G-02 / G-15. |
| Outcode_Link | Linked Record | FK → [Ref] Outcodes. Written by Make Scenario 1. Provides lat/long for Haversine and County/Region for matching. Mirrors pattern on Events table. Audit fix G-02. |
| Postcode_Raw | Single Line | Full postcode as entered, e.g. GL7 2BX. Must include a space between outcode and incode halves. Tally form validates format. Audit note G-15. |
| Active | Checkbox | Match Engine filter — only Active suppliers are matched |
| Field | Type | Logic / Description |
|---|---|---|
| Contact_ID | Formula | PK "CON-" & RECORD_ID() |
| First_Name | Single Line | Used in Make email salutation: "Hi {{First_Name}}," — Decision S-02. Audit fix G-12. |
| Last_Name | Single Line | Concatenate with First_Name when full name needed. Decision S-02. Audit fix G-12. |
| Primary key for Softr login — must be unique across all contacts | ||
| Phone | Phone | |
| Supplier | Linked Record | FK → Suppliers. Multiple contacts can link to one Supplier. |
| Role | Single Select | Owner / Manager / Staff / Read-Only |
| Portal_Role | Single Select | Supplier Admin / Supplier Member / Organiser Admin / Organiser Member |
| Contact_Stage | Single Select | Waitlist / Onboarding / Active / Inactive. Make filters pre-launch email workflows by this field. Decision S-06. Audit fix G-13. |
| Primary_Contact | Checkbox | Flags main billing/correspondence contact |
| Portal_Enabled | Checkbox | Activates Softr login for this email |
| Created_At | Created Time | Auto |
| Field | Type | Logic / Description |
|---|---|---|
| Event_ID | Formula | PK "EVT-" & RECORD_ID() |
| Event_Name | Single Line | Human-readable name (e.g. "The Johnson Wedding", "Latitude Village 2026"). Airtable primary field. Referenced by 6 email templates (E2, E5, A1, H1, H3, H4). Captured via Tally Event Request form. Audit fix N-02. |
| Organiser_Company | Single Line | Organisation name. Optional for private individuals — Duplicate_Check_Key falls back to Organiser_Name if blank. Decision S-18. |
| Organiser_Name | Single Line | Primary contact at the organisation |
| Organiser_Email | Recipient for quotes and satisfaction survey | |
| Organiser_Phone | Phone | Optional. For Lou/Katie operational use — not used in automations. Decision A-03. |
| Organiser_Portal_Email | Portal login — set only for repeat/multi-event organisers | |
| Portal_Enabled | Checkbox | Only checked for qualifying organisers |
| Event_Type | Single Select | Wedding / Private Party / Festival / Pop-Up Campsite / Public Gathering |
| Structure_Category | Single Select | Glamping / Marquee / Both — drives which suppliers are matched |
| Event_Date | Date | Used for Honesty Loop trigger and Late_Threshold check |
| Event_End_Date | Date | Honesty Loop fires +48hrs after this date |
| Guest_Count | Number | Matched against Supplier.Max_Capacity for marquee events |
| Glamping_Tent_Preference | Single Select | Open / Type Specified / Fully Specified. Determines how much detail the organiser provided about tent requirements. Decision S-19. |
| Glamping_Tent_Types_Requested | Multi-Select | Bell Tent / Tipi / Yurt / Safari / Geodome / Shepherd Hut / Other. Populated when Glamping_Tent_Preference = Type Specified or Fully Specified. Decision S-19. |
| Glamping_Units_Requested | Number | Total units requested by the organiser. Decision S-19. |
| Glamping_Spec_Notes | Long Text | Free text for fully specified requests (e.g. "10 bell tents, 5 tipis, all with real beds"). Decision S-19. |
| Payment_Arrangement | Single Select | Organiser Pays Supplier / Guests Book Direct / TBC. Decision S-19. |
| Postcode_Raw | Single Line | Full postcode as entered. Outcode formula extracts first half. Must include a space between outcode and incode halves for correct 4-char outcode extraction. Tally form validates format. Audit note G-15. |
| Outcode | Formula | Formula Extracts first half of Postcode_Raw. Used as lookup key for Make to link Outcode_Link. |
| Outcode_Link | Linked Record | FK → [Ref] Outcodes. Written by Make Scenario 1. Provides lat/long for Haversine and County/Region for matching. |
| Event_County | Lookup | Pulls County from linked Outcode_Link record. Make reads this for Named Counties matching. Decision S-11 / C-01 chain. |
| Event_Region | Lookup | Pulls Region from linked Outcode_Link record. Make reads this for Named Regions matching. Decision S-11. |
| Status | Single Select | New / Matching / Quotes Sent / Confirmed / Cancelled / Completed |
| Quote_Tier | Single Select | Free (up to 3) / Paid (up to 5) |
| Max_Quotes_Allowed | Formula | Formula IF(Quote_Tier = "Free", 3, 5) |
| Quotes_Sent_Count | Count | Count of linked Quote records |
| Duplicate_Check_Key | Formula | Formula IF(Organiser_Company, UPPER(TRIM(Organiser_Company)), UPPER(TRIM(Organiser_Name))) & "-" & Outcode & "-" & TEXT(Event_Date,"YYYY-MM") — falls back to Organiser_Name if company blank. Decision S-18. |
| Duplicate_Flag | Checkbox | Set by Make when Duplicate_Check_Key matches an existing record. Triggers E3 internal alert. |
| Dedup_Reviewed | Checkbox | Ticked by Lou/Katie after reviewing a flagged duplicate. Clears record from Duplicate Flags view. Decision S-15. |
| Duplicate_Of | Linked Record | → Events. Manually linked by Lou/Katie after review. |
| Referred_By_Supplier | Linked Record | FK → Suppliers. Written exclusively by Make on submission via Referral_Code_Used lookup. Defaults to PPN House if no valid code found. Decision S-10. |
| Referral_Code_Used | Single Line | Raw value captured from hidden ?ref= URL parameter on Event Request form. Make uses this to look up and populate Referred_By_Supplier. Decision S-10. |
| How_Did_You_Find_Us | Long Text | Open text from form. Marketing insight only — no automation reads this field. Decision S-10. |
| Winning_Quote | Linked Record | FK → Quotes (single record). Written by Make when a Quote status moves to Accepted. Email A3 trigger fires on this field being set. Decision S-16. |
| Field | Type | Logic / Description |
|---|---|---|
| Quote_ID | Formula | PK "QTE-" & RECORD_ID() |
| Event | Linked Record | FK → Events |
| Supplier | Linked Record | FK → Suppliers |
| Supplier_ID | Lookup | Pulled from Supplier link — used as Softr filter anchor |
| Event_ID | Lookup | Pulled from Event link. Required by CC_Reference_Code formula. Airtable formulas cannot reference linked table fields directly. Audit fix G-10. |
| Event_Date | Lookup | Pulled from Event link. Required by Days_Until_Event formula. Audit fix G-08. |
| Late_Threshold_Days | Lookup | Pulled from Supplier link. Required by Is_Late_Request formula. Audit fix G-09. |
| Referred_By_Supplier_Name | Lookup | Pulls the linked record display name from Event → Referred_By_Supplier. Required by Has_Referral formula. Audit fix G-07. |
| CC_Reference_Code | Formula | Formula "PPN-" & Event_ID & "-" & Supplier_ID — both values are local Lookups. Supplier includes this in email subject when quoting. Audit fix G-10. |
| Quote_Submitted_Via_CC | Checkbox | Set by Make when CC alias email is detected |
| ITQ_Sent_At | DateTime | Written by Make when the ITQ email is dispatched. Starts the 72-hour overdue clock. Decision S-14 (C-06 fix). |
| Days_Until_Event | Formula | Formula DATETIME_DIFF(Event_Date, TODAY(), "days") — Event_Date is a local Lookup. Audit fix G-08. |
| Hours_Since_Invited | Formula | Formula DATETIME_DIFF(NOW(), ITQ_Sent_At, "hours") — references ITQ_Sent_At, not Submitted_At. Decision S-14. |
| Is_Overdue | Formula | Formula IF(Hours_Since_Invited > 72, "Overdue", "Active") |
| Is_Late_Request | Formula | Formula IF(Days_Until_Event <= Late_Threshold_Days, TRUE, FALSE) — Late_Threshold_Days is a local Lookup from Supplier. Audit fix G-09. |
| Quote_Amount | Currency | Supplier's quoted price |
| Applied_Commission_Rate | Number (2 d.p.) | Written by Make at quote creation. Checks Referrals for active protection period between same Organiser and Supplier — sets 0.10 / 0.08 / 0.06 / 0.04 depending on year. All commission formulas reference this field. Decision S-13 (C-01 fix). |
| Commission_Total | Formula | Formula Quote_Amount * Applied_Commission_Rate |
| Commission_Overhead | Formula | Formula Quote_Amount * 0.02 — always 2% to PPN regardless of referral or tapering |
| Commission_Splittable | Formula | Formula Commission_Total - Commission_Overhead |
| Has_Referral | Formula | Formula IF(Referred_By_Supplier_Name != "PPN House", TRUE, FALSE) — uses local Lookup, not cross-table reference. Also surfaced as a Lookup on Payment_Schedules. Audit fix G-07. |
| Referral_Supplier_Share | Formula | Formula IF(Has_Referral, Commission_Splittable * 0.5, 0) |
| PPN_Net | Formula | Formula Commission_Overhead + (Commission_Splittable - Referral_Supplier_Share) |
| Applied_Structure | Single Select | Full Upfront / Deposit+Balance / Three-Stage — set at acceptance |
| Structure_Was_Overridden | Checkbox | Set by Make if supplier changed from onboarding default |
| Total_Contract_Value | Rollup | SUM(Payment_Amount) from linked Payment_Schedules |
| Total_Commission_Due | Rollup | SUM(Commission_Due) from linked Payment_Schedules |
| Total_Commission_Received | Rollup | SUM(Commission_Paid_Amount) from linked Payment_Schedules. Uses helper formula field (not conditional filter, which Airtable doesn't support). Audit fix G-11. |
| Outstanding_Commission | Formula | Formula Total_Commission_Due - Total_Commission_Received |
| Status | Single Select | Invited / Submitted / Accepted / Rejected / Withdrawn |
| Honesty_Loop_Stage | Single Select | Not Started / Loop 1 / Loop 2 / Loop 3 / Escalated / Resolved / Voided |
| Did_It_Happen | Single Select | Yes / No / No Response |
| Final_Agreed_Price | Currency | Entered via Tally on Honesty Loop "Yes" — overrides Quote_Amount if set |
| Organiser_Contacted | Checkbox | Set when escalation email fires to organiser |
| Field | Type | Logic / Description |
|---|---|---|
| Payment_ID | Formula | PK "PAY-" & RECORD_ID() |
| Quote | Linked Record | FK → Quotes |
| Supplier | Lookup | Via Quote link — for Softr filtering |
| Supplier_ID | Lookup | Via Quote → Supplier — Softr filter anchor |
| Has_Referral | Lookup | Pulled from linked Quote. Required here because Airtable formulas cannot reference fields on a linked table directly — Referral_Share formula depends on this. Decision C-02 fix. |
| Applied_Commission_Rate | Lookup | Pulled from linked Quote. Required here because Commission_Due formula references this value and Airtable formulas cannot reach across linked tables. Audit fix G-01. |
| Payment_Type | Single Select | Deposit / Interim / Balance / Settlement |
| Due_Date | Date | Computed by Make from supplier timing selection at acceptance. See Open Decision I-04 for timing field design. |
| Payment_Amount | Currency | Amount of this instalment |
| Commission_Due | Formula | Formula Payment_Amount * Applied_Commission_Rate — Applied_Commission_Rate is a local Lookup from linked Quote. Audit fix G-01. |
| Commission_Overhead | Formula | Formula Payment_Amount * 0.02 |
| Referral_Share | Formula | Formula IF(Has_Referral, (Commission_Due - Commission_Overhead) * 0.5, 0) — half of the splittable commission. Adapts to tapering rates automatically (at 10%: 4%, at 8%: 3%, at 6%: 2%, at 4%: 1%). Uses local fields only. Audit fix N-01. |
| PPN_Net | Formula | Formula Commission_Due - Referral_Share |
| Supplier_Receives | Formula | Formula Payment_Amount - Commission_Due |
| Status | Single Select | Pending / Confirmed / Overdue / Waived |
| Confirmed_Date | Date | When supplier reports payment received |
| Commission_Invoiced | Checkbox | PPN has raised invoice for this tranche |
| Commission_Paid | Checkbox | PPN has received payment — final reconciliation flag |
| Commission_Paid_Amount | Formula | Formula IF(Commission_Paid, Commission_Due, 0) — helper field for Quotes.Total_Commission_Received rollup. Airtable rollups cannot conditionally filter, so this formula zeroes out unpaid rows. Audit fix G-11. |
| Field | Type | Logic / Description |
|---|---|---|
| Referral_ID | Formula | PK "REF-" & RECORD_ID() |
| Referring_Supplier | Linked Record | FK → Suppliers. Defaults to "PPN House" if no external referral. |
| Referred_Event | Linked Record | FK → Events |
| Resulting_Quote | Linked Record | FK → Quotes. Set when a quote is accepted. |
| Status | Single Select | Pending / Active / Converted / Void |
| Commission_Split_To_Supplier | Lookup | Referral_Supplier_Share from linked Quote — 4% of contract value |
| Applied_To_Subscription | Checkbox | Has this credit reduced the next subscription invoice? |
| Introduction_Date | Date | Date the organiser was introduced to the supplier through PPN. Starts the 4-year protection clock. Decision S-07 / C-04 fix. |
| Protection_End_Date | Date (or Formula) | DATEADD(Introduction_Date, 4, 'years'). Make scheduled scenario (May and October) queries where Protection_End_Date is in the future for R5 email. Decision S-07 / C-04 fix. |
| Field | Type | Logic / Description |
|---|---|---|
| Billing_ID | Formula | PK "BIL-" & RECORD_ID() |
| Supplier | Linked Record | FK → Suppliers |
| Period_Start | Date | Billing period start date. Audit fix N-06. |
| Period_End | Date | Billing period end date. Audit fix N-06. |
| Gross_Fee | Currency | Standard subscription fee |
| Referral_Credits_Applied | Currency | Pulled from Supplier's referral credit balance for this billing cycle |
| Net_Amount_Due | Formula | Formula Gross_Fee - Referral_Credits_Applied |
| Payment_Status | Single Select | Unpaid / Paid / Overdue |
| Stripe_Customer_ID | Single Line | Stripe Customer ID for this supplier. Used to associate invoices and payments in Stripe. Decision S-12. |
| Stripe_Subscription_ID | Single Line | Stripe Subscription object ID if using recurring billing. Decision S-12. |
| Next_Billing_Date | Date | Next invoice due date. Set by Make when subscription is activated or renewed. Decision S-12. |
| Field | Type | Logic / Description |
|---|---|---|
| Review_ID | Formula | PK "REV-" & RECORD_ID() |
| Event | Linked Record | FK → Events |
| Supplier | Linked Record | FK → Suppliers |
| Supplier_ID | Lookup | Via Supplier link — Softr filter anchor |
| Survey_Sent_Date | Date | When Tally survey link was emailed to organiser |
| Survey_Responded | Checkbox | |
| Overall_Rating | Number | 1–5 star score (from Tally) |
| Would_Recommend | Checkbox | |
| Review_Text | Long Text | Free-text organiser response |
| Consent_To_Publish | Checkbox | "May we share this as a testimonial?" — asked in Tally |
| Published | Checkbox | Lou/Katie marks when used on website/socials |
| Internal_Flag | Single Select | All Good / Monitor / Escalate |
| Internal_Notes | Long Text | Lou/Katie notes on poor experiences |
| Field | Type | Logic / Description |
|---|---|---|
| Log_ID | Formula | PK "LOG-" & RECORD_ID() |
| Supplier | Linked Record | FK → Suppliers |
| Supplier_ID | Lookup | Via Supplier link — Softr filter anchor. Required by Decision S-08. Audit fix G-04. |
| Event | Linked Record | FK → Events (optional) |
| Quote | Linked Record | FK → Quotes (optional) |
| Type | Single Select | New Match / Quote Submitted / Quote Accepted / Commission Due / Referral Converted / Payment Confirmed |
| Message | Single Line | Human-readable, e.g. "You've been matched to Midsummer Festival 2026" |
| Read | Checkbox | Supplier marks as read via Softr |
| Created_At | Created Time | Auto |
| Field | Type | Logic / Description |
|---|---|---|
| Stock_ID | Formula | PK "STK-" & RECORD_ID() |
| Supplier | Linked Record | FK → Suppliers |
| Supplier_ID | Lookup | Via Supplier link — Softr filter anchor |
| Tent_Type | Single Select | Bell Tent / Tipi / Yurt / Safari / Geodome / Shepherd Hut / Other |
| Tent_Size_m | Number | Diameter in metres e.g. 4, 5, 6 |
| Units_Available | Number | Total units of this type the supplier has |
| Persons_Per_Unit_From | Number | Ideal minimum occupancy per unit |
| Persons_Per_Unit_To | Number | Ideal maximum occupancy per unit |
| Beds_Included | Checkbox | |
| Bed_Type | Single Select | Real / Inflatable / Both |
| Capacity_From_This_Type | Formula | Formula Units_Available * Persons_Per_Unit_From |
| Capacity_To_This_Type | Formula | Formula Units_Available * Persons_Per_Unit_To |
| Notes | Long Text | Optional notes about this stock line |
| Field | Type | Logic / Description |
|---|---|---|
| Outcode | Single Line | PK e.g. GL7, OX1, SW1A |
| Latitude | Number (decimal) | Centroid latitude for this outcode area |
| Longitude | Number (decimal) | Centroid longitude for this outcode area |
| Town | Single Line | Nearest town/city. From Gibbs CSV. Useful for ops views and manual review. Decision S-04. Audit fix G-03. |
| County | Single Line | e.g. Gloucestershire, Greater London. Required for Named Counties matching — Events.Event_County is a Lookup pulling this field. Decision S-11. Audit fix G-03. |
| Region | Single Line | e.g. South West, London — for Named Regions matching and manual filtering. Events.Event_Region is a Lookup pulling this field. |
| Country | Single Line | England / Wales / Scotland / Northern Ireland. From Gibbs CSV. Decision S-04. Audit fix G-03. |
Referred_By_Supplier when no external referral exists. This means commission split logic runs identically for all quotes — no special-case code needed.
| Scenario | PPN Overhead (2%) | PPN Profit Share (4%) | Referring Supplier (4%) | PPN Total |
|---|---|---|---|---|
| No external referral | 2% | 8% | 0% | 10% |
| External referral exists | 2% | 4% | 4% | 10% |
Commission is tracked per payment instalment in Payment_Schedules, not as a single lump sum. Standard rate is 10% of each tranche. For repeat bookings under the 4-year tapering schedule (Decision B-02), Applied_Commission_Rate on each Quote is set by Make to 10% / 8% / 6% / 4% depending on the year since introduction. All formulas reference this field, not a hardcoded rate. Audit fix P-01.
Implemented in Make (Match Engine scenario)
IF Is_National → always include
ELIF Coverage_Type = "Named Counties"
→ check Event_County ∈ Supplier.Coverage_Counties
ELIF Coverage_Type = "Named Regions"
→ check Event_Region ∈ Supplier.Coverage_Regions
ELIF Coverage_Type = "Counties + Regions"
→ check Event_County ∈ Coverage_Counties
OR Event_Region ∈ Coverage_Regions
ELIF Coverage_Type = "Miles Radius"
→ run Haversine, filter by Max_Range_Miles
Event_County and Event_Region are Lookup
fields on Events pulling from Outcode_Link.
Decision S-11.
Implemented in Make (Match Engine scenario)
Event.Event_Type ∈ Supplier.Event_Types_Served AND Event.Event_Type ∉ Supplier.Event_Types_Excluded
Formula field on Quotes
Is_Late_Request = IF( Days_Until_Event <= Late_Threshold_Days, TRUE, FALSE ) Days_Until_Event uses Event_Date (local Lookup). Late_Threshold_Days is a local Lookup from Supplier. Audit fixes G-08 / G-09.
Formula on Events + Make scenario
Duplicate_Check_Key = IF(Organiser_Company, UPPER(TRIM(Organiser_Company)), UPPER(TRIM(Organiser_Name)) ) & "-" & Outcode & "-" & TEXT(Event_Date, "YYYY-MM") Falls back to Organiser_Name when company is blank (e.g. private weddings). Decision S-18. Audit fix G-14.
Make searches for matching key on creation. Sets Duplicate_Flag if found. Human review decides.
Formula on Quotes
Has_Referral = IF( Referred_By_Supplier_Name != "PPN House", TRUE, FALSE ) Referred_By_Supplier_Name is a local Lookup (Event → Referred_By_Supplier). Audit fix G-07.
Formula on Quotes
Hours_Since_Invited = DATETIME_DIFF(NOW(), ITQ_Sent_At, "hours") Is_Overdue = IF(Hours_Since_Invited > 72, "Overdue", "Active") Decision S-14: ITQ_Sent_At replaces Submitted_At. Audit fix N-03.
action_base_url. Phase 1 points to Tally forms. Phase X (Softr) updates this single variable and all email links update automatically.
| # | Trigger | Recipient | Email Template | Data injected into email | Tool |
|---|---|---|---|---|---|
| 01 | New Event created | Internal | E2 — New Enquiry Alert | Event_Name, Date, Location, Guest_Count, Structure_Category | Airtable |
| 01b | New Event created | Organiser | E1 — Event Form Submission Confirmation | Event_ID, all submitted fields echoed, quote tier, upgrade form link | Make |
| 02 | Duplicate_Flag → TRUE | Internal | E3 — Duplicate Event Flag | Event_ID, matched Event_ID, Duplicate_Check_Key | Airtable |
| 03 | Match Engine complete | Internal | Internal only — no template | Event_ID, list of matched Supplier names, count | Make |
| 04 | Status → Quotes Sent | Organiser | E5 — Quotes On The Way | Event_Name, number of quotes incoming, 72hr deadline | Airtable |
| 05 | Status → Quotes Sent (per supplier) | Supplier | Q1 — Invitation to Quote (ITQ) | Event_ID, Event_Date, Location Outcode, Guest_Count, Structure_Category, CC_Reference_Code, Quote deadline | Make |
| 06 | CC alias email detected | Internal | Q3 — Quote Received (Internal) | Supplier name, Quote_Amount, Event_ID — Quote set to Submitted | Make |
| 06b | Supplier confirms intent (Yes on ITQ) | Supplier | Q2 — Full Event Details + Introduction | Full event brief, organiser contact, quote deadline, PPN intro record, 4-yr commission schedule | Make |
| 06c | Supplier declines ITQ | Supplier | Q4 — Declined ITQ | Event summary, soft referral nudge | Make |
| 06d | 72hrs after ITQ, Quote still = Invited | Both | Q5 — Quoting Deadline Check | Supplier: did you send? Organiser: did you receive? | Make |
| 07 | Winning_Quote set | Supplier | A1 — Booking Confirmed (Winning Supplier) | Event_Name, Date, Organiser contact, bank details prompt, 48hr window | Airtable |
| 08 | Winning_Quote set | Organiser | A2 — Booking Confirmed (Organiser) | Supplier name, Quote summary, ToS link | Airtable |
| 09 | Winning_Quote set | Losing Suppliers | A3 — Unsuccessful Quote Notification | Generic "not selected" message | Make |
| 09b | 2–3 weeks after intro, no outcome recorded | Both | A4 — No Response After Intent to Quote | Supplier: did this result in a booking? Organiser: have you confirmed a supplier? | Make |
| 10 | +48hrs after Event_End_Date | Supplier | H1 — Honesty Loop Initial Nudge | Event_Name, Quote_Amount, Commission_Total, Did_It_Happen Tally link | Make |
| 11 | Did_It_Happen → Yes | Internal | H2 — Commission Ready (Internal) | Commission_Total, PPN_Net — ready to invoice | Airtable |
| 12 | Did_It_Happen → Yes (+2 days) | Organiser | H3 — Post-Event Satisfaction Survey | Event_Name, Business_Name, Survey link (Tally) | Make |
| 13 | Honesty_Loop_Stage → Escalated | Organiser + Internal | H4 — Honesty Loop Escalation | Business_Name, Event_Name, Event_Date — combined survey + internal flag | Make |
| 14 | Subscription Payment_Status → Overdue | Supplier | B1 — Subscription Payment Overdue | Invoice amount, due date, payment link (Tally) | Make |
| 15 | Referral record created | Supplier | R1 — Referral Confirmation | Referral details, 4% commission note, bank detail prompt | Make |
| 16 | Referral.Status → Active | Supplier | R2 — Referral Matched | Referred event matched, commission update to follow | Make |
| 17 | Commission_Paid → TRUE | Supplier | R4 — Commission Ready to Pay | Commission amount, bank details link, 14-day window | Airtable |
| 18 | Scheduled — May & October | Supplier | R5 — 4-Year Commission Reminder | Introduction_Date, Protection_End_Date, current commission rate, booking prompt | Make |
Final_Agreed_Price is written to the Quote. Settlement Payment_Schedule record created dynamically if Three-Stage structure. Satisfaction survey fires to organiser +2 days later.
Suppliers declare two structures at onboarding: Standard (default) and Late Request (triggered when Days_Until_Event ≤ Late_Threshold_Days). At quote acceptance, Make pre-fills a Tally confirmation with the applicable structure. Supplier confirms or overrides.
Acceptance_Date · 30 Days Before → Event_Date - 30 · Post-Event 14 Days → Event_End_Date + 14. Every Payment_Schedule record has a concrete Due_Date the moment it's created.
IF(
FIND(" ", Postcode_Raw) > 0,
TRIM(LEFT(Postcode_Raw, FIND(" ", Postcode_Raw) - 1)),
LEFT(UPPER(TRIM(Postcode_Raw)), 3)
)
Examples: "GL7 2BX" → "GL7"
"SW1A 2AA" → "SW1A"
"B1 1BB" → "B1"
Trigger: Record created/updated in Suppliers or Events Action: Search [Ref] Outcodes where Outcode = extracted value Action: Update Outcode_Link field with matching record ID Runs once on creation. Re-runs only if postcode changes.
distance_km = 2 × 6371 × ASIN(SQRT(
SIN((lat2-lat1) × PI/180 / 2)² +
COS(lat1 × PI/180) × COS(lat2 × PI/180) ×
SIN((lon2-lon1) × PI/180 / 2)²
))
Filter: distance_miles = distance_km × 0.621371
KEEP IF distance_miles ≤ Supplier.Max_Range_Miles
SKIP IF Supplier.Is_National = TRUE
The schema is Softr-ready from day one. The following additions have been baked in now to avoid retrofitting later.
action_base_url Data Store variable in Make.