4 Replies Latest reply on Feb 7, 2013 2:05 PM by philmodjunk

    Using greater than and no empty value conditions

    VanessaAnne

      Title

      Using greater than and no empty value conditions

      Post

           Hello, 

            

           I have two database linked to each other.  Main database I'll call A.  Second database I'll call B.  Any fields I mention below with have A or B in it depending on which database the field originates in. 

           I have a main database layout that has a Field1A (text) and Field2B (container) which relates to each other - if I'm searching for FieldA then Field2B will show up with the visual represenation of FieldA.  [Field2B (container) is from the related second database, the field doesn't actually exist in the main one. ]

           Linked to the main database is also an external Field1B (text) which is how the Field2B is populated with the correct image in the A main database. 

           I also have a Field3A (text) with is an alternate text name to Field1A.  In my linked database I have a matching Field3B field - the values are the same.  

           Basically what I need is whenever Field1A is empty and can't link to any value in Field1B, thus causing Field2B to be empty, I want the alternate Field3A to link with the Field B database to populate the Field2B container field with an alternate image - Field3B container.  

           Hard to explain but I'm sure it has something to do with a formula "If Field is Empty" or a "greater than" calcuation.  I need the 1A field values to always override the Field 3A values.  

           Thanks! 

        • 1. Re: Using greater than and no empty value conditions
          philmodjunk

               "database" is not a precise term when talking about FileMaker Databases. It might refer to a single table, a file made up of many tables or many files each of which contain many tables...

               I'd guess that here, you mean "table" when you say "database".

               A calculation field can be defined in A and set to return "container" as it's field type. It can use a case or If function to reference the container field via either of the two relationships based on whether or not the first relationship matches to a record.

               If ( IsEmpty ( FirstRelationshipTO::Field2B ) ; SecondRelationshipTO::Field2 ; FirstRelationshipTO::FIeld2 )

          • 2. Re: Using greater than and no empty value conditions
            VanessaAnne

                 Thank you for your help.  I'm getting the error message that a field is not one, when in fact it is.  I'm sure I'm formatting something wrong.  Below is the breakdown of how each of these fields relates to my original explanation.  Attached is the error message I am receiving. 

                  

                  
                 Case ( shot vfx number = Picture DB::A_VFX Shot # ; Picture Calculation = Picture DB::Picture {; ( Storyboards::storyboard board filename = Picture DB::Storyboard filename ; Picture Calculation = Picture DB::Storyboard Image; )
                  
                 Means
                  
                 Case ( Field1A = Linked Table B::Field1B ; Calculation Field = Linked Table B::Field2B {; ( Linked Table C::Field1C = Linked Table B::Field1B ; Calculation Field = Linked Table B::Field2C; )

                  

                  

            • 3. Re: Using greater than and no empty value conditions
              VanessaAnne

                   I should add that when I remove the "{" symbols, the calculation is accepted but my value turns up as a question mark.  

              • 4. Re: Using greater than and no empty value conditions
                philmodjunk

                     This is not the calculation I suggested. And the brackets you have in it make no sense at all.

                     Picture Calculation = Picture DB::Picture

                     in your expression will return a value of either 1 (True) or 0 (False) depending on whether Picture Calculation and Picture DB::Picture are equal or not.