I have a portal with a date field and a number field. I am trying to create a field that displays the number from the record with the most recent date where the number field is not empty. Ideas? thanks.
One way to do this would be to create another relationship to the child table, where you add a constant calculated field (probably global) to your parent table. That calculated field would be a very small number, like 0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001. Then, you add another criterion to your new relationship:
ParentTable::VerySmallNumber < ChildTable::Number
This will cause all records equal to zero in the child table to be filtered out of the relationship. Then, you can sort the relationship by Date (descending). At that point the first record in the relationship will be the most recent date.
(Note that this will NOT work if negative numbers are expected in your child table number field. In that case, additional conditions will be needed.)
ExecuteSQL() could handle this calculation without adding another relationship. As long as Lukian is using filemaker 12
IE - get the first value from a returned list.
WHERE childkey = ?
AND ( number IS NOT NULL OR number =/= '' )
ORDER BY date DESC
" ; "" ; "" ; parentkey )
; 1 )
I just wish that ExecuteSQL() supported LIMIT. wrapping it in GetValue() all the time is a pain.
Im having trouble with the Returned List method:
Do you mean put the relationship criteria between child and parent (ParentID (FK) = ParentID (PK))?
Do you mean ≠ ?
yes, I couldn't draw out the not equal to sign, forgot the keyboard shortcut. NULL is not always equal to empty or blank, so get in the habit of testing for both in your calculations. I am testing for both by using ( OR ) in the above example.
The question mark acts as a placeholder for a variable that you can pass later. I am passing "parentkey" at the end of the ExecuteSQL() function in the example above. Leave the question mark in place, replace "parentkey" with whatever field the parent key is coming from.
You can pass as many variables (in order of question mark in your SELECT statement) at the end of the executeSQL() function, separated by carriage returns, as you want/need to place.
If I use:
AND ( number IS NOT NULL OR number ≠ '' )
the calculation field does not work (results in ?)
If I use :
AND ( number IS NOT NULL)
The Calculation field works
I see why you are using the ( OR ) but I'm not sure why its not working in the formula.
Side Note: The purpose of the number field is to enter raw data, shouldn't it be either null or not null (never empty or blank?) - therefore do I need to have the ( OR )?
Thanks for your Insight
I've run into trouble before with NOT NULL / NULL, so I always test for multiple versions in my scripts.
If someone clears a value out of the field, it's not necessarily "NULL", it's just empty. If you wanted more insight into this, Beverly Voth is the person who tipped me off to this issue, you might want to track her down to consult with you. When I was running into this issue it was with a fairly large record set, with a ridiculous calculation inside of ExecuteSQL(), this by comparison is simple and you might never need to test for multiple conditions with ( OR )
You might need to replace your not equals sign with either != or <>, I'm not sure if ExecuteSQL respects the ≠ symbol.
Retrieving data ...