I need to be able to either create a calculation field or a custom filter that compares two date fields within a single record, so that I can isolate records where date A is greater than date B.
I've tried to create a calculation field based on the evaluation (which would give me something easy to filter on) but I keep getting "field not found" errors.
I'm sure this is a relatively simple process, but I'm new to FileMaker and don't understand how the scripting works.
A simple Boolean (True/False) calculation would do the job:
DateA > DateB
… with the result set to number would result in 1 for any record where the condition is met, otherwise empty or 0.
CalcField = Case( Table::DateA > Table::DateB;1;0) // data type number
Then search on CalcField = 1
Thank you so much! I didn't realize I had to specify the TABLE as well as the field. That was the problem. StephenWonfor
If both fields are defined in the same table, you don't have to specify the table occurrence name, just the fields' names should be all that you need in a calculation field.
If you are comparing dates from two different records and/or two different tables, then you need the table occurrence name as well in order to specify the relationship used to access the data from a related record..
This does not appear to be correct. No matter what I do, every field instance in a calculation field requires the table name, even when there are no other tables. Otherwise, I get a "field not found" error.
Kevin
It never hurts to include the table name. If you copy a calc formula without table names in it you’ll find, if you use FMP Advanced, that the Data Viewer really likes to have the tablenames in the calculation. Where "‘really likes” means insists.
Stephen
---
"The worst moment for the atheist is when he is really thankful and has nobody to thank." -- Dante Gabriel Rossetti
Something is not right then. I use the fields without table:: names all the time. In fact, if you double click a field name to add it to the calculation and it's from the same table and table occurrence context as the calculation field that you are defining, the field is added without the "Table::" text.
In this example, I defined "calcField" without using "Table::" names. Clicked OK to make it appear in the background window's list of fields, then re-opened the calculation dialog for this screen shot.
Can you post a screenshot of this same calculation dialog from your file where you have this problem?
Not really. I have it working. Apparently, you MUST specify table names in FileMaker Pro Advanced, which is what I'm using. That seems to be the issue.
I am using FileMaker 16 advanced and used it for the above screen shots.
StephenWonfor — I think you've identified the issue. I'm using FileMaker Pro Advanced. I couldn't understand why the simple instructions I found everywhere (just select the field name) weren't working for me. That must be the reason.
Thanks so much!
I agree. And I wish FileMaker would always use the fully qualified field name everywhere. It is being lazy, I think, keeping the table name off. Maybe not lazy, but confusing to us.
I just use it all the time, always typing it in.
It never hurts to include the table name.
Not always. The apparent need for them could indicate a misunderstanding as to what to implement here or an attempt to set this up from the wrong context--which may produce unexpected results after further use or testing takes place.
Using the wrong table name would also cause problems for your solution.
kevinburk wrote:
Apparently, you MUST specify table names in FileMaker Pro Advanced, which is what I'm using.
I cannot allow that statement to go unchallenged, as it is simply not correct. It is all about context, and in FM context is king. Note as follows:
1. If you define a calculation within a specific table and it refers to other fields within that same table, you do not require the table name for the formula to be legitimate. The reason? — FM recognises that the calculation field itself and the fields it refers to are all within the current context. This is the case whether you are using FMAdvanced or FMPro. This point is amply demonstrated in Phil's post showing the screenshots of just this process.
2. If you enter a field name from a different table into a calculation you must include the TOname:: as well. The reason? — FM knows that you are referring to a field that is not part of its current context, so it needs to know the relationship path you want it to follow to find the relevant data. FM is a very smart tool, but it cannot read your mind. It is smart enough to tell you, however, if you try to refer to an unrelated table—it knows its own context and operations very well and will not allow you to construct a formula it knows it cannot interpret.
3. Both the above points are true regardless of whether you enter the field names into the calculation by clicking them from the list or simply typing them. Furthermore, if you enter a field name by clicking it knows whether it needs the TOname:: or not and includes it if needed.
4. The additional tool you have available with FMAdvanced is the Data Viewer. Here you can construct calculations in a context that is outside of any Table or any File. The reason? — Even though you can refer directly to any database currently in focus in FM, you are working outside of any table context within that database (on a scratchpad, as it were), so must include TOnames:: along with field names.
5. Your statement is correct only when you are defining a calculation using Data Viewer, which of course only comes with FMAdvanced. If you are operating within a database table (via Manage –> Database –> Fields) context rules apply as noted in points 1 and 2 above.
Whatever. Nothing you assert works for me. The solution of specifying the table fixed the issue. That’s why it’s marked as SOLVED. Please don’t keep this thread going. I’m tired of getting eMails on it.
CalcField = Case( Table::DateA > Table::DateB;1;0) // data type number
Then search on CalcField = 1