DCFS Memorial Tracker — Data Migration Mapping

Section 1 — Summary Statistics

163

Enquiry / Order Records

405

File Attachments
(images, PDFs, Word docs)

125

Internal Notes /
Activity Log Entries

6

Sheets Total

~6

Records with Online
Payments Recorded

Section 2 — Main Field Mapping Table

DIRECT No transformation needed

TRANSFORM Data conversion required

NO MATCH Field lost / not imported

COMBINE Multiple fields merged

Old Field (Sheet)	Old Column	New System Field	Mapping Type	Notes / Transform Required
Identity / Reference
Record ID	`MemorialEnquiryAndEstimate_Id` (integer)	`orderId`	TRANSFORM	Prefix with "LEGACY-" e.g. `LEGACY-251`
Our Reference	`OurRef`	`orderRef`	DIRECT	Use as-is; blank records get auto-ref on import
Order Date	`OrderDate` (Excel serial)	`orderDate`	TRANSFORM	Convert Excel serial to ISO date (e.g. 46125 → 2026-04-14)
Old Order ID	`Order_Id` (e.g. F28E251T1)	—	NO MATCH	Store in `masonNotes` as "Legacy Order ID: F28E251T1"
Customer
Customer First Name	`ClientName_First`	`customerName` (part)	COMBINE	Combine First + Last + Prefix into one field
Customer Last Name	`ClientName_Last`	`customerName` (part)	COMBINE
Customer Prefix	`ClientName_Prefix`	`customerName` (part)	COMBINE	e.g. "Mr Jim Boyd"
Customer Middle	`ClientName_Middle`	—	NO MATCH	Drop (rarely populated)
Phone	`Phone`	`phone`	DIRECT	Strip formatting inconsistencies (e.g. "0 7974…" → "07974…")
Email	`Email` (col AC)	`email`	DIRECT
Address Line 1	`Address_Line1`	`address` (part)	COMBINE	Join Line1, Line2, City, PostalCode into one address string
Address Line 2	`Address_Line2`	`address` (part)	COMBINE
City	`Address_City`	`address` (part)	COMBINE
Postal Code	`Address_PostalCode`	`address` (part)	COMBINE
Country	`Address_Country` / `CountryCode`	—	NO MATCH	Assumed NI/UK — drop
Deceased / Memorial
Deceased Surname	`SurnameCemeteryGraveNo`	`deceasedName`	TRANSFORM	Contains surname only (e.g. "SOMERVILLE"). Full name may be in Inscription field
Inscription (full text)	`Inscription` (col BO)	`inscriptionText`	DIRECT	Multi-line text — preserve line breaks
Deceased DOB	—	`deceasedDob`	NO MATCH	Not recorded in old system
Deceased DOD	—	`deceasedDod`	NO MATCH	Not recorded in old system (dates appear only within inscription text)
Cemetery / Location
Cemetery Location (free text)	`CemeteryLocation` (col I)	`cemetery`	DIRECT	Combined name+plot e.g. "Roselawn - T 2280"
Cemetery (dropdown)	`Cemetery` (col AZ)	—	NO MATCH	Old dropdown value (e.g. "Other/See additional Charges") — redundant with above
Grave Number	`GraveNumer` (col BB)	`graveNumber`	DIRECT	Note: old field has typo "Numer" not "Number"
Grave Owner Name	`GraveOwnerDetails_First/Last`	—	NO MATCH	Rarely populated — drop or store in `masonNotes`
Completion/Install Date	`CompletionDate` (Excel serial)	`installDate`	TRANSFORM	Convert Excel serial to ISO date
Headstone / Product
Headstone Style	`HeadstoneStyle`	`hsType`	TRANSFORM	Old values: OG, G3, G1, 1/2 Densmore, Other → map to new price book types or store as-is
Size	`Size`	`hsSize`	DIRECT	Free text e.g. "30"×30"×4"" — store as-is
Granite Selection	`GraniteSelection`	`hsColour`	TRANSFORM	Old: "Black", "South African Grey", "Chinese Grey", "Blue Pearl" → map to new colour codes
Lettering	`Lettering`	`hsFinish`	TRANSFORM	Old: "Gold", "Silver", "White", "Raised Panel Silver" — closest match is hsFinish field
Base Type	`BaseType`	—	NO MATCH	"Standard" / "Ogee" — add to `masonNotes` as "Base: Standard"
Vase Holder	`VaseHolder`	`accessories` (array)	TRANSFORM	If not "None" → add to accessories array e.g. "Vase Holder (Centre)"
Bespoke Price	`BespokePrice`	`totalSellPrice`	DIRECT	If populated, use as override total
Surround / Stone
Surrounds	`Surrounds`	`surroundType`	DIRECT	e.g. "Granite Garden", "Concrete Garden"
Surround Amount	`Surrounds_Amount`	—	NO MATCH	Per user: don't migrate costs
Stone Chippings	`StoneChippins`	`stoneType`	DIRECT	"White", "Grey" — map to new stoneType field
Number of Bags	`NumberOfBags`	—	NO MATCH	Add to `masonNotes` as "Stone bags: N"
Inscription
Type of Work	`TypeOfWork`	`inscriptionType`	TRANSFORM	"Additional Inscription" → "additional"; "New Stone" → "new"; others → note
Lettering Costs	`LetteringCosts`	—	NO MATCH	Per user: don't migrate costs
Financials
Total Price Erected	`TotalPriceErrected`	`totalSellPrice`	DIRECT	Use as sell price total
Part Payment / Deposit	`PartPaymentDeposit`	`depositPaid`	DIRECT
Balance	`Balance`	`balanceDue`	DIRECT
Discount	`Discount` (Yes/No flag)	`extraCharges` (array)	TRANSFORM	If "Yes", add a discount entry to extraCharges; amount from Discount field
Additional Charges	`AdditionalCharges` (Yes/No)	`extraCharges` (array)	TRANSFORM	If "Yes", add charge entry; description from `AdditonalChargesBreakdown`
Additional Charges Breakdown	`AdditonalChargesBreakdown`	`extraCharges[].description`	DIRECT
Staff Discount	`StaffDiscount`	—	NO MATCH	Drop
Cemetery Fee	`Cemetery_Amount`	`cemeteryFee`	DIRECT	If populated
Online Payment Amount	`Order.LineItems → Amount`	`extraCharges` (array)	TRANSFORM	Add as "Part Payment / Deposit" line item in additional items. No cost figures needed
Processing Fees	`Order.Fees → Amount`	—	NO MATCH	Drop — internal payment processor fee
Status
Entry Status	`Entry_Status`	`status`	TRANSFORM	"Complete" → "Installed"; "Submitted" → "Confirmed"; "Incomplete" → "Enquiry"; "Void" → "Enquiry" + note "VOID in old system"
Invoiced flag	`Invoiced` (Yes/No)	—	NO MATCH	Add to log entry as "Invoiced: Yes" if set
PD (Paid) flag	`PD` (Yes/No)	—	NO MATCH	If "Yes" and balance=0, reflect in depositPaid matching total
Notes & Activity Log
Notes (main record)	`Notes` (col AA)	`masonNotes`	DIRECT	Plain text note
Internal Use Only	`InternalUseOnly`	`masonNotes` (append)	TRANSFORM	Append to masonNotes if populated
Additional Information	`AdditionalInformation`	`masonNotes` (append)	TRANSFORM	Append to masonNotes
Activity/Notes Log	`Table sheet → Notes + Name + Date`	`masonNoteEntries` (array)	TRANSFORM	Each row becomes `{ts, author, text}`. Convert Excel serial date.
Files / Attachments
Reference images	`AdditonalInfoPicturesEtc` sheet	`files` (array)	TRANSFORM	Metadata only (filename, size, type). Actual files stored externally — not importable. Map ContentType to new file type dropdown.
Completed job photos	`ImageOfCompletedJob` sheet	`files` (array)	TRANSFORM	Same as above — tag as "Completed Job Photo" type
PDF / Word files	`AdditonalInfoPicturesEtc` (PDF/DOCX)	`files` (array)	TRANSFORM	Tag as "Document" type

