Prefilter in Recordlist for 1-to-many relation

🧩 Low-Code Development
#1

Corteza version: 2023.3.1
Summary: I want to show all contacts that are assigned to a specific compaign in the compaigns record page (using the CRM Application as an example)

I added a Record list (module “Contact”) to the Compaign Record details page and set Prefilter to CompaignId = ${recordID}.
This works for one Compaign. However, if i assign multiple compaigns to a contact, then this works only for one compaign (did not try wether this is the first or last one assigned).

It somehow makes sense to me because the expression in Prefilter looks like a simple comparison. But because the help text below Prefilter states:

Simplified SQL condition (WHERE …) syntax is supported. Variables like ${recordID} , ${ownerID} and ${userID} are evaluated (when available)

i tried something like this ${recordID} IN CompaignId but that failed entirely.

I have not found any documenation about the “Simplified SQL condition/Where syntax” that is supported in Prefilter. Can someone help?

#2

If you are looking for a list of all campaigns that have a particular contact, I would add a record list on the contact page where the module for the record list is the campaign and the prefilter would be something like

Contact=${recordID}

Contact would be the field for the contact on the campaign record and then you’ll see all of the campaigns that match the recordID of the contact record you currently have open.

#3

Thank you - Yes this works. It would show “Compaigns by Contact”. However what i am also looking for is “Contacts by Compaign” - I open a compaign and see all related contacts.
In the default CRM application, Compaigns are defined as a “1->many” relationship on Contacts. So 1 Contact can have many compaigns.
I would like to see these assigned contacts, when opening a specific compaign and from what i understand, this would require a “Prefilter” that allows to search in an array instead of comparing with just one value (in this example the array of contacts->compaignid).

Generally speaking, this requirement exists in any similar use-case based on the built-in “1->many” relationship.

Since i was unable to find a proper “Prefilter” expression, the only workaround i see so far is to create a new module like “CompaignsContacts” where the module has 2 fields: ComapignId and ContactId.

But this requires to embed a recordlist in contacts and i prefer the more simple field based GUI approach using the built-in 1->many relationship that allows multiselects in just one field. This field also shows up nicely in the record page view and in list view.

#4

What happens if you do '${recordID}' IN CompaignId (notice the single quotes)?
Does it fail with an error or just doesn’t return anything?
Can you send over a cURL request from your browser’s network tab (do remove any sensitive information).

#5

'${recordID}' IN CompaignId does not work and fails in the same way as ${recordID} IN CompaignId

Here the cURL requests when hitting save after changing teh prefilter for both cases:

With: ${recordID} IN CompaignId

curl 'http://172.20.20.82:8080/api/compose/namespace/338002797230489603/module/338002798321598467/record/?query=(0+IN+CompaignId)&deleted=0&limit=20&incTotal=true&incPageNavigation=true&sort=RecordLabel+ASC' \
  -H 'Accept: application/json, text/plain, */*' \
  -H 'Accept-Language: en' \
  -H 'Authorization: Bearer eyJhbGxxxx' \
  -H 'Cache-Control: no-cache' \
  -H 'Connection: keep-alive' \
  -H 'Content-Language: en' \
  -H 'Cookie: jwt=eyJhbGxxxx; i18next=en' \
  -H 'Pragma: no-cache' \
  -H 'Referer: http://172.20.20.82:8080/compose/ns/crm/admin/pages/338002799898198019/builder?layoutID=338005175484678147' \
  -H 'User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36' \
  --compressed \
  --insecure

Response:

{
    "error": {
        "message": "unknown attribute \"CompaignId\" used in query expression"
    }
}

With: '${recordID}' IN CompaignId

curl 'http://172.20.20.82:8080/api/compose/namespace/338002797230489603/module/338002798321598467/record/?query=(%270%27+IN+CompaignId)&deleted=0&limit=20&incTotal=true&incPageNavigation=true&sort=RecordLabel+ASC' \
  -H 'Accept: application/json, text/plain, */*' \
  -H 'Accept-Language: en' \
  -H 'Authorization: Bearer eyJhbGxxxx' \
  -H 'Cache-Control: no-cache' \
  -H 'Connection: keep-alive' \
  -H 'Content-Language: en' \
  -H 'Cookie: jwt=eyJhbGxxxx; i18next=en' \
  -H 'Pragma: no-cache' \
  -H 'Referer: http://172.20.20.82:8080/compose/ns/crm/admin/pages/338002799898198019/builder?layoutID=338005175484678147' \
  -H 'User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36' \
  --compressed \
  --insecure

Response:

{
    "error": {
        "message": "unknown attribute \"CompaignId\" used in query expression"
    }
}

Strange: After reverting back to CampaignId = ${recordID}, i sometimes see the same error Could not load record list: unknown attribute "CompaignId" used in query expression. Trying to hit save mutiple times, somehow works.

I also saw this error in the console when opening the Compaign details page (with CampaignId = ${recordID} :

vue.esm.js:1906 Error: Can not use ${record...} variable in non-record pages
    at a.prepRecordList (RecordListBase.vue:1239:17)
    at a.handler (RecordListBase.vue:1018:14)
    at ot (vue.esm.js:1872:26)
    at e.$watch (vue.esm.js:4974:7)
    at br (vue.esm.js:4930:13)
    at yr (vue.esm.js:4912:7)
    at cr (vue.esm.js:4670:5)
    at e._init (vue.esm.js:5029:5)
    at new a (vue.esm.js:5177:12)
    at ln (vue.esm.js:3313:10)

Note: I mentioned a similar/same Error here, so it might not be related.

For reference: Here the block that i added to compaign record page:

image
image800Ă—674 73.2 KB

#6

@tjerman , have you had time to look into my reply to your question?

#7

@Wolfgang
Not sure if you noticed that you are refering to it as CompaignId but the field name is CampaignId, that is the only thing i see that could be causing this.

#8

@jfortun and others: It works. :grinning:

Basically the solution from @tjerman was correct. However i introduced a typo, that jfortun spotted: I misspelled CampaignId with CompaignId.

So the solution is to use this expression AND to put the variable in single quotes:

'${recordID}' IN CampaignId

Again thanks for the support and sorry for the typo.

1 Like
#9