I'm quite new to the ExecuteSQL function in FM and wondering if someone can give me some guidance.
Basically what I'm trying to do is get a Fee (number) from a table (Fees_Other) based on three conditions:
1. Fee_Name = 'Registration' (for this case, but I have many more to do...)
2. Effective From >= Purchase_Date
3. Effective To <= Purchase_Date
So basically I'm trying to get the applicable fee based on name, and effective dates. It works fine if I just have one Fee and only use the first condition, but I get the '?' result when I add the date clauses. Below is the Field Calculation I have at the moment:
ExecuteSQL ( "SELECT Fee FROM Fees_Other WHERE
Fee_Name = 'Registration'
Finance::Purchase_Date ≥ Fees_Other::Effective_From
Finance::Purchase_Date ≤ Fees_Other::Effective To
; "" ; "")