Choose a lookup field value from 'new' records that exist in another model

  • 1
  • Question
  • Updated 2 years ago
  • Answered
I have a requirement where the user needs to be able to create records in Model 'A' and records in Model 'B' where one of the fields in Model 'B' is a lookup to Model 'A'.  

This is a relatively straightforward scenario when the records in Model 'A' already exist in the database and/or can be saved prior to creating the records in Model 'B'.  Unfortunately, my scenario is not that simple.  The requirement is that records in Model 'A' and Model 'B' are created by the user and then everything saved to the database.  Additionally, using drawers to provide context on 'A' when creating 'B' is also not possible due to UI/UX reasons for this use case.

The page layout is:

1) Table of records in Model B
2) Global Action on table that displays a popup for creating a record in Model 'A'
3) Field in table on Model B that is a lookup to Model A where the user can choose any record that they've created in Model A

All "searches" on lookup filters expect the data to exist in the database.  Is there a way to have a lookup field use records from a client side model that has new records?

The reason the page must behave this way is a couple-fold:

1) In the real solution, there is actually two fields on Model B, one that looks up to Model A and one that looks up to Model Z and both A & Z will be "new" records created via a popup
2) Model A & Model Z can't be saved as they are 'created' because everything must be saved or cancelled - all or nothing type of behavior
3) the page is incredibly complex and drawers introduce too much additional "clicking" and the need to take up additional real-estate that would otherwise be unnecessary.  
4) Since there are two models (A & Z) and any combination of these two records is valid to assign to Model B records, drawers wouldn't provide that level of context anyway.

Any assistance on how to approach this is greatly appreciated!
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb

Posted 2 years ago

  • 1
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
I don't know if this is exactly what you are looking for, but what I did in one case was create "temporary records". You mentioned that you want to be able to create all or none, so you can't save the records as they are created. You could save the records as they are created and mark them with a checkbox field of "temporary". Then you would filter out all of the temporary records from your models so they don't display. This would give you a place holder record for searching. Then, when you do your final save you could remove the temp checkbox to make the record permanent. You could also delete out temps through model actions, triggers, or jobs, I believe.
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Barry, I think I must be missing somthing because I think this is pretty straightforward. Here's what I am thinking.   

You have a table of object B and a global action that opens popup.  

In that popup you create records for object A and Z.  

When user is done an action sequence does the following: 
1. Creates new row in Object B
2. Prepopulates the row with the ID's from the new records in object A and Z.  This will require global merge syntax,  and will require that the new rows in A and Z are the first ones in your model for those objects. 
3. You'll also want to prepopulate the Name of the reference fields from new object A and Z so you don't just get "1" or whatever temporary Id skuid creates in the lookup column on object B
3. Closes the popup without saving any of the objects. 

Then a global save on all the objects completes the deal and allows for global rollback. 

Does this not work? 
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
This is what I was thinking, but there are a few layers of complexity.
  1. Why is the popup a global action? Is that just to save a click from creating the row in model B and then using a row action? Or is there a case where multiple rows in model B are being created, and need to look up to the same new row that is subsequently created in Models A or Z?
  2. Can the user create multiple rows in A or Z with the popup (in other words, are they tables)? If so, just have row actions on the tables which populate the context row in B.
If the primary problem is setting the right context in model B (meaning, multiple model B rows for each model A/Z row), then you could create a dialog asking the user to select which rows in model B to point the Ids from A and/or Z to after popup close.
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Raymond/Rob/Matt - First off, thank you all for your input, greatly appreciated!!

Raymond - Unfortunately, one requirement of this entire system is that no "temporary" data be inserted in to the database.  This requirement makes things incredibly complex on multiple fronts unfortunately.  Your approach would work well if this requirement didn't exist.  The one thing that would be required though is to have a background job that goes and deletes temporary records because a user could "save" a temporary record and then close their browser essentially orphaning the temporary record.  That is solvable of course though :)

