1 2 Previous Next 21 Replies Latest reply on Apr 24, 2017 8:37 AM by TomHays

    Substitute isn't differentiating between a 1,10, and 11

    tcwaters

      I'm having a problem with this calc field:

       

      Substitute ( linked_Outcomes;

      ["1" ;  TextStyleAdd ("      PittPharmacy Outcome 1: Learner" ; Bold)];

      ["2" ;  TextStyleAdd ("      PittPharmacy Outcome 2: Patient Assessor" ; Bold)];

      ["3" ;  TextStyleAdd ("      PittPharmacy Outcome 3: Caregiver" ; Bold)];

      ["4" ;  TextStyleAdd ("      PittPharmacy Outcome 4: Manager and Leader" ; Bold)];

      ["5" ;  TextStyleAdd ("      PittPharmacy Outcome 5: Health Promoter and Provider" ; Bold)];

      ["6" ;  TextStyleAdd ("      PittPharmacy Outcome 6: Preparer and Dispenser" ; Bold)];

      ["7" ;  TextStyleAdd ("      PittPharmacy Outcome 7: Problem Solver" ; Bold)];

      ["8" ;  TextStyleAdd ("      PittPharmacy Outcome 8: Educator and Communicator" ; Bold)];

      ["9" ;  TextStyleAdd ("      PittPharmacy Outcome 9: Professional and Advocate"; Bold)];

      ["10" ;  TextStyleAdd ("      PittPharmacy Outcome 10: Collaborator"; Bold)];

      ["11" ;  TextStyleAdd ("      PittPharmacy Outcome 11: Life-Long Learner and Innovator" ; Bold)]

      )

       

      What happens, is if the "linked_Outcomes" has a 10, the calc produces"

      PittPharmacy Outcome       PittPharmacy Outcome 1: Learner0: Collaborator

        • 1. Re: Substitute isn't differentiating between a 1,10, and 11
          tcwaters

          I understand why. 1, 10, 11 are evaluated as text strings and therefore, all three use the rule ["1" ;  TextStyleAdd ("      PittPharmacy Outcome 1: Learner" ; Bold)];

           

          I'm just not sure how to revise my calc to give me the desired output.

           

          THX

          • 2. Re: Substitute isn't differentiating between a 1,10, and 11
            rgordon

            If linked outcomes only has one value you could use an If statement to test if linked outcomes equals 1. If true then do the substitute for 1. if false do the rest of the substitute. 

            • 3. Re: Substitute isn't differentiating between a 1,10, and 11
              ch0c0halic

              The values are not numbers they are a string of text. So when the substitute gets to "10" it substitutes the "1" character with your string of text. It then continues to the next character "0" and does nothing.

               

              How to fix this will depend on what the input really looks like.

              Hopefully this is the scenario.

              If the value has a trailing space "10 " then you can add the space to each of the values and it will work. Same if there is a return after the value.

               

              Not as good but possible:

              If the value is just a "10" with nothing else in the field then you can use GetAsNumber() in your calc and use numbers instead of text for the Substitute().

              Substitute ( GetAsNumber ( linked_Outcomes ) ;

              [1 ; TextStyleAdd ("      PittPharmacy Outcome 1: Learner" ; Bold)];

               

              If it's something else then look for a terminator to the value and use that in the compare part of the substitute, see space example above.

              • 4. Re: Substitute isn't differentiating between a 1,10, and 11
                philmodjunk

                I suggest that you toss this calculation. You don't need it.

                 

                Instead, set up a table of these values in a related table and link to them by the numeric value instead of doing this substitution calculation.

                • 5. Re: Substitute isn't differentiating between a 1,10, and 11
                  beverly

                  I'm going to plus plus this one. A lookup table can have formatted text.

                  beverly

                  • 6. Re: Substitute isn't differentiating between a 1,10, and 11
                    philmodjunk

                    A lookup table can have formatted text.

                     

                    or an auto-enter calculation set up to look up this data can apply the formatting....

                     

                    So you have options here

                    • 7. Re: Substitute isn't differentiating between a 1,10, and 11
                      beverly

                      auto-enter Lookup (from a table of data) - would be easier to change the table than a calulation. any changes to the table will be used by new records, moving forward. (old records will not change unless you use "re-lookup").

                      • 8. Re: Substitute isn't differentiating between a 1,10, and 11
                        user19752

                        If the value is just a "10" with nothing else in the field

                        Such case he doesn't need Substitute() function,

                        Case ( field = "1" ; ...

                        • 9. Re: Substitute isn't differentiating between a 1,10, and 11
                          philmodjunk

                          Yes, change is easier to manage by editing formats in the table as it's a data-entry task, but redefining an auto-enter calculation also does not modify the value in the field for existing records.

                           

                          And the calculation method gives you the option of formatting the same data in different ways as needed.

                           

                          So you have options.

                          1 of 1 people found this helpful
                          • 10. Re: Substitute isn't differentiating between a 1,10, and 11
                            tcwaters

                            The field "linked_Outcomes" holds data from checkbox/valuelist, so it looks like this:

                             

                            1

                            4

                            5

                            9

                            10

                             

                            THX for all the comments.  I have a number of optiuons to try, but I think I need to rethink what data gets stored in "linked_Outcomes."

                            1 of 1 people found this helpful
                            • 11. Re: Substitute isn't differentiating between a 1,10, and 11
                              philmodjunk

                              Your value list could just enter the text, but then you lose the formatting. A relationship matching on the number lets you either link to or copy over the text from a related record.

                              • 12. Re: Substitute isn't differentiating between a 1,10, and 11
                                TomHays

                                With your calculation you have two things going wrong.

                                (1) As you described the "1" is located and substituted in 1, 10, and 11.

                                (2) The substitution strings also have digits in them which are substituted again.

                                 

                                The key to getting it to work in its current formulation is to match the number with a boundary marker before and after the number.  Since the original data is in a list, using ¶ is a natural choice.  Something like this should work.

                                 

                                Substitute ( "¶" & linked_Outcomes & "¶";

                                ["¶1¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 1: Learner¶" ; Bold)];

                                ["¶2¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 2: Patient Assessor¶" ; Bold)];

                                ["¶3¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 3: Caregiver¶" ; Bold)];

                                ["¶4¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 4: Manager and Leader¶" ; Bold)];

                                ["¶5¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 5: Health Promoter and Provider¶" ; Bold)];

                                ["¶6¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 6: Preparer and Dispenser¶" ; Bold)];

                                ["¶7¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 7: Problem Solver¶" ; Bold)];

                                ["¶8¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 8: Educator and Communicator¶" ; Bold)];

                                ["¶9¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 9: Professional and Advocate¶"; Bold)];

                                ["¶10¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 10: Collaborator¶"; Bold)];

                                ["¶11¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 11: Life-Long Learner and Innovator¶" ; Bold)]

                                )

                                 

                                The resulting value has an extra ¶ at the start and an extra ¶ at the end.

                                 

                                The following calc is essentially the same as the one above but does some extra work to remove the extra line breaks that were added as boundary markers.

                                 

                                Let(

                                [

                                thePaddedList =

                                Substitute ( "¶" & linked_Outcomes & "¶";

                                ["¶1¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 1: Learner¶" ; Bold)];

                                ["¶2¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 2: Patient Assessor¶" ; Bold)];

                                ["¶3¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 3: Caregiver¶" ; Bold)];

                                ["¶4¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 4: Manager and Leader¶" ; Bold)];

                                ["¶5¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 5: Health Promoter and Provider¶" ; Bold)];

                                ["¶6¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 6: Preparer and Dispenser¶" ; Bold)];

                                ["¶7¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 7: Problem Solver¶" ; Bold)];

                                ["¶8¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 8: Educator and Communicator¶" ; Bold)];

                                ["¶9¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 9: Professional and Advocate¶"; Bold)];

                                ["¶10¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 10: Collaborator¶"; Bold)];

                                ["¶11¶" ;  TextStyleAdd ("¶      PittPharmacy Outcome 11: Life-Long Learner and Innovator¶" ; Bold)]

                                )

                                ];

                                Middle(thePaddedList;2;Length(thePaddedList)-2)

                                )

                                 

                                 

                                 

                                -Tom

                                1 of 1 people found this helpful
                                • 13. Re: Substitute isn't differentiating between a 1,10, and 11
                                  philmodjunk

                                  Yes, but there's no need to use any calculation

                                  • 14. Re: Substitute isn't differentiating between a 1,10, and 11
                                    TomHays

                                    Your original formula's concept will follow the original order of the numbers in the list.  When the data entry is done by checkboxes, this can generate a list that is not in the same order of the value list.  It is in the order that the checkboxes were checked during data entry.  You could have a list that is

                                    1

                                    4

                                    5

                                    9

                                    10

                                     

                                    or

                                     

                                    4

                                    10

                                    5

                                    1

                                    9

                                     

                                    depending on how the person clicked the boxes.

                                    Your calculation will also preserve in the output any numbers in the list (or other values for the checkboxes) that do not match 1 through 11.

                                     

                                    If you want the output to always be ascending order and you want only to display text that matches 1 through 11 with other numbers, you could use a calculation like the following.  Note that I moved the TextStyleAdd() to be outside of the calculation since the formatting was identical on all entries.  If you want to use different formatting on each line, put the TextStyleAdd() back in around each text string.

                                     

                                    Let(

                                    [

                                    PL = "¶" & linked_Outcomes & "¶"

                                    ];

                                    TextStyleAdd(

                                    List(

                                    If (IsEmpty(FilterValues(PL;"1")); ""; "      PittPharmacy Outcome 1: Learner");

                                    If (IsEmpty(FilterValues(PL;"2")); ""; "      PittPharmacy Outcome 2: Patient Assessor");

                                    If (IsEmpty(FilterValues(PL;"3")); ""; "      PittPharmacy Outcome 3: Caregiver");

                                    If (IsEmpty(FilterValues(PL;"4")); ""; "      PittPharmacy Outcome 4: Manager and Leader");

                                    If (IsEmpty(FilterValues(PL;"5")); ""; "      PittPharmacy Outcome 5: Health Promoter and Provider");

                                    If (IsEmpty(FilterValues(PL;"6")); ""; "      PittPharmacy Outcome 6: Preparer and Dispenser");

                                    If (IsEmpty(FilterValues(PL;"7")); ""; "      PittPharmacy Outcome 7: Problem Solver");

                                    If (IsEmpty(FilterValues(PL;"8")); ""; "      PittPharmacy Outcome 8: Educator and Communicator");

                                    If (IsEmpty(FilterValues(PL;"9")); ""; "      PittPharmacy Outcome 9: Professional and Advocate");

                                    If (IsEmpty(FilterValues(PL;"10")); ""; "      PittPharmacy Outcome 10: Collaborator");

                                    If (IsEmpty(FilterValues(PL;"11")); ""; "      PittPharmacy Outcome 11: Life-Long Learner and Innovator");

                                    )

                                    ; Bold)

                                    )

                                    1 of 1 people found this helpful
                                    1 2 Previous Next