When writing reports in the Report Writer its important to know where data is stored in the application. This is a guide to commonly used tables
Table Name | Purpose | Keys | Joining | Comments |
---|---|---|---|---|
Product | Storing product data integrated from ERP with additional fields that might only be stored online | ProductCode | To get all items that are shown on the website use the where clause: itemvalid = 1 Generally field names from ERP will be the same online but without the dashes. | |
ProntoStockWarehouse | A mirror of the PRONTO ERP table stock-warehouse-detail | StockCode WhseCode | Product: Product LEFT OUTER JOIN ProntoStockWarehouse on ProductCode = StockCode | |
ProntoSalesOrder | Sales Order header table. Stores both carts online and integrated orders from ERP | SoOrderNo | Customer: ProntoSalesOrder LEFT OUTER JOIN Customer on ProntoSalesOrder.socustcode = Customer.Code
User: ProntoSalesOrder LEFT OUTER JOIN [User] on ProntoSalesOrder.souseridcode= [User].EmailAddress | Filter on orders that were created online and have been integrated to ERP: ConfirmedbyERP = 1 Filter on orders that got created in ERP: fwcreatedby = 'WS' Filter on carts in progress: fwcreatedby != 'WS' and soordertotalpackages > 0 Filter on Ready to Integrate: sendrequired = -1 |
ProntoSalesOrderLine | Sales order lines | SoOrderNo SoBoSuffix SolLineSeq | ProntoSalesOrder: ProntoSalesOrderline LEFT OUTER JOIN ProntoSalesOrder on ProntoSalesOrderline.SoOrderNo = ProntoSalesOrder.SoOrderNo AND ProntoSalesOrderline.SoBoSuffix = ProntoSalesOrder.SoBoSuffix
Product: ProntoSalesOrderline LEFT OUTER JOIN product on ProntoSalesOrderline.StockCode = Product.ProductCode | |
CustomerPayments | Payments received either associated with a order or could also be associated with account payments | RowId | ProntoSalesOrder: CustomerPayments LEFT OUTER JOIN Prontosalesorder on CustomerPayments.OrderNumber = ProntoSalesOrder.SoOrderNo User: CustomerPayments LEFT OUTER JOIN [User] on CustomerPayments.UserName = [user].emailAddress | Status: 1 = ready to integrate 2 = integrated 9 = payment failed
Also note for account payments the OrderNumber will be 0 so if you are joining to ProntoSalesOrder suggest to use a LEFT OUTER JOIN instead of LEFT OUTER JOIN |
User | All users | EmailAddress | ProntoSalesOrder: ProntoSalesOrder LEFT OUTER JOIN [User] on ProntoSalesOrder.souseridcode= [User].EmailAddress
Customer: [User] LEFT OUTER JOIN Customer on [User].CustomerCode = Customer.Code | Note that all use of User table must use [User] and not User
Best to ignore guest users which will have emailaddress like 'guest%' |
UserSession | Top level details of every session | EmailAddress | User: UserSession LEFT OUTER JOIN [User] on UserSession.EmailAddress = [User].Emailaddress | Encourage to put a date range on the extract of this as it can be large file |
UserSessionLog | Detailed information on user session | EmailAddress | UserSession: UserSessionLog LEFT OUTER JOIN UserSession on UserSession.EmailAddress = UserSessionLog.Emailaddress | This table is only stored for maximum of 4 weeks due to its size Filter on EventType to get details. See below for UserSessionLog Event Types
|
UserSessionLog Event Types
Event | Description | Other Data |
---|---|---|
CategoryView | A view of a category | EventKey1 = CategoryCode Description = Category Name |
OrderPlaced | The placing of an order | EventAmount = Order Value |
PageView | The viewing of a page | EventKey1 = Pagename and role E.G: (PUBLICR) ProductDisplay.aspx means PUBLICR role and productdisplay.aspx is the name of the page |
ProductInCart | A product being placed in the cart | EventKey1 = ProductCode |
ProductOutOfCart | A product being taken out of the cart | EventKey1 = ProductCode |
ProductSearch | A search of products | EventKey1 = Search Key |
ProductSold | Sale of a product | EventKey1 = ProductCode EventQty = Qty EventAmount = Qty * Price |
ProductView | Detailed view of a product | EventKey1 = ProductCode EventAmount = Price |
Trace | Trace of milliseconds to create the page | Description = PageName |
Related Articles