Merge Syntax Subquery Conditions

  • 1
  • Question
  • Updated 2 months ago
  • Answered
I need some guidance on subqueries. Can you use merge syntax in a subquery? I have an invoice line object connected to a Rebate Line object via the item object. I want a subquery condition that looks at the invoice date and see if it is in between the start and end date found on the rebate line. 

I get the below error:

1. An error occurred while attempting to perform the following SOQL query: SELECT SCMC__Invoicing__r.SCMC__Invoice_Date__c,SCMC__Item__c,SCMC__Item__r.Name,SCMC__Applied_Rebate_Amount__c,Id,CurrencyIsoCode FROM SCMC__Invoice_Line_Item__c WHERE (SCMC__Item__c in (SELECT SCMC__Item__c FROM SCMC__Rebate_Line__c WHERE (((SCMC__Rebate__r.SCMC__Start_Date__c >= SCMC__Invoicing__r.SCMC__Invoice_Date__c)AND(SCMC__Rebate__r.SCMC__End_Date__c <= SCMC__Invoicing__r.SCMC__Invoice_Date__c)))))AND(SCMC__Applied_Rebate_Amount__c != null) LIMIT 21 Error:expecting a colon, found 'SCMC__Invoicing__r.SCMC__Invoice_Date__c'
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb

Posted 2 years ago

  • 1
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Hard to understand the error, but I think you could use "Field from Another Model" for the value.
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb
The "Field from Another Model" only looks at the first line of the model. I can get a flag created with ui-only fields to accomplish this but because I can't use the ui-only field in a condition I can't filter the actual data.
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
I see. You only want to display invoice line items where the invoice date is between the start or end dates of rebates.

I  assume these records don't have any direct relationships?
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb
Exactly! You are correct there is not a direct relationship. 
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
So, by extension, you would display line items for the invoice(s) who's date is between the start and end date of the rebates.

I'd create agg models for the rebate records for max and min. You can then use "Field from Another Model".
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb
Ahh that is a very good idea. Let me give it a shot. Thanks Pat.
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb
Pat - I tried your suggestion but using the "Field from Another Model" for the Agg field still only looks at the first row when using greater or less than.
Photo of Nicholas L.

Nicholas L.

  • 382 Points 250 badge 2x thumb
So global merge syntax in the sub-conditional logic of a conditional sub-query is not possible i take it?