Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

Execute Sql with 2 conditions

Discussion created by cmj on Nov 22, 2018
Latest reply on Nov 24, 2018 by beverly

Like • Show 0 Likes0
Comment • 15

I've been trying numerous iterations with no luck for what should be simple.

I want to get a value from a previous record where the fk is the same and the year is one less than the current record.

Note that LoanFC_Year is a text field and the calculation: LoanFC_Year -1 does return the correct value.

The fk is a number and LoanFC_LastPymtDateYearly is a date.

This calculation should return a date.

ExecuteSQL ( "SELECT Sum(LoanFC_LastPymtDateYearly)

FROM Loan_Forecast

WHERE ? = LoanFCfkLoanID

AND LoanFC_Year = ?"

; "" ; "" ; ""; LoanFCfkLoanID; LoanFC_Year -1)

All I get is ?

Thank You

Outcomes

15 Replies

Fred(CH) Nov 22, 2018 8:00 PM
I see 3 error in your SQL; try this :

ExecuteSQL ( "SELECT LoanFC_LastPymtDateYearly
FROM Loan_Forecast
WHERE LoanFCfkLoanID = ?
AND LoanFC_Year = ?"

; "" ; "" ; LoanFCfkLoanID; LoanFC_Year -1)
Like • Show 0 Likes0
Actions
cmj @ Fred(CH) on Nov 22, 2018 8:12 PM
Thanks. I tried this:
ExecuteSQL( "SELECT LoanFC_LastPymtDateYearly
FROM Loan_Forecast
WHERE LoanFCfkLoanID =?
AND LoanFC_Year =?"
;"";"";LoanFCfkLoanID;LoanFC_Year -1)
Still getting a ?
Very confusing!
Like • Show 0 Likes0
Actions
cmj @ Fred(CH) on Nov 22, 2018 8:22 PM
When I set the calculation to return a number it works.
Why would that be when the field I'm getting the value from is a date field (LoanFC_LastPymtDateYearly).
Do I need to specify that I want to get the result as a date?
Like • Show 0 Likes0
Actions
Fred(CH) @ cmj on Nov 22, 2018 8:35 PM
Ah yes : ExecuteSQL returns date in SQL format, like 2018-11-23.
This is why it return ? when the type is a date.
You have to convert it before in FMP Date format.
Like • Show 1 Like1
Actions
Fred(CH) @ Fred(CH) on Nov 22, 2018 8:42 PM
I use a Custom Function for this that can Wrap your ESQL.

Name : ConvertSQLDate
Parameter : SQLDate
Formula :
Let ( [ D = Substitute ( SQLDate ; "-" ; " " )
        ;
        M = MiddleWords ( D ; 2 ; 1 )
        ;
        D = MiddleWords ( D ; 3 ; 1 )
        ;
        A = MiddleWords ( D ; 1 ; 1 )
      ] ;
        If ( M and J and A ; Date ( M ; D ; A ) )
)
3 people found this helpful
Like • Show 0 Likes0
Actions
cmj @ Fred(CH) on Nov 22, 2018 8:44 PM
You have to convert it before in FMP Date format.
Sorry, I'm not clear on what you mean.
Like • Show 0 Likes0
Actions
cmj @ Fred(CH) on Nov 22, 2018 8:44 PM
Thanks! I will give that a try.
Like • Show 0 Likes0
Actions
cmj @ Fred(CH) on Nov 22, 2018 9:24 PM
Thank you so much!
Works perfectly.
Like • Show 2 Likes2
Actions
user19752 @ cmj on Nov 23, 2018 5:03 PM
The result of SQL is formatted as YYYY-MM-DD, then if your date format can't get it with GetAsDate(), it fails (In other words if you can't type in the format into your date field).

Another way is convert it in SQL like
SELECT STRVAL(aDate) ...
Like • Show 0 Likes0
Actions
comment @ user19752 on Nov 23, 2018 8:33 PM
SELECT STRVAL(aDate) ...
That doesn't do anything for me.
Like • Show 0 Likes0
Actions
user19752 @ comment on Nov 23, 2018 9:05 PM
Hmm, sorry I thought someone said both get same, but only this (or some other way) does.
SELECT ''|| aDate ...
(Convert date to text concatenating empty string)
Like • Show 0 Likes0
Actions
user19752 @ user19752 on Nov 23, 2018 10:39 PM
I couldn't find the thread here, but Google could find it easily (and it is in "Idea" place...)
Improve ExecuteSQL Date and Timestamp handling
And it looks SQL function changed the format, after FM14?
Concatenate, STRVAL, and COALESCE re-format dates

I could test it with ScriptMaster sample file, made on foreign format other than YYYY-MM-DD. On FM17 STRVAL() does nothing (return YYYY-MM-DD) but ''|| does (current format).

I'm lucky living in the standard YYYY-MM-DD format.
Like • Show 1 Like1
Actions
user19752 @ user19752 on Nov 24, 2018 12:24 AM
Hmmm, I thought the workaround (''||aField) respect formatting of the file, but not. It might only use "as entered" value for the field, so not worked with function like ''||DATEVAL('2018-11-24') nor ''||DATEVAL(aTimestamp). (return Y-M-D)
But worked on field with calculation ''||(aTimestamp - 86400) (M/D/Y)
OTOH, DATEVAL('11/24/2018') is error, so is this bug of DATEVAL??
Like • Show 0 Likes0
Actions
comment @ user19752 on Nov 24, 2018 12:50 AM
AFAICT,

SELECT ''|| Datefield ...

returns the data "as entered" - which may or may be not the current format used by the file Note also that || is not listed as an operator in the FM SQL Reference document. So this is a dubious hack at best.
1 person found this helpful
Like • Show 0 Likes0
Actions
beverly @ Fred(CH) on Nov 24, 2018 2:59 PM
A quick tip that also works:
valid date for FileMaker Date (entered into a field):
YYYY+MM+DD
So, just use this:
Substitute ( SQLDate ; "-" ; "+" )
Beverly
3 people found this helpful
Like • Show 0 Likes0
Actions

Execute Sql with 2 conditions

Outcomes

Related Content

Recommended Content