3 Replies Latest reply on Jul 30, 2012 4:28 PM by philmodjunk

    Table Occurrence depending upon field contents



      Table Occurrence depending upon field contents



      Excuse my newbie-ness, but sometimes this stuff just gets more confusing the more you start to understand it.

      Here's my issue:

      I've got 3 tables:  Classes, ClassOrders, Orders

      Classes::Class# = ClassOrders::Class#.

      ClassOrders::Order# = Orders::Order#

      (The ClassOrders table is an interim table that lets me have multiple classes per order.)  

      On an order, I will need to cancel a class, but have it show up on a "Cancelled Classes" portal on the Orders layout.  So, I want to delineate between a "classOrder" that's valid, and one that's cancelled.  I'm not sure of the best way to do this.  Any help is greatly appreciated.


        • 1. Re: Table Occurrence depending upon field contents

          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.

          • 2. Re: Table Occurrence depending upon field contents

            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:

              1. In Classes, I want to "count" the number of ClassOrders::ClassStatus that do not equal "Cancelled"
              2. In Orders, I want to show (in separate portals) Non-Cancelled Classes, and Cancelled Classes
              3. In Orders, I want to "count" the number of Non-Cancelled Classes, and get a Total of the prices of those classes
            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.
            • 3. Re: Table Occurrence depending upon field contents

              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.

              Cancelled orders:

              Not IsEmpty ( ClassOrders::cancelled )

              Noncancelled Orders:

              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.