1 2 Previous Next 22 Replies Latest reply on Nov 18, 2013 11:52 AM by CrisLee

    calculation field trouble with IsEmpty

    CrisLee

      Title

      calculation field trouble with IsEmpty

      Post

            

                Hi
                 
                First time posting to the forum. Thank you in advance. 
                 
                I have a calculation field named artContent. It copies data from a related table field into the calculation field however if the override field is used it replaces the related data. below is the code that works:
                 
                     Let ([
                     override = GetField  ( GetFieldName ( Self ) & ".override" );
                     source = GetField ( Portfolio::artContent )
                     ];
                     If ( IsEmpty ( Artwork::artContent.override ) ; source ; override )
                     )
                 
                 
                here is the code I want to use however, the field will not recalculate properly. The qualified field name in the IsEmpty in the above code is the the same as the override variable used in the function below. I want this to work so that I do not have to open a 100 different fields and manual change the IsEmpty. 
                 
                Let ([
                override = GetField  ( GetFieldName ( Self ) & ".override" );
                source = GetField ( Portfolio::artContent )
                ];
                If ( IsEmpty ( override ) ; source ; override )
                )
            

        • 1. Re: calculation field trouble with IsEmpty
          gcatnine

               override is the content of field (self& ".override") not a field name

               isempty is expecting a field name

          • 2. Re: calculation field trouble with IsEmpty
            CrisLee

                 I thought this as well and still could not get it to work. I tried this:

                  

                 Let ([
                     override = GetField  ( GetFieldName ( Self ) & ".override" );
                     source = GetField ( Globals::artworkSource & "::" & ConvertFieldName ( GetFieldName (Self) ; "parent") );
                     overrideName = GetFieldName (override)
                 ];
                     If ( IsEmpty ( overrideName ) ; source; override )
                 )

            • 3. Re: calculation field trouble with IsEmpty
              philmodjunk

                   is this an auto-enter calculation?

                   Why are you using getField and GetFieldName? What problems do you solve by using them instead of direct references to the fields? (You may want to get the calculation to work with direct references before trying to use indirect references.)

              • 4. Re: calculation field trouble with IsEmpty
                CrisLee

                     Hi, there are hundreds of these fields so I do not want to direct enter all of the fields. I am capturing the field name with the self converting it to a field name and appending the .override then converting back with GetField. It is not an auto-enter it is a straight calculation field. Thanks again

                • 5. Re: calculation field trouble with IsEmpty
                  CrisLee

                       Hi again... yes it does work with the direct references, it also works with the variable as the default in the if statement it just wont accept the variable in the IsEmpty statement.

                  • 6. Re: calculation field trouble with IsEmpty
                    philmodjunk

                         Self does not work as a reference to the current field in a calculation field. It only works in conditional formats and auto-entered calculations. You'll need some other way to set up an indirect reference to the correct field.

                         

                              there are hundreds of these fields

                         That suggests that you may be using fields where you should be using multiple records with a relationship that allows you to match to the correct values for purposes of getting the correct calculated results.

                         There are times when "hundreds of fields" in one table are necessary, but anytime that you do, it's time to take a step back and consider whether your data model might be improved with some design changes. The apparent need to use the same calculation over and over again but with different fields also sugests that possibility.

                    • 7. Re: calculation field trouble with IsEmpty
                      CrisLee

                           Thank you for your time.

                           

                                Self does not work as a reference to the current field in a calculation field. It only works in conditional formats and auto-entered calculations. You'll need some other way to set up an indirect reference to the correct field.

                           I may not be understanding you here, becuase I am using Self in calculation fields. IE this code is in a calculation field (in the override variable below) :

                           Let ([
                               override = GetField  ( GetFieldName ( Self ) & ".override" );
                               source = GetField ( Globals::artworkSource & "::" & ConvertFieldName ( GetFieldName (Self) ; "parent") )

                           ];
                               If ( IsEmpty ( Artwork::artContent.override ) ; source; override )
                           )

                           

                                That suggests that you may be using fields where you should be using multiple records with a relationship that allows you to match to the correct values for purposes of getting the correct calculated results.

                           

                                There are times when "hundreds of fields" in one table are necessary, but anytime that you do, it's time to take a step back and consider whether your data model might be improved with some design changes. The apparent need to use the same calculation over and over again but with different fields also sugests that possibility.

                           I didn't mean to suggest that all of the fields are on the same layout. I am saying I have many fields I would like to use this technique on. It saves a lot of time managing and modifying fields.

                            

                           The problem persists that I can not use a variable in the IsEmpty that is working in the same If statement.

                            

                            

                      • 8. Re: calculation field trouble with IsEmpty
                        philmodjunk

                             Thank you for teaching me something new!

                             I was certain that I had previously gotten error messages when I used self in a calculation field, but a quick test shows that you are correct: GetFIeldName ( self ) will return the tableoccurrenceName::FieldName correctly. (And the table occurrence name returned will be the name selected in the context drop down.)

                             Yet another test calculation:

                             Let ( [ test1 = "3" ; test2 = "" ] ; List ( IsEmpty ( test1 ) ; IsEmpty ( Test2 ) ) )

                             will return the expected values of 1<return> 0.

                             The problem is not the Let function nor IsEmpty. It's your use of GetFIeldName. The parameter has to be a direct field reference such as you might add by using a double click to add a field from the list of fields in the top of the specify calculation dialog. It does not work with a text expression. Yes, FileMaker's help entry for this function appears to indicate that this should work, but a quick test in FileMaker Advanced's DataViewer shows that:

                             GetFieldName ( "Table::Field" ) produces an error message. I even double clicked to add the field reference, then enclosed it in quotes to make it a text expression. The result returned was ?--which is not "empty".

                             Not IsValid ( GetFieldName ( GetField ( letVariable ) ) )

                             Would seem to be the expression you need to make sure that your computed field reference is a reference to a field that actually exists.

                        • 9. Re: calculation field trouble with IsEmpty
                          CrisLee

                               Well thanks for all your time. I cant brainstorm a way for this to work then.

                                

                               What I need is for the calculation to check to see if another field is empty. If it is empty then copy in some related data or if the other field is not empty then copy that data in. This works when I use a typed in field name in the IsEmpty but not using a variable in the IsEmpty that is created by a text name with appended text. I know it sounds difficult but I think you follow me.

                          • 10. Re: calculation field trouble with IsEmpty
                            philmodjunk

                                 Use IsEmpty ( GetField, not IsEmpty ( GetFieldName...

                                 GetFieldName returns the TableOccurrenceName::FieldName of the referenced field. GetFIeld returns the contents of the field named in the GetField parameter.

                            • 11. Re: calculation field trouble with IsEmpty
                              CrisLee

                                   You lost me there with your syntax... Can you show me with the code below:

                                    

                                        Let ([
                                        override = GetField  ( GetFieldName ( Self ) & ".override" );
                                        source = GetField ( Portfolio::artContent )
                                        ];
                                        If ( IsEmpty ( Artwork::artContent.override ) ; source ; override )
                                        )

                                    

                              • 12. Re: calculation field trouble with IsEmpty
                                philmodjunk

                                     Use: IsEmpty ( GetField ( yourvariableHere ) )

                                     Do not use: IsEmpty ( Getfieldname ( yourvariablehere ) )

                                • 13. Re: calculation field trouble with IsEmpty
                                  CrisLee

                                       Hi

                                       You are right, that is what I had several times and appeared to not work. It was working, the problem, I have now discovered, is that when the variable is inserted it takes out the direct field reference and the calculation field is no longer updating.:

                                  This does not update:

                                       Let ([
                                           override = GetField  ( GetFieldName ( Self ) & ".override" );
                                       $$test1 = override;
                                           source = GetField ( Globals::artworkSource & "::" & ConvertFieldName ( GetFieldName (Self) ; "parent") )

                                       ];
                                           If ( IsEmpty ( GetField ( override ) ) ; source; override )

                                       )

                                        

                                  This does:

                                            Let ([
                                            override = GetField  ( GetFieldName ( Self ) & ".override" );
                                            source = GetField ( Portfolio::artContent )
                                            ];
                                            If ( IsEmpty ( Artwork::artContent.override ) ; source ; override )
                                            )
                                             
                                            I suppose it is because the direct reference to the dependent field is removed and filemaker does not recalculate when a variable is used to reference another field. Is there a way around this?

                                        

                                  • 14. Re: calculation field trouble with IsEmpty
                                    philmodjunk

                                         Indirect data references can easily leave you chasing your tail.

                                         Does Override store data that you want to return or does it store the name of a field that stores the data you want to return?

                                         I think you need to use IsEmpty ( override )  instead of using: IsEmpty ( GetField ( Override ) )

                                         But that assumes that Override contains data, and not the name of the field that contains the data...

                                    1 2 Previous Next