Mixing
SOQL returning incorrect value
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
5
down vote
favorite
This is throwing me for a loop. I have a custom field on user, Backend_User__c. The formula is pretty simple:
IF(
OR( Profile.Name = "API Only",
Profile.Name = "Boldchat API Only",
Profile.Name = "Business Intelligence (Custom)",
Profile.Name = "Business Office (Custom)",
Profile.Name = "Chat Representative (Custom)",
Profile.Name = "Compliance (Custom)",
Profile.Name = "GUAppDev API Only",
Profile.Name = "IT (Custom)",
Profile.Name = "IT - App Dev",
Profile.Name = "Marketing (Custom)",
Profile.Name = "Marketing User",
Profile.Name = "Read Only All Records",
Profile.Name = "Registrar (Custom)",
Profile.Name = "System Administrator",
Profile.Name = "System Administrator (Clone)",
Profile.Name = "Read Only",
Profile.Name = "Partner Community User"
),
TRUE,
FALSE
)
This appears to be showing correctly when I look at the user records. However, when I run SOQL against it, the values are not correct. For example:SELECT Name, Id, Profile.Name, Username, Alias, Bogus_User__c, IsActive, Backend_User__c FROM User returns false for a System Administrator. I go to the user record, and the field is checked. As it should be.
What is causing this?
soql formula-field user
asked Sep 5 at 16:06
Chance
443210
add a comment |Â
up vote
5
down vote
favorite
This is throwing me for a loop. I have a custom field on user, Backend_User__c. The formula is pretty simple:
IF(
OR( Profile.Name = "API Only",
Profile.Name = "Boldchat API Only",
Profile.Name = "Business Intelligence (Custom)",
Profile.Name = "Business Office (Custom)",
Profile.Name = "Chat Representative (Custom)",
Profile.Name = "Compliance (Custom)",
Profile.Name = "GUAppDev API Only",
Profile.Name = "IT (Custom)",
Profile.Name = "IT - App Dev",
Profile.Name = "Marketing (Custom)",
Profile.Name = "Marketing User",
Profile.Name = "Read Only All Records",
Profile.Name = "Registrar (Custom)",
Profile.Name = "System Administrator",
Profile.Name = "System Administrator (Clone)",
Profile.Name = "Read Only",
Profile.Name = "Partner Community User"
),
TRUE,
FALSE
)
This appears to be showing correctly when I look at the user records. However, when I run SOQL against it, the values are not correct. For example:SELECT Name, Id, Profile.Name, Username, Alias, Bogus_User__c, IsActive, Backend_User__c FROM User returns false for a System Administrator. I go to the user record, and the field is checked. As it should be.
What is causing this?
soql formula-field user
asked Sep 5 at 16:06
Chance
443210
Can u verify field level security?
– Ayub
Sep 5 at 16:24
I can. I have read permission granted to every user. Also, it IS returning a value, False!
– Chance
Sep 5 at 16:25
You can always replaceIF(condition, true, false)withcondition. It's already the exact same boolean value.
– Adrian Larson♦
Sep 5 at 16:59
add a comment |Â
up vote
5
down vote
favorite
up vote
5
down vote
favorite
This is throwing me for a loop. I have a custom field on user, Backend_User__c. The formula is pretty simple:
IF(
OR( Profile.Name = "API Only",
Profile.Name = "Boldchat API Only",
Profile.Name = "Business Intelligence (Custom)",
Profile.Name = "Business Office (Custom)",
Profile.Name = "Chat Representative (Custom)",
Profile.Name = "Compliance (Custom)",
Profile.Name = "GUAppDev API Only",
Profile.Name = "IT (Custom)",
Profile.Name = "IT - App Dev",
Profile.Name = "Marketing (Custom)",
Profile.Name = "Marketing User",
Profile.Name = "Read Only All Records",
Profile.Name = "Registrar (Custom)",
Profile.Name = "System Administrator",
Profile.Name = "System Administrator (Clone)",
Profile.Name = "Read Only",
Profile.Name = "Partner Community User"
),
TRUE,
FALSE
)
This appears to be showing correctly when I look at the user records. However, when I run SOQL against it, the values are not correct. For example:SELECT Name, Id, Profile.Name, Username, Alias, Bogus_User__c, IsActive, Backend_User__c FROM User returns false for a System Administrator. I go to the user record, and the field is checked. As it should be.
What is causing this?
soql formula-field user
asked Sep 5 at 16:06
Chance
443210
This is throwing me for a loop. I have a custom field on user, Backend_User__c. The formula is pretty simple:
IF(
OR( Profile.Name = "API Only",
Profile.Name = "Boldchat API Only",
Profile.Name = "Business Intelligence (Custom)",
Profile.Name = "Business Office (Custom)",
Profile.Name = "Chat Representative (Custom)",
Profile.Name = "Compliance (Custom)",
Profile.Name = "GUAppDev API Only",
Profile.Name = "IT (Custom)",
Profile.Name = "IT - App Dev",
Profile.Name = "Marketing (Custom)",
Profile.Name = "Marketing User",
Profile.Name = "Read Only All Records",
Profile.Name = "Registrar (Custom)",
Profile.Name = "System Administrator",
Profile.Name = "System Administrator (Clone)",
Profile.Name = "Read Only",
Profile.Name = "Partner Community User"
),
TRUE,
FALSE
)
This appears to be showing correctly when I look at the user records. However, when I run SOQL against it, the values are not correct. For example:SELECT Name, Id, Profile.Name, Username, Alias, Bogus_User__c, IsActive, Backend_User__c FROM User returns false for a System Administrator. I go to the user record, and the field is checked. As it should be.
What is causing this?
soql formula-field user
asked Sep 5 at 16:06
Chance
443210
asked Sep 5 at 16:06
Chance
443210
asked Sep 5 at 16:06
Chance
443210
asked Sep 5 at 16:06
Chance
443210
443210
Can u verify field level security?
– Ayub
Sep 5 at 16:24
I can. I have read permission granted to every user. Also, it IS returning a value, False!
– Chance
Sep 5 at 16:25
You can always replaceIF(condition, true, false)withcondition. It's already the exact same boolean value.
– Adrian Larson♦
Sep 5 at 16:59
add a comment |Â
Can u verify field level security?
– Ayub
Sep 5 at 16:24
I can. I have read permission granted to every user. Also, it IS returning a value, False!
– Chance
Sep 5 at 16:25
You can always replaceIF(condition, true, false)withcondition. It's already the exact same boolean value.
– Adrian Larson♦
Sep 5 at 16:59
Can u verify field level security?
– Ayub
Sep 5 at 16:24
Can u verify field level security?
– Ayub
Sep 5 at 16:24
I can. I have read permission granted to every user. Also, it IS returning a value, False!
– Chance
Sep 5 at 16:25
I can. I have read permission granted to every user. Also, it IS returning a value, False!
– Chance
Sep 5 at 16:25
You can always replace
IF(condition, true, false) with condition. It's already the exact same boolean value.– Adrian Larson♦
Sep 5 at 16:59
You can always replace
IF(condition, true, false) with condition. It's already the exact same boolean value.– Adrian Larson♦
Sep 5 at 16:59
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
6
down vote
accepted
This is a Known Issue that can occur. It goes way back to the days when the standard profiles were named PTX, where X was a number. They still occasionally end up showing up in odd places, and you've found one of them.
As a matter of practicality, you don't need an "if" statement here, since your intent is to return true/false. You can also optimize this with a CASE statement. Here's the resulting formula:
1 = CASE(
Profile.Name,
"API Only", 1,
"Boldchat API Only", 1,
"Business Intelligence (Custom)", 1,
"Business Office (Custom)", 1,
"Chat Representative (Custom)", 1,
"Compliance (Custom)", 1,
"GUAppDev API Only", 1,
"IT (Custom)", 1,
"IT - App Dev", 1,
"Marketing (Custom)", 1,
"Marketing User", 1, "PT5", 1,
"Read Only All Records", 1,
"Registrar (Custom)", 1,
"System Administrator", 1, "PT1", 1,
"System Administrator (Clone)", 1,
"Read Only", 1, "PT3", 1,
"Partner Community User" 1,
0
)
This formula presumes that the bug will eventually be fixed, so includes the PTX variants for standard profile names that are affected. The full list is mentioned in this help topic.
answered Sep 5 at 16:37
sfdcfox
226k10172387
Hmm interesting - one for the ancient bug that I've never heard of and two for your interesting logic... is it true that the system auto evaluates 1=0 as False? (it is false, of course, but the auto evaluation is unexpected)
– Caspar Harmer
Sep 5 at 17:51
This looks great and I made the changes you suggest, but the problem still persists. Specifically for the system admin, which is mapped to PT1, correct? Would I be better suited to just use the IDs of the profile? And since this is a sandbox, would the profile IDs translate into Production (provided they were refreshed from production)?
– Chance
Sep 5 at 17:55
1
@CasparHarmer Yes, it's a workaround since CASE cannot return true/false directly. It's often used as a shortcut when you want to compare one field to many values. All of the Boolean operators return a true/false result you can use directly.
– sfdcfox
Sep 5 at 18:07
1
@Chance I would consider that a last resort, but should be acceptable if you can't find any other way. I would suggest experimenting with a formula that just returns Profile.Name directly to see what the results are, then use those values.
– sfdcfox
Sep 5 at 18:08
1
@Chance If the checkbox is checked, the SOQL should find it, especially since you also queried Profile.Name. You may need to contact Support if the problem persists. I'm not sure there's much more we can do for you.
– sfdcfox
Sep 5 at 18:33
 |Â
