Blog

Overview

This case study highlights a Power Platform solution I built for a small custom jewelry store in Columbus, Ohio. Their original process relied on a complex Excel worksheet to calculate pricing, then required manual copy/paste into a customer-facing document for review and signature. The goal was to preserve the store’s established pricing logic while eliminating repetitive manual work and reducing pricing errors.

The result was a structured, repeatable workflow built with Microsoft Power Apps, Microsoft Lists, and Power Automate: staff enter job details and pricing inputs once, the app calculates totals automatically, and a Word document is generated and saved to SharePoint without manual formatting.

The business problem

• Manual calculations increased the risk of pricing mistakes.
• Repetitive data entry slowed down quoting and customer turnaround time.
• Inconsistent documentation made it harder to standardize approvals and signatures.
• No central system of record for historical custom design pricing and artifacts.

Solution goals

• Replace the Excel worksheet workflow with a guided, user-friendly app.
• Use structured data sources so pricing can be maintained without app rewrites.
• Generate a consistent Word document the customer can review and sign.
• Store outputs centrally in SharePoint for easy retrieval and audit history.

Architecture (high level)

The solution uses a clean separation between reference pricing data, the app experience, and automation:

• Microsoft Lists store pricing matrices and lookup tables (e.g., diamonds, castings, settings labor).
• Power Apps provides the multi-screen pricing experience and performs real-time calculations.
• Output List captures a complete pricing snapshot for each job (system of record).
• Power Automate generates a customer-ready Word document from the captured record.
• SharePoint document library stores the final generated documents.

Data model: using Lists as a pricing engine

Instead of hardcoding prices into the app, the store’s pricing logic is driven by structured lists. This enables a simple maintenance model: update list items to adjust prices without updating the app itself.

Reference lists (inputs):

• Diamonds pricing (including clarity/category and price-per-carat)
• Castings pricing (price-per-DWT)
• Setting labor pricing (labor cost per setting)
• Wax carving pricing (hours/labor rates)
• Setting types / categories (drives selections and filtering)

Transactional list (output):

• One “output” list item per job containing all captured fields and computed totals
• Acts as an audit trail and a reliable source for downstream automation

Power Apps: translating a spreadsheet into a guided workflow

The Power Apps canvas app was designed to mimic the structure of the original Excel worksheet while improving usability. It consolidates the pricing experience into a guided, multi-screen workflow with dynamic controls and validation.

Key app behaviors:

• Conditional visibility (e.g., diamond fields only appear when diamonds are included).
• Filtered dropdowns to guide selection based on category (clarity/type, labor categories, etc.).
• Automatic line totals calculated in real time as quantities and selections change.
• Category subtotals (diamonds, castings, settings, wax, other items) aggregated for final totals.
• Sales tax and grand total computed consistently every time.

Submission pattern: writing a complete record with Patch

On submission, the app uses a Patch-based approach to write the complete job record to the output list. This pattern creates a durable, queryable “pricing snapshot” that can be referenced later and used for automation.

Implementation note: writing a complete snapshot to an output list reduces dependency on live reference lists after the fact. If prices change later, you still retain the original job pricing exactly as quoted.

Document generation: Word template + Power Automate

After an item is created in the output list, Power Automate runs a flow that generates a customer-ready document. The document is based on a Word template that mirrors the store’s original format and includes fields for review and signature.

Flow steps:

1. Trigger: When an item is created (SharePoint output list).
2. Format totals: Normalize currency/decimal formatting for consistency.
3. Populate Word template: Map output list fields into Word content controls.
4. Create file: Save the generated document into a SharePoint document library with a consistent naming pattern.

User experience and branding

A subtle but important aspect of adoption is making the solution feel “owned” by the business. I aligned the app’s UI with the store’s branding (including a gold color theme) and added an end screen to provide clear next steps.

End screen actions included:

• Open the generated document output folder
• Start another custom design pricing request
• Navigate to relevant store/sharepoint pages

Outcomes and impact

• Reduced manual work by eliminating repeated copy/paste into customer documents.
• Improved consistency of pricing calculations and documentation.
• Created an audit trail of every pricing event and corresponding generated artifact.
• Faster turnaround on custom quotes, improving the customer experience.

Why this approach works well for small business

Many small businesses are “stuck in spreadsheets” — not because Excel is bad, but because it’s where the business logic lives. Power Platform is an ideal bridge: it can preserve the core logic while adding structure, automation, and governance without the overhead of custom software development.

What I’d improve next (if scaled)

• Role-based access for staff vs. managers (quote approval workflow).
• Customer e-signature integration for a fully paperless experience.
• Reporting on margins, popular options, and quote-to-sale conversion.
• Environment strategy (Dev/Test/Prod) if expanded to multiple locations.

Closing

This project is a great example of Power Platform delivering practical business value: a guided app experience, structured data, automated document creation, and a centralized repository — all built using Microsoft 365-native services.

If you’re considering modernizing a spreadsheet-driven workflow, Power Apps + Lists + Power Automate is often the fastest and most maintainable path from “manual process” to “repeatable system.”