3 Replies Latest reply on Nov 5, 2014 7:08 AM by philmodjunk

    List Calculation based on contents of another field

    Annette

      Title

      List Calculation based on contents of another field

      Post

      Hello, I have a table which I would like to create a list for the related record of all the records of one of the field contents.  Easy with the calculation function.  The twist is I only want a record to be included in the list if a second field is blank.  The if is empty function with the list isn't working for me, unless I'm doing it wrong.  Any ideas? 

      Basically I have a one table that is people.  I have a second table that has items.  The two are related by the people pk.  If an item that was on the table at one time is no longer valid for the person for any reason there is an additional field a note is placed in.  So the list field I want is to show only those items which are still valid, ie have no note in the additional field therefore it's blank.

        • 1. Re: List Calculation based on contents of another field
          philmodjunk

          sounds like it would be simpler to use a portal to list the items with a portal filter to omit those where that field is empty.

          Your if function won't work because it will only "look" at the first related record and then either returns a list of values or not depending on whether the field in that first related record is empty.

          You'd need to use the list function with a relationship that won't match to the records you want to omit or to list a calculation field in the related table that is empty when this other field is empty.

          You could also use the ExecuteSQL function to produce this list with a WHERE clause that will omit the records where this field is empty.

          • 2. Re: List Calculation based on contents of another field
            Annette

            Hi, thanks for the reply.  I would normally just put the portal on the layout and filter it, which I have on other layouts.  But I was looking for a way to make a list for a related record this way because it needs to go on a form where I don't have room to put a portal.  I need the results to appear on one line.   I will look at other ways to work around this.  Thanks again.

            • 3. Re: List Calculation based on contents of another field
              philmodjunk

              To take a return separated list and produce a horizontal line of values, use substitute to replace the return character with some other delimiter such as a comma or space:

              Substitute ( YourFieldHere ; ¶ ; ", " )

              Though if you use ExecuteSQL, you can simply specify the comma as your record separator in the first place.