Section 3 — Status Mapping

Old `Entry_Status`	→	New `status`	Notes
Complete	→	`Installed`
Submitted	→	`Confirmed`
Incomplete	→	`Enquiry`
Void	→	`Enquiry`	Add note "VOID" in `masonNotes`

Section 4 — Type of Work → Inscription Type Mapping

Old `TypeOfWork`	→	New `inscriptionType`	Notes
Additional Inscription	→	`"additional"`
New Stone	→	`"new"`	Full headstone order
Accessories	→	—	Accessories-only — note in `masonNotes`
Tidy up	→	—	Note in `masonNotes`: "Tidy up job"
Other	→	—	Note in `masonNotes`

Section 5 — Colour / Granite Mapping

Old `GraniteSelection`	→	New `hsColour` Code
Black	→	`black`
South African Grey	→	`sa-grey` (or closest)
Chinese Grey	→	`chinese-grey`
Blue Pearl	→	`blue-pearl`
Other	→	Store raw value in `masonNotes`

Section 6 — Lettering → Finish / Colour Mapping

Old `Lettering`	→	New `hsFinish` / Note
Gold	→	Gold lettering (`hsFinish` or note)
Silver	→	Silver lettering
White	→	White lettering
Raised Panel Silver	→	Raised Panel Silver (note in `masonNotes`)
Other	→	Store raw value in `masonNotes`

