Copy Price Tool
Index
Overview
The copy price tool is designed to enable manipulating price matrices in bulk. This is particularly useful for copy last year’s prices on current products and putting in the next year’s prices easily and quickly as it is just a matter of copying, changing dates and prices, and pasting back into the tour prices matrix.
Editing the Prices through Copy Price Tool
When editing prices through the Copy Price Tool, matrices need to be constantly checked so that there are no errors or duplicates each line. For instance, when changing a tour code both tour codes may have Standard - Standard as the Basis and SubBasis but it will come up as an error unless each Basis and SubBasis is re-selected as each individual Basis and SubBasis have a unique id. This section will explain the fundamentals tools to use...
Check the check box next to Tour Codes and enter in TUTEXP. F2 will bring down your tourcode list as well.
Click Search & Copy and it will bring up the Copy Price Matrices...
Please Note: If a price is overwritten in the Copy Price Tool with the intention of replacing a price matrix, please note that this is a COPY tool and no prices are replaced in this tool.
To select multiple rows, click on the first desired row...
Hold the Shift key and select the last desired row...
To delete a row to avoid editing, select the desired row/rows and click Delete Selected...
To set every rows From Dates, change Replacement Date with the date that is to be set and click Set FROM Dates...
Set Selected allows a user to change the selected dates to what is in the Replacement Date field. Set Days allows a user to change the days in which each price matrix applies to...
To individually change each field such as Tour Code, Pax Type, Basis, SubBasis, etc. select the field and press F2 on the keyboard. This will bring up a list of tours, pax types, bases, etc.
Please read on to Importing Prices to the Copy Price Tool.
Exporting Prices to CSV Format
Please Note: This tool is not for creating new Basis and SubBasis and it works on caches so before using this tool, all your desired Basis and SubBasis need to be created and if have just created them just before using the Copy Price Tool you need to restart ResPax.
Before using the Copy Price Tool, there should be at least one price set via the Price Matrix section in one of the tours.
Maintenance menu -> Copy Price Tool
When deciding how many prices you would like to create/edit, know that you are able to edit every price in every tour at once but it is not advisable. Separating by certain sections saves both time and efficiency. In this example, we are selecting only the prices to do with the Tour Code, TUTEXP. Check the check box next to Tour Codes and enter in TUTEXP. F2 will bring down your tourcode list as well.
Click Search & Copy and it will bring up the Copy Price Matrices...
To export this list, click Save to File...
Save the CSV file to a local directory for editing. If your local harddrive is not available, see Mapping Network Drives on RDP
Now you can access this file with Microsoft Excel.
Editing the CSV File in Excel
Note: All categories are case sensitive. If you misspell any tourcodes, agent types, basis etc. it will generate errors when importing back into Respax.
Open the saved CSV file in Microsoft Excel
To change the From date, select the cells under dteFrom and edit the date.
To change the To date, select the cells under dteTo and edit the date.
To change the Sell price, select the cells under curSell and edit the amount to emulate the price (ie.200 = $200.00)
If your Levy has changed, you can adjust the prices under curLevy to emulate the price (ie. 20 = $20.00)
Once the adjustments have been completed, the CSV file can now be re-integrated into ResPax.
File -> Save
Excel will ask if we would like to keep the workbook in this format...
Click Yes, this will save the file and you can now exit.
Delete existing price matrices
VERY IMPORTANT - THE NEXT SECTION EXPLAINS HOW TO DELETE PRICE MATRICES FROM YOUR DATABASE. IF YOU DO NOT HAVE A WORKING, TESTED CSV FILE TO REPLACE THEM AS EXPLAINED IN THE DOCUMENT ABOVE, YOU WILL END UP WITH NO PRICES AND CAN NOT TAKE ANY BOOKINGS. TRINITY SOFTWARE WILL HAVE TO RESTORE A BACKUP AND RESTORE YOUR PRICE MATRICES. THIS WILL TAKE TIME AND YOU WILL NOT BE ABLE TO USE RESPAX UNTIL WE HAVE DONE THAT.
WE STRONGLY RECOMMEND YOU TEST/PRACTICE THIS IN YOUR TRAINING DATABASE FIRST BEFORE YOU DO ANYTHING IN THE LIVE DATABASE
DO NOT DO THIS OUT OF TRINITY SOFTWARE OFFICE HOURS AEST MON - FRI 09:00 TO 17:00
The reason that you want to delete prices is because the copy price tool does not allow conflicting prices to be entered over overlapping date ranges and such.
Select as much or little criteria to filter your tours and click on "Search and Delete"
If you need to get rid of some more prices that you DO NOT want to delete there are two methods:
1. Click on the row that you do not want to get deleted by this tool and press the "Delete" key on your keyboard. The selected price will be removed from the screen and the tool will not delete it.
2. To remove more than one line, click on the first row, hold in "Shift" on your keyboard and click on the last row. Press the "Delete" key on your keyboard. The selected price will be removed from the screen and the tool will not delete it.
Click on "Delete Data" and "Yes". EVERYTHING THAT IS ON YOUR SCREEN WILL BE DELETED
Now follow the next section how to import the modified CSV file.
Importing Prices to the Copy Price Tool
Open ResPax, log in, and go to Maintenance menu -> Copy Price Tool
Click Open Existing File...
Locate the CSV file and click Open, it will import the prices into the Copy Price Matrix...
Before we try to integrate the new price ranges into the database, we must check that there are no errors, duplicates etc. Click Check Data and ResPax will check the new prices in the database.
Please Note: When you click Check Data, ResPax will only check the data in the existing database, NOT the other prices in the new price range. If there are duplicates in the imported CSV file, it will only find them when you try to Commit Data
Now that ResPax has found no errors...
We can commit the new prices into your database. Click Commit Data
and it will show that the integration was successful...
To exit the Copy Price Tool, click Cancel/Exit...
Troubleshooting your Price Matrices...
When entering new price ranges into the matrices, there are a few common things that can hinder your integration:
Duplicate price ranges between the existing database and CSV file
Non-existant Basis, SubBasis, Tourcode, Agent Code, Agent Type and Agent Category
This HowTo will briefly show an example of how each of these happen...
Duplicate price ranges between the existing database and CSV file
A common way of doing this is trying to integrate the same price ranges twice. This example is going to use the same file that was used in Editing the CSV File in Excel and Importing Prices to the Copy Price Tool. In Microsoft Excel, Open TUTDAY.csv.
Using this existing price matrix, add a few new price ranges to the list. Select the rows...
And copy them to new cell rows below the existing prices...
Change strTypeDesc to Infant and FOC, adjust the curSell also.
As you can see the CSV does not have any duplicate price ranges in itself but the Adult and Child price ranges have already been updated into the ResPax database.
Save the CSV file, exit and open ResPax.
In Maintenance menu -> Copy Price Tool, Open TUTDAY.csv by clicking Open Existing File. In the Copy Price Matrices screen, all the price ranges are visible...
When clicking Check Data, this error shows up...
This error means that it has found duplicates between the CSV file and the existing database.
This is easily adjustable. Before commiting this data, delete the duplicate rows. To do this, Select the rows (in this example Row 1 and 2) and click Delete Selected...
Once all the duplicate data have been deleted, Click Commit Data and it will integrate the price ranges into the database.
Duplicate price ranges in the CSV file
A common way of doing this is having two matching price ranges in the CSV file. This example is going to continue from the same file that was used in Duplicate price ranges between the existing database and CSV file. In Microsoft Excel, Open TUTDAY.csv.
These price ranges have already been committed to the ResPax database so they must be changed. Change the dteFrom and dteTo columns.
Select the top two rows and copy them to new cell rows under the existing prices...
As you can see the Adult and Child rows are identical to each other (TourCode, Type Desc, Basis, SubBasis, Currency). This will cause an error when re-integrating back into ResPax.
Save the CSV file, exit and open ResPax.
In Maintenance menu -> Copy Price Tool, Open TUTDAY.csv by clicking Open Existing File. In the Copy Price Matrices screen, all the price ranges are visible...
When clicking Check Data, no errors show up as, according to ResPax the data is unique to the existing database.
But when clicking Commit Data, this error shows up...
This is easily adjustable. Before commiting this data, delete the duplicate rows. To do this, Select the rows (in this example Row 5 and 6) and click Delete Selected...
Once all the duplicate data have been deleted, Click Commit Data and it will integrate the price ranges into the database.
Incorrectly named Basis, SubBasis and Tourcode
A common way of doing this is incorrectly naming a Basis or SubBasis with something as small as a space or fullstop. This example is going to use the same file that was used in Duplicate price ranges in the CSV file. In Microsoft Excel, Open TUTDAY.csv.
Change the first strTypeDesc row that has Infant to Infants and change the Basis from Standard to Standard..
Now change the first FOC type pax's SubBasis from Standard to Standard(The same word but a space has been put in front of it.)
As you can see the CSV file has very minor clerical mistakes but this will cause an error when re-integrating back into ResPax.
Save the CSV file, exit and open ResPax.
In Maintenance menu -> Copy Price Tool, Open TUTDAY.csv by clicking Open Existing File. In the Copy Price Matrices screen,
The error appears before any checks are made as it does not match with already existing Basis, SubBasis and Pax Type. These can be corrected by pressing F2 in the cell with the error or they can be corrected through the CSV file directly.
Extra empty lines in the CSV file
A common way of this error occurring is having an unfilled row between prices. This example is going to continue from the same file that was used in Duplicate price ranges in the CSV file. In Microsoft Excel, Open TUTDAY.csv.
Cut Row 2 and move the price range down to a new cell below the existing prices...
As you can see the CSV has unfilled rows before the end of the matrix. This will cause an error when re-integrating back into ResPax
Unexpected data in the CSV file
A common way of this error occurring is entering unfamiliar lettering into columns that start with ysn (eg.ysnMon). This example is going to continue from the same file that was used in Duplicate price ranges in the CSV file. In Microsoft Excel, Open TUTDAY.csv.
Scroll to the right to the ysnMon column and adjust one of the row to say instead of -1 to 1...
In ResPax, ysnMon - ysnSun are check boxes that allows the user to choose which days this particular range would be enabled. A check box's tick/untick interface translates into -1 or 1(Ticked) or 0 (Unticked). When a foreign alphanumeral has been entered into this field and then re-integrated back into ResPax, an error occurs and the check box automatically reverts back to unticked.
Non-existant Basis, SubBasis, Tourcode, Agent Code, Agent Type and Agent Category
This error is very similar to Incorrectly named Basis, SubBasis and Tourcode yet it has more detailed differences. This section is specialised towards users intentionally using Copy Price Tool as a form of creating new Bases, SubBases, Tourcodes, Agent Codes, Agent Types and Agent Categories. This example is going to continue from the same file that was used in Duplicate price ranges in the CSV file. In Microsoft Excel, Open TUTDAY.csv.
Using this existing price matrix, add a few new price ranges to the list. Select the rows and copy them to new cell rows below the existing prices...
Change the strBasis from Standard to Deluxe...
As you can see the CSV file now has a new Basis (Deluxe) that has not been previously created in ResPax. When this is re-integrated back into the database, it will cause an error.
Save the CSV file, exit and open ResPax.
In Maintenance menu -> Copy Price Tool, Open TUTDAY.csv by clicking Open Existing File. In the Copy Price Matrices screen,
The error appears before any checks are made as it does not match with already existing Bases, SubBases. Before integrating the new Basis into ResPax, it will need to be created through Maintenance -> Edit/Add Tours -> Tour Maintenance -> Price Matrix -> Edit Basis. The tour would need to be saved and ResPax restarted, also.
Non-existant Currency Codes
A common way of this error occurring is misspelling or incorrectly inputting currency codes into the CSV file. This example is going to continue from the same file that was used in Duplicate price ranges in the CSV file. In Microsoft Excel, Open TUTDAY.csv.
Adjust the first two rows of currency (Column strCurr_Bkg) from AUD to NZD (New Zealand Dollars). Adjust the last two rows of currency from AUD to CDA...
As you can see, the CSV now has two different currency codes. One that is correct (NZD - New Zealand Dollars) and one that is incorrect (CDA - Error). When re-integrated back into ResPax, the invalid currency will cause an error.