show 2 more comments
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
6
down vote
accepted
This is a Known Issue that can occur. It goes way back to the days when the standard profiles were named PTX, where X was a number. They still occasionally end up showing up in odd places, and you've found one of them.
As a matter of practicality, you don't need an "if" statement here, since your intent is to return true/false. You can also optimize this with a CASE statement. Here's the resulting formula:
1 = CASE(
Profile.Name,
"API Only", 1,
"Boldchat API Only", 1,
"Business Intelligence (Custom)", 1,
"Business Office (Custom)", 1,
"Chat Representative (Custom)", 1,
"Compliance (Custom)", 1,
"GUAppDev API Only", 1,
"IT (Custom)", 1,
"IT - App Dev", 1,
"Marketing (Custom)", 1,
"Marketing User", 1, "PT5", 1,
"Read Only All Records", 1,
"Registrar (Custom)", 1,
"System Administrator", 1, "PT1", 1,
"System Administrator (Clone)", 1,
"Read Only", 1, "PT3", 1,
"Partner Community User" 1,
0
)
This formula presumes that the bug will eventually be fixed, so includes the PTX variants for standard profile names that are affected. The full list is mentioned in this help topic.
answered Sep 5 at 16:37
sfdcfox
226k10172387
Hmm interesting - one for the ancient bug that I've never heard of and two for your interesting logic... is it true that the system auto evaluates 1=0 as False? (it is false, of course, but the auto evaluation is unexpected)
– Caspar Harmer
Sep 5 at 17:51
This looks great and I made the changes you suggest, but the problem still persists. Specifically for the system admin, which is mapped to PT1, correct? Would I be better suited to just use the IDs of the profile? And since this is a sandbox, would the profile IDs translate into Production (provided they were refreshed from production)?
– Chance
Sep 5 at 17:55
1
@CasparHarmer Yes, it's a workaround since CASE cannot return true/false directly. It's often used as a shortcut when you want to compare one field to many values. All of the Boolean operators return a true/false result you can use directly.
– sfdcfox
Sep 5 at 18:07
1
@Chance I would consider that a last resort, but should be acceptable if you can't find any other way. I would suggest experimenting with a formula that just returns Profile.Name directly to see what the results are, then use those values.
– sfdcfox
Sep 5 at 18:08
1
@Chance If the checkbox is checked, the SOQL should find it, especially since you also queried Profile.Name. You may need to contact Support if the problem persists. I'm not sure there's much more we can do for you.
– sfdcfox
Sep 5 at 18:33
 |Â
