Parramatta City meals on wheels MOW Brian Shaughnessy

Parramatta City meals on wheels (MOW) Brian Shaughnessy Lighthouse Consulting & Design www. lcdservices. biz

background § district/region of Sydney § provides services to the residents § Civi. CRM used extensively for back-office support o meals on wheels

meals on wheels § serves elderly citizens of the district § delivers meals at discounted prices M-F § provides various options o o o chilled/frozen meals soups salads juice dessert

meals on wheels § creative use of Civi. CRM architecture § allows full integration with other services managed through the site § required extensive customization, but also relies heavily on the core data structures

activity record § clients place orders on a weekly basis § order details o week/year [one order per week/year; future only] o exceptions [skipped by bulk generator] § custom data set for M-F with fields for each meal type § dietary details stored in custom data attached to contact record

payment record § separated order (activity) from financial obligation (contribution) § modeled after event/membership structure o link activity to contribution o orders_activity_payment (activity_id, contribution_id) § added support for > 1 payment per order

payment record § auto-created with activity order § calculates total due based on standard meal rates o constants defined in code o eventually place in option values § detail the order in contribution record § record changes to the order o adjustment to contribution record o order history through notes (>1 per contrib)

bulk order generation § clients place orders on a weekly basis § most orders are exactly the same from one week to the next § needed to develop method for quickly generating future-week activity records based on prior week

bulk order generation § manually triggered, but options locked down § log table tracks bulk order history o determine last week run o log order counts and activity IDs

bulk order: special handling § order exceptions o skipped by bulk order generator o most immediate prior non-exception order used § account for existing orders for a given week/year § on-hold o hold start date/hold end date o if any overlap, skip bulk generation

bulk order: special handling § third party payer o soft credit to client o payment to TPP entity o handled with relationship defining when TPP begins/ends § admin fee o system default; contact override § direct deposit o set contrib status = completed o else = pending

stock management § set starting values § update when order is created/updated/deleted § special contact: Parramatta Stock Manager § stock adjustment activity types § dashlet report to summarize current levels § summary data handled in stock_log table o tracks current levels § line item stored as activities

reporting § ughhhh… § originally planned to use reporting framework o specs required many unique calculations o unique data handling (field criteria interaction) o too many options § most reports built (or rebuilt) as purely custom implementations

packaging labels § generate by week number/year § calculate number of packages based on meal/salad/soup/dessert combo § track frozen/chilled

packaging labels

full delivery run sheet § used by delivery staff § each contact is assigned a run number (geographic assignment) § MS Word export (scalability/easy to modify)

full delivery run sheet

order invoices § generate invoices for pending payments or by date range

order invoices

order statements § filter by payment status, contact, date range § calculate balance based on various criteria

order statement

other stuff § active services o custom data group o other data groups dependent on active status

quarterly stats report § generate calculations per service for a variety of stats

quarterly stats report

what I’d do differently… § torture by custom field proliferation… § move as much as possible into dedicated CRM path § spend more time working through data model with client § better method for classifying meal field types § spend more time developing specs. for report requirements (much time was spent, but it always seems like more is needed)

challenges § deciding what to lock down to prevent usercreated failures § date-model (week/year): effective for the situation, but required some "translation" for end users § end of year wrap-around a pain § mission-critical system with constantly evolving specs

project status § core system in production for approx. 1 year § ongoing report/statement/invoice tweaking for quite some time § currently wrapping up final adjustments for reports and closing the project