Question

I'm trying to create a proposal form for our landscaping business. I like to provide a summary of hardscape items, softscape items, and a plant list in addition to the summary page that would pull the totals from each of those pages.

Ideally creating a database of all materials we use where the summary pages pull from and automatically total each item and total each page.
It would also be helpful for efficiency to be able to autofill or pull from a dropdown.
Also if it's possible to hide each line item price except on the summary page.
Create a work order from the proposal form as well from the proposal?

I know I'm asking a lot but any direction would be appreciated!

Answer 1

You can build this with three linked databases and rollups. Notion can't hide line prices on a page, but a summary page can show totals only.

1. Create a "Materials" database with properties: Name, Category (Hardscape, Softscape, Plants), Unit, and Unit Price. This is your master price list.
2. Create a "Proposals" database (one row per job) and a "Line Items" database with these properties: Proposal (relation to Proposals), Material (relation to Materials), Quantity (number), Unit Price (rollup of Materials > Unit Price, Show original), Line Total (formula: prop("Quantity") * prop("Unit Price")), and Category (rollup of Materials > Category).
3. Picking a Material from the relation dropdown pulls in the price automatically, so there is no retyping.
4. In Proposals, add three rollups on the Line Items relation. Use Line Total with Calculate = Sum, filtered by Category through a separate relation or through three views, one per category. The simplest way is one relation per category: Hardscape Items, Softscape Items and Plant Items. Each rollup then sums its own Line Total. Add a formula for Grand Total that adds the three.
5. Build the proposal page layout: put Proposal properties (the three subtotals and the Grand Total) at the top. Below, add linked views of Line Items filtered by this proposal, with grouping by Category.
6. To hide line prices on the summary, show only the Proposals view with the subtotal properties. Keep the Line Items view on a separate "Work Order" page and hide the Unit Price and Line Total properties there.
7. For a work order, make a template in Line Items or Proposals with a linked Line Items view that shows only Material, Quantity and Unit.

If it helps, I made a free starter template with this setup: https://2rds.github.io/freelancer-ops/lite.html?ref=notionanswers