show 2 more comments
up vote
6
down vote
accepted
This is a Known Issue that can occur. It goes way back to the days when the standard profiles were named PTX, where X was a number. They still occasionally end up showing up in odd places, and you've found one of them.
As a matter of practicality, you don't need an "if" statement here, since your intent is to return true/false. You can also optimize this with a CASE statement. Here's the resulting formula:
1 = CASE(
Profile.Name,
"API Only", 1,
"Boldchat API Only", 1,
"Business Intelligence (Custom)", 1,
"Business Office (Custom)", 1,
"Chat Representative (Custom)", 1,
"Compliance (Custom)", 1,
"GUAppDev API Only", 1,
"IT (Custom)", 1,
"IT - App Dev", 1,
"Marketing (Custom)", 1,
"Marketing User", 1, "PT5", 1,
"Read Only All Records", 1,
"Registrar (Custom)", 1,
"System Administrator", 1, "PT1", 1,
"System Administrator (Clone)", 1,
"Read Only", 1, "PT3", 1,
"Partner Community User" 1,
0
)
This formula presumes that the bug will eventually be fixed, so includes the PTX variants for standard profile names that are affected. The full list is mentioned in this help topic.
answered Sep 5 at 16:37
sfdcfox
226k10172387
Hmm interesting - one for the ancient bug that I've never heard of and two for your interesting logic... is it true that the system auto evaluates 1=0 as False? (it is false, of course, but the auto evaluation is unexpected)
– Caspar Harmer
Sep 5 at 17:51
This looks great and I made the changes you suggest, but the problem still persists. Specifically for the system admin, which is mapped to PT1, correct? Would I be better suited to just use the IDs of the profile? And since this is a sandbox, would the profile IDs translate into Production (provided they were refreshed from production)?
– Chance
Sep 5 at 17:55
1
@CasparHarmer Yes, it's a workaround since CASE cannot return true/false directly. It's often used as a shortcut when you want to compare one field to many values. All of the Boolean operators return a true/false result you can use directly.
– sfdcfox
Sep 5 at 18:07
1
@Chance I would consider that a last resort, but should be acceptable if you can't find any other way. I would suggest experimenting with a formula that just returns Profile.Name directly to see what the results are, then use those values.
– sfdcfox
Sep 5 at 18:08
1
@Chance If the checkbox is checked, the SOQL should find it, especially since you also queried Profile.Name. You may need to contact Support if the problem persists. I'm not sure there's much more we can do for you.
– sfdcfox
Sep 5 at 18:33
 |Â
