1 2 Previous Next 20 Replies Latest reply on Mar 3, 2012 2:36 AM by keith

    Calc Match Field Problem

    keith

      Title

      Calc Match Field Problem

      Post

      FM11 Mac

      3 Related Tables - Contacts, Orders, Bank Statements.

      Order record created. Order received and date received entered. Order No entered against relevant item on Bank Staement and Paid Amount jumps into field on Order record. Order Status Calc field returns Open, Received or Paid accordingly.

      I then have Order Portals on Contacts and on Orders (self Join) so that I can see Orders listed depending on their Status, using Status as the Match Field.

      All this works fine except that I am unable to use the Status Calc field as a Match as it is not indexed. 

      When I try to index it I get: The calculation “Status Calc” cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage.

      I currently use a plain Status field to copy the Status Calc but it doesn't always work and I have a 'update script button to copy it.

      Is there a way of making my Status field work automatically as the Match field?

      Thanks

      Keith

        • 1. Re: Calc Match Field Problem
          philmodjunk

          Not as long as its calculation refers to a field with global storage or a field from a related table.

          If you post the definition of your calculation field, we may be able to suggest an alternate approach. One such possibility is to use a portal filter instead of trying to match to your unstored field. You'd use the cartesian join operator (x) instead of = in the relationship and then the expression you use in your calculation field can be recreated as the portal's filter expression. (This requires FileMaker 11.)

          • 2. Re: Calc Match Field Problem
            keith

            Thanks. I would need to read about Portal Filter - can you point me to any info? I have FM11.

            In the meantime, is the following what you need? 

             

            Status Calc field:

            Case (

            Done Date ≤ "" ; "Action" ; Done Date > "" and Payment OK Calc <> "OK" ; "Payment" ; Done Date > "" and Payment OK Calc = "OK"; "Complete"

            )

             

            Payment OK Calc feild:

            Case (

            Done Date > "" and Debit 1 = Statements Diary Db1::Debit and Debit 2 = Statements Diary Db2::Debit and Debit 3 = Statements Diary Db3::Debit and Credit 1 = Statements Diary Cr1::Credit and Credit 2 = Statements Diary Cr2::Credit and Credit 3 = Statements Diary Cr3::Credit; "OK" ;

            )

             

            Notes:

            1 The Payment OK Calc is another Calc field which just looks at payment. It could be incorprated into the main Status Calc field.

            2 For simplicity, I used different terminology in my initial enquiry above, i.e. Action = Open, Diary = Orders, Done Date = Order Received Date, Complete = Paid (and received), Cr = Credit, Db = Debit (there are 3 debit and credit fields to cover those occasions when an order is paid or refunded with more than one transaction.

             

            Thanks again

            Keith

            • 3. Re: Calc Match Field Problem
              philmodjunk

              Note: Payment OK Calc field is the reason status is unstored. Since refers to fields in a related table, it can only be unstored and any calculation fields, such as status that refer to it are also then unstored.

              For your contacts portal, define this relationship:

              Contacts::SelectedStatus X Orders::anyField AND
              Contacts::ContactID = Orders::ContactID

              SelectedStatus would be a field formatted with a value list of the different "status" values that might be returned by the status calculation. It needs to be part of this X based relationship so that changes made to that field will automatically update the portal's filter to show the correct set of order records linked to the current contact. In portal setup for this portal, you would click the portal filter check box and enter this filter expression, but using your field and table occurrence names in place of mine:

              contacts::SelectedStatus = Orders::Status

              FileMaker evaluates the expression you enter for each related record and displays only those for which the expression evaluates as True.

              Portal Filtering is a new feature introduced in FileMaker 11 so this is not an option if you have an older version of FileMaker. I'd normally point you to the FileMaker help system to learn more about portal filters, but don't see much there and one of the articles, while not incorrect, gives a less than optimum example of a portal filter expression.

              • 4. Re: Calc Match Field Problem
                keith

                That works great. I did also read about Portal Filtering. Thank you.

                It raises questions for me:

                1   Where possible, should I generally default to X Relationships and Portal filtering rather than additional TOs? It seems simpler but is there a downside to Filtering?

                2   This thread started due to the problems created by the need to reconcile Bank Statement Db / Cr with the Order Db / Cr.  Sometimes an order is paid or refunded with more than one item and the problem then is that there is only one Order Record but there could be several Statement Records for the same Order.  I currently have 3 Db and 3 Cr Order Fields plus 3 Db and 3 Cr Statement TOs to allow for those times. This works but I now wonder if this is a correct method or is there a better one? Portal Filtering does not occur in this situation.

                Any comments would be appreciated.

                Keith

                • 5. Re: Calc Match Field Problem
                  philmodjunk

                  1) There are two major advantages to filtered portals:

                       a) It simplifies your relationship graph in Manage | Database | relationships
                       b) Some portal filters will work where a relationship will not. This is one example there are also cases where the caclution used in the filter itself can use more sophsticated methods for matching values than is possible in  relationship.

                  2) There are disadvantages.

                       a) calcualtion fields with expressions that refer to related tables will ignore the portal filter as they evaluate at a lower level not dependent on any layout based features such as a portal filter. Sometimes we can pull a summary field into a filtered portal to get the total/average/max/ min/ etc that we want, but not always and sometimes the screen refreshes get really clunky.

                      b) on multi-user systems where the file is hosted from a server, filters evaluate on the client side rather than the server. This can produce siginificant delays in getting your layout to update in some circumstances--especially with large data sets.

                  • 6. Re: Calc Match Field Problem
                    keith

                    Thanks for explaining Tables / Portal Filters pros and cons.

                    Do you have any comments on my other query?

                    2   This thread started due to the problems created by the need to reconcile Bank Statement Db / Cr with the Order Db / Cr.  Sometimes an order is paid or refunded with more than one item and the problem then is that there is only one Order Record but there could be several Statement Records for the same Order.  I currently have 3 Db and 3 Cr Order Fields plus 3 Db and 3 Cr Statement TOs to allow for those times. This works but I now wonder if this is a correct method or is there a better one? Portal Filtering does not occur in this situation.

                    Thanks for all your valuable help.

                    Keith

                    • 7. Re: Calc Match Field Problem
                      philmodjunk

                      there is only one Order Record but there could be several Statement Records for the same Order.

                      Does the reverse ever happen? Could one statement record document payment for more than one order?

                      What you describe so far is a one to many relationship, but if a payment can pay off multiple orders, you have a many to many relationship.

                      With only one order for any given statement but possibly many statements for one order, you could set up this relationship:

                      Orders::__pk_OrderID = Statements::_fk_OrderID   (use your field names in place of mine.)

                      Then Sum ( Statements::AmountPaid ) will return the total paid for all statements linked to a given order. You can also define a summary field in Statements that computes the total of AmountPaid and put it on your orders layout to see the same total.

                      If you have a many to many relationship, you'll need a join table between the two tables so that it can both link orders to statements and also document what portion of the statement applies to a given order.

                      • 8. Re: Calc Match Field Problem
                        keith

                        Haven't tried your suggestion yet but thought I'd just confirm that a Statement entry always relates to one Order, i.e. it never covers different orders - thankfully!

                        I will let you know when I've had a play.

                        Thanks

                        Keith

                        • 9. Re: Calc Match Field Problem
                          keith

                          Re: 'Then Sum ( Statements::AmountPaid ) will return the total paid for all statements linked to a given order. You can also define a summary field in Statements that computes the total of AmountPaid and put it on your orders layout to see the same total.'

                          I can follow idea but not quite sure what/how many fields I add.

                          For example, if I have 3 Statement records (2 Db and 1 Cr) relating to 3 items on an Order, what fields do I add and how do I make ensure that 2 Db and 1 Cr are returned as the total Db and Cr for the same Order?

                          Thanks

                          Keith

                          • 10. Re: Calc Match Field Problem
                            philmodjunk

                            It depends on how you have your statement record defined and even then there's more than one way to set it up.

                            When I work with debit and credit info, I prefer to define the fields like this:

                            Debit   (number)
                            Credit  (number)
                            cBal      (calculation: Debit - Credit)
                            sBalance (Summary: Total of cBal)

                            This makes it easy to set up "ledger style" listing of your account records along with sBalance to compute a total for each account. A version of sBalance defined as a running total is often useful to get a running total for such a layout, but is not what we need here.

                            To see the total of Debit - Credit on your order layout, you would simply add sBalance to the layout. If you need the signs reversed so that debits reduce the total and credits increase it, swap the two fields in the cBal field.

                            A calculation field defined as Sum ( Account::cBal ) would produce the same total, but since I'd very likely need sBalance for other reports based on the Account table, I'd just use the summary field here.

                            • 11. Re: Calc Match Field Problem
                              keith

                              Thanks for that. Seems to be working great.

                              I just have a small problem. The sBalance on the Orders Portal does not update, i.e. field is empty but figure jumps in if I merely click in field or figures jump in for all records if I click off the Portal (on the Layout). Have I done something wrong?

                              Thanks again

                              Keith

                              • 12. Re: Calc Match Field Problem
                                philmodjunk

                                Didn't realize you would be using a portal here.

                                It sounds like you are editing data in the portal. When you open the portal record for editing by clicking in a field, summary fields do go blank. Sometimes a script trigger can be used to commit the record and get the summary field to update when you finish editing the field, but the results often still seem a bit "clunky". If you aren't pleased with the results, replace the summary field with the calculation field using Sum that I described earlier. It will not show this issue like happens with a summary field.

                                • 13. Re: Calc Match Field Problem
                                  keith

                                  To ensure I'm not confusing you and as a reminder:

                                  This is my 'Reconciliation Portal' which is an Orders Layout with an Orders Portal plus the Statement Summary field on it. I also have a Statements Portal at the side. I can then see the new imported Statement Records and all the Orders. I drag the Order ID to the relevant Statement record and the Order Status changes accordingly. 

                                  Apart from that I don't really edit anything.

                                  i have used this system for some time but it didn't update instantly (hence your Portal Filter) which is working great and you have now got rid of the 6 Db Cr Tables but we are just left with this small problem.

                                  Note that the Summary field on the Orders Portal is blank when I go to the layout but figure jumps in when I click in the Summary field.

                                  Please advise if I should just carry on with your suggestions above.

                                  Thanks

                                  Keith

                                  • 14. Re: Calc Match Field Problem
                                    philmodjunk

                                    What happens if you drag and drop, then click a blank area of the layout? (that click will commit the record.)

                                    1 2 Previous Next