If these are fields of type summary defined in the same table, what, other than the names are different between the way the two fields are defined? Normally, the same summary field is used to provide both totals. The difference being whether you put a copy of this field inside a sub summary layout part sorted by category or in another part of the layout.
But when used in a calculation, you get the "grand total" result, not the "sub total" result shown in the sub summary layout part.
If that's what you have here, then you are subtracting the same value from itself and will get zero everytime.
To get the subtotal as shown in the sub summary layout part, use the getSummary function (which you can look up in help) and you don't need two summary fields, just one.
I'm not using these summary fields in a summary report. I start by importing bank records and categorizing each transaction...most done with a Case statement on import. Before running reports I want to verify that all transaction have a category. So I set up the two total fields and if I look at the data I see 374 TotalRecordCount and if I have one uncategorized transaction the RecordWithCategory shows 373. I want to subtract those two and if the amount is greater than 0 I want to bring up a custom dialog and give the operator the option of proceeding on to the report or exiting and then go to a layout where they can enter a category. I even tried compare the two count fields to see if they are equal but that didn't work either. So I guess I'm really looking for a way to see if the two numbers are the same or not.
OK I just tried a different approach without success. I tried to set two variables using the Count Function. One variable was set using Value:Count (Bank Transactions::Date) and the other using Value:Count (Bank Transactions::Category) All records have a date and all but one have a Category. So the two Counts should be 374 and 373 respectively. I tried to bring up a Custom Dialog when the two variables were not equal but it did not work. I'm sure I missing some little detail but can't figure out what it is.
Please describe the design of your tables and records in more detail. I can't tell from here what your original design was let alone the new version.
I referred to the summary report because that seemed to fit your description of your original attempt at solving this issue. But what I described in my last post is how summary fields evaluate in a calculation. They will evaluate like that whether you have a summary report layout or not.
So I set up the two total fields and if I look at the data I see 374 TotalRecordCount and if I have one uncategorized transaction the RecordWithCategory shows 373.
And HOW did you set up those fields? Are they fields of type Summary? Or calculation fields? When you examine the values of the two fields on a layout, how is that layout designed.
From what you have described thus far, the two fields should always have the same value unless you sort records and use a sub summary layout part. Since this is not the case, there is some difference in either your layout design or the definition of these two fields. What is that difference?
The Count function will not return a useful count unless you define a relationship that matches only to the records you want to count and then count records in that context. This is also generally true for summary fields, but there are a few details different between Aggregate functions like Count and summary fields.
Thanks Phil...as always you are shedding some light on this for me.
Tables are very simple at this point of design. I have three tables
"Main" table that does not have any records that I use as an entry point to the system. Main Layout has several buttons that run scripts to produce reports, import records, etc.
"Bank Transactions" table with fields of Date, Description, AmountCredit, AmountDebit, Fees are the basic import fields other fields include -fk_Category and the summary fields which are type Summary using the Count Option...one counts the date field the other counts the _fk_Category field
Category Table has two fields _pk_Category and Category
Relationship Category::_pk_Category -----------< Bank Transactions::_fk_Category
Layout is based on Bank Transactions.
In explaining this I realized that the Script being run from the Main table needs to first open the layout that is based on Bank Transactions. I was trying to compare the two summary fields before opening the right layout. duh
So when I changed my script to
Go to Layout [YTD Expense Summary" (Bank Transactions)]
Show All Records
If (Bank Transactions::TotalRecordCount > Bank Transactions::RecordsWithCategory
it seems to return the results I expect. I'm not sure the way I'm doing this is the brightest or best way.
My goal is to be able to tell if there are any records in the Bank Transactions table that do not have a _fk_Category...this was the only way I could think of to get that answer. But from your comments it sounds like I might get into trouble down the road if I try to use these fields on a subset of records like in the second year when I try to only pull current year records. Is there a better way to get what I need?
one counts the date field the other counts the _fk_Category field
That's the key detail that I was missing here.
If you never allow records to persist with an empty _fk_category field, this should be a pretty safe way to do this. Even if you do allow records to not be assigned categories, it's not a huge issue as each time you import records, the newly imported records forms your current found set and then these two calculation fields will evaluate from that context and accurately tell you how many fields do not have a value in the _fk_category field.
There are other possible approaches, but I don't know that they are really better or worse than what you have. You could set up a calculation field that returns 1 when _fk_Category is empty and then you could use a relationship to match to all records in your table that do not have an assigned category, but I'm not convinced that this is any better than what you currently have. But it would allow you to display all records with a missing category in a portal where you could then more easily update this field if your next step after import is to manually assign categories.
Thanks Phil...I feel much more confident in the solution after reading your response. I struggle with these things for a long time until I realize I can't get it working without a little help. I appreciate your feedback and help more than words can explain.