1 2 Previous Next 15 Replies Latest reply on Feb 21, 2017 11:10 AM by ezeitgeist

    Calculation based on related records existing?

    ezeitgeist

      Is it possible to have a Calculation field in one table that is triggered (1 Boolean) as soon as a related record in a related table is created?

       

      Example: Main Table keeps track of 1st-time meetings. Related table keeps track of follow-up meetings (related by 1st-time meeting record ID). Can I create a Calculated Field (Checkbox) in the Main table that denotes that at least one follow-up has occurred as soon as a follow-up meeting has been logged in the related table?

        • 1. Re: Calculation based on related records existing?
          Philip_Jaffe

          MainTable::Booleanfield

           

          Case ( IsValid ( RelatedTable::ID_fk) ; 1 ; 0 )

          1 of 1 people found this helpful
          • 2. Re: Calculation based on related records existing?
            philmodjunk

            An unstored calculation field could do this.

             

            A layout object could be made visible via Hide Object When expression when at least one related record exists.

             

            A script might the field to a value at the time the record is created--depends on how you created that related record as to how well that might work.

            1 of 1 people found this helpful
            • 3. Re: Calculation based on related records existing?
              ezeitgeist

              Just so I follow:

               

              Main Table:

              Meeting_ID

              Boolean [is a Calculation field where calc is: Case ( IsValid (Follow-Up::FollowUp_ID) ; 1 ; 0 )

               

              Follow-Up Table:

              FollowUp_ID

              Meeting_ID

               

              Related via Meeting_ID. Both Meeting_ID and FollowUp_ID are auto-enter serial number.

               

              Is that right?

              • 4. Re: Calculation based on related records existing?
                Philip_Jaffe

                Not quite.

                 

                If your relationship between the main table and the follow up table is:

                 

                MainTable::Meeting_ID (primary key)    =    FollowUpTable::Meeting_ID (foreign key)  then:

                 

                Your Boolean field calculation would be:

                 

                Case ( IsValid ( FollowUpTable::Meeting_ID ) ; 1 ; 0 )

                 

                 

                The idea is to check for a valid relationship (at least one record) between the Main Table (parent) and the FollowUp Table (child).

                1 of 1 people found this helpful
                • 5. Re: Calculation based on related records existing?
                  philmodjunk

                  That should work but you don't need case. IsValid will return the 1 or 0 value by itself.

                  1 of 1 people found this helpful
                  • 6. Re: Calculation based on related records existing?
                    Philip_Jaffe

                    True dat.  Just habit.  But yup, you don't need the Case

                    1 of 1 people found this helpful
                    • 7. Re: Calculation based on related records existing?
                      smith7180

                      Why IsValid instead of not IsEmpty?  IsValid can return a 0 when a related record exists but there is a field type mistmatch.  According to FMP 15 help:

                       

                      IsValid(Amount) returns 0 if there is only text in the number field Amount.

                       

                      I realize that would seem impossible/unlikely with an ID field, but it just seems like a bad habit to get into especially as you may use this approach later for non-id fields.  I definitely want to know if I'm missing something, as I use not IsEmpty.

                      1 of 1 people found this helpful
                      • 8. Re: Calculation based on related records existing?
                        Philip_Jaffe

                        Not IsEmpty is certainly valid, and I have seen discussions about that point here and elsewhere.  I will try to find it and link to it if I do.

                         

                        IsValid works if you know that you are working with ID fields w/o type mismatches as you point out.  And in this case either would work.

                         

                        I'm not sure I would go so far as to call IsValid a "bad habit" as it is a tried and true method that has been used for a long time by many developers.  You just have to know when it will work and when it won't.

                        1 of 1 people found this helpful
                        • 9. Re: Calculation based on related records existing?
                          smith7180

                          My 'bad habit' remark came off as condescending- sorry about that.  I only meant that if not IsEmpty always yeilds 0 when no related record has a value for the field, but IsValid could in theory yield 0 when a value does exist, I think not IsEmpty should be recommended unless IsValid has some other benefit of which I'm unaware.

                           

                          I quickly jumped on the issue because I had this exact problem a ways back when I was just getting started, and it took a while to troubleshoot.  Once everything was fixed, I was sort of annoyed that the Lynda.com tutorial recommended IsValid instead of not IsEmpty.  An expert would never have this issue of course, but beginners might.

                          1 of 1 people found this helpful
                          • 10. Re: Calculation based on related records existing?
                            Philip_Jaffe

                            No, you are right.  Not IsEmpty is safer.  Someone will please correct me on this but I think the IsValid function pre-dates the IsEmpty function by a version or two.  Both pre-date my first use of Filemaker.  It may be that for a while IsValid was the only choice and old habits die hard (especially when they usually work just fine).

                             

                            I can find a few articles that agree with your position, (and I don't disagree), but I remember reading somewhere an article that laid out the pros and cons of both...but I can't locate it or even remember what it said.

                             

                            It may have to do with the fact that there is a difference between an EMPTY value and a NULL value, and perhaps when interfacing with databases that are more stringent with this distinction Not IsEmpty may return unexpected results.  But don't quote me on that.  My memory is vague on the subject.

                            1 of 1 people found this helpful
                            • 11. Re: Calculation based on related records existing?
                              ezeitgeist

                              Just to make sure I'm still with you ...

                               

                              My Boolean field calculation would be:

                               

                              Not IsEmpty ( FollowUpTable::Meeting_ID ) ; 1 ; 0 )

                               

                              And this will only look at related records, as in, if there is a different Meeting in the Main Table (with it's specific Meeting_ID) and it has a FollowUp, but this other Meeting does not have a FollowUp, this other Meeting will not have the Boolean checked because of the unrelated FollowUp?

                              • 12. Re: Calculation based on related records existing?
                                Philip_Jaffe

                                it would just be:

                                 

                                Not IsEmpty ( FollowUpTable::Meeting_ID )

                                 

                                It will return 1 if not empty (relationship exists) and 0 if empty (no relationship)

                                1 of 1 people found this helpful
                                • 13. Re: Calculation based on related records existing?
                                  smith7180

                                  IsEmpty () is itself a Boolean calculation as Phil pointed out.  It only ever returns a 1 or 0.  The "; 1; 0)" at the end of your example would not have any meaning (in fact it would break the calculation).  It's just: Not IsEmpty ( FollowUpTable::Meeting_ID ).  It's always best to start by reading the function defintion in the FileMaker help.

                                   

                                  https://www.filemaker.com/help/15/fmp/en/index.html#page/FMP_Help/isempty.html

                                   

                                  "Not," of course, turns a 1 into a 0 and vice versa.  Not IsEmpty ( Table::Field ) returns a 1 if the field is empty, 0 if it is not empty.

                                   

                                  What is the significance of this for related records?

                                  This function returns 1 (true) if a field is empty, if a related field, a related table, relationship, or a file is missing, or if another error occurs; otherwise, 0 (false).

                                  If there are no related records, IsEmpty () will return a 1.  This will be the case both if the table has no records, and if the relationship is not true for any records (but the table itself has records).  It's the latter property that people rely on to use IsEmpty () to determine if a particular record has any related records in some other table based on a relationship defined in the relationship graph.  As for your specific scenario, I recommend playing around with IsEmpy () until it clicks for you.

                                  1 of 1 people found this helpful
                                  • 14. Re: Calculation based on related records existing?
                                    Philip_Jaffe

                                    In ezeitgeist's scenario, he wants a check when related records exist.  Assuming a boolean value list with just a "1" and a checkbox field using that value list, the check needs to appear if 1, so not isempty would put a check. Of course the inverse works the same.

                                     

                                    Filemaker is full of "double negatives" that mess with your head.  My favorite is "Do not replace existing values (if any)" which will evaluate when unchecked.  Even now I have to translate in my head to "uncheck = replace"

                                     

                                    IsEmpty, Not IsEmpty...  it can get downright silly at times.  ;-)

                                    1 of 1 people found this helpful
                                    1 2 Previous Next