Quotes, orders and invoices with their lines

A quote, a sales order, a purchase order or an invoice holds line items: one row for each product, with a quantity and a price. They are a subform of the record, so they are imported the way any other rows inside a record are: one sheet holds the records, a second sheet holds the lines, and each line names the record it belongs to.

The examples here are a Quotes sheet and a Quote lines sheet for made-up companies and a catalogue of made-up products.

The records sheet, one row for each quote:

Subject	Account
Spring order	Harbour Cafe Ltd
Autumn order	Linden Books Pty

The sheet of lines, one row for each line, each naming its quote:

Quote	Product code	Quantity	Discount
Spring order	P-100	3	10
Spring order	P-205	1
Autumn order	P-100	12

What a record needs

• At least one line. The CRM refuses a record with no line items, so a record can be made only from a sheet that gives it at least one line. Where every row of the records sheet would make its record, Validate stops on a record the lines sheet gives none, naming the first row. Where only some rows might make one, it warns instead, and the run refuses, row by row, any record it would make with no lines. For a record that already exists, a lines sheet that gives it no rows leaves its line items as they are; the records sheet can still update the record’s own fields.
• A sheet of lines. Until a sheet is set up as the lines of a record, the module can only be updated: its tile on Target says it can only be updated until a file of its lines is added, and Validate says the same, with Updates only as the way to keep going. Add the sheet of lines and the module can be made as well.

Set the sheet of lines up on Map fields, as the rows of a subform (the same steps).

Products are matched, never made

Each line names a product that already exists in your CRM. Map the line’s product as a search, by the column that holds the product’s code or name. Sloose never makes a product, and never skips a line: a line whose product is not found, is found several times, or is switched off fails its record whole, and the result names the line. Fix the product in Products (itself an ordinary import), or change the sheet, and run again.

The CRM gives the same answer for a product that does not exist, one that is switched off and a line with no product, so Sloose checks the products itself. Validate asks your CRM about every distinct product the sheet names, in a few calls and not one for each line, and stops, before anything is written, on a line whose product is not found, found several times or inactive: it says how many lines and the first row, never the value. While it is asking, Validate and the run wait, and if the CRM cannot be asked it says so and stops; an empty answer is never read as “all found”. A sheet that names more than 500 different products has the first 500 checked, and says so; the run checks the rest, and checks every line again as it writes, since a product can be switched off in between.

Prices and totals are the CRM’s

Map the line’s quantity, price, discount and description, and a price book if you use one. The totals, a line’s total and net total and the record’s sub total, tax and grand total, are worked out by the CRM and are never offered for mapping.

• A blank quantity is 1, and a blank price is the product’s own price.
• A discount is an amount. A cell that says 10% is sent as written and the CRM turns it into an amount on the line’s total (10% of a line of 200 is stored as 20). Validate warns when a discount column holds a percentage.
• A quantity of nought, a negative quantity or part of a whole, and a discount above the line’s total, are taken by the CRM and totalled as they are. Validate warns, naming the first row; the warning does not stop the run. A clear Validate is not a confirmation that every discount stays within its line. It reads only the quantity, price and discount columns as they are mapped (a formula’s result is not read), and a line whose quantity is blank is not judged against a total, since a blank quantity is not known to be one.
• No tax. This release applies no tax to a line: no tax column is offered, and a line carries a tax only where the product’s own settings put one there.
• A price book is looked up like a product; one the product is not in is refused by the CRM, and so is the record. The book’s own discount becomes the line’s discount, which is the CRM’s rule.

Limits

• 200 lines to a record, read from the CRM’s layout for each of the four. Validate refuses a record over it, as for any subform’s limit.
• Your plan’s rows per run count each line of the sheet as a row.

Stock

Writing sales orders, purchase orders or invoices changes the stock figures of the products on their lines, in the CRM’s own words for each: an invoice takes a product’s Quantity in stock down, a sales order raises its Quantity in demand and a purchase order raises its Quantity ordered. Quotes move no stock. The Run step says so before every run that writes them, with the number of products, for example this moves the stock of 2 products, and Validate warns once for each file of lines.

Replacing a record’s lines with the same products and quantities moves nothing more, while making another record does, and deleting a record gives the stock back. Sloose cannot undo what a run moved: it deletes nothing.

Running it again

A run replaces a record’s lines with the sheet’s, never adds to them, so running the same sheet over the same records leaves the same lines. The CRM gives each line a new id when it is replaced, and nothing in Sloose keeps one. A product switched off since the last run fails the record’s lines, which stay as they were. The run is not one write for the whole record: where the records sheet also updates the record’s own fields, those are written first and are not undone if its lines are then refused. (A record that is refused when it is made is not made at all.)

What the results say

There are two ways a record’s lines are refused, and the rows say which.

• A line’s product is refused before anything is sent (not found, found several times, or inactive): the line that failed is a failed row, and its message names the line and the problem (for example row 3: Product Name “…” is a record the destination will not take (it is not active)). The record’s other lines say Not replaced, naming that row, and are left as they were.
• The CRM rejects the replacement (a price book the product is not in, say): every line of that record is a failed row, each with the CRM’s own error.

See Results for the words. Fix the product (or the sheet), then run the import again.

Before you run it

1. Check Validate: the product and line findings are the ones the run refuses by.
2. Run a dry run: each row says Would be written, or why its record would be refused.
3. Read the stock note on Run, then run it.