Why is my calculation returning blank?

[Martin_S]

Hi

 

I'm trying to get my head around the logic of a calculation, and have gone through several iterations of what was wanted.

 

Initially, they just wanted a Y or N, Y if the latest date of a field from a table for the current record was within the last 12 months, otherwise a N.

 

Then they said, actually, can we have a corresponding field for the latest date instead of Y, and N if the date is over 12 months ago or there is no date returned.

 

So here is my logic:

Let(

[vDate1 = ExecuteSQL( "SELECT MAX( d_Date_Completed ) FROM ACTIVITY WHERE a_kf_Client = ?"; ""; ""; a__kp_CLIENT );

vDate2 = Date ( Month( Get ( CurrentDate ) ) ; Day(Get ( CurrentDate ) ) ; Year(Get ( CurrentDate ) ) - 1 );

ActivityDesc = ExecuteSQL( "SELECT d_Activity_Description FROM ACTIVITY WHERE a_kf_Client = ? AND d_Date_Completed = ? FETCH FIRST ROW ONLY"; ""; ""; a__kp_CLIENT ; vDate1 )];

If ( vDate1  ≥  vDate2 ; ActivityDesc ; "N" )
)

The initial logic worked fine, i.e. I didn't have the third variable for the Let statement, and was simply "Y" for the if then clause. That returned a Y or N correctly.

 

Many thanks in advance

 

Martin

[comment]

Not sure what exactly your question is. Or why are you using  ExecuteSQL() instead of a relationship. In any case, if your calculation returns blank, then vDate1 ≥ vDate2 and ActivityDesc is empty.

 

 

Note also that "a corresponding field for the latest date" is problematic, because the latest date is not necessarily unique.

Edited June 19, 2014 by comment

[Martin_S]

Thanks.

 

OK So the story here is the application has been mainly written by a third party, and I have been tasked with supporting it as best I can with bug fixes and changes. Anythign I can't do will have to be passed back to the third party, but I've kind of been dumped in it with this, having had no FM training and limited exposure. I'm a C# developer by trade, so have a good working knowledge of databases and SQL as part of that and so tried to integrate the logic I'd have used in my solution.

 

FYI this calculation is behind a field in the CLIENT table.

 

The coded calculation by the third party used wrongly named fields and always returned a ?, so I was asked to look to resolve the ? first, which I did by updating the fields, but as you say, there may be more than one date the same, which was why I used the FETCH FIRST ROW ONLY addition.

 

This is the original (updated) calculation (by the TP), which also returned a blank once I'd updated the field names:

Let(

vDate = Date ( Month( Get ( CurrentDate ) ) ; Day(Get ( CurrentDate ) ) ; Year(Get ( CurrentDate ) ) - 1 );

ExecuteSQL("SELECT 'Action: ' + d_Activity_Description FROM ACTIVITY a JOIN Client c ON c.a__kp_CLIENT = a.a_kf_Client WHERE a.d_Date_Completed >=? AND c.a__kp_CLIENT = ?"; ""; "¶"; vDate; a__kp_CLIENT) 
)

I have the field on a dev layout and it always just shows blank, even if there are only entries with data in both appropriate fields.

 

The business then came back and said actually, whilst I was looking at it, they only wanted any 1 of the possible values returned (again, which was why I ended up using FETCH FIRST ROW ONLY in my query) for the latest date. There is already a relationship between a__kp_CLIENT and a_kf_Client.

 

So how can I get around the problematic issue of non-unique dates? Any suggestions on how I approach this? I do totally appreciate that it's hard without seeing the whole picture...

 

Thanks

 

Martin

[Martin_S]

FYI this works...

Let(

[vDate1 = ExecuteSQL( "SELECT MAX( d_Date_Completed ) FROM ACTIVITY WHERE a_kf_Client = ?"; ""; ""; a__kp_CLIENT );

vDate2 = Date ( Month( Get ( CurrentDate ) ) ; Day(Get ( CurrentDate ) ) ; Year(Get ( CurrentDate ) ) - 1 )];

If ( vDate1 ≥  vDate2 ; "Y" ; "N" )
)

[comment]

TThis is the original (updated) calculation (by the TP), which also returned a blank once I'd updated the field names:

Let(

vDate = Date ( Month( Get ( CurrentDate ) ) ; Day(Get ( CurrentDate ) ) ; Year(Get ( CurrentDate ) ) - 1 );

ExecuteSQL("SELECT 'Action: ' + d_Activity_Description FROM ACTIVITY a JOIN Client c ON c.a__kp_CLIENT = a.a_kf_Client WHERE a.d_Date_Completed >=? AND c.a__kp_CLIENT = ?"; ""; "¶"; vDate; a__kp_CLIENT)

)

