Suquery condition not applying current condition value when used as Option Source

  • 1
  • Problem
  • Updated 3 years ago
  • In Progress
  • (Edited)
When a Model is used as an option source and the model contains a subquery condition, the condition value is not being applied correctly.  The first time the condition has the correct value, but subsequent searches on the same field do not reflect the current condition value (it always remains what it was the first time).

Scenario Explained: User is choosing a field value for Field A on Model #1.  User is choosing a field value for Field B on Model #2.  The filter for Field B is based on a junction object where rows are retrieved that contains Field B but also contain Field A.  

Steps to reproduce:
1) Create page using XML below
2) Preview page
3) Click the "Parent Account Id" field mag glass

Expected Behavior = Actual Behavior
The list of available accounts to choose from are correctly filtered on the current accounts Owner.  Only accounts that hvae the same owner are returned

4) Change the Owner Id field to another user
5) Click the "Parent Account Id" field mag glass

Expected Behavior
The list of accounts returned should contain accounts that are owned by the user set in Step #4

Actual Behavior
The list of accounts returned is the same as it was in Step #3

Sample Page XML
<skuidpage unsavedchangeswarning="yes" personalizationmode="server" showsidebar="true" showheader="true" tabtooverride="Account">   <models>
      <model id="Account" limit="1" query="true" createrowifnonefound="false" sobject="Account" adapter="" type="" doclone="">
         <fields>
            <field id="Name"/>
            <field id="CreatedDate"/>
            <field id="Parent.ParentId"/>
            <field id="Parent.Parent.Name"/>
            <field id="Parent.OwnerId"/>
            <field id="Parent.Owner.Name"/>
            <field id="OwnerId"/>
            <field id="Owner.Name"/>
            <field id="ParentId"/>
            <field id="Parent.Name"/>
         </fields>
         <conditions>
            <condition type="param" enclosevalueinquotes="true" operator="=" field="Id" value="id"/>
         </conditions>
         <actions/>
      </model>
      <model id="AccountOwnedBy" limit="" query="false" createrowifnonefound="false" adapter="" type="" sobject="Account" doclone="no">
         <fields>
            <field id="Name"/>
            <field id="OwnerId"/>
            <field id="Owner.Name"/>
         </fields>
         <conditions>
            <condition type="join" value="" field="OwnerId" operator="in" enclosevalueinquotes="true" joinobject="User" joinfield="Id">
               <conditions>
                  <condition type="modelmerge" value="" field="Id" operator="=" model="Account" enclosevalueinquotes="true" mergefield="OwnerId" novaluebehavior="noquery"/>
               </conditions>
            </condition>
         </conditions>
         <actions/>
      </model>
   </models>
   <components>
      <pagetitle model="Account" uniqueid="sk-wz-99-70">
         <maintitle>
            <template>{{Name}}</template>
         </maintitle>
         <subtitle>
            <template>{{Model.label}}</template>
         </subtitle>
         <actions>
            <action type="delete"/>
            <action type="clone"/>
            <action type="share"/>
            <action type="savecancel" window="self"/>
         </actions>
      </pagetitle>
      <basicfieldeditor showsavecancel="false" showheader="true" model="Account" mode="edit" uniqueid="sk-wz-99-71" buttonposition="">
         <columns>
            <column width="50%">
               <sections>
                  <section title="Basics" collapsible="no">
                     <fields>
                        <field id="Name"/>
                        <field id="ParentId" valuehalign="" type="" optionsource="model" optionmodel="AccountOwnedBy">
                           <searchfields/>
                        </field>
                     </fields>
                  </section>
               </sections>
            </column>
            <column width="50%">
               <sections>
                  <section title="System Info" collapsible="no">
                     <fields>
                        <field id="CreatedDate"/>
                        <field id="OwnerId"/>
                     </fields>
                  </section>
               </sections>
            </column>
         </columns>
      </basicfieldeditor>
   </components>
   <resources>
      <labels/>
      <css/>
      <javascript/>
   </resources>
   <styles>
      <styleitem type="background" bgtype="none"/>
   </styles>
</skuidpage>
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb

