Subquerying filtering on main query field
My intention is to filter the child records which are != to a field of the master.
Account: Master
Application__c : Child
Using Subquery:
select acc.id,acc.Callista_Org_Unit_Code__c , (select Agent_Org_Unit_Code__c from Applications__r)from Account acc
I would like to add the following filter in the subquery:
acc.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c
This is not allowed in SOQL.
Or alternatively, filter from a child record:
select id,Agent_Org_Unit_Code__c,Agent__r.Callista_Org_Unit_Code__c from Application__c where Agent__r.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c
This also gives an error.
Is there any way I can build out these filters?
apex soql
edited Feb 11 at 23:30
Adrian Larson♦
109k19115246
asked Feb 11 at 23:29
Shalini SFShalini SF
450615
add a comment |
My intention is to filter the child records which are != to a field of the master.
Account: Master
Application__c : Child
Using Subquery:
select acc.id,acc.Callista_Org_Unit_Code__c , (select Agent_Org_Unit_Code__c from Applications__r)from Account acc
I would like to add the following filter in the subquery:
acc.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c
This is not allowed in SOQL.
Or alternatively, filter from a child record:
select id,Agent_Org_Unit_Code__c,Agent__r.Callista_Org_Unit_Code__c from Application__c where Agent__r.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c
This also gives an error.
Is there any way I can build out these filters?
apex soql
edited Feb 11 at 23:30
Adrian Larson♦
109k19115246
asked Feb 11 at 23:29
Shalini SFShalini SF
450615
add a comment |
My intention is to filter the child records which are != to a field of the master.
Account: Master
Application__c : Child
Using Subquery:
select acc.id,acc.Callista_Org_Unit_Code__c , (select Agent_Org_Unit_Code__c from Applications__r)from Account acc
I would like to add the following filter in the subquery:
acc.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c
This is not allowed in SOQL.
Or alternatively, filter from a child record:
select id,Agent_Org_Unit_Code__c,Agent__r.Callista_Org_Unit_Code__c from Application__c where Agent__r.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c
This also gives an error.
Is there any way I can build out these filters?
apex soql
edited Feb 11 at 23:30
Adrian Larson♦
109k19115246
asked Feb 11 at 23:29
Shalini SFShalini SF
450615
My intention is to filter the child records which are != to a field of the master.
Account: Master
Application__c : Child
Using Subquery:
select acc.id,acc.Callista_Org_Unit_Code__c , (select Agent_Org_Unit_Code__c from Applications__r)from Account acc
I would like to add the following filter in the subquery:
acc.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c
This is not allowed in SOQL.
Or alternatively, filter from a child record:
select id,Agent_Org_Unit_Code__c,Agent__r.Callista_Org_Unit_Code__c from Application__c where Agent__r.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c
This also gives an error.
Is there any way I can build out these filters?
apex soql
apex soql
edited Feb 11 at 23:30
Adrian Larson♦
109k19115246
asked Feb 11 at 23:29
Shalini SFShalini SF
450615
edited Feb 11 at 23:30
Adrian Larson♦
109k19115246
asked Feb 11 at 23:29
Shalini SFShalini SF
450615
edited Feb 11 at 23:30
Adrian Larson♦
109k19115246
edited Feb 11 at 23:30
Adrian Larson♦
109k19115246
edited Feb 11 at 23:30
Adrian Larson♦
109k19115246
109k19115246
asked Feb 11 at 23:29
Shalini SFShalini SF
450615
asked Feb 11 at 23:29
Shalini SFShalini SF
450615
asked Feb 11 at 23:29
Shalini SFShalini SF
450615
450615
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Please note from the SOQL and SOSL Reference that you cannot compare one field to another.
fieldExpression Syntax
The field expression syntax of the WHERE clause in a SOQL query consists of a field name, a comparison operator, and a value that’s used to compare with the value in the field name.
fieldExpressionuses the following syntax:
fieldName comparisonOperator value
where:
fieldName
The name of a field in the specified object. Use of single or double quotes around the name will result in an error. You must have at least read-level permissions to the field. It can be any field except a long text area field, encrypted data field, or base64-encoded field. It does not need to be a field in the fieldList.
comparisonOperator
Case-insensitive operators that compare values.
value
A value used to compare with the value in fieldName. You must supply a value whose data type matches the field type of the specified field. You must supply a native value—other field names or calculations are not permitted. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error.
Again, note:
You must supply a native value—other field names or calculations are not permitted.
This is a very common shortcoming of SOQL to need to circumvent. The standard workaround is to use a formula.
You need to create a formula on the child record to basically extend SOQL in this way. Name your formula something like Org_Unit_Code_Matches_Parent__c with an output type of Boolean, then simply set it to:
Agent_Org_Unit_Code__c = Account__r.Callista_Org_Unit_Code__c
The above assumes the lookup field has an API Name of Account__c.
answered Feb 11 at 23:32
Adrian Larson♦Adrian Larson
109k19115246
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "459"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f249905%2fsubquerying-filtering-on-main-query-field%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Please note from the SOQL and SOSL Reference that you cannot compare one field to another.
fieldExpression Syntax
The field expression syntax of the WHERE clause in a SOQL query consists of a field name, a comparison operator, and a value that’s used to compare with the value in the field name.
fieldExpressionuses the following syntax:
fieldName comparisonOperator value
where:
fieldName
The name of a field in the specified object. Use of single or double quotes around the name will result in an error. You must have at least read-level permissions to the field. It can be any field except a long text area field, encrypted data field, or base64-encoded field. It does not need to be a field in the fieldList.
comparisonOperator
Case-insensitive operators that compare values.
value
A value used to compare with the value in fieldName. You must supply a value whose data type matches the field type of the specified field. You must supply a native value—other field names or calculations are not permitted. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error.
Again, note:
You must supply a native value—other field names or calculations are not permitted.
This is a very common shortcoming of SOQL to need to circumvent. The standard workaround is to use a formula.
You need to create a formula on the child record to basically extend SOQL in this way. Name your formula something like Org_Unit_Code_Matches_Parent__c with an output type of Boolean, then simply set it to:
Agent_Org_Unit_Code__c = Account__r.Callista_Org_Unit_Code__c
The above assumes the lookup field has an API Name of Account__c.
answered Feb 11 at 23:32
Adrian Larson♦Adrian Larson
109k19115246
add a comment |
Please note from the SOQL and SOSL Reference that you cannot compare one field to another.
fieldExpression Syntax
The field expression syntax of the WHERE clause in a SOQL query consists of a field name, a comparison operator, and a value that’s used to compare with the value in the field name.
fieldExpressionuses the following syntax:
fieldName comparisonOperator value
where:
fieldName
The name of a field in the specified object. Use of single or double quotes around the name will result in an error. You must have at least read-level permissions to the field. It can be any field except a long text area field, encrypted data field, or base64-encoded field. It does not need to be a field in the fieldList.
comparisonOperator
Case-insensitive operators that compare values.
value
A value used to compare with the value in fieldName. You must supply a value whose data type matches the field type of the specified field. You must supply a native value—other field names or calculations are not permitted. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error.
Again, note:
You must supply a native value—other field names or calculations are not permitted.
This is a very common shortcoming of SOQL to need to circumvent. The standard workaround is to use a formula.
You need to create a formula on the child record to basically extend SOQL in this way. Name your formula something like Org_Unit_Code_Matches_Parent__c with an output type of Boolean, then simply set it to:
Agent_Org_Unit_Code__c = Account__r.Callista_Org_Unit_Code__c
The above assumes the lookup field has an API Name of Account__c.
answered Feb 11 at 23:32
Adrian Larson♦Adrian Larson
109k19115246
add a comment |
Please note from the SOQL and SOSL Reference that you cannot compare one field to another.
fieldExpression Syntax
The field expression syntax of the WHERE clause in a SOQL query consists of a field name, a comparison operator, and a value that’s used to compare with the value in the field name.
fieldExpressionuses the following syntax:
fieldName comparisonOperator value
where:
fieldName
The name of a field in the specified object. Use of single or double quotes around the name will result in an error. You must have at least read-level permissions to the field. It can be any field except a long text area field, encrypted data field, or base64-encoded field. It does not need to be a field in the fieldList.
comparisonOperator
Case-insensitive operators that compare values.
value
A value used to compare with the value in fieldName. You must supply a value whose data type matches the field type of the specified field. You must supply a native value—other field names or calculations are not permitted. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error.
Again, note:
You must supply a native value—other field names or calculations are not permitted.
This is a very common shortcoming of SOQL to need to circumvent. The standard workaround is to use a formula.
You need to create a formula on the child record to basically extend SOQL in this way. Name your formula something like Org_Unit_Code_Matches_Parent__c with an output type of Boolean, then simply set it to:
Agent_Org_Unit_Code__c = Account__r.Callista_Org_Unit_Code__c
The above assumes the lookup field has an API Name of Account__c.
answered Feb 11 at 23:32
Adrian Larson♦Adrian Larson
109k19115246
Please note from the SOQL and SOSL Reference that you cannot compare one field to another.
fieldExpression Syntax
The field expression syntax of the WHERE clause in a SOQL query consists of a field name, a comparison operator, and a value that’s used to compare with the value in the field name.
fieldExpressionuses the following syntax:
fieldName comparisonOperator value
where:
fieldName
The name of a field in the specified object. Use of single or double quotes around the name will result in an error. You must have at least read-level permissions to the field. It can be any field except a long text area field, encrypted data field, or base64-encoded field. It does not need to be a field in the fieldList.
comparisonOperator
Case-insensitive operators that compare values.
value
A value used to compare with the value in fieldName. You must supply a value whose data type matches the field type of the specified field. You must supply a native value—other field names or calculations are not permitted. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error.
Again, note:
You must supply a native value—other field names or calculations are not permitted.
This is a very common shortcoming of SOQL to need to circumvent. The standard workaround is to use a formula.
You need to create a formula on the child record to basically extend SOQL in this way. Name your formula something like Org_Unit_Code_Matches_Parent__c with an output type of Boolean, then simply set it to:
Agent_Org_Unit_Code__c = Account__r.Callista_Org_Unit_Code__c
The above assumes the lookup field has an API Name of Account__c.
answered Feb 11 at 23:32
Adrian Larson♦Adrian Larson
109k19115246
edited Feb 12 at 3:37
answered Feb 11 at 23:32
Adrian Larson♦Adrian Larson
109k19115246
answered Feb 11 at 23:32
Adrian Larson♦Adrian Larson
109k19115246
answered Feb 11 at 23:32
Adrian Larson♦Adrian Larson
109k19115246
109k19115246
add a comment |
add a comment |
Thanks for contributing an answer to Salesforce Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f249905%2fsubquerying-filtering-on-main-query-field%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