Section 7 — Fields That Cannot Be Migrated (Data Lost)

Actual file/image content (only metadata available — filenames, sizes, types)
Grave owner contact details (GraveOwnerDetails_*, GraveOwnerAddress_*)
Deceased DOB/DOD as structured fields (only embedded in inscription text)
Online payment confirmation numbers, refund data
Processing fee records
Staff discount flag (StaffDiscount)
Customer middle name (ClientName_Middle)
Address country (Address_Country) — assumed UK/NI
Internal form system IDs (Order_OrderId, Table_Id, etc.)

Section 8 — Date Conversion Note

Excel serial dates must be converted to ISO dates before import. Subtract the Unix epoch offset (25569) then multiply by the number of milliseconds in a day.

Formula:

new Date((serial - 25569) * 86400 * 1000)

Example: Serial 46125 → 14 April 2026

Apply to: OrderDate, CompletionDate, and all date fields in the Table (notes log) sheet.

Section 9 — What a Migrated Record Looks Like (ID 250 — Boyd)

Before — Old System Raw Values

MemorialEnquiryAndEstimate_Id: 250
OurRef: E177
ClientName_First: Jim
ClientName_Last:  Boyd
Phone: 07746 291681
TypeOfWork: Additional Inscription
CemeteryLocation: Roselawn 177
GraveNumer: E177
GraniteSelection: Black
Lettering: Other
Inscription: "And their daughter Jean
              Died 9th Oct 2025"
TotalPriceErrected: 0
Entry_Status: Submitted

Notes (Table sheet):
  - "received info from family"
  - "look at stone first"
  - "rang client"
  - "emailed BC"

After — New System JSON

{
  "orderId": "LEGACY-250",
  "orderRef": "E177",
  "customerName": "Jim Boyd",
  "phone": "07746291681",
  "cemetery": "Roselawn 177",
  "graveNumber": "E177",
  "hsColour": "black",
  "inscriptionType": "additional",
  "inscriptionText": "And their daughter Jean\nDied 9th Oct 2025",
  "status": "Confirmed",
  "masonNotes": "Lettering: Other",
  "masonNoteEntries": [
    {
      "ts": "2026-04-04T00:00:00Z",
      "author": "sh",
      "text": "received info from family"
    },
    {
      "ts": "2026-04-04T00:00:00Z",
      "author": "sh",
      "text": "look at stone first"
    },
    {
      "ts": "2026-04-04T00:00:00Z",
      "author": "Andrew",
      "text": "rang client"
    },
    {
      "ts": "2026-04-04T00:00:00Z",
      "author": "Andrew",
      "text": "emailed BC"
    }
  ],
  "log": [
    {
      "ts": "2026-04-17T00:00:00Z",
      "author": "Migration",
      "text": "Imported from legacy system (ID: 250)"
    }
  ]
}

Section 10 — Migration Checklist (when ready to proceed)

Export a fresh copy of Excel from the old system
Convert all Excel date serials to ISO dates
Combine name fields (Prefix + First + Last) into a single customerName string
Combine address fields (Line1, Line2, City, PostalCode) into a single address string
Map Entry_Status values to new status values per the mapping table
Map TypeOfWork values to inscriptionType
Map HeadstoneStyle, GraniteSelection, and Lettering to new price book codes
Convert Table sheet notes into masonNoteEntries arrays per order
Flag "Void" records — confirm with team whether to import or skip
Add a migration log entry to each imported record
Handle duplicates — check if any OurRef values already exist in the new system
File attachments — note filenames only; re-upload originals manually if needed
Test with 5 records first before bulk import
Back up the Google Sheet before running the import