6 Replies Latest reply on May 15, 2014 10:54 AM by alext

    Auto-Enter calculation woes



      Auto-Enter calculation woes


           I am bewildered...

           I have 2 associated tables in a "one to many" relationship; CONTACTS and DOCUMENTS.  I am trying to auto-enter a field in CONTACTS called "ConsentForm", with a "Yes" if any related document titles contain the string "consent".

           To do this I've used the following calculation in the auto-enter calculation area for field ConsentForm:

           Case ( PatternCount( List(cntct_DOCS::Title)  ; "consent") ; "Yes" ; "")

           For whatever reason the field ConsentForm refuses to have a "Yes" auto-entered.  I've sanity checked my calculation by pasting it as the tooltip for ConsentForm and alas I get a "Yes", however nothing I seem to do will populate the actual field.  I made sure the layout is referencing the same Occurrence as the auto-calculation. I even tried unchecking "Do not replace existing values" with no luck.  What am I missing?


           client machine: Filemaker Pro Advanced 13.0v3 (Mac)

           accessing tables of a database on Filemaker Pro Server 13 (Windows)


        • 1. Re: Auto-Enter calculation woes

               Is Title a field of type text or number?

               An auto-enter calculation does not appear to be the right choice here. Changes to data in the related table--such as adding or removing a related record in cntct_DOCS will not trigger an automatic update of an auto-entered calculation.

               It looks from here like this should be changed to a field of type calculation.

          • 2. Re: Auto-Enter calculation woes

                 I should have included this, it's a text field.  I double check that too.

            • 3. Re: Auto-Enter calculation woes

                   Which doesn't change the fact that the auto-entered calculation field will fail to correctly update when changes are made in the related table. From what I see here, you need this to be a calculation field instead.

              • 4. Re: Auto-Enter calculation woes

                     Ahh, I apologize, for some reason I only saw the first line of your response yesterday...

                     You're right, Ive done some tests and apparently auto-enter calculations will work across related records if the auto-update field resides on the child table (as in a field in a portal calling data from a related parent field), however auto update will not refresh on the parent record calling data from the child.

                     This seems very odd when a calculation will work this way- and counter intuitive auto-enter should allow the entry of impossible calculations.  I can use  a calculation field instead however then users can't manually enter the information, so the resulting solution will be what feels convoluted in my eyes. Additionally calculation fields don't behave as bidirectional key fields and an auto-enter field would work well in this instance, so this refresh behavior seems very limiting.


                • 5. Re: Auto-Enter calculation woes

                            Additionally calculation fields don't behave as bidirectional key fields

                       That's not actually the case in general, though it is the specific case here. Unstored calculation fields cannot be indexed and thus cannot be used on the "many" side of a relationship and thus can't be "bi-directional". But stored calculation fields can be indexed and work just fine as match fields. But as in your case, calculation fields that refer to fields from related tables cannot be anything but an unstored calculation (The unstored setting is what enables them to update correctly).


                            then users can't manually enter the information,

                       That's a new detail not described as needed in your original post. I can't quite see why you'd want such a "manual override" for the situation described here, but such is possible and the end result can be transparent to the user even though it can be a bit tricky to set up as the developer.

                       And there is a third alternative:

                       Use scripts--most driven by script triggers to update a standard text field in the parent record whenever a change is made in the related table that may require modifying the value in this field in one or more parent records. This can be tricky to do without leaving "loopholes" that result in such changes being missed--you have to make sure that adding records, editing records and deleting records are all correctly handled in every possible user interaction. But it can be done and sometimes is the best option in this type of situation.


                  • 6. Re: Auto-Enter calculation woes

                         Thank you for your help with this.  I would love if Filemaker could work their magic and make the beauty of auto-entry calculations work with related tables as well as they work within the same table.  My 2¢.