Selecting the record I want

[kato87]

I'm attempting to create a calculated field that displays the last date a customer received maintenance. This is the calculation I'm using:

 

Case ( GetNthRecord ( Agreements::PM Type ; Agreements::Agreement count ) = "GPM" or "GPMA-FULL" or "GPMA-HEAT" ; GetAsDate ( GetNthRecord ( Agreements::six mo date ; Agreements::Agreement count ) ) ; GetNthRecord ( Agreements::PM Type ; Agreements::Agreement count ) = "SPM" or "BPM" or "STU" or "BTU" ; GetAsDate (GetNthRecord ( Agreements::Purchase Date ; Agreements::Agreement count )  ) ; "NO PM" )

 

I thought that this would work, grabbing the last created record, but I am realizing that some of these agreements were inputted out of order, therefore causing my calculation to work incorrectly. What I'm hoping for is a way to sort records by date and grab the most recent one. Any help would be greatly appreciated.

[eos]

Use a relationship that is sorted by agreement type – in a way that Maintenance records come first (e.g. by using Value List) – then by date descending.

 

Look at the first Agreement record via that relationship; if it's a Maintenance type, that's your date; if it isn't, there are no Maintenance agreements for that customer.

 

Alternatively, you could use ExecuteSQL, like 

ExecuteSQL ( "
  SELECT MAX ( theDate ) 
  FROM Agreements 
  WHERE 
    customerID_fk = ? AND 
    agreementType IN ('GPM','GPMA-FULL','GPMA-HEAT') 
  " ; "" ; "" ; Customer::customerID_pk 
)

Be aware that a date returned by SQL needs to be parsed into a date in FM format.

 

As to your calculation: though syntactically valid, e.g. this GetNthRecord ( Agreements::PM Type ; Agreements::Agreement count ) = "SPM" or "BPM" or "STU" or "BTU" doesn't do what you probably expect; and there is no need to use GetAsDate ( aDateField ) …

 

… and in general, the calculation is just plain confusing: you should use proper formatting to make it better understandable – which would also allow you to see faster if it makes sense, or not, as it were.

[kato87]

Ok, That makes sense because they're related to Job Codes, which are sorted by creation date.

I know nothing about SQL are there any good primers you could recommend?

[eos]

As for SQL proper … well, just google …:-)

 

This is a primer for using ExecuteSQL within FileMaker:

 

http://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

 

Not sure if there is an updated version for FM13, but AFAIK all that has changed is the addition of OFFSET and FETCH.