Text Object formula question

[#1]

I have a dashboard which users access only through URL bookmarks that bring them into the application pre-filtered to their name.  The names of the users are in two different fields-- one is CTO and the other is CTO-1.

The relationship between CTO and CTO-1 is many to many (I think?).. meaning CTO's can have multiple -1's and vice versa.

The bookmarks i've created only have one name selected between the two fields mentioned above, and what I'm trying to do is have a text object display the person who is selected..

I am using the following code in my text object:

=If(SubStringCount(vCTO,'CTO's Name')=1,'CTO's Name',

If([ProgramAMDeptCTO-1Manager]='CTO-1's Name' , [CTO-1],

If([ProgramAMDeptCTO-1Manager]='CTO-1's Name' , [CTO-1],

If([ProgramAMDeptCTO-1Manager]='CTO-1's Name' , [CTO-1],

If([ProgramAMDeptCTO-1Manager]='CTO-1's Name' , [CTO-1],

If([ProgramAMDeptCTO-1Manager]='CTO-1's Name' , [CTO-1],

If([ProgramAMDeptCTO-1Manager]='CTO-1's Name' , [CTO-1],

If([ProgramAMDeptCTO-1Manager]='CTO-1's Name' , [CTO-1],Null()

))))))))

vCTO is a varibale I made that = the CTO's name

The problem is when I have a CTO-1 selected and that person only has one CTO mapped to them it is displaying the CTO's name rather than the CTO-1

[Anonymous]

Hi Julian89,

Don't use simple comma into a string, use chr(39)

=If(SubStringCount(vCTO,'CTO'&chr(39)&'s Name')=1,'CTO&chr(39)&s Name',

If([ProgramAMDeptCTO-1Manager]='CTO-1&chr(39)&s Name' , [CTO-1],

If([ProgramAMDeptCTO-1Manager]='CTO-1&chr(39)&s Name' , [CTO-1],

If([ProgramAMDeptCTO-1Manager]='CTO-1&chr(39)&s Name' , [CTO-1],

If([ProgramAMDeptCTO-1Manager]='CTO-1&chr(39)&s Name' , [CTO-1],

If([ProgramAMDeptCTO-1Manager]='CTO-1&chr(39)&s Name' , [CTO-1],

If([ProgramAMDeptCTO-1Manager]='CTO-1&chr(39)&s Name' , [CTO-1],

If([ProgramAMDeptCTO-1Manager]='CTO-1&chr(39)&s Name' , [CTO-1],Null()

))))))))

Regards

[#3]

You can also use

If([ProgramAMDeptCTO-1Manager]='CTO-1''s Name' , [CTO-1],

using a double ' in a String instead of a single one. For me it is more readable.

[#4]

Hey guys there is not actually an apostrophe in the name.. it would something look like this:

=If(SubStringCount(vCTO,'Andrew')=1,'Andrew',

If([ProgramAMDeptCTO-1Manager]='Randy' , [CTO-1],

If([ProgramAMDeptCTO-1Manager]='Gary' , [CTO-1],

If([ProgramAMDeptCTO-1Manager]='Mark' , [CTO-1],

If([ProgramAMDeptCTO-1Manager]='John' , [CTO-1],

If([ProgramAMDeptCTO-1Manager]='Joe' , [CTO-1],

If([ProgramAMDeptCTO-1Manager]='Bob' , [CTO-1],

If([ProgramAMDeptCTO-1Manager]='Jack' , [CTO-1],Null()

))))))))

[Anonymous]

=If(SubStringCount(vCTO,'Andrew')=1,'Andrew',

If([ProgramAMDeptCTO-1Manager]='Randy' OR

[ProgramAMDeptCTO-1Manager]='Gary' OR

[ProgramAMDeptCTO-1Manager]='Mark' OR

[ProgramAMDeptCTO-1Manager]='John' OR

[ProgramAMDeptCTO-1Manager]='Joe' OR

[ProgramAMDeptCTO-1Manager]='Bob' OR

[ProgramAMDeptCTO-1Manager]='Jack' , [CTO-1],Null()

))