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.

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.

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?

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.