In this case a filter need to evaluate something. You should filter something like this:
id = ExecuteSQL("SELECT id FROM contacts WHERE name = 'joe'"; ""; "")
I'm not sure why you are using "name" to search/return and then supplying the literal name
WHERE name = 'joe'
I think perhaps you want to select the id?
Also you may nave reserved words that need to be escaped.
" SELECT \"id\"
WHERE \"name\" = 'joe'
Tom was correct to assign the
id = <<executesql here>>
because that was the 'filtering' where only matches will show.
you are correct:
will not work as you still need values to match for the filter to work.
I don't think that you understand how portal filters work. Portal filter calculations are expressions that:
- Are Boolean, they must evaluate to True or False results
- Are evaluated against every related record that would appear in your portal if there were no filter. If the expression returns true for that record, the record is allowed to appear in the portal.
I think you should back up a few steps and explain:
a) The relationship on which the portal is based
b) What you are trying to accomplish with the portal filter
Since ExecuteSQL can produce a return separated list of values, I am more likely to use this type of calculation to produce a return separated list of Primary Keys that I place in a match field in the portal's relationship, than try to apply an SQL query against each individual related record--which could have a massive performance cost if there are a lot of related records.
"this did not work" isn't something that helps others to help you very much. If you can describe exactly how it didn't work, that will help others to better help you.
I just had little more play around. What I've noticed is that ExecuteSQL looks for single match.
id = ExecuteSQL("SELECT id FROM contacts WHERE name = 'joe'"; ""; "")Above statement will only work if one person name is joe.But If I have 2 people names as joe in my database, the above calculation will break.How will do filter If I have more than one people named as "Joe".Thank you.
Phil is right about having a relationship to another table. This makes the end result easier and much less costly.
But if you must...Maybe:
PatternCount( ExecuteSQL("SELECT id FROM contacts WHERE name = 'joe'"; ""; ""); id)
Position ( ¶ & ExecuteSQL("SELECT id FROM contacts WHERE name = 'joe'"; ""; "") & ¶ ; ¶ & id & ¶ ; 1 ; 1 )
This is also a better way to build the query:
ExecuteSQL("SELECT \"id\" FROM \"contacts\" WHERE \"name\" = ?"; ""; ""; "joe")
be aware that the ExecuteSQL statement can slow down your solution significantly if you have more then a couple of hundred records in the table of the portal if you have any open records of that related table or
if you allow editing in that portal the records ..
So far, what I have seen here suggests, that for a portal to contacts, you could replace your SQL query with:
Contacts::Name = "Joe"
Using ExecuteSQL this way is something I would try to avoid.