Sage 200 Exchange Rates

"Its 1.48" I said looking at the database, "but its 0.9 according to the application" I was told "and all the others are correct, its just the exchange rate for the Euro that's wrong".

Eventually we worked out that when the base currency is not the Euro you have to enter the exchange rate for the Euro against your base currency. This update the field for the base rate in the currency table but that doesn't help when I want to look up the exchange rate for the Euro.

The solution was to create a trigger that updates the Euro exchange rate when the base rate currency changes.

ALTER TRIGGER [BaseRateUpdate] ON [SYSCurrency] AFTER UPDATE
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

IF (SELECT ThisIsBaseCurrency FROM DELETED) = 1
BEGIN
IF (SELECT ThisIsEuroCurrency FROM DELETED) = 0
BEGIN
UPDATE SYSCurrency SET OneUnitBaseEquals = (
SELECT 1/OneEuroEquals
FROM SYSCurrency
WHERE ThisIsBaseCurrency = 1)
WHERE ThisIsEuroCurrency = 1
END
END
END

This now fires whenever any row is amended, but only updates the Euro currency exchange rate if it is the base currency and is not the Euro currency.

Hopefully this little fix will help anyone who also needs to do exchange rate conversions using the SQL tables.

Using client side Javascript to show the current selected value on a slider

We have recently been asked to create a webpage that has multiple sliders created dynamically using php. Not a problem, but we then asked if we could show the selected value as the slider was moved. Using hints from the following websites we managed to do it using a few extra lines of code:

http://snipplr.com/view/3624/ - This gave us a starting point for showing the floating values
http://javascript.info/tutorial/coordinates - This was needed to fix any issues with the page being scrolled

So to the code, the script below is an overview of how it works.

1:  <head>  
2:  <style>  
3:  #sliderVal {  
4:    display:none;   
5:    position:absolute;   
6:    border-style: solid;   
7:    background-color: white;   
8:    padding: 5px;  
9:    }  
10:  </style>  
11:  <script type="text/javascript" language="JavaScript">  
12:       function ShowContent(item) {  
13:            var dd = document.getElementById('sliderVal');  
14:            var rect = item.getBoundingClientRect();  
15:            var body = document.body  
16:            var docElem = document.documentElement  
17:            var scrollTop = window.pageYOffset || docElem.scrollTop || body.scrollTop  
18:            var scrollLeft = window.pageXOffset || docElem.scrollLeft || body.scrollLeft  
19:            var clientTop = docElem.clientTop || body.clientTop || 0  
20:            var clientLeft = docElem.clientLeft || body.clientLeft || 0  
21:            var top = rect.bottom + scrollTop - clientTop  
22:            var left = rect.left + scrollLeft - clientLeft  
23:            dd.style.left = left + "px";  
24:            dd.style.top = top + "px";  
25:            dd.style.display = "block";  
26:            dd.innerHTML = item.value;  
27:       }  
28:  </script>  
29:  </head>  
30:  <body>  
31:  <div id="sliderVal"> </div>  
32:  <form>  
33:  0<input onchange="javascript:ShowContent(this)" type="range" name="slider1" ID="slider1" min="0" max="9">9<br />  
34:  0<input onchange="javascript:ShowContent(this)" type="range" name="slider1" ID="slider1" min="0" max="9">9<br />  
35:  </form>  
36:  </body>  

Lines 2 to 10 setup the style for the floating value (sliderVal), but lines 11 to 28 is the Javascript that does all the work. The Javascript is set to run when any of the sliders change. Firstly the sliderVal element is grabbed, then lines 14 to 22 calculate the position of the slider on the page. The position of the sliderVal element is then set, and it is shown. Lastly the text of the sliderVal element is set to the value of the slider.

SQL Table Joins (Left, Right, Inner, Outer)

A quick explanation of a join

I am always being asked about SQL joins, so I thought I'd write a quick blog.

SQL Joins are used to combine data from two tables, based on a common field. The returned results (called a result-set) can be used to in the same way as the result-set from a single query.

There are four different SQL Joins you can use:

• Inner: Returns the rows where the fields match in BOTH tables
• Left:  Returns all the rows from the left table and the matched rows from the right table
• Right: Returns all the rows from the right table and the matched rows from the left table
• Outer: Returns all rows from both tables, this is also called a Full Join

Some Examples

I'm going to use the following tables to demonstrate the differences. Note that the last row of each table does not have a matching row in the other table.

Employee Location EmpID EmpName 13 Jason 8 Alex 3 Ram 17 Babu 25 Johnson EmpID EmpLoc 13 San Jose 8 Los Angeles 3 Pune, India 17 Chennai, India 39 Bangalore, India

The following query

SELECT * 

FROM

    Employee Inner Join Location ON Employee.empID = Location.empID

Employee.EmpID	Employee.EmpName	Location.EmpID	Location.EmpLoc
13	Jason	13	San Jose
8	Alex	8	Los Angeles
3	Ram	3	Pune, India
17	Babu	17	Chennai, India

As you can see, the only rows returned are where there is a match in both tables. Changing the query to use the Outer Join returns a different results-set.

SELECT * 
FROM
    Employee Full Join Location ON Employee.empID = Location.empID

Employee.EmpID	Employee.EmpName	Location.EmpID	Location.EmpLoc
13	Jason	13	San Jose
8	Alex	8	Los Angeles
3	Ram	3	Pune, India
17	Babu	17	Chennai, India
25	Johnson	NULL	NULL
NULL	NULL	39	Bangalore, India

This returns two extra rows in the results-set. These have Null values where no match has been found in the other table.

Left Joins and Right Joins are similar to Outer Joins, except that all rows are returned from only one table (the Left or Right determines which one)

SELECT * 
FROM
    Employee Left Join Location ON Employee.empID = Location.empID

Employee.EmpID	Employee.EmpName	Location.EmpID	Location.EmpLoc
13	Jason	13	San Jose
8	Alex	8	Los Angeles
3	Ram	3	Pune, India
17	Babu	17	Chennai, India
25	Johnson	NULL	NULL

SELECT * 
FROM
    Employee Right Join Location ON Employee.empID = Location.empID

Employee.EmpID	Employee.EmpName	Location.EmpID	Location.EmpLoc
13	Jason	13	San Jose
8	Alex	8	Los Angeles
3	Ram	3	Pune, India
17	Babu	17	Chennai, India
NULL	NULL	39	Bangalore, India

The two results-sets above show all of the rows from one table and the matching rows from the other table. Null values are used where no match has been found.

So what determines the left and right table? It is simply the position in regards to the Join. This means you could rewrite the Left Join about like so

SELECT * 
FROM
    Location Right Join Employee ON Location.empID = Employee.empID

and you would get the same results-set

Employee.EmpID	Employee.EmpName	Location.EmpID	Location.EmpLoc
13	Jason	13	San Jose
8	Alex	8	Los Angeles
3	Ram	3	Pune, India
17	Babu	17	Chennai, India
25	Johnson	NULL	NULL