Rob - One key detail that I didn't explain very well in my OP unfortunately makes it such that your approach won't work :(  For the rows in B, you can choose any value from Model 'A' or Model 'Z'.  In other words, multiple rows in B could have the same values for 'A' and 'Z'.  Given that, the user needs to be able to "search" for 'A' or 'Z' from Field B and I can't create model B records from the popup that creates 'A' and 'Z'.  To really complicate things the user needs to be able to search records in 'A' and 'Z' as well as existing records in the database for 'A' and 'Z'.  Essentially, I need the user to be able to find anything new that they have created on the page OR anything that already exists in the database.  I left the existing record requirement out of the OP just to try to not complicate things in my description - was going to layer that part in after we found a solution for the new record part ;)

Matt - If I'm understanding your #1, you've pretty much nailed it.  The user will create multiple Model B records, and then choose a value for 'A' and 'Z' from records that are in that model and those values can be the same for multiple 'B' records.  For your #2, I've thought about doing something with a row action to have a "chooser" like your suggestion and even thought about a custom renderer that triggers a popup.  Those are my "fallback" options but the users want to avoid clicks or unnecessary popups (they are data entry tasks and therefore keyboard focused).

At the end of the day, there are some solutions (Matt mentioned a few), but in trying to achieve the best UI/UX for the users, the ideal scenario is a standard lookup field that just includes the 'new' rows in 'A' and 'Z'.

Below is a sample page that demonstrates what I'm after.  In it, you can create one or more parent accounts using the global action on the "accounts" table or via in-line on the "parent" accounts table itself.  Then, you can create multiple accounts, some of which might have the same parent.  What I'd like is to have the "parent" field in the account table include the accounts in the parents model (and ideally in the database as well - a union of "new" and "existing").

Appreciate any further thoughts/insight!

Sample Page

<skuidpage unsavedchangeswarning="yes" personalizationmode="server" showsidebar="true" showheader="true" tabtooverride="Account">   <models>
      <model id="Parents" limit="20" query="false" createrowifnonefound="false" adapter="" type="" sobject="Account" doclone="" processonclient="true">
         <fields>
            <field id="Id"/>
            <field id="Name"/>
         </fields>
         <conditions/>
         <actions/>
      </model>
      <model id="Account" limit="1" query="false" createrowifnonefound="false" sobject="Account" adapter="" type="" doclone="" processonclient="true">
         <fields>
            <field id="Name"/>
            <field id="CreatedDate"/>
            <field id="ParentId"/>
            <field id="Parent.Name"/>
         </fields>
         <conditions/>
         <actions/>
      </model>
   </models>
   <components>
      <pagetitle model="Account" uniqueid="sk-3hldwl-68">
         <maintitle>
            <template>{{Name}}</template>
         </maintitle>
         <subtitle>
            <template>{{Model.label}}</template>
         </subtitle>
         <actions>
            <action type="multi" label="Save" icon="sk-icon-save">
               <actions>
                  <action type="save" rollbackonanyerror="true">
                     <models>
                        <model>Parents</model>
                        <model>Account</model>
                     </models>
                  </action>
               </actions>
            </action>
            <action type="multi" label="Cancel" icon="sk-icon-cancel">
               <actions>
                  <action type="cancel">
                     <models>
                        <model>Parents</model>
                        <model>Account</model>
                     </models>
                  </action>
               </actions>
            </action>
         </actions>
      </pagetitle>
      <richtext multiple="false" uniqueid="sk-3jplPA-112">
         <contents>&lt;p&gt;&lt;span style="font-size:16px;"&gt;&lt;strong&gt;Parents&lt;/strong&gt;&lt;/span&gt;&lt;/p&gt;
</contents>
      </richtext>
      <skootable showconditions="true" showsavecancel="false" showerrorsinline="true" searchmethod="server" searchbox="false" showexportbuttons="false" pagesize="10" createrecords="true" model="Parents" buttonposition="" mode="read" uniqueid="sk-3jpNz8-257" emptysearchbehavior="query">
         <fields>
            <field id="Name"/>
         </fields>
         <rowactions>
            <action type="edit"/>
            <action type="delete"/>
         </rowactions>
         <massactions usefirstitemasdefault="true">
            <action type="massupdate"/>
            <action type="massdelete"/>
         </massactions>
         <views>
            <view type="standard"/>
         </views>
         <searchfields/>
      </skootable>
      <richtext multiple="false" uniqueid="sk-3jpvOl-141">
         <contents>&lt;p&gt;&lt;span style="font-size:16px;"&gt;&lt;strong&gt;Accounts&lt;/strong&gt;&lt;/span&gt;&lt;/p&gt;
</contents>
      </richtext>
      <skootable showconditions="true" showsavecancel="false" showerrorsinline="true" searchmethod="server" searchbox="false" showexportbuttons="false" pagesize="10" createrecords="true" model="Account" buttonposition="" mode="edit" uniqueid="sk-3jnk12-91" emptysearchbehavior="query">
         <fields>
            <field id="Name"/>
            <field id="ParentId" valuehalign="" type=""/>
         </fields>
         <rowactions>
            <action type="edit"/>
            <action type="delete"/>
         </rowactions>
         <massactions usefirstitemasdefault="true"/>
         <views>
            <view type="standard"/>
         </views>
         <actions defaultlabel="Global Actions" defaulticon="sk-icon-magic" usefirstitemasdefault="true">
            <action type="multi" label="Create New Parent" icon="sk-icon-magic">
               <actions>
                  <action type="createRow" model="Parents" appendorprepend="prepend" defaultmodefornewitems="edit"/>
                  <action type="showPopup">
                     <popup title="New Popup" width="90%">
                        <components>
                           <pagetitle model="Parents" uniqueid="sk-3joZPA-217">
                              <maintitle>
                                 <template>{{Name}}</template>
                              </maintitle>
                              <subtitle>
                                 <template>{{Model.label}}</template>
                              </subtitle>
                              <actions>
                                 <action type="multi" label="Add Parent">
                                    <actions>
                                       <action type="closeTopmostPopup"/>
                                    </actions>
                                 </action>
                              </actions>
                           </pagetitle>
                           <basicfieldeditor showheader="true" showsavecancel="false" showerrorsinline="true" model="Parents" buttonposition="" uniqueid="sk-3joR8Q-192" mode="edit">
                              <columns>
                                 <column width="100%">
                                    <sections>
                                       <section title="Section A" collapsible="no" showheader="false">
                                          <fields>
                                             <field id="Name"/>
                                          </fields>
                                       </section>
                                    </sections>
                                 </column>
                              </columns>
                           </basicfieldeditor>
                        </components>
                     </popup>
                  </action>
               </actions>
            </action>
         </actions>
         <searchfields/>
      </skootable>
   </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
Note - The sample page is not fully vetted, has limitations/issues, etc. but provides a sample of the requirement.
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Barry,

Have you considered a custom field renderer for your reference fields on Model B, where you set the source for your options to a Model instead of the database?  It might require you to load a LOT of records into your models for A and Z, but I think it would work to capture both new and existing.
From http://help.skuid.com/m/11720/l/214147-skuid-ui-field-renderers :

  • Change the Option Source
By default, options are auto-sourced, meaning the records are fetched directly from the referenced object. You may also choose to model-source options by retrieving records from an existing Skuid Model. Set field.options.optionsource to "model" andfield.options.optionmodel to the Skuid Model ID which contains records of the referenced field's type. Sourcing options from a Model allows for a finer level of control than auto-sourcing, as Models can be easily filtered, sorted and manipulated by the user (using Skuid's declarative components) or programmatically via Skuid's Model API.
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
You can probably set those options in the builder ui, too, right?
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
You can probably set those options in the builder ui, too, right?
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Hey Matt - Thanks again for the insight.  Unfortunately, using option source of model won't work in this case.  Underneath, Skuid actually makes a database call based on the model definition to retrieve records.  It's essentially behaves the same way as automatic from a visibility to records perspective but allows you to configure fields, sort order, etc. which automatic doesn't.  Also, if you use option source model and the model has unsaved changes, the "search" will fail because Skuid won't use the model to query for records because it has unsaved changes.  In short, a good idea but unfortunately, won't achieve the end goal :(
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Hmm, foiled again.

