4 Replies Latest reply on Jan 11, 2016 9:48 AM by facaylar

    How to ignore empty fields in calculations

    facaylar

      Hello fellow devs, I'm hoping one of you can give me a clue as to how to write a particular calculation.

       

      Our database is used to track user information and has several fields:

       

      • Name
      • Address_1
      • Address_2
      • Address_3
      • Address_4
      • City
      • State
      • County
      • Postal_Code
      • Country

       

      Since Addresses in the United States are all the same, the calculation to truncate them is easy. However, my international users, being from different countries, have addresses that don't necessarily conform to the US standard.

       

      Is there a calculation that will go through each field and only feed fields into the calculation that have data in them? Let me know if I need to clarify.

        • 1. Re: How to ignore empty fields in calculations
          Menno

          Screenshot_Calc.png

          in a calculation de-select this value and a calculation will evaluate even if not all referenced fields contain data

          • 2. Re: How to ignore empty fields in calculations
            DanielShanahan

            One way to build the calculation is to use the List () function like so:

             

            List (

              TABLE::Name ;

            TABLE::Address_1 ;

            TABLE::Address_2 ;

            TABLE::Address_3 ;

            TABLE::Address_4 ;

            TABLE::City & ", " & TABLE::State & "  " & TABLE::Postal_Code ;

            TABLE::Country

            )

             

            When a value is empty ( For example, TABLE::Address_3 and TABLE::Address_4), the List () function "pushes" up the data underneath.  (I'm sure there' s a better way to explain it, but it escapes me at the moment.).

            • 3. Re: How to ignore empty fields in calculations
              Extensitech

              We use a "mask" in a countries table, such as:

               

              <<Address_1>>

              <<City>>, <<State>> <<Postal_Code>>

               

              Then our full address is basically a substitute function, including getting rid of blank spaces.

               

              Generally, I'd advise against address 1, 2, 3, etc. and recommend one address with potentially space for more than one line. Since there's no set rules as to what goes on each line, having multiple fields usually ends up with, for example, address2 sometimes being an apartment, sometimes a department, sometimes a c/o line, sometimes a PO box (which is actually a completely different address), etc., so searching the data becomes somewhere between difficult and impossible. My two cents.

               

              Chris Cain

              Extensitech

              • 4. Re: How to ignore empty fields in calculations
                facaylar

                I can't believe I forgot about the List function, thank you for bringing me back to this simpler solution.

                 

                In this case, it was simply:

                 

                Case ( Country = "United States" ; List ( Address 1 ; Address 2 ; Address 3 ; Address 4 ; City & ", " & State & " " & Postal Code ) ; List ( Address 1 ; Address 2 ; Address 3 ; Address 4 ; City ; State ; Postal Code ; County ; Country ) )

                 

                This allows for the calculation to provide the address in the US format when it's a US address and the extended blurb when it's not.