11 Replies Latest reply on Aug 8, 2012 1:55 PM by AlanStirling

    Formatting some text in a string.

    tcwaters

      I have a field in a DB called authors, which is a simple list of authors on a paper. I need to bold those names of authors who are faculty, and leave plain, those authors who are not faculty. There is a list of about 60 faculty. Is there a simple way to do this?

       

      Thanks!

        • 1. Re: Formatting some text in a string.
          beverly

          TC, list? as in ONE field with return-delimited? if so, how did/does the list get compiled?

           

          How do you know which are faculty and which are not?

           

          Beverly

          • 2. Re: Formatting some text in a string.
            patricia

            This sound a job for conditional formatting?

             

            Use a formula which says if author is a member of faculty

            Bold

             

            Patricia

            • 3. Re: Formatting some text in a string.
              DavidZakary

              If your author list is a single field with carriage returns you'll have to parse through each line in the field using get value and then comparing it to your faculty list. If it is a factulty member you'll use substitute to replace it with a bold version. Very brute force method.

               

              I'd consider creating a new table for the authors of a paper. This way each author is a single record that can be analyzed much easier. You'd still have to do some checking. Posiibly have a second name field that checks to see if the author is a faculty member. You'd have 3 fields (name, faculty, name_display). In this scenario name_display is a calculation

               

              Case (

              faculty = "yes"; TextStyleAdd (name; Bold);

              faculty = "no"; name;

              name

              )

               

              You could then use the List function to compile a single field of all the authors. It could be make a lot more condensed but this seems pretty easy to follow along.

               

              HTH

              • 4. Re: Formatting some text in a string.
                comment

                tcwaters wrote:


                Is there a simple way to do this?

                 

                Probably not, although you might want to have a look at:

                http://www.briandunning.com/cf/579

                 

                This issue (and others with it) could be simplified enormously by placing the authors in individual records of a related table.

                • 5. Re: Formatting some text in a string.
                  patricia

                  Bev asked an important question.

                  Are you creating a list of authors in one field or is it a list of separate records?

                   

                  patricia

                  • 6. Re: Formatting some text in a string.
                    tcwaters

                    The data was entered by faculty, mostly via copy and paste from their CV.  In a record the field "authors" looks genderally like this:

                     

                    Gellad WF, Aspinall SL, Handler SM, Stone RA, Castle N, Semla TP, Good CB, Fine MJ, Dysken M, Hanlon JT.

                     

                    Because faculty often fail to follow all instructions, the string of authors can be different- sometimes first names first, sometimes with more punctuation.

                     

                    So in this example, only AspinallSL is a faculty member, and I want the string to end up looking like:

                    Gellad WF, Aspinall SL, Handler SM, Stone RA, Castle N, Semla TP, C. Bernie Good CB, Fine MJ, Dysken M, Hanlon JT.

                     

                    The list of faculty could come from a field in the database (there is a table related to publications, of all faculty), or it could be a manually generated text string. I thought about the latter mewthod because for some authors, they sometimes use one initial (Aspinall S) and on other publications use both initials (Aspinall SL)

                    • 7. Re: Formatting some text in a string.
                      comment

                      tcwaters wrote:

                       

                      the string of authors can be different- sometimes first names first, sometimes with more punctuation.
                      ...

                      they sometimes use one initial (Aspinall S) and on other publications use both initials (Aspinall SL)

                       

                      I get the impression you have more exceptions than rules. Unfortunately, computers follow instructions precisely and apply no common sense. Unless your data has at least some consistency, this is not going to work at all.

                       

                      At best (or rather worst), you could tokenize the names (provided that a comma is used consistently and exclusively as a separator), shorten the initial to one letter and then run the comparison against a known list, testing for an all-words match (not necessarily in order). This would be a lot of work to set up and it would probably fail with a name like "C. Bernie Good CB".

                       

                      Perhaps it would be simpler and easier to put a human in charge of cleaning up the data.

                      • 8. Re: Formatting some text in a string.
                        tcwaters

                        I'm not sure if there are more exceptions than rules, really, but I appreciate and understand your point. There are a finite number of possible "faculty names" so the list is possible to create and maintain. Agree some manual cleaning up of the data is mandatory.

                         

                        I think my original question is still the main point- a way to highlight names, in a field, where those names match a list of names. This given that preparatory data cleaning is done so the names in the field are properly formatted.

                        • 9. Re: Formatting some text in a string.
                          ch0c0halic

                          tcwaters may have mandatory exceptions,

                           

                          I'd recommend a custom function to check each 'name' and if its in the "list" then return "" & $original_name & "".

                           

                          You can do this with an Auto-enter calculation (AEC) that replaces the original value (must also allow for this so dual replaces don't happen, maybe remove the *'s before doing the compare/replace) or you can use it in a Calculation field that is used to display the desired highlights.

                          • 10. Re: Formatting some text in a string.
                            comment

                            tcwaters wrote:

                             

                            I think my original question is still the main point- a way to highlight names, in a field, where those names match a list of names.

                             

                            I believe I have already answered this question: if you apply the custom function mentioned in my first post, you will have highlighted the matching names. Note, however, that matching in this context means matching exactly (with the possible exception of case-sensitivity).

                            • 11. Re: Formatting some text in a string.
                              AlanStirling

                              Hi TCWaters

                               

                              Perhaps you need another approach to this issue?

                               

                              If you can't ensure that the data is entered correctly, then any attempt to match this data against a list of faculty names is never going to be reliable.

                               

                              You want the result to be a comma separated list of names and initials, but the method of input does not have to be in this format.

                               

                              I suggest that you make a new table 'Author Entry', which displays in a portal showing two fields, Surname and Initials.

                               

                              Your users enter the list of Authors into this Portal (Set to allow Entry of New Records). Since the list is now entered accurately, then you can compare entries in this list against your list of Faculty, marking those records that match.

                               

                              When you come to output the list, you can use a combination of a calculated field combining Surname and Initials, set to Bold where required.  This can be picked up with the 'List' function, substituting the 'Returns' for 'Comma Space' (except the last one!).

                               

                              This is more cumbersome, but will provide a more reliable method to give you the result you require.

                               

                              Best Wishes - Alan Stirling, London UK.