Using url params in a subquery

  • 1
  • Question
  • Updated 4 years ago
  • Answered
Hi, 

I have a page to list all the products of a country (with a query param in the url: 'countryid').

Each product has a supplier.

The supplier doesn't have any relation with the country.

I'm trying to do a filter in the table component with all the products to filter by supplier.

The problem is If I set the filter based in the model data in the select box shows one file per product :



So, I have to try another approach: create a model. The problem is the suppliers and the country doesn't have a relation. 1 supplier sells in a country if there are products of this supplier in the country.

So I have tried to load a model with this condition:



And:

1. An error occurred while attempting to perform the following SOQL query: SELECT Name,Id FROM Account WHERE (Id in (SELECT Supplier__c FROM Purchase_Product__c WHERE ((COUNTRY__C=\'{{$PARAMS.COUNTRYID}}\'))))AND(RecordType.Name = 'provider') LIMIT 21 Error:line 1:105 no viable alternative at character '\'

Other things I have tried:

Country__c=a01m0000000vemlAAA (this is the id of a country)

WHERE ((COUNTRY__C=A01M0000000VEMLAAA)))) Error:expecting a colon, found 'A01M0000000VEMLAAA'

Country__c='a01m0000000vemlAAA'

WHERE ((COUNTRY__C=\'A01M0000000VEMLAAA\')))) LIMIT 21 Error:line 1:105 no viable alternative at character '\'

What I want?
Make a filter with the different providers of the products:
- with a distinct in the filter fields I will be happy :)
- Use the Param countryid in the subquery to load the model
Photo of Pablo Martinez

Pablo Martinez

  • 2,468 Points 2k badge 2x thumb

Posted 4 years ago

  • 1
Photo of Pablo Martinez

Pablo Martinez

  • 2,468 Points 2k badge 2x thumb
OK I have it,



The problem is now if I change the supplier of a product the filter data is not updated... but this is not very important
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
duplicate the model for the products. Change the model type to aggregate. Group by the supplier name. Group by the ID. Apply this new model to your product table filter. Display the name. Provide the ID for the condition. just make sure that only your URL parameter is used in this new model.
(Edited)
Photo of Pablo Martinez

Pablo Martinez

  • 2,468 Points 2k badge 2x thumb
yeah, I haven't think that. But I think my final approach has the better perfomance.
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Actually Pat has the canonical method for creating a filter on a string feild. Take a look at this document.  Look specifically at use case 6.  
Photo of Pablo Martinez

Pablo Martinez

  • 2,468 Points 2k badge 2x thumb
Ok, I will try that way
Photo of Menachem Shanowitz

Menachem Shanowitz

  • 1,074 Points 1k badge 2x thumb
I have a similar problem but a different use case. I want to put a condition on Model 'A' that the Id field is in the set of values of a field in Model 'B'.

The issue is that (for heap purposes) I want to put a limit on Model 'A', the problem with that is that when I search on Model 'B' its limited to the results of what has loaded in Model 'A'.

I tried using a subquery instead that uses the object of Model 'A', however, I cannot put any 'dynamic' data in Subcondition Logic. Either the Id parameter or a field from another model would do the trick but its reading it as plain text.
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
I'd help, but my head hurts already today. Can you take some screenshots or video. Not sure I could understand your use case from the text above.
Photo of Menachem Shanowitz

Menachem Shanowitz

  • 1,074 Points 1k badge 2x thumb

Household is Model 'A' and CampaignMember is Model 'B'.
Note: Since we are using Person Accounts, we put a custom Account__c field on CampaignMember
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Oh. That subcondition logic is for when you have multiple subqueries. ie. (1 OR 2) AND 3.

Your next step is to create a subquery. That query will contain a condition for Field = CampaignID & Value is {{$Param.Id}}.



I mocked this up in one of my conditions. Just swap out:
- Quote_Component__c for CampaignMember
- Component__c for CampaignID

Photo of Menachem Shanowitz

Menachem Shanowitz

  • 1,074 Points 1k badge 2x thumb
That was simple, I did not notice that add a sub-condition button. Thanks. It still doesn't work for my case though because "The inner select field 'Account__r.Primary_Household__c' cannot have more than one level of relationships"
(Edited)