Help Please - Calculated Field Using Lookups to Parameters Table

[ozcpa]

I was hoping that someone here would be kind enough to help me with an AppGini/MySQL issue (see below)?

I have setup two Tables using AppGini. To illustrate my problem, the first table (called “main”) has a number of fields including:

main.f1 (INTEGER; Autoincrement; Primary Index)
main.f2 (VARCHAR)
main.f3 (VARCHAR)
main.f4 (VARCHAR)
main.status (VARCHAR; Calculated Field)

The second table (called “param”), which will only need to hold a SINGLE record, has a number of fields including:

param.f1 (INT; Autoincrement; Primary Index)
param.f2 (VARCHAR)
param.f3 (VARCHAR)
param.f4 (VARCHAR)

What I want to try to achieve can be illustrated in the following CALCULATED FIELD for main.status:

SELECT
(
CASE
WHEN main.f2 < 10 THEN "Red"
WHEN main.f2 >= 10 AND main.f1 < = 20 THEN "Amber"
ELSE "Green"
END)
FROM `%TABLENAME%`
WHERE `%PKFIELD%`='%ID%'

The above calculated field for main.status works perfectly, but I want to be able to select ANY field within "main" table and any comparison value; both by using values from the "param" table. To illustrate, using a calculated field expression for main.status (which I know does NOT work), I want to conceptually perform the following:

SELECT
(
CASE
WHEN [the field in main specified by the value in param.f2] < [the value in param.f3] THEN "Red"
WHEN [the field in main specified by the value in param.f2] >= [the value in param.f3] AND [the field in main specified by the value in param.f2] < = [the value in param.f4] 20 THEN "Amber"
ELSE "Green"
END)
FROM `%TABLENAME%`
WHERE `%PKFIELD%`='%ID%'

In the above conceptual example, and in my required use case, the field in “main” table that I want to test will always be contained in param.f2 … and the two comparison values will always be contained in param.f3 and param.f4.

Is the above problem solvable? I have spent almost a complete day trying various combination of statements etc in MySQL without any success, as I have EXTREMELY limited knowledge of MySQL. I want to try to solve this issue within a CALCULATED AppGini field (not within PHP etc) as the generated AppGini app will then be able to be "driven" by a user without ANY programming knowledge and without me having to regenerated the app every time the fields to be tested change.

Thanks in advance to anyone who may be willing to help.