show 2 more comments
up vote
6
down vote
accepted
up vote
6
down vote
accepted
This is a Known Issue that can occur. It goes way back to the days when the standard profiles were named PTX, where X was a number. They still occasionally end up showing up in odd places, and you've found one of them.
As a matter of practicality, you don't need an "if" statement here, since your intent is to return true/false. You can also optimize this with a CASE statement. Here's the resulting formula:
1 = CASE(
Profile.Name,
"API Only", 1,
"Boldchat API Only", 1,
"Business Intelligence (Custom)", 1,
"Business Office (Custom)", 1,
"Chat Representative (Custom)", 1,
"Compliance (Custom)", 1,
"GUAppDev API Only", 1,
"IT (Custom)", 1,
"IT - App Dev", 1,
"Marketing (Custom)", 1,
"Marketing User", 1, "PT5", 1,
"Read Only All Records", 1,
"Registrar (Custom)", 1,
"System Administrator", 1, "PT1", 1,
"System Administrator (Clone)", 1,
"Read Only", 1, "PT3", 1,
"Partner Community User" 1,
0
)
This formula presumes that the bug will eventually be fixed, so includes the PTX variants for standard profile names that are affected. The full list is mentioned in this help topic.
answered Sep 5 at 16:37
sfdcfox
226k10172387
This is a Known Issue that can occur. It goes way back to the days when the standard profiles were named PTX, where X was a number. They still occasionally end up showing up in odd places, and you've found one of them.
As a matter of practicality, you don't need an "if" statement here, since your intent is to return true/false. You can also optimize this with a CASE statement. Here's the resulting formula:
1 = CASE(
Profile.Name,
"API Only", 1,
"Boldchat API Only", 1,
"Business Intelligence (Custom)", 1,
"Business Office (Custom)", 1,
"Chat Representative (Custom)", 1,
"Compliance (Custom)", 1,
"GUAppDev API Only", 1,
"IT (Custom)", 1,
"IT - App Dev", 1,
"Marketing (Custom)", 1,
"Marketing User", 1, "PT5", 1,
"Read Only All Records", 1,
"Registrar (Custom)", 1,
"System Administrator", 1, "PT1", 1,
"System Administrator (Clone)", 1,
"Read Only", 1, "PT3", 1,
"Partner Community User" 1,
0
)
This formula presumes that the bug will eventually be fixed, so includes the PTX variants for standard profile names that are affected. The full list is mentioned in this help topic.
answered Sep 5 at 16:37
sfdcfox
226k10172387
answered Sep 5 at 16:37
sfdcfox
226k10172387
answered Sep 5 at 16:37
sfdcfox
226k10172387
answered Sep 5 at 16:37
sfdcfox
226k10172387
226k10172387
Hmm interesting - one for the ancient bug that I've never heard of and two for your interesting logic... is it true that the system auto evaluates 1=0 as False? (it is false, of course, but the auto evaluation is unexpected)
– Caspar Harmer
Sep 5 at 17:51
This looks great and I made the changes you suggest, but the problem still persists. Specifically for the system admin, which is mapped to PT1, correct? Would I be better suited to just use the IDs of the profile? And since this is a sandbox, would the profile IDs translate into Production (provided they were refreshed from production)?
– Chance
Sep 5 at 17:55
1
@CasparHarmer Yes, it's a workaround since CASE cannot return true/false directly. It's often used as a shortcut when you want to compare one field to many values. All of the Boolean operators return a true/false result you can use directly.
– sfdcfox
Sep 5 at 18:07
1
@Chance I would consider that a last resort, but should be acceptable if you can't find any other way. I would suggest experimenting with a formula that just returns Profile.Name directly to see what the results are, then use those values.
– sfdcfox
Sep 5 at 18:08
1
@Chance If the checkbox is checked, the SOQL should find it, especially since you also queried Profile.Name. You may need to contact Support if the problem persists. I'm not sure there's much more we can do for you.
– sfdcfox
Sep 5 at 18:33
 |Â
