4 Replies Latest reply on Jun 9, 2017 4:30 PM by fitch

    How do I create a field with a condition?

    taskaras

      I have an author field which may contain up to 10 names.  I'd like to create a new field based on the author field which will output like this:

       

      If the author field contains more than 6 names, then output as first author., et al.

       

      Example:

      Grothe, B., et al.

       

      If the author field contains 6 authors or less, then keep as is:

       

      Grothe, B., Jacobs, G., Padgett, K., Aaronson, N. K., & Jacobs, G.

       

       

       

        • 1. Re: How do I create a field with a condition?
          Markus Schneider

          depends on the way auhors are stored. Typically, there's a separate table for them

           

          If there is a way to get a FileMaker-list (list function) of authors (list(authors::names)) You can get the count by valuecount(list..)

           

          if they are stored in one single field without a new-line between two of them, it's difficult since they can hav names like Tom Test, John Paul Doe, etc.)

           

          if there is a new-line (the 'pi' character...) between 2 names in one field, You can get a list of them via substitute (YourTextField;"pi";";") - replace the new-line by a ";"

           

          if You have a repeating field for them, the 'list' function works as well

          • 2. Re: How do I create a field with a condition?
            JimmyCox

            My Colleague (and father) GordonCox just answered this in the fmpexperts mailing list.

            Here was his response:

             

             

            Tas,

             

            I’d certainly look at putting the Authors in a related table, however, the field solution could be something like:

             

            Lets suppose our field, testTable::author, contains “Grothe, B., Jacobs, G., Padgett, K., Aaronson, N. K., Jacobs, G."
            Then

             


            Let (  [
                    toList = Substitute ( testTable::author ; ".," ; ". ,¶" ) ;
                    countAuthors = valuecount ( toList )
                   ] ;

             

            If( countAuthors > 6 ; GetValue ( toList ; 1 ) & " et al." ;
               Substitute ( LeftValues ( toList ; countAuthors - 1 ) ; ". ,¶" ; ",." ) & " & " & GetValue ( toList ; countAuthors )
              )

             

              )

             

            Will result in "Grothe, B,. Jacobs, G,. Padgett, K,. Aaronson, N. K,. &  Jacobs, G.”

             

            And if the testTable::author field contains "Grothe, B., Jacobs, G., Padgett, K., Aaronson, N. K., Smith, R.G., James, W.K., Jacobs, G.”

             

            Then the output will be
            Grothe, B. , et al.

             

            I’d still advise you to revisit putting the authors in a separate table, but this will work. The issue is that if the names aren’t formatted perfectly, i.e. with “.,” as a delimiter, then it will break.

             

            • 3. Re: How do I create a field with a condition?
              keywords

              If you current field has authors in a carriage-return separated list like this:

              Grothe, B.

              Jacobs, G.

              Padgett, K.

              Aaronson, N.K.

              Jacobs, G.

               

              … then you can parse the list using a Let ( ) calc along the following lines:

              Let (

              [

                 theList = "Grothe, B.¶Jacobs, G.¶Padgett, K.¶Aaronson, N.K.¶Jacobs, G."

                ; count = ValueCount ( theList )

                ; long = count > 6

                ; authsShort = Substitute ( theList ; "¶" ; ", " )

                ; last = " & "

                ; etAl = " et al"

                ; authsLong = GetValue ( theList ; 1 ) & etAl

                ; result = If ( long ; authsLong ; authsShort )

              ] ;

              result // in this example: "Grothe, B., Jacobs, G., Padgett, K., Aaronson, N.K., Jacobs, G."

              )

               

              Note:

              1.     if you had authors listed in a separate table (which is what I would do) you can generate the list from there and use it in the first parameter.

              2.     You would need to work out a way to put " & " before the final entry in the short list instead of ", ". I don't have time right now.

              • 4. Re: How do I create a field with a condition?
                fitch

                Here's the answer I posted to the FMXperts list. FYI it's appreciated if you label such posts as "[xpost]" so people don't waste time posting duplicate answers.

                 

                If(

                  PatternCount( authors ; ".," ) > 5 ;

                  Left( authors ; Position( authors ; ".," ; 1 ; 1 ) + 1 ) & " et al." ;

                  //else

                  authors

                )

                 

                or perhaps neater:

                 

                If(

                  PatternCount( authors ; ".," ) < 6 ; authors ;

                //else

                  Left( authors ; Position( authors ; ".," ; 1 ; 1 ) + 1 ) & " et al."

                )