Tables and Views
Accessing your data via interactive SQL is easy in roomMaster. This gives you the ability to run queries against your database.
In addition, you can create Embedded SQL Reports and store them directly on the Reports menu using the Custom Reports option.
InnQuest Software Support cannot and does not provide Query, Syntax and SQL Help or personalized training for custom reports and queries. There are hundreds of books and material available in bookstores and the Internet to learn SQL.
Below are the Common tables which you have access to. For a list of every table in the roomMaster database, click here:
Inhouse folios
| FOLIODT | Folio detail file | This file contains the balances of all current folios. These are the four tabs you see on a folio (Folio A, Folio B, Folio C, Folio D) |
| FOLIOHD | Folio header file | This file contains all the property information for each inhouse and checked out guest (current folios). It contains the name, room number, check-in/check-out, rates, adults, child, etc. For FOLIOSTATUS field ... R = Current (Reserved) Folios H = History (Checked Out) K = Held (Red Suitcase) Z = Current Desk Folio For Discount Type field ... 1,2,3,4,A are discounts by Percent 5,6,7,8,B are discounts by Amount |
| FOLIOTRN | Folio transaction file | This file contains all detail transactions for each folio. These are child records of FOLIODT/FOLIOHD. |
History folios
| HISTDT | History detail file | |
| This file contains the balances of all history folios. (Most likely all zero since all folios have checked out) | ||
| HISTHD | History header file | This file contains all the property information for each folio after the night audit. It contains the name, room number, check-in/check-out, rates, adults, child, etc. Note: Use PREVROOM field for the actual room number as the ROOMNUMBER for all rows would be *OUT. For Discount Type field ... 1,2,3,4,A are discounts by Percent 5,6,7,8,B are discounts by Amount |
| HISTTRN | History transaction file | This file contains all detail transactions for each folio. These are child records of HISTDT/HISTHD. |
Accounting Tables
| ACCGRP | Account group | Listing of the eight account groups. |
| ACCHIST | Accounting history file | This file has the total transaction amount per account for each month/year combination. This file will also show how many times the transaction type was used each month. (Monthly Summary of TRANHIST) |
| ACCOUNTS | Account listing | This file lists all transaction types, descriptions, and other related information. See ACCTYPE to indicate what type of Account Code (Room Revenue, Charges, Payment, Transfer, etc.) |
| SUBGRP | Account Sub-Group | Listing of all sub-groups of account groups (ACCGRP) |
| TRANHIST | Transaction history file | This file has the total transaction amount per account for each day. This file will also show how many times the transaction type was used each day. |
Common tables
| ACCEXPT | Quicken export file | This file is updated on a nightly basis with when Export to Quicken is checked in the dayend options. |
| ACCGROUP | Account filter file | This file stores the listing of account filters which are setup in the report menu. The file has the name of the filter as well as all the account codes for that filter. Use this file when you want your report to only include transaction types previously setup in an account filter. |
| ACCGRP | Account group | Listing of the eight account groups. |
| ACCHIST | Accounting history file | This file has the total transaction amount per account for each month/year combination. This file will also show how many times the transaction type was used each month. |
| ACCOUNTS | Account listing | This file lists all transaction types, descriptions, and other related information. |
| ADVANCE | Advance deposits file | This file contains each detail transaction for advance deposits. |
| CITYDT | City ledger detail file | The file lists each detail invoice in the city ledger. Each record contains invoice open, open amount, finance charge information and related date information such as paid date, discount date, etc. |
| CITYHD | City ledger header file | This file contains a listing of all city ledger accounts, addresses, account type and balance information. |
| CITYPY | City ledger payment file | This file contains a listing of all city ledger payment batches. |
| DAILYACT | Daily activity file | This file contains a record for each dayend close. Each record will show how many walkins, stayovers, sold rooms, empty rooms and how many checkins for each hour. The file also contains the balance of each ledger at the end of the day. |
| DENIALS | Reservation denials | This file contains the reservation denials information, including reason, description, date, entry date/time, entry user. |
| FORECAST | Forecast file | This file contains a record for each day. Each record will contain the total number of rooms available, stayovers, reservations and out-of-order rooms. |
| GROUPS | Groups file | This file contains a listing of all reservation groups that have been setup. |
| GROUPSLS | Group sales file | This file contains a history of your group sales, including room nights and revenue. |
| JOURNAL | Journal file | This file contains a listing of all transactions which have been created for the current day. Ledgers affected: Blank - Guest Ledger Only A -Guest and Advance C - Guest and City W - City and deposit Y - City Ledger only Z - Deposit Ledger only |
| MESHEAD | Message header file | This file lists all current messages for each guest. |
| PCENTERC | Salescenters – companies | This file contains a record for each company, for each month, with revenue amounts by sales center, as well as rooms posted, comped, and no shows. |
| PCENTERG | Sales centers – groups | This file contains a record for each group, for each month, with revenue amounts by sales center, as well as rooms posted, comped, and no shows. |
| PCENTERT | Sales centers – travel agents | This file contains a record for each travel agent, for each month, with revenue amounts by sales center, as well as rooms posted, comped, and no shows. |
| RATCLOSE | Rate Closures | This file contains the a record for each rate closure that was entered in the Rate Setup. |
| RATES | Rates file | This file lists the rates setup in the system. A record for each room type/rate/date range will exist with the total for each adult/child. |
| RATHIST | Rate history file | This file has statistical data by month/year for each rate type. It lists the total income, total rooms sold, total stay overs by rate type. |
| RESERVE | Reservation file | This file lists all current and future reservations. It also contains the total deposit on file for the reservation. To get the detail transactions, use ADVANCE file. For RESERVESTATUS field ... R = Current (Reserved) Reservations C = Canceled Reservations W = WaitListed I = Currently Checked In H = Check Out (History) G = Gift Card P = Pending (Some Checked In/Some Not) For Discount Type field ... 1,2,3,4,A are discounts by Percent 5,6,7,8,B are discounts by Amount |
| ROOMBOOK | Room blocking file | This file contains a listing of all assigned rooms for each reservation. Use the CONFNUM (confirmation number to link to RESERVE table) Be sure to filter out confirmation numbers greater than 990000 To get a list of all Out of Service rooms, select all confirmation numbers greater than 997000. Example: SELECT ROOMBOOK.ROOMNUMBER AS 'Room Number', ROOMBOOK.FROMDATE AS 'Start Date', ROOMBOOK.TODATE AS 'Ending Date', ROOMBOOK.OOSREASON AS 'Reason', ROOMBOOK.ENTERDATE AS 'Enter Date', ROOMBOOK.ENTEREDBY AS 'Entered By' FROM ROOMBOOK WHERE ROOMBOOK.CONFNUM > 9970000 ORDER BY ROOMBOOK.ROOMNUMBER,ROOMBOOK.FROMDATE |
| ROOMSOLD | Rooms Sold | This file contains each room sold for each night - and is a good file to use for determining rooms sold (nights). POSTTYPE * I = Ignore in Occupancy and Room Figures * N = No Show Folios * S = Same Day Stay * H = Share Folio |
| ROOMS | Rooms file | This file lists all rooms, descriptions, keycode and room type. |
| ROOMLIST | Rooming List for Groups | This file lists all group names and information related to the rooming list feature. Use the GROUPS file as the parent. |
| SCHCATEGORY | Schedule Categories | Listing of all Calendars (use CATEGORYID throughout) |
| SCHEVENT | Schedule Events | Listing of all Calendar Events |
| SCHNOTES | Schedule Profile Notes | Holds the Guest Profile Notes for each Calendar |
| SCHPOS | Schedule POS items | POS Items attached to each selectable item in the Calendar |
| SCHRES1 | Schedule Resource | Calendar Resources |
| SCHRES2 | Schedule People Resource | Calendar People Resources |
| SCHTOUR | Schedule Multi-Person Tour | Listing of all people assigned to a Multi-Person Calendar Event |
| SUBNAME | More names file | This file contains a listing of “more names” for each reservation and folio. |
| TRANHIST | Transaction history file | This file will list the total amount for each transaction type for each day. |
| USERHIST | User field history file | This file contains statistical information for each of the custom fields on the folio properties screen. |
| USERS | User profiles | This file lists all user profiles in roomMaster. (cannot use this file, use VIEW_USERNAMES instead) |
| VIPFILE | Guest profiles | This file contains all related information as appears in guest profiles (Back Office, Guest Profiles) If you want to exclude OPT OUT records, you must look at the VIPFILE.SKIPMAIL field. The value could be 0 through 3. * 0 = Allow mail and emails * 1 = Do not Mail * 2 = Do not Email * 3 = Do not Mail or Email For example: VIPFILE.SKIPMAIL < 2 |
iQ-POS Tables (Point of Sale)
| POSHLDHD | Orders on Hold Header | This file holds the header information of checks on hold. |
| POSHLDDT | Orders on Hold Detail | This file holds the detail/line items of checks on hold. Note: When adding up Quantity, be sure to ignore all that have a SPLITTYPE = 'C'. For ITEM_TYPE field... M = Chargeable Items Blank is non chargeable modifiers P = Payments T = Taxes E = Tips D = Discounts |
| POSLOGHD | Today's Orders Completed Header | This file holds the header information of checks closed. |
| POSLOGDT | Today's Orders Completed Detail | This file holds the detail/line items of checks closed. Note: When adding up Quantity, be sure to ignore all that have a SPLITTYPE = 'C'. For ITEM_TYPE field ... M = Chargeable Items Blank is non chargeable modifiers P = Payments T = Taxes E = Tips D = Discounts |
| POSHSTHD | History of all Orders Header | This file holds the header information of checks completed on previous audit days. |
| POSHSTDT | History of all Orders Detail | This file holds the detail/line items of checks completed on previous audit days. Note: When adding up Quantity, be sure to ignore all that have a SPLITTYPE = 'C'. For ITEM_TYPE field ... M = Chargeable Items Blank is non chargeable modifiers P = Payments T = Taxes E = Tips D = Discounts |
| POSRESV | POS Reservations | POS Reservation table |
| INVHDR | Inventory Header | Inventory Master File |
| INVDTL | Inventory Sales | Detail summary of item sales by day |
| CATEGORY | Categories | Item Categories |
| VIEW_POSUSERNAMES | Sales person names | Used to get the names of the servers in you need full names of the servers in your report: Table Join is SALES_PERSON > CODE POSXXXDT.SALES_PERSON = VIEW_POSUSERNAMES.CODE |
In addition to these tables, you can also see special views that can be used for formatting reports.