show 2 more comments
Hmm interesting - one for the ancient bug that I've never heard of and two for your interesting logic... is it true that the system auto evaluates 1=0 as False? (it is false, of course, but the auto evaluation is unexpected)
– Caspar Harmer
Sep 5 at 17:51
This looks great and I made the changes you suggest, but the problem still persists. Specifically for the system admin, which is mapped to PT1, correct? Would I be better suited to just use the IDs of the profile? And since this is a sandbox, would the profile IDs translate into Production (provided they were refreshed from production)?
– Chance
Sep 5 at 17:55
1
@CasparHarmer Yes, it's a workaround since CASE cannot return true/false directly. It's often used as a shortcut when you want to compare one field to many values. All of the Boolean operators return a true/false result you can use directly.
– sfdcfox
Sep 5 at 18:07
1
@Chance I would consider that a last resort, but should be acceptable if you can't find any other way. I would suggest experimenting with a formula that just returns Profile.Name directly to see what the results are, then use those values.
– sfdcfox
Sep 5 at 18:08
1
@Chance If the checkbox is checked, the SOQL should find it, especially since you also queried Profile.Name. You may need to contact Support if the problem persists. I'm not sure there's much more we can do for you.
– sfdcfox
Sep 5 at 18:33
Hmm interesting - one for the ancient bug that I've never heard of and two for your interesting logic... is it true that the system auto evaluates 1=0 as False? (it is false, of course, but the auto evaluation is unexpected)
– Caspar Harmer
Sep 5 at 17:51
Hmm interesting - one for the ancient bug that I've never heard of and two for your interesting logic... is it true that the system auto evaluates 1=0 as False? (it is false, of course, but the auto evaluation is unexpected)
– Caspar Harmer
Sep 5 at 17:51
This looks great and I made the changes you suggest, but the problem still persists. Specifically for the system admin, which is mapped to PT1, correct? Would I be better suited to just use the IDs of the profile? And since this is a sandbox, would the profile IDs translate into Production (provided they were refreshed from production)?
– Chance
Sep 5 at 17:55
This looks great and I made the changes you suggest, but the problem still persists. Specifically for the system admin, which is mapped to PT1, correct? Would I be better suited to just use the IDs of the profile? And since this is a sandbox, would the profile IDs translate into Production (provided they were refreshed from production)?
– Chance
Sep 5 at 17:55
1
1
@CasparHarmer Yes, it's a workaround since CASE cannot return true/false directly. It's often used as a shortcut when you want to compare one field to many values. All of the Boolean operators return a true/false result you can use directly.
– sfdcfox
Sep 5 at 18:07
@CasparHarmer Yes, it's a workaround since CASE cannot return true/false directly. It's often used as a shortcut when you want to compare one field to many values. All of the Boolean operators return a true/false result you can use directly.
– sfdcfox
Sep 5 at 18:07
1
1
@Chance I would consider that a last resort, but should be acceptable if you can't find any other way. I would suggest experimenting with a formula that just returns Profile.Name directly to see what the results are, then use those values.
– sfdcfox
Sep 5 at 18:08
@Chance I would consider that a last resort, but should be acceptable if you can't find any other way. I would suggest experimenting with a formula that just returns Profile.Name directly to see what the results are, then use those values.
– sfdcfox
Sep 5 at 18:08
1
1
@Chance If the checkbox is checked, the SOQL should find it, especially since you also queried Profile.Name. You may need to contact Support if the problem persists. I'm not sure there's much more we can do for you.
– sfdcfox
Sep 5 at 18:33
@Chance If the checkbox is checked, the SOQL should find it, especially since you also queried Profile.Name. You may need to contact Support if the problem persists. I'm not sure there's much more we can do for you.
– sfdcfox
Sep 5 at 18:33
 |Â
show 2 more comments
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f231350%2fsoql-returning-incorrect-value%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Can u verify field level security?
– Ayub
Sep 5 at 16:24
I can. I have read permission granted to every user. Also, it IS returning a value, False!
– Chance
Sep 5 at 16:25
You can always replace
IF(condition, true, false)withcondition. It's already the exact same boolean value.– Adrian Larson♦
Sep 5 at 16:59