I have the field on a dev layout and it always just shows blank, even if there are only entries with data in both appropriate fields.

 

I believe (I have no way of testing this at the moment) that the problem here is that vDate is a Filemaker date, not an SQL date.

 

 

 

 

FYI this works...

Let(

[vDate1 = ExecuteSQL( "SELECT MAX( d_Date_Completed ) FROM ACTIVITY WHERE a_kf_Client = ?"; ""; ""; a__kp_CLIENT );

vDate2 = Date ( Month( Get ( CurrentDate ) ) ; Day(Get ( CurrentDate ) ) ; Year(Get ( CurrentDate ) ) - 1 )];

If ( vDate1 ≥  vDate2 ; "Y" ; "N" )
)

 

Are you sure?

[Martin_S]

Are you sure?

Definitely. I get either Y or N for all records, N when there are no dates (I have no records with dates prior to 1 year ago) but all records that have one or more dates in the activity table return a Y. That's why I'm confused, as the logic test works, it's just that instead of Y I want the corresponding text for the top row in the selection.

 

Thanks

 

Martin

[comment]

I have no records with dates prior to 1 year ago

 

Then you cannot be sure.

[Martin_S]

OK, so have changed it to be minus 1 day rather than 1 year, and I still get Y when all dates are more than 1 day away, which confirms your theory.

 

Is it possible convert one of the dates to the correct format to fix this?

 

Thanks

 

Martin

[comment]

The business then came back and said actually, whilst I was looking at it, they only wanted any 1 of the possible values returned (again, which was why I ended up using FETCH FIRST ROW ONLY in my query) for the latest date.

 

IMHO, the business requirement here is wrong. It is bad user interface having the same field show values from two different domains (date and Boolean). It also makes this field practically unsearchable.

I would suggest showing a portal with the client's latest activities, and either hide it or grey it out when the client has been dormant for over a year. Which in native Filemakerese could be expressed simply as:

 

Max ( ACTIVITY:_Date_Completed ) < Get (CurrentDate) - 365

(assuming a day or two are inconsequential here).

[comment]

Is it possible convert one of the dates to the correct format to fix this?

 

Again untested, but try defining vDate2 as:

vDate2 = Year ( Get (CurrentDate) ) - 1 & SerialIncrement ( "-00" ; Month ( Get (CurrentDate) ) )  & SerialIncrement ( "-00" ; Day ( Get (CurrentDate) ) )

This should return a string of "2013-06-20", which should be comparable with the dates returned by ExecuteSQL().

 

 

I still think using SQL here is an unnecessary complication.

 

[Martin_S]

Again untested, but try defining vDate2 as:

vDate2 = Year ( Get (CurrentDate) ) - 1 & SerialIncrement ( "-00" ; Month ( Get (CurrentDate) ) )  & SerialIncrement ( "-00" ; Day ( Get (CurrentDate) ) )

This should return a string of "2013-06-20", which should be comparable with the dates returned by ExecuteSQL().

 

 

I still think using SQL here is an unnecessary complication.

 

Thanks - this works perfectly with - 1 day as well as -1 year, i.e. I get Y for the records that have a max date before today - 1, but still can't get ActivityDesc to display instead of Y.

The issue here is that this isn't for a layout, but a field that is required for an export, so it needs to be a single text entry that is returned. I think I will just tell them it's a Y or N and live with that.

With the very limited knowledge I have, I wouldn't know how to do this without SQL, though i don't know if the fact it's not a display field changes that?

Thanks again

Martin

[comment]

With the very limited knowledge I have, I wouldn't know how to do this without SQL

 

How about:

Let ( [
allDates = List ( ACTIVITY:_Date_Completed ) ;
lastDate = Max ( ACTIVITY:_Date_Completed ) ;
index = ValueCount ( Left ( allDates ; Position ( ¶ & allDates & ¶ ; ¶ & lastDate & ¶ ; 1 ; 1 ) ) )
] ;
Case (
lastDate < Get (CurrentDate) - 365 ; "N" ;
GetNthRecord ( ACTIVITY:_Activity_Description ; index )
)
)

---

Note also that if activity records are entered in chronological order, and the relationship is not sorted otherwise, then this could be simply:

Case (
Last ( ACTIVITY:_Date_Completed ) < Get (CurrentDate) - 365 ; "N" ;
Last ( ACTIVITY:_Activity_Description )
)

Edited June 20, 2014 by comment

[Martin_S]

Many thanks...though neither work because Activity is an unrelated table . I'm guessing that's why it was done the way it was.

[comment]

Many thanks...though neither work because Activity is an unrelated table . I'm guessing that's why it was done the way it was.

 

Uhm...

 

There is already a relationship between a__kp_CLIENT and a_kf_Client.

 

it seems highly unreasonable that you would not have such relationship in place. But in case it's true, why not simply create it? Or perhaps your existing relationship is using a different table occurrence of the Activity table - in which case you need to use the exact name of the TO as the prefix for the field references.

[Martin_S]

OK, my mistake. On the relationships screen, the link is between Client and Client_Activity, which appears to only hold the identity field. The activity table has no relationships.

But looking at this again, I did just spot a field called d_Date_Activity_Completed_Max (in CLIENT) that uses the following formula:

Max( Client_ACTIVITY__reverse_date:_Date_Completed )

Ad if I then revise my formula to:

Let(

vDate = Year ( Get (CurrentDate) ) - 1 & SerialIncrement ( "-00" ; Month ( Get (CurrentDate) ) )  & SerialIncrement ( "-00" ; Day ( Get (CurrentDate) ) )

If ( IsEmpty ( d_Date_Activity_Completed_Max ) or vDate <  d_Date_Activity_Completed_Max ; "N" ; "Y" )
)

I still get Y/N in the right places, and no need to use ExecuteSQL. It still doesn't give me the description, but am not going to worry about that as they have agreed Y/N is sufficient.

 

Thanks for all your help. My issue here is not understanding the structure enough, but think this is where I push back for some decent training!

 

Cheers

 

Martin

[comment]

Ad if I then revise my formula to:

Let(

vDate = Year ( Get (CurrentDate) ) - 1 & SerialIncrement ( "-00" ; Month ( Get (CurrentDate) ) )  & SerialIncrement ( "-00" ; Day ( Get (CurrentDate) ) )

If ( IsEmpty ( d_Date_Activity_Completed_Max ) or vDate <  d_Date_Activity_Completed_Max ; "N" ; "Y" )
)

 

I don't think this is going to work correctly, because once again you are comparing different date types. Assuming d_Date_Activity_Completed_Max is a calculation field with the result type set to Date, you need to define vDate as a Filemaker date , i.e. as it was in your original calculation.

 

 

the link is between Client and Client_Activity, which appears to only hold the identity field.

 

That's probably just the way it's displaying. You should see what you get as the result of:

Last ( Client_Activity:_Activity_Description )

You have the Advanced version - you can open the Data Viewer (from the Tools menu) and test these expressions very quickly.

[Martin_S]

I don't think this is going to work correctly, because once again you are comparing different date types. Assuming d_Date_Activity_Completed_Max is a calculation field with the result type set to Date, you need to define vDate as a Filemaker date , i.e. as it was in your original calculation.

 

 

 

That's probably just the way it's displaying. You should see what you get as the result of:

Last ( Client_Activity:_Activity_Description )

You have the Advanced version - you can open the Data Viewer (from the Tools menu) and test these expressions very quickly.

It's odd as I swapped the code for the date back to the old code, and it stopped working, it gave me N for everything. Putting it back to the SQL version appeared to then work - bizarre.

 

OK, so I've updated the code to:

Let ( [
allDates = List ( Client_ACTIVITY:_Date_Completed ) ;
lastDate = Max ( Client_ACTIVITY:_Date_Completed ) ;
index = ValueCount ( Left ( allDates ; Position ( ¶ & allDates & ¶ ; ¶ & lastDate & ¶ ; 1 ; 1 ) ) )
] ;
Case (
Last ( Client_ACTIVITY:_Date_Completed ) < Get (CurrentDate) - 365 ; "N" ;
Last ( Client_ACTIVITY:_Activity_Description )
)

And I now get the description when it's Y but nothing for N, so have modified it to:

Last ( Client_ACTIVITY:_Date_Completed ) < Get (CurrentDate) - 365 OR IsEmpty( Last ( Client_ACTIVITY:_Date_Completed ) )

and it works

 

Thanks again

 

Martin

[comment]

Putting it back to the SQL version appeared to then work - bizarre.

 

You have to be very careful when testing such things, because you may get a "correct" result by pure coincidence. For example, "2013-06-20" (Text) will be greater than 20/6/2014 (Date), but not 21/6/2014 - because the comparison is performed in alphabetical order, after converting the dates to text.