Model Merge condition on subquery field (Left Outer Join) doesn't update when the condition dependent model is refreshed

  • 1
  • Question
  • Updated 2 years ago
This is Brooklyn releaase and I’m loving the filters. It could end up being a partial delivery of this idea

https://community.skuid.com/skuid/topics/filter-table-row-by-sub-queried-object-field-values

 

Which is related to what I’m trying to do. Right now I’m querying the opportunity and subquerying the notes of the opportunity. However, I want to allow the user filter the subqueried notes that are shown. Here’s how I’m trying to solve that.

  1. Create an opportunity model
  2. As a model field in the opportunity model, subquery the notes object
  3. Add a condition to the subquery. Condition is ‘field from another model.’ (from model in the next step)
  4. Create a Notes model and add filter to the page so the user can filter on the notes model.
  5. Add on model action to the Notes model so that when Notes is requeried (i.e. filtered), the opportunity is requeried and the subqueried noted recalculates its condition.

 

The problem is that for some reason when the opportunity is requeried, the model merge values don’t update. They remain whatever they were on page load. This wouldn't be such an issue but a side problem is that subqueried field conditions aren't available through javascript as regular condition objects meaning this can't be solved through javascript.

 

Steps I took to verify my process:

  1. Add model action on requery of notes and opportunity model that blocks the UI and displays “{{modelname}} successfully requeried” to confirm each model is being queried in the correct order. Result is that querying order is happening as expected.
  2. Before and after filtering the Notes model type in console 
    skuid.model.getModel('Notes').getRows() 
    and verifiy that their returned results are different. Result: They are different as expected.
  3. Check Opportunity SOQL before and after filtering using
    skuid.model.getModel('Opportunities').getSOQL()
    .Result is that the SOQL is unchanged – this is the problem.
  4. Create Aggregate note model that counts number of notes with 0 in it. – this correctly reflects the number of notes with 0 in it.

Here’s a sample page. When you press "Remove notes that contain 0 in title” it filters out notes with a title that contain the text “0” from the note model, and requeries the opportunity which has a subquery field with a condition depending on the notes model.

So before pressing the button I expect to see this. Which is what I’m seeing. (image)



 

After I press the button I expected  not so see notes with "0" in the title. But they still show up. The highlighted records are the ones I expect not to show up.



To test this, paste the xml below into a new page and create some note records related to the opportunity that contain anything. Then create some note records related to the opportunity that contain "0” in the title. It should remove the notes containing "0" in the title after pressing “Remove notes that contain 0 in title”, but it doesn’t.


