Add a "cancelled" field to ClassOrders. Format it with a single value check box that enters "Cancelled" or the number 1 into this field when you click the check box.
Find Criteria, relationships, calculations, sort orders, portal filters can all refer to this field to select for or filter out cancelled orders while retaining them in the database.
Okay, I'm finding that as I make one change, it affects 3 different things, so it gets real confusing.
Here are the problems I'm experiencing with this:
In fact, there are so many related tables that I'm starting to understand nothing. Would it help to actually see the database? I'm thinking that using Table Occurrences would be the better route, but I'm not sure what I'm doing there.Thanks a lot, I really appreciate it.
- In Classes, I want to "count" the number of ClassOrders::ClassStatus that do not equal "Cancelled"
- In Orders, I want to show (in separate portals) Non-Cancelled Classes, and Cancelled Classes
- In Orders, I want to "count" the number of Non-Cancelled Classes, and get a Total of the prices of those classes
What you have appears to be the correct setup, but you've only described 3 tables with one table occurrence each.
1) Remember my suggestion that the cancelled check box enter a "1"? From claseses, Count ( ClassOrders::Class# ) - Sum ( ClassOrders::cancelled ) will return the total non cancelled orders for the current class.
2) create two portals to classOrders, but set up different portal filters (must have filemaker 11 or later) with filter expressions that filter for one or the other.
Not IsEmpty ( ClassOrders::cancelled )
IsEmpty ( ClassOrders::cancelled )
If you have a version older than FileMaker 11, you have to set up calculation fields and relationships to additional table occurrences, but you can still get your two portals.
3) Counting would be the same as 1) above. To get an order total, update your calculation field in classOrders that computes the cost for that record to put it in an if function that does not return a value if that record is cancelled. Something like:
If ( IsEmpty ( cancelled ) ; Qty * Cost )
Now your order totals will ignore the cancelled records.
You might want to try setting up summary reports based on the ClassOrders table. You can sort by Cancelled or use a find to omit cancelled classOrder records, then sort to group your records and compute the desired totals and sub totals. There are a number of different ways you can group the recors for such a report and you can limit the found set used for the report to just the records for one class or one order if desired or you can include many different classes/orders over a specified date range.