I assume you have a field that is changed when a record is archived.
I'll call this field Status and assume it's contents is changed to Archived when that record is archived.
Create a calculation field called IsArchived:
if ( Status = "Archived" ; 1 )
Then count IsArchived, this will be the number of Archived items which you can take away from the FoundCount.
You can use a summary field to count or sum the Calculation field that Phil_1986 suggests, and then subtract the value of that field from your foundcount, but it sounds like this may be what you are already doing.
Do you need this count to be over all records in your table or just the records currently in your found set?
ExecuteSQL for users of FileMaker 12 or newer could also return a count of non archived records, but this, unless you got really creative with how users perform finds, would be a count based on all records in your table instead of all records in your found set.
O.K. Thanks Phil,
I don't have a problem how to create calculation fields, but I was hoping there was a direct function or expression for this one. (I am working with the Dutch version and I can't always find the appropriate translations).
You helped me with this one, so now I know for sure I have to create calculated fields. Just a little more work.
Thanks for your effort and time,
Now I have to create calculated fields, It is a good idea to add an extra summary field to show the number found directly.
What I am doing, I show the numbers of records found in "perform find" to the user. That's no problem.
But I also show them the total number of records. But in my case the total number of records, is not the real number of records, but the number of records that are "not in archive"
Thans for your time and effort,
If you want the total number of records in the table that do not have the text "archived" in a field named ArchiveStatus, ExecuteSQL can do this much more simply:
"SELECT Count ( * ) FROM \"YourTable\" WHERE \"YourTable\".ArchiveStatus <> 'archived' "
; "" ; "" )
You are right, I want all the records in the table that have (Field) "Archive = No". (For the user the real number of total records).
The Execute SQL seams easier and less work. But how to use the execute SQL? I know the Execute SQL is a script step - I used it before - but how keep the field with the result of the script step constantly updated. Every time a record is added, the field has to be updated. Create the result in a record field?
Thanks in advance,
This is not the Execute SQL script step, it's the ExecuteSQL function.
What I've posted would be put in side the Specify Calculation dialog for a unstored calculation field.
This is clear Phil,
I misunderstood that, the SQL function works fine, and easier then een calculation and summary field, and less work.
Thanks again for helping me out.
Should use the SQL function more, works great Phil.