How do I filter a model list results based on string of IDs?

  • 1
  • Question
  • Updated 1 year ago
  • Answered
I have a text field that I use to store a list of User ID values that are delimited by commas.

I want my User Model to use the comma delimited values as a condition for the rows that show up. (i.e. If the User ID is present in the comma delimited text field value then the row will show up).

How do I build a condition to do this? How should my text string be formatted (i.e. Should I add single quotes around each ID value, should I be adding the single quote in a special way to avoid it being escaped)
Photo of Joseph Ucuzoglu

Joseph Ucuzoglu

  • 1,074 Points 1k badge 2x thumb

Posted 1 year ago

  • 1
Photo of Matt Small

Matt Small

  • 1,182 Points 1k badge 2x thumb
Maybe try using "contains"on the condition rather than = ? I think this might work for you.
Photo of Joseph Ucuzoglu

Joseph Ucuzoglu

  • 1,074 Points 1k badge 2x thumb
I used contains and the SOQL produced is not valid. It produces something like IN ('ID1,ID2,ID3,ID4') when it needs to produce ('ID1','ID2','ID3','ID4') to be valid. I tried to kind of force in the extra single quotes but to avoid SQL injections they escape the quotes I introduce which then produces something like IN ('ID1\',\'ID2\',\'ID3\',\'ID4') which is also invalid SOQL.

Ultimately I think it would be a useful feature to be able to filter based on a delimited string value in field but its definitely an edge case so I can understand why it probably wasn't a consideration.
Photo of Mike Dwyer

Mike Dwyer

  • 3,390 Points 3k badge 2x thumb
Joseph - 

I'm not clear on what exactly you mean by "a text field...of values" in a skuid context. But I'm thinking you can do something with pageload javascript. I worked up a simple page to populate a model from the User object based on a comma-delimited list in a UI-Only model. You'll see the list as your and my last names on line 5. The javascript would be a tad simpler if you ran it from a button rather than pageload, but I wasn't sure of your use case.
<skuidpage unsavedchangeswarning="yes" personalizationmode="server" showsidebar="true" useviewportmeta="true" showheader="true">
    <models>
        <model id="List" query="true" createrowifnonefound="true" datasource="Ui-Only" processonclient="true">
            <fields>
                <field id="Names" displaytype="TEXT" label="Names" defaultvaluetype="fieldvalue" defaultValue="Dwyer,Ucuzoglu"/>
            </fields>
            <conditions/>
            <actions/>
        </model>
        <model id="Users" limit="20" query="false" createrowifnonefound="false" datasource="salesforce" type="" sobject="User">
            <fields>
                <field id="Name"/>
                <field id="Id"/>
            </fields>
            <conditions>
                <condition type="multiple" value="" field="LastName" operator="in" state="" inactive="false" mergefield="Names" novaluebehavior="noquery" enclosevalueinquotes="true">
                    <values/>
                </condition>
            </conditions>
            <actions/>
        </model>
    </models>
    <components>
        <skootable showconditions="true" showsavecancel="false" showerrorsinline="true" searchmethod="server" searchbox="true" showexportbuttons="false" pagesize="10" createrecords="false" model="Users" buttonposition="" mode="read" allowcolumnreordering="false" uniqueid="sk-2wRorU-180">
            <fields>
                <field id="Name" hideable="true" uniqueid="fi-2wRuVo-197"/>
                <field id="Id" hideable="true" uniqueid="fi-2wS4W4-215"/>
            </fields>
            <rowactions/>
            <massactions usefirstitemasdefault="true"/>
            <views>
                <view type="standard"/>
            </views>
        </skootable>
    </components>
    <resources>
        <labels/>
        <javascript>
            <jsitem location="inline" name="newInlineJS" cachelocation="false" url="" namespace="">(function(skuid){
var $ = skuid.$;
$(document.body).one('pageload',function(){
var listModel = skuid.model.getModel('List');
var list = listModel.data[0].Names.split(',');
console.log(list);
var userModel = skuid.model.getModel('Users');
var condition = userModel.conditions[0];
console.log(condition.values);
userModel.setCondition(condition,list);
console.log(condition.values);
userModel.updateData(function(result) { updateIsDone(result) } );
function updateIsDone(result)
{
return;
}
});
})(skuid);</jsitem>
        </javascript>
        <css/>
    </resources>
    <styles>
        <styleitem type="background" bgtype="none"/>
    </styles>
</skuidpage>