6 Replies Latest reply on Oct 27, 2015 5:13 PM by bigtom

    Value list from field containing return-separated list of values

    StevenRubenstein

      Couldn't find guidance on this elsewhere:

       

      I am trying to create a Value List based on a field where values comprise carriage-return separated "lists". The Value List is already referencing two fields so I have resorted to making the second field a calculation  - basically combining various fields into a carriage-return separated list.

       

      However, when I use this field in the Value List  definition, only the first, top-most value is included.

       

      Is there an easy (or any) way to combine more than just 2 fields into a Value List? Additionally, are Value Lists guaranteed to have only unique values through the proposed solution (if any).

       

      Thanks!

        • 1. Re: Value list from field containing return-separated list of values
          jbrown

          Good evening.

          I've never seen a value list be able to pick up other rows of a field. It takes unique values from the table & field upon which the value list is created and creates the value list, but I don't think it can use multiple values in a field.

           

          Can you turn these into records? Is there a reason for multiple values in a field?

           

          You can easily combine two fields into a value list by using a 3rd field that concatenates the first two together. If you want to show the Full name of a person, you can create another field called FullName_c that equals Name_First & " " & Name_Last. That FullName_c field could easily be your value list.

          • 2. Re: Value list from field containing return-separated list of values
            bigtom

            If you use ExecuteSQL stored calculation fields in a separate table, then use those fields for your list it should work. I think there might be a limit on the number of entries in a value list though.

            • 3. Re: Value list from field containing return-separated list of values
              bigtom

              There should be a way to do what you want, but a little more information would be helpful.

               

              What is the relationship of the two fields? Can you just use one field with the delimited list?

              • 4. Re: Value list from field containing return-separated list of values
                StevenRubenstein

                I am trying to create a delimited list as you propose:


                All the fields are in the same table. This is the definition of the Calculation Field that I'm basing the Value List on:

                ==================================================

                NAME &

                If ( (not IsEmpty ( GetRepetition ( AKA; 1))) or (not IsEmpty ( GetRepetition ( AKA; 2))) or (not IsEmpty ( GetRepetition ( AKA; 3))) or  (not IsEmpty ( GetRepetition ( AKA; 4))) ; "¶" ; "" )  &

                Case (

                    not IsEmpty ( GetRepetition ( AKA; 1)) ;

                       GetRepetition ( AKA; 1) & If ( (not IsEmpty ( GetRepetition ( AKA; 2))) or (not IsEmpty ( GetRepetition ( AKA; 3))) or  (not IsEmpty ( GetRepetition ( AKA; 4))) ; "¶" ; "" ) ;

                    not IsEmpty ( GetRepetition ( AKA; 2)) ;

                      GetRepetition ( AKA; 2) & If ( (not IsEmpty ( GetRepetition ( AKA; 3))) or (not IsEmpty ( GetRepetition ( AKA; 4))) ; "¶" ; "" ) ;

                    not IsEmpty ( GetRepetition ( AKA; 3)) ;

                      GetRepetition ( AKA; 3) & If ( (not IsEmpty ( GetRepetition ( AKA; 4))) ; "¶" ; "" ) ;

                    not IsEmpty ( GetRepetition ( AKA; 4)) ;

                      GetRepetition ( AKA; 4) ;

                    ""

                )

                ==================================================

                This creates a list of return-delimited values but in the Value List, only the topmost value shows up (i.e. Table::NAME, without any repetitions of Table::AKA). Hope this makes clearer!

                • 5. Re: Value list from field containing return-separated list of values
                  bigtom

                  It does not look like there is a return after NAME

                  So you need a list with the NAME and AKA for only a certain record you are looking at or all records at once in one big list?

                   

                  How do you want the data displayed actually in the pop-up menu? Seems like:

                   

                  NAME

                  AKA1

                  AKA2

                  AKA3

                  AKA4

                   

                  Will Table::NAME & "" & List(Table::AKA) work?

                  • 6. Re: Value list from field containing return-separated list of values
                    bigtom

                    You may want to double check that the field calculation is set to return Text. That can sometimes cause an issue. The rest of the calc looks like something is going wrong after the first AKA value.

                     

                    I threw together a simple sample file. I hope it helps. I am not sure why the list is being sorted alphabetically by default, but maybe that is standard. If you want a certain known order use unicode to do it.

                     

                    If you want it all just set the field and if you only want ones for that record use the self join and modify the value list settings.