Layout for Pricing (client / vendor)

[CRoberts]

Hey Everyone, new here. This forum has been very helpful getting me pretty far in my build. I'm on the home stretch but cannot figure out the following...

I have 6 total documents I work with from an item list that has 2 sets of pricing (sales and purchases).

Document Set 1 (sales quote, sales order, sales invoice) - Client Facing

Ex. sales quote is built with multiple items (grass cut $100.00), sent to the client for approval. Once approved we generate a sales order with the items approved (could be only a few of the total originally sent), sales order turns into a sales invoice once completed.

Document Set 2 (purchase quote, purchase order, purchase invoice) - Vendor Facing

Ex. vendor sends us their estimate, we enter this into a purchase quote. Once client approves work above we then select the approved items in the purchase quote and generate a purchase order. Once the vendor completes the work, we then generate a purchase invoice.

How would I set this up?

Edit: I'm not at the point where I'm looking to create documents just yet, this is a setup for tracking the data only.

[Joseph_Seddik]

Welcome to the community!

An app with approval process was presented during the last Office Hours session. This would be similar to your needs. 

Check it out:  AppSheet Office Hours: How to Use and Optimize App Expressions  

[CRoberts]

Thank you for the link. Unless I am missing something, the part I cannot figure out is how to handle 2 sets of pricing for the same line item with one pricing for the sales side and one for the purchase side. This is what I had setup and this is where I'm having an issue...

ID	Sales ID	PurchaseID	Description	Sales Qty	Sales Rate	Sales Total	PurchaseQty	PurchaseRate	PurchaseTotal
xxx	123	456	Grass Cut	1	75.00	75.00	1	50.00	50.00

Green - sales quotes, sales orders, sales invoices

Red - purchase quotes, purchase orders, purchase invoices

[Joseph_Seddik]

You should have separate tables for Items, Sales and Purchase, as well as Providers and Clients. 

Please read this guide carefully:

Data: The Essentials - AppSheet Help 

[CRoberts]

Joseph,

I have the following setup...

Clients Table

Projects Table

Vendors Table

sales sheet with tables setup as - sales quotes, sales quote detail, sales orders, sales order detail, sales invoices, sales invoice detail

purchasing sheet with tables setup as - purchase quotes, purchase quote details, purchase orders, purchase order detail, purchase invoices, purchase invoice detail

What I cannot figure out is the items table.

How do I set this up where the same exact items can be used across all details sheets but different pricing from the sales side to the purchasing side.

[Joseph_Seddik]

I don't seem to understand where the problem is. Since you have an Items table, you can have a purchasePrice column and another salesPrice column, no? 

[CRoberts]

This is what I had originally but wanted to run this by this group to see if this was in fact the correct way.

This is how I'm understanding your explanation...

ID	SalesQuoteID	SalesOrderID	SalesInvoiceID	PurchaseQuoteID	PurchaseOrderID	PurchaseInvoiceID	Item	SalesQTY	SalesRate	SalesTotal	PurchaseQTY	PurchaseRate	PurchaseTotal
123	135			246			GrassCut	1	75.00	75.00	1	50.00	50.00
456	135	abc		246	xyz		Lock Change	1	100.00	100.00	1	75.00	75.00

So in the table above, in the end we submitted a grass cut and lock change estimate but only the lock change was approved, this would show on the quotes but on the orders only the lock change, correct?

[Joseph_Seddik]

No, unfortunately. My explanation does not involve joining Sales and Purchases in one table. Actually, everyone of the six columns in your table should have its own table. The different tables in your app should then be linked together through establishing the corresponding References between Tables. 

[CRoberts]

Yes I do have 6 tables, the example above is just the items table layout referencing to which of the 6 documents the item belongs to. If a sales and purchase goes full cycle from estimate to approval to invoice then that item would be on all 6 of those documents via the corresponding documentID.

[Joseph_Seddik · Accepted solution]

Yes, you are right! The referencing should be done from the other table towards the item table. You can think about it like this:  

• An item can exist without sales or purchases
• Sales and Purchases cannot exist without the item. They belong to it. 
• Then Sales and Purchases should reference the item, not vice versa.

[CRoberts]

THANK YOU!!!! Much appreciated.

[Joseph_Seddik]

Welcome my friend. All the best!