12 Replies Latest reply on Feb 26, 2013 4:30 PM by philmodjunk

    HELP!!!!! Portal restriction

    CarlHancock

      Title

      HELP!!!!! Portal restriction

      Post

           Okay I'm trying to get a portal to only show records that do not contain part of a string in another field. 

           So if in a record,  a text field named Author contains  "George, H. G. and Patterson, C. D."

      I want the portal to compare text in the text field ::CAuthor and  not display portal records that contain any of the text from either field. So if ::CAuthor contains "George" or "Patterson" or a combination of George plus other text or Patterson and other text it will not be present in the portal. Any ideas?

      help2.png

        • 1. Re: HELP!!!!! Portal restriction
          philmodjunk

               I don't quite follow how you intend that to work. This may sound silly but I'm trying to clarify what you mean by "any of the text from either field."

               So if one field has "abc" and the other field has "dag", they match because they both have "a" in them?????

               Some examples of what text should result in a match and what text should not result in a match would be very helpful.

                

          • 2. Re: HELP!!!!! Portal restriction
            CarlHancock

                 Scenario 1: (Easy I can do this)

                 In the main layout, one field has two names in it...

                 "B. Anderson and C. Johnson"

                 and a field in another table (to possibly displayed in the portal) contains...

                 "B. Anderson" or  "C. Johnson"

                 I want the portal to not display that record in the portal. 

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

                 Scenario 2: 

                 In the main layout, one field has two names in it...

                  

                 "B. Anderson and C. Johnson"

                 and a field in another table (to possibly displayed in the portal) has...

                 "B. Anderson and N. Saban" or "N. Saban and C. Johnson"

                 I want the portal to not display that record in the portal. 

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

                 Ideas???

                  

                  

            • 3. Re: HELP!!!!! Portal restriction
              CarlHancock

                    

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

                   Scenario 3: 

                   In the main layout, one field has two names in it...

                   "B. Anderson and C. Johnson"

                   and a field in another table (to possibly displayed in the portal) has...

                   "N. Saban" or "N. Saban and T.S. Elliot"

                   I want the portal to display that record in the portal. 

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

              • 4. Re: HELP!!!!! Portal restriction
                philmodjunk

                     What is the significance of the words "or" and "and" in these fields?

                     Am I correct that the text to be compared is the text located between these words?

                     If so, use this calculation in calculation fields in both tables to break up the lists accordingly:

                     Substitute ( Authors ; ["or" ; ¶ ] ; ["and" ; ¶ ] )  // if commas can also separate author names, add one more pair of brackets ["," ; ¶ ].

                     Then this expression:

                     IsEmpty ( FilterValues (Table1::Calculationfield ; Table2::calculationfield ) )

                     will be true only if an author name does not appear in both fields.

                • 5. Re: HELP!!!!! Portal restriction
                  CarlHancock

                       Okay, so there is slightly different problem. 

                       When the field in the first table has more than two names such as

                  "Anderson, B. J., Johnson, C., and Hancock, J."

                       and a field in another table (to possibly displayed in the portal) has more than two names

                       " Johnson, C., YYYYY, Y. Y., and XXXXX, J" 

                       or two of the names

                        

                       "Johnson, C., Anderson, B. J., and XXXXX, J" 

                        
                       It still displays the records. How do I adjust your solution to work for this? 

                       Thanks again!!

                  • 6. Re: HELP!!!!! Portal restriction
                    CarlHancock

                         Addendum!

                          

                         When the field in the first table has two names such as

                    "Anderson, B. J. and Johnson, C. J."

                         and a field in another table (to possibly displayed in the portal) has more than two names

                         " Anderson, B. J., Johnson, C. J., and XXXXX, X. X." 

                         or

                         " Anderson, B. J., ZZZZZ, Z. Z., and XXXXX, X. X." 

                         It still displays the records. 
                          
                         Thank you for looking at this!
                    • 7. Re: HELP!!!!! Portal restriction
                      philmodjunk

                           That's a good question since you no longer have a simple way to separate the text in these fields into individual names.

                           How is the text entered into these fields in the first place? If both are populated by manually entering the data, there is a real chance that no method will work with 100% reliability due to data entry errors in the fields.

                           If this data comes from other fields where the author names are in separate records, then I suggest building your list from them via script or calculation so that you can get more consistent results.

                           It is possible to use a modified approach that first subsitutes a return for ", and" and then replaces every second comma in the resulting text with a return, but this will easily break if this is manually entered data.

                      • 8. Re: HELP!!!!! Portal restriction
                        CarlHancock

                        "It is possible to use a modified approach that first subsitutes a return for ", and" and then replaces every second comma in the resulting text with a return, but this will easily break if this is manually entered data."

                              

                        This will really help! How do I identify the second comma? Thank you for your help with this. 

                             Is there a better way to do it than this?

                             Substitute (Author ; [".," ; ¶ ] ; [", and" ; ¶ ])

                              

                        • 9. Re: HELP!!!!! Portal restriction
                          philmodjunk

                               Is this data manually entered?

                               The BEST way is not to work from such fields with their potential complications in the first place.

                               A looping script can loop through the text finding every second comma but consider how screwed up things will become if your typist accidentlally gets a bit of "keybounce" and inputs two commas where he should have entered one...

                          • 10. Re: HELP!!!!! Portal restriction
                            CarlHancock

                                 All of the data came from an online database and the formatting is consistent (thank goodness). 

                                  

                                 I tried this...Substitute (Author ; [", and" ; ¶ ] ; ["., " ; ¶ ] ) But the second substitution removes a "." and I have to replace it to work. 

                            Thanks again for your sage advice. I pretty stuck with the combined names in the author fields. 

                            • 11. Re: HELP!!!!! Portal restriction
                              CarlHancock

                                   I got it!! This works....

                                   Substitute (Author ; ["., " ; ¶ ] ; [" and " ; ¶ ]  ; ["." ; "" ])

                                   Thank you for your help! I wouldn't have broken through this without your help!

                              • 12. Re: HELP!!!!! Portal restriction
                                philmodjunk

                                     and if field 1 =

                                adams, John, and smith, andy

                                     with field 2 =

                                adams, Jeff

                                     should there be a match and the record be excluded?