Price in Hand
Competitor prices, straight from the counter.
I rebuilt here the path of one visit in a field app I made at work. The flow is the real one: store list, visit, record, review and history. The stores, the products and the prices are made up. The original has 18 people using it and about 1,000 records a month.
The database behind it
A simplified slice of the SQL schema, with the names changed. The original has 18 tables and 15 views connected to the app.
Written by the app
Visit
| id | key |
|---|
| seller_id | → Seller |
|---|
| store_id | → Store |
|---|
| started_at | |
|---|
PriceRecord
| id | key |
|---|
| visit_id | → Visit |
|---|
| product_id | → Product |
|---|
| price | |
|---|
| type | |
|---|
| status | |
|---|
| parent_id | → PriceRecord |
|---|
StockRecord
| id | key |
|---|
| visit_id | → Visit |
|---|
| product_id | → Product |
|---|
| units | |
|---|
| status | |
|---|
PriceNotShared
| visit_id | → Visit |
|---|
| product_id | → Product |
|---|
From the master data
Seller
| id | key |
|---|
| name | |
|---|
| manager_id | |
|---|
Store
| id | key |
|---|
| name | |
|---|
| state | |
|---|
| channel | |
|---|
Portfolio
| seller_id | → Seller |
|---|
| store_id | → Store |
|---|
Rules set by the administrator
RequiredProduct
| product_id | → Product |
|---|
| start | |
|---|
| end | |
|---|
Goal
| channel | |
|---|
| stores_per_month | |
|---|
| start | |
|---|
| end | |
|---|
Each arrow leaves a column and points to the table it refers to. The fix for a record points to the reversed record (parent_id): nothing is deleted.
How it was built
- Works with no signal
- The record stays on the device and uploads by itself when the connection returns. At a store counter, signal is luck.
- Records are never erased
- Once saved, a record does not change. A mistake is fixed with a reversal: the old one is flagged and a new one takes its place. The analysis only counts valid ones.
- Required products
- The administrator defines which products every visit must record, and for how long that rule applies.
- From field to management
- The original has 18 screens: 11 for people in the field and 7 for administration, with goals, questions and products.
- Tools
- Power Apps, Azure SQL, Power BI
Back to projects