Here's the xml for the sample page.



 <skuidpage unsavedchangeswarning="yes" personalizationmode="server" showsidebar="true" useviewportmeta="true" showheader="true">
    <models>
        <model id="Notes" limit="" query="true" createrowifnonefound="false" datasource="salesforce" type="" sobject="Note">
            <fields>
                <field id="Id"/>
            </fields>
            <conditions>
                <condition type="fieldvalue" value="0" enclosevalueinquotes="true" field="Title" operator="does not contain" state="filterableoff" inactive="true" name="TitleDoesNotContainZero"/>
            </conditions>
            <actions>
                <action>
                    <actions>
                        <action type="blockUI" model="Notes" behavior="standard" message="NOTES MODEL REQUERIED SUCCESSFULLY" timeout="3000"/>
                        <action type="requeryModels" model="Opportunites" behavior="standard">
                            <models>
                                <model>Opportunities</model>
                                <model>NotesAggregate</model>
                            </models>
                        </action>
                    </actions>
                    <events>
                        <event>models.loaded</event>
                    </events>
                </action>
            </actions>
        </model>
        <model id="Opportunities" limit="" query="true" createrowifnonefound="false" datasource="salesforce" type="" sobject="Opportunity">
            <fields>
                <field id="Notes" type="childRelationship" limit="">
                    <conditions>
                        <condition operator="in" type="modelmerge" field="Id" value="" model="Notes" enclosevalueinquotes="true" mergefield="Id" novaluebehavior="deactivate"/>
                    </conditions>
                    <fields>
                        <field id="Body"/>
                        <field id="Title"/>
                    </fields>
                </field>
                <field id="Name"/>
            </fields>
            <conditions/>
            <actions>
                <action>
                    <actions>
                        <action type="blockUI" timeout="3000" message="OPP REQUERIED SUCCESSFULLY"/>
                    </actions>
                    <events>
                        <event>models.loaded</event>
                    </events>
                </action>
            </actions>
        </model>
        <model id="NotesAggregate" limit="" query="true" createrowifnonefound="false" datasource="salesforce" type="aggregate" sobject="Note">
            <fields>
                <field id="Id" name="countId" function="COUNT"/>
            </fields>
            <conditions>
                <condition type="fieldvalue" value="0" enclosevalueinquotes="true" field="Title" operator="contains" state=""/>
                <condition type="modelmerge" value="" field="Id" state="" operator="in" model="Notes" enclosevalueinquotes="true" mergefield="Id" novaluebehavior="deactivate"/>
            </conditions>
            <actions/>
            <groupby method="simple"/>
        </model>
    </models>
    <components>
        <filterset model="Notes" searchmethod="server" searchbox="false" uniqueid="sk-1HATgd-107" emptysearchbehavior="query">
            <filters>
                <filter type="toggle" filteroffoptionlabel="New Filter" createfilteroffoption="true" affectcookies="true" autocompthreshold="25" conditionsource="manual" labelmode="auto" label="Remove notes that contain 0 in title">
                    <effects>
                        <effect action="activate" value="" condition="TitleDoesNotContainZero"/>
                    </effects>
                </filter>
                <filter type="toggle" filteroffoptionlabel="New Filter" createfilteroffoption="true" affectcookies="true" autocompthreshold="25" conditionsource="manual" labelmode="auto" label="Show all notes">
                    <effects>
                        <effect action="deactivate" value="" condition="TitleDoesNotContainZero"/>
                    </effects>
                </filter>
            </filters>
            <searchfields/>
        </filterset>
        <template multiple="false" uniqueid="sk-1HGlk9-244" model="NotesAggregate">
            <contents>Number of notes with '0' in title in notes model: {{{countId}}}</contents>
        </template>
        <skootable showconditions="true" showsavecancel="false" showerrorsinline="true" searchmethod="server" searchbox="true" showexportbuttons="false" pagesize="10" createrecords="false" model="Opportunities" buttonposition="" mode="read" allowcolumnreordering="true" uniqueid="sk-1H6KAU-172">
            <fields>
                <field id="Name" hideable="true" uniqueid="fi-1H6zfV-292" valuehalign="" type=""/>
                <field id="Notes" type="CHILDREL" limit="100" hideable="true" uniqueid="fi-1H6a6E-224" valuehalign="" allowhtml="true">
                    <label>Note Titles</label>
                    <template>&lt;ul style="margin: 0; padding: 0;"&gt;
{{#Title}}&lt;li&gt;{{Title}}&lt;/li&gt;{{/Title}}
&lt;/ul&gt;

&lt;ul style="margin: 0; padding: 0;"&gt;</template>
                </field>
                <field id="Notes" type="CHILDREL" limit="100" hideable="true" uniqueid="fi-1H6PkD-192" valuehalign="" allowhtml="true">
                    <label>Note Bodies</label>
                    <template>&lt;ul style="margin: 0; padding: 0;"&gt;
{{#Body}}&lt;li&gt;{{Body}}&lt;/li&gt;{{/Body}}
&lt;/ul&gt;</template>
                </field>
            </fields>
            <rowactions/>
            <massactions usefirstitemasdefault="true"/>
            <views>
                <view type="standard"/>
            </views>
        </skootable>
    </components>
    <resources>
        <labels/>
        <javascript/>
        <css/>
    </resources>
    <styles>
        <styleitem type="background" bgtype="none"/>
    </styles>
</skuidpage>

Photo of Shmuel Kamensky

Shmuel Kamensky, Champion

  • 4,328 Points 4k badge 2x thumb

Posted 2 years ago

  • 1
Photo of Shmuel Kamensky

Shmuel Kamensky, Champion

  • 4,328 Points 4k badge 2x thumb
Can anyone verify this issue? The xml can be pasted into any org running the Brooklyn release.
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,704 Points 20k badge 2x thumb
K. I've had issues when using another model as part of a subquery and not querying together in Skuid.
Photo of Shmuel Kamensky

Shmuel Kamensky, Champion

  • 4,328 Points 4k badge 2x thumb
But even when querying together it doesn't work. I tried both in the action framework and also in the chrome developer console to query simultaneously and the conditions in the subquery aren't respected.
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,704 Points 20k badge 2x thumb
Is the model used in the subquery above the model using it it in the condition?
Photo of Shmuel Kamensky

Shmuel Kamensky, Champion

  • 4,328 Points 4k badge 2x thumb
Yes. I must have made a mistake. The update is working when I query them together, thanks a bunch! Now I'm working on a way to use after model events to requery the dependent model while avoiding recursion... 
Photo of Shmuel Kamensky

Shmuel Kamensky, Champion

  • 4,328 Points 4k badge 2x thumb
Thanks for Pat for the information on needing to query both models simultaneously.

For anyone that has run into this specific situation, here's how I solved it:

  1. Create a UI only checkbox called 'secondQueryHasNotRun'
  2. On load, subscribe to all three condition events ('condition.valueChanged','condition.deactivated','condition.activated',) and add a callback function that updates the 'secondQueryHasNotRun' field to true.
  3. In the filter model, add an after requery action. In the action, add branch logic that checks to see if 'secondQueryHasNotRun' is true. If it is true, update the dependent models (including the model that initiated the query) and change the value of 'secondQueryHasNotRun' to false. 

Now each time you change a condition and update the model, the filtering model and dependent model will be updated in the same action, allowing for the subquerying condition to work, and avoiding recursion.

Warnings:
  • You may want to do some additional filtering on your subscription callbacks if you don't want every change of every condition on every model to update the UI only field
  • This feature is still somewhat of a hack since the initiating model (filter model) is still queried an additional time unnecessarily. 
(Edited)