Posted 3 years ago

  • 1
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Thanks Barry - we are looking in to this. 
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Thanks Rob!
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Barry - I may be missing something in your page construction.  I don't see where you are ever updating the condition values on the "AccountOwnedBy" model.  Merely requerying a model (which is done when the reference field is searched)  does not force the model to evaluate whether the "field from another model" condition value has changed.   You need to update that value somehow.  I expected to see a model action on update of of Account.Owner that would send a new value to the condition on the AccountOwnedBy model. 
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Hey Rob - Sorry for the delayed reply on this.  I can see why you'd expect to find an action somewhere (e.g. when owner is updated) that would update the condition.  The problem with that approach is that in a situation where you have the field editor in a drawer, the last "condition value" to be set will be the last one that was changed.  This won't be the one they are currently searching however.

When the search option source is set to "Automatic", Skuid automatically obtains the latest values that correspond to the defined filters and then the SOQL that is used is based on that.  This works well for "drawer" situations.  For option source model, the same approach needs to be taken since relying on action framework wont' reliably set the condition values.

I did a little further testing and found that it's not just subqueries but any scalar value as well.  I've update the test page to demonstrate using "Name" as a filter on the "AccountOwnedBy2" model.  If you repeat the same steps using "Parent Account Id (Filter by Name)" field instead of "Parent Account Id", you'll see the filter value isn't being updated "automatically."

In short, the only way to reliably set the condition values on the model that is used as the option source is for Skuid to do this before issuing the query for the model to retrieve the results (this is do to the drawer scenario, etc.).

In my particular use case, I need to be able to filter Field #2, on the value of Field #1 where there is a record in a junction object that has Field #1.  Since Automatic Lookup filters don't support subqueries, I believe the only way I can achieve this filter is using option source model.

Thoughts?

Updated Test Page
<skuidpage unsavedchangeswarning="yes" personalizationmode="server" showsidebar="true" showheader="true" tabtooverride="Account">   <models>
      <model id="Account" limit="1" query="true" createrowifnonefound="false" sobject="Account" adapter="" type="" doclone="">
         <fields>
            <field id="Name"/>
            <field id="CreatedDate"/>
            <field id="Parent.ParentId"/>
            <field id="Parent.Parent.Name"/>
            <field id="Parent.OwnerId"/>
            <field id="Parent.Owner.Name"/>
            <field id="OwnerId"/>
            <field id="Owner.Name"/>
            <field id="ParentId"/>
            <field id="Parent.Name"/>
         </fields>
         <conditions>
            <condition type="param" enclosevalueinquotes="true" operator="=" field="Id" value="id"/>
         </conditions>
         <actions/>
      </model>
      <model id="AccountOwnedBy" limit="" query="false" createrowifnonefound="false" adapter="" type="" sobject="Account" doclone="no">
         <fields>
            <field id="Name"/>
            <field id="OwnerId"/>
            <field id="Owner.Name"/>
         </fields>
         <conditions>
            <condition type="join" value="" field="OwnerId" operator="in" enclosevalueinquotes="true" joinobject="User" joinfield="Id">
               <conditions>
                  <condition type="modelmerge" value="" field="Id" operator="=" model="Account" enclosevalueinquotes="true" mergefield="OwnerId" novaluebehavior="noquery"/>
               </conditions>
            </condition>
         </conditions>
         <actions/>
      </model>
      <model id="AccountOwnedBy2" limit="" query="false" createrowifnonefound="false" adapter="" type="" sobject="Account" doclone="no">
         <fields>
            <field id="Name"/>
            <field id="OwnerId"/>
            <field id="Owner.Name"/>
         </fields>
         <conditions>
            <condition type="modelmerge" value="" field="Name" operator="=" model="Account" enclosevalueinquotes="true" mergefield="Name" novaluebehavior="noquery"/>
         </conditions>
         <actions/>
      </model>
   </models>
   <components>
      <pagetitle model="Account" uniqueid="sk-wz-99-70">
         <maintitle>
            <template>{{Name}}</template>
         </maintitle>
         <subtitle>
            <template>{{Model.label}}</template>
         </subtitle>
         <actions>
            <action type="delete"/>
            <action type="clone"/>
            <action type="share"/>
            <action type="savecancel" window="self"/>
         </actions>
      </pagetitle>
      <basicfieldeditor showsavecancel="false" showheader="true" model="Account" mode="edit" uniqueid="sk-wz-99-71" buttonposition="">
         <columns>
            <column width="50%">
               <sections>
                  <section title="Basics" collapsible="no">
                     <fields>
                        <field id="Name"/>
                        <field id="ParentId" valuehalign="" type="" optionsource="model" optionmodel="AccountOwnedBy">
                           <searchfields/>
                           <filters/>
                        </field>
                        <field id="ParentId" valuehalign="" type="" optionsource="model" optionmodel="AccountOwnedBy2">
                           <searchfields/>
                           <filters/>
                           <label>Parent Account Id (Filter By Name)</label>
                        </field>
                     </fields>
                  </section>
               </sections>
            </column>
            <column width="50%">
               <sections>
                  <section title="System Info" collapsible="no">
                     <fields>
                        <field id="CreatedDate"/>
                        <field id="OwnerId" valuehalign="" type=""/>
                     </fields>
                  </section>
               </sections>
            </column>
         </columns>
      </basicfieldeditor>
   </components>
   <resources>
      <labels/>
      <css/>
      <javascript/>
   </resources>
   <styles>
      <styleitem type="background" bgtype="none"/>
   </styles>
</skuidpage>
(Edited)
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Hi Barry,

As of the current released version of Skuid, we're only checking and recalculate modelmerge conditions (not ones that are sub conditions in "join" type conditions) after model updates.

Going forward, we plan on recalculating both conditions and subconditions on model updates and model saves.

Just curious for your use case, would you expect conditions to be refreshed on a row update (no save has happened yet)
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Hey Ben -

Thanks for the update and looking in to this, great to hear that subconditions are going to be recalc'd.

Possibly I'm misunderstanding what you are saying by conditions being refreshed on row update vs save, but in my use case, I think the only solution would be to evaluate model condition values just before a model is requeried as updating the condition value as data changes (or saves) would still produce incorrect results.

Here's the scenario/use-case - Table of Accounts has drawer that contains field editor for that account that has Field A & Field B.  Field B is using model source and filtered based on Field A value.

1) User expands drawer one, updates Field A
2) User expands drawer two, and searches Field B

If the condition was updated only when Field A was changed, the results for Step #2 would be inaccurate.

So to answer your question, my use case would require that conditions are evaluated as the model is being requeried and there wouldn't be a need to update as the row updates and/or after save - in fact, I think that would just be an unnecessary perf hit.  In short, using current context to apply condition values, then call "load".

Again, possibly I'm misunderstanding what you meant by refreshing as row updates/saved.  

Hope that makes sense?
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
yeah, that makes sense. I'm trying to think of a reason why we're not calculating the condition values "just in time". I'll have to think about this a bit.
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Funny you should mention that.  Just got back from taking the dog for a walk and had been thinking the same thing - why would it not have been JIT'd all along.  Here's my thoughts:

1) The logic for this was in-place before Drawers came along.  Prior to Drawers (and some other recent functionality), you couldn't 'edit' multiple things simultaneously - or at least not as easily as you can today.  It was likely an issue even then but, unlikely ever to be exposed.

2) With server side models, if I recall correctly, if you have Model B based on value from Model A, you must include Model A in the "load" call or you won't get the correct results ( I could be remembering incorrectly).  I haven't had the need to dig in to how client side models actually get processed but not sure if this same requirement is the case.  If it is, then things get rather sticky when you want to just query a single model since you'd have to have all models involved included in the load.  

All that said, automatic models from a reference field are really what we're after here but applied to standard models.  I think JIT'ing the condition values client side and then issuing the load would solve for most (if not all) use cases.  The rub here of course is when Model A has a condition on Model Z, etc.  The chain could end up getting complex but I think it's necessary to get desired behavior in all situations.  As of right now, since there is no "JOIN" capability in Automatic model on reference fields, the only solution is to use Models for search.

Thoughts?
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Hi Barry,

I think we came up with a good solution for this, it will be in Rockaway when it comes out.
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Awesome news, thank you!
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
This fix is now available from skuid.com/releases in the Rockaway Release.
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Tested and confirmed with Skuid 8.8.  Thank you!