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

edited September 2019 in Questions
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)

Comments

  • edited April 2018
    Maybe try using "contains"on the condition rather than = ? I think this might work for you.
  • edited September 2017
    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.
  • edited September 2019
    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>
Sign In or Register to comment.