Skip to main content

Reserve/Folio TotalStay

To access the ReserveTotalStay and FolioTotalStay extensions in the SQL Wizard:

  • Select either the RESERVE.CONFNUM or FOLIOHD.NUMBER field and press Formula
  • Click the Total Stay tab and select which value to use
  • Click the Format tab and give the column a proper title (ie: Total Stay)
  • Click on OK

ReserveTotalStay vs FolioTotalStay

  • RESERVETOTALSTAY(column,parm)

column A column number OR column name in the SQL query results which contains the confirmation number of the reservation and where the result will be.

parm 1 (See below)

parm 2 (Optional Conditional formula that returns True/False to execute this Extension)

  • FOLIOTOTALSTAY(column,parm)

column A column number OR column name in the SQL query results which contain the folio number of the inhouse folio and where the result will be.

parm 1 (See below)

parm 2 (Optional Conditional formula that returns True/False to execute this Extension)

parm 1 values for RESERVETOTALSTAY and FOLIOTOTALSTAY:

  • 0 = The reservation or folio value which is the Grand Total including all taxes (all values 1-4 below)

  • 1 = The reservation or folio value which includes only the Room portion

  • 2 = The reservation or folio value which includes only the Room tax portion

  • 3 = The reservation or folio value which includes only the Daily Charges portion

  • 4 = The reservation or folio value which includes only the Daily Charges tax portion

  • 5 = The reservation or folio value which includes Room + Daily Charges portion

  • 6 = The reservation or folio value which includes Room Tax + Daily Charges tax portion

  • 7 = RESERVETOTALSTAY = The reservation remaining balance owed (Total Charges - Deposits)

  • 7 = FOLIOTOTALSTAY = The folio amount which will be posted for the current audit date.

  • 8 = The reservation or folio average night without tax (Total Stay divided by Number of Nights)

  • 9 = The reservation or folio average night with tax (Total Stay divided by Number of Nights)

parm 2 Conditional formula (using Clarion Syntax) that returns either 0 (False) or 1 (True)

  • Use FIELDx where x to address any column's data
  • Use ROW to address the row number
  • Use any other Clarion Syntax to write a valid formula - Click here for functions available in the Clarion Syntax.

Example:

  • RESERVETOTALSTAY(1,0,FIELD2 = 'RESERVE') would only fire the extension if column 2 had the value of RESERVE in it.

  • RESERVETOTALSTAY(1,0,FIELD2 < 100000) would only fire the extension if column 2 was less than 100000

  • RESERVETOTALSTAY(1,0,ROW > 100) would only fire the extension for rows higher than 100

important

The reservation or folio number must be retrieved in the SQL Query for each column you wish the results to appear. For example, if you want the Reservation Stay total to appear in column four of the reservation, the CONFNUM must be retrieved in column 4.

Examples in SQL

SELECT CONFNUM,FIRSTNAME,LASTNAME,CONFNUM FROM RESERVE

The TotalStay extension will then convert that confirmation number to the TotalStay value once all the results have been retrieved.

SELECT CONFNUM,FIRSTNAME,LASTNAME,CONFNUM FROM RESERVE

  RESERVETOTALSTAY(4,0)

In this above example, the fourth column will show the total stay value of the reservation.

You can use the extension as many times as you need, however, each column must contain the confirmation number or folio number:

SELECT CONFNUM,FIRSTNAME,LASTNAME,CONFNUM AS 'Room',CONFNUM as 'DailyCharge' FROM RESERVE

  RESERVETOTALSTAY(4,1)

  RESERVETOTALSTAY(5,3)

In the above example, the final results of this query would be:

  1. Confirmation Number
  2. First Name
  3. Last Name
  4. Room Amount portion of reservation
  5. Daily Charge Amount portion of the reservation

Example of a query returning the value of inhouse guests:

SELECT NUMBER,FIRSTNAME,LASTNAME, NUMBER as 'Total Stay' FROM FOLIOHD

  FOLIOTOTALSTAY(TotalStay,0)

It is important to remember to place these extensions at the very bottom portion of the SQL Statement, before the SUBTOTAL ON extension:

SELECT NUMBER,FIRSTNAME,LASTNAME, NUMBER as 'Total Stay' FROM FOLIOHD

 WHERE FOLIOSTATUS = 'R' 

   ORDER BY LASTNAME

  SUBTOTAL 4        

  FOLIOTOTALSTAY(4,0)

Example of showing all fields:

SELECT CONFNUM as 'Conf#',
CONFNUM as 'Room',
CONFNUM as 'Tax',
CONFNUM as 'Daily Charges',
CONFNUM as 'Daily Charge Taxes',
CONFNUM as 'Total Room',
CONFNUM as 'Total Taxes',
CONFNUM as 'Grand Total',
CONFNUM as 'Balance Owed'
   FROM RESERVE
         RESERVETOTALSTAY(2,1)
         RESERVETOTALSTAY(3,2)
         RESERVETOTALSTAY(4,3)
         RESERVETOTALSTAY(5,4)
         RESERVETOTALSTAY(6,5)
         RESERVETOTALSTAY(7,6)
         RESERVETOTALSTAY(8,0)
         RESERVETOTALSTAY(9,7)

With VAT/GST inclusive taxes

Use a third parameter, 1= True and 0= False, to include taxes for properties using VAT/GST inclusive. For example, properties would use the following to include taxes in the amounts displayed for the previous example:

SELECT CONFNUM,FIRSTNAME,LASTNAME,CONFNUM AS 'Room' FROM RESERVE

  RESERVETOTALSTAY(4,1,1)