Self Link Tables

Hey ​@FPX!

What data points (fields) are you capturing for Enquiries and what data points (fields) are you capturing for Supplier Quotes?

Trying to figure out whether these are different types of the same broader object/entity, similar objects, completely different objects.

Mike, Consultant @ Automatic Nation 

Hey Mike.

For Customer Enquiries, the main data points we capture are:

Customer (linked to Business and Location)

Delivery Address 

Requested delivery date 

Status (Enquiry, Quoted, Ordered, etc.)

Comments/Notes

Line Items (linked) → each line item captures:

- Product (linked from master product table)

- Quantity of units (requested by customer)

- Unit of Measure

- Bid price (optional)

I am then adding the supplier details and pricing and splitting the enquiry into multiple orders if there are multiple suppliers (I.e. one supplier to one customer). 

However…

Now I want to send out enquiry to my suppliers to allow them to quote against the enquiry line items and then allow the customer to approve the quote line items (I.e. many suppliers to one customer). However I still want the option to skip the quote process and go straight to order (as this is the status quo).

For Supplier Quotes, the key data points are:

Supplier (linked to Business and Location)

Customer (hidden field passed from enquiry)

Validity/Expiry Date

Status (Quoted, Accepted, Rejected)

Optional: Comments/Conditions (e.g., minimum order, freight terms)

Linked Line Items (the specific product/quantity the customer enquired on)

Line Items:

Product (what the supplier can offer per linked line item from customer- this can be alternative products depending on availability. 

Quantity of product

Price (per Unit of Measure) of product

Hi ​@FPX,

I would use separate tables, not self-links. You’ve got multiple suppliers per line item.

Suggested Tables:

• Enquiries
• Enquiry Line Items
• Supplier Quotes
• Quote Line Items, linked to the quote and to the line item it answers
• Approvals, linking the line item to the chosen quote line item with fields like Approved Qty, Markup, Customer Price

Self-links are great for parent-child or version chains. Here you’re comparing several supplier offers on the same item and sometimes splitting the qty. Separate tables keep it tidy and make rollups and filters simple.

How I’d run it:

• Request quotes button creates one Supplier Quote per supplier and quote lines for each enquiry line item.
• Approve picks the winning quote lines and writes Approval rows, so splits are easy.
• If you skip quotes, create an Approval straight from the line item to a supplier.

Hm, so you want to put Supplier Quotes into the Customer Enquiries table?  If so, nah, I reckon I’d keep them as separate tables

Hey ​@TheTimeSavingCo and ​@airvues , appreciate the input.

I already have just two core tables that drive everything end-to-end:

• Order Overview (parent table of order line items: customer/supplier, date required, summary of order line items etc.)

• Order Line Items (details: product, qty, prices; status moves through Enquiry → Quote → Ordered → Partially Dispatched → Dispatched → Complete)

Dispatches and Dispatch Line Items are handled in their own table and link back to Order Overview and Order Line Items.

Questions:

1. Whats your reasoning for the multiple tables vs using self linking? By doing self linking i would not need automations whilst with multiple tables i would need to duplicate all of the same fields for essentially what is just a change of status.

2. For enquiries (Purchase flow) and their line items and quotes (Sales flow) and their line items would it not be smarter to just have these self linked and then just update their statuses to ordered when they become ordered based on ‘approval’ checkbox fields? 

3. If I go with self-links in Order Overview (purchases (one customer) linked to sales (one or many suppliers) and self links in Line Items table (e.g. enquired line items linked to multiple quoted line items), what are the pitfalls you’ve seen at scale?

4. What advantages do you see by splitting out my ‘order line items’ table to ‘Enquiry Line Items’, ‘Quote Line Items’, ‘Order line items’ into separate tables?

5. Carrying on from point 4, would it be preferential to just split a seperate enquiries line items table then the quotes line items can lie within the order line items (and just a status change to go from quote to order)

Thank you!

​@TheTimeSavingCo ​@Mike_AutomaticN ​@airvues Do you guys have any more feedback on the above? Thanks!