There's got to be a way to trick skuid into searching for both new and existing records.
Create your own massively long picklist?
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
haha, I'm hoping your right regarding the trick part :)

You and I think a like!  I've considered the massively long picklist as well but it would truly be massive (over time).  There are a few solutions, but none of them are really "good" solutions as they either require introducing complexity in to the UI/UX or require a lot of code to create a custom renderer that essentially mimicks what the Skuid default lookup field renderer does but also includes new records in the client.  It's a road I'm hoping to avoid.

I'm still holding out hope that there's a "trick" in there somewhere!
Photo of Ben Hubbard

Ben Hubbard, Employee

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

I thought about this a little bit. The ideal solution to your problem would be to allow your reference field to have a custom option source snippet that we just hooked into the standard reference field renderer. We have something like that for filter option sources, the custom option source. We have plans on improving that filter option source to have the ability to be asynchronous instead of synchronous as it currently is. If we made it asynchronous, eand extended that functionality to reference fields as well, you could construct an "on-the-fly" model, do your search, then combine with new records that also matched your search and present that list to the user to pick from.

Unfortunately, that's not part of the product yet. :(

I have 2 ideas though that may or may not work for you.

1. We use the jQueryUI Autocomplete Widget to do our reference renderer, so you may be able to use the _renderMenu extension point described here

Something like this in your custom render after calling the standard reference renderer...

var input = element.find('input');
input.data('ui-autocomplete')._renderMenu = function(ui,items){}; 

2. You could add a draggable area to your Model A using jQueryUI's draggable interface and a custom renderer and a droppable area on Model B using the droppable interface and a custom renderer. That way users could drag records from Model A into Model B and associate the records.
Photo of Barry Schnell

Barry Schnell, Champion

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

Thank you so much for looking at this, especially over the weekend, greatly appreciated!

I think there might be some legs to the _renderMenu idea, I'll take dig further on that.  The draggable area idea is pretty slick but unfortunately, I don't think my users will sign off on having two different approaches to setting a value in the field (draggable for new, searchable for existing).  Also, Model 'A' and Model 'Z' could have hundreds of records so finding the one they need to then drag could become challenging.

I'll also submit a new post asking for the enhancement of custom option source for reference fields.

Thanks again!