OData Table Headings

When you connect to BUZ over OData, each report is exposed as its own feed (an OData entity). Every feed carries its own set of columns — the headings you see depend entirely on which report you’re querying.

Note

This page is a reference. The exact columns in any feed are best confirmed live: connect your OData tool (Excel, Power BI, etc.) and inspect the field list for the feed you’ve selected. Column sets change as reports evolve, so the feed itself is always the source of truth.

How the feeds are organised

BUZ groups its OData feeds by reporting area. The feeds available to you include:

• Leads — Lead Conversion, Lead Conversion by Sales Rep, Lead Reports, Lead Analysis, Lead Analysis by Branch, Lead Sources, Leads Report, Lead Source Matrix.
• Sales — Product Ordered, Sales Conversion by Product, Sales Orders, Sales Order Options (one row per answered question on each order line — join to Sales Orders on OrderItemPkId), Sales Report.
• Jobs & production — Job Line Levels, Jobs Schedule Detailed, Jobs Schedule Summary, Jobs Schedule Summary (Shutters), Work in Progress.
• Banking — Banking Transaction Summary, Deposits Held.

Reading the columns

A few patterns hold across most feeds:

• Key columns (e.g. Id, LeadPkId, OrderID, OrderNo) uniquely identify each row and are useful for joining feeds together.
• *PkId columns (e.g. SalesRepPkId, LeadStatusPkId, ProductionStatusPkId) are internal identifiers for related records. They’re handy for filtering, while the matching descriptive column (e.g. SalesRep, LeadStatus) is what you’ll usually display.
• Date columns (e.g. DateDoc, CreationDate, DateAppointment, InvoicedDate) let you slice by period. Some feeds also break dates out into Year / Month / Week / Day for easier grouping.
• Amount columns (e.g. Amt, OrderAmt, Order_Amount_ExTax, Order_Tax, Order_Amount_IncTax) hold the monetary values; watch for the ex-tax / inc-tax distinction where both are present.

Joining Sales Order Options to Sales Orders

Each row in Sales Order Options is one answered question on one order line. To see those answers next to the product, price and customer, join it to Sales Orders.

Join on OrderItemPkId in Sales Order Options, matched to Id in Sales Orders. That pair is the only reliable link between the two feeds.

Caution

Do not join on order number and line number. A single order line can hold several products that all share LineNumber 1 and differ only by FixedLine, so joining that way attaches every product’s answers to every other product on the line and inflates your row count. On a real order with five such products it turns 79 answer rows into 1,659.

Which column is the line number you see in BUZ?

It is FixedLine, not LineNumber. On screen BUZ numbers an order’s products 1, 2, 3 and so on, and that is what FixedLine holds. LineNumber is an internal position that reads 1 for most products, so it looks wrong if you expect it to match what you see on the order. Use FixedLine whenever you want to display or sort by the line a person would recognise.

Revisions: you get every version of an order

When an order is revised, BUZ keeps each revision. Both Sales Orders and Sales Order Options return all of them, so a date range covering a revised order gives you revision A and revision B, each with its own set of answers.

That is deliberate, and it is long-standing behaviour of the Sales Orders feed. It matters most if you are feeding another system: pick the latest revision per order before you act on the data, or you will process a version the customer has already replaced.

In Power Query, group by OrderNo and keep the maximum OrderRev, then join the answers to the rows you kept.

Related pages

• Excel Reporting with OData
• OData Overview
• OData Restrictions