
1. Re: Substitute isn't differentiating between a 1,10, and 11
tcwaters Apr 21, 2017 1:47 PM (in response to 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 Apr 21, 2017 1:48 PM (in response to tcwaters)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 Apr 21, 2017 1:53 PM (in response to tcwaters)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 Apr 21, 2017 1:59 PM (in response to tcwaters)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 Apr 21, 2017 2:23 PM (in response to philmodjunk)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 Apr 21, 2017 2:54 PM (in response to tcwaters)A lookup table can have formatted text.
or an autoenter 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 Apr 22, 2017 12:48 AM (in response to philmodjunk)autoenter 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 "relookup").

8. Re: Substitute isn't differentiating between a 1,10, and 11
user19752 Apr 22, 2017 1:19 AM (in response to ch0c0halic)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 Apr 22, 2017 6:55 AM (in response to beverly)1 of 1 people found this helpfulYes, change is easier to manage by editing formats in the table as it's a dataentry task, but redefining an autoenter 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.

10. Re: Substitute isn't differentiating between a 1,10, and 11
tcwaters Apr 22, 2017 8:36 AM (in response to philmodjunk)1 of 1 people found this helpfulThe 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."

11. Re: Substitute isn't differentiating between a 1,10, and 11
philmodjunk Apr 22, 2017 8:54 AM (in response to tcwaters)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 Apr 22, 2017 11:08 AM (in response to tcwaters)1 of 1 people found this helpfulWith 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: LifeLong 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: LifeLong Learner and Innovator¶" ; Bold)]
)
];
Middle(thePaddedList;2;Length(thePaddedList)2)
)
Tom

13. Re: Substitute isn't differentiating between a 1,10, and 11
philmodjunk Apr 22, 2017 11:24 AM (in response to tcwaters)Yes, but there's no need to use any calculation

14. Re: Substitute isn't differentiating between a 1,10, and 11
TomHays Apr 22, 2017 11:25 AM (in response to tcwaters)1 of 1 people found this helpfulYour 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: LifeLong Learner and Innovator");
)
; Bold)
)