Xataface Forums

Hiya,

I have the following table "BillDatVATRates" that looks like this

Code: Select all

+-------------------+-------------+------+-----+---------+----------------+
| Field             | Type        | Null | Key | Default | Extra          |
+-------------------+-------------+------+-----+---------+----------------+
| BillDataVATRateID | int(11)     | NO   | PRI | NULL    | auto_increment |
| BillDataVATRate   | varchar(20) | NO   |     | NULL    |                |
| BillDataVATName   | varchar(6)  | NO   |     | NULL    |                |
+-------------------+-------------+------+-----+---------+----------------+


I have these statements in BillData/valuelists.ini

Code: Select all

[LowBillDataVATRates]
__sql__= "SELECT BillDataVATRate FROM BillDataVATRates ORDER BY BillDataVATRate"

[HighBillDataVATRates]
__sql__= "SELECT BillDataVATRate FROM BillDataVATRates ORDER BY BillDataVATRate"


What I'm after is having a drop down menu say (for example) "17.5%" (BillDataVATName) but still use "0.175" (BillDataVATRate) in the calculation that I'm running. Currently, my drop downs say the same as the "BillDataVATRate". Is there a way I can a function to change what the dropdown says but still maintain the "real" number to be used for the calculations?

NB: The calculations use the field "BillDataVATRate".

Thanks

Paul

Yes. There are a couple of things you can try.

1. If you can think of an SQL expression that will yield the label that you want, then you could just use that in your valuelists.ini file:

Code: Select all

[LowBillDataVATRates]
__sql__= "SELECT BillDataVATRate, CONCAT(BillDataVATRate*100,' (',BillDataVATName),')') FROM BillDataVATRates ORDER BY BillDataVATRate"


2. If SQL doesn't give you enough flexibility, you could define the valuelist in a delegate class:

Code: Select all

function valuelist__LowBillDataVATRates(){
    // Use static variable so that we only load the valuelist once per request
    static $out = -1;
    if ( $out == -1 ){
        $res = mysql_query("SELECT BillDataVATRate,BillDataVATName FROM BillDataVATRates ORDER BY BillDataVATRate", df_db());
        if ( !$res ) throw new Exception(mysql_error(df_db()));
        $out = array();
        while ($row = mysql_fetch_row($res) ){
            $out[$row[0]] = (100*$row[0]).'% ('.$row[1].')';
        }
        @mysql_free_result($res);
    }
    return $out;
}


Hi Steve,

Too cool!

The valuelist.ini worked perfect.

Cheers

Paul