Determine the Attachment Owner User Type

  • 2
  • Question
  • Updated 4 years ago
  • Answered
  • (Edited)
We're using Attachments on a Custom Object whereby Partner Community Users need read only access to Attachments posted by an Employee User but edit access to the Attachments they have posted.  It would stand to reason that the most straight-forward approach would be to use a filter on a model condition on the Attachment model for Owner.UserType.  However, this throws a pretty nasty error:

1. An error occurred while attempting to perform the following SOQL query: SELECT ContentType,CreatedDate,CreatedById,CreatedBy.Name,OwnerId,Owner.Name,ParentId,Parent.Name,LastModifiedDate,LastModifiedById,LastModifiedBy.Name,Name,Description,BodyLength,Owner.UserType,Id FROM Attachment LIMIT 21 Error:No such column 'UserType' on entity 'Name'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.

I have been able to bring in a User Model and filter on UserType IN ('CSPLitePortal','PowerPartner') and then place a condition to merge the models on the Attachment Model.

I'm a bit worried about this as we'll ultimately have 70,000 portal (er, I mean "community") users.  Seems like a setup for major performance issues.  For this same reason, a subquery is also not something I'd want to consider.

I have created a formula field using IsPickVal against UserType on the User object.  Works in standard SFDC but produces the same "LIMIT 21" error in Skuid.

Now that I've bored you with the fun facts, please let me know what successes you might have encountered when dealing with accessing UserType from the Owner reference in Attachment.
Photo of Christopher Beckworth

Posted 4 years ago

  • 2
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Limit 21 is actually the numbers of rows to set to be returned. On that model, it is set to 20. Not sure why the generated SOQL limit is set 21 though. It's always one more than what is set.

The error is everything after "Error:"
(Edited)
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Found an article about this particular error. Explains it pretty well. http://www.tgerm.com/2010/11/no-such-column-on-entity-error.html

Parent.Name is the issue in your model. Parent can reference many objects. Basically the SOQL query doesn't support the polymorphic Parent reference field in getting fields related by it. ie. Name

Take Parent.Name out and the model should work just fine.
Thanks on the replies Pat.  I actually have no issues displaying the parent.name.  I'd considered the polymorphic nature of the ParentId field and did some testing without any conditions whatsoever.

The problem comes into play when I introduce the condition on the Attachment model to have a filter on Owner.UserType in ('CSPLitePortal','PowerPartner').  Remove this condition, no issues.  Add it back in and the ERROR is reproduced.

To be triple sure, I removed the Parent.Name reference.  Still same issue when trying the desired condition.

Feel free to slam together a simple list on the Attachment Object and attempt the condition mentioned.  (by feel free I mean highly encouraged. :-) ).  Perhaps I missing something very obvious.
Photo of Zach McElrath

Zach McElrath, Employee

  • 49,056 Points 20k badge 2x thumb
Use a "Result of Subquery" Condition on the OwnerId field, --- this is a perfect use case for it. I've tested it and it works for me with no errors. This approach avoids the polymorphic field reference issue, and it avoids the scalability issue of the "Field from another Model IN" Condition approach.

I've posted a screenshot of the setup below. Your Condition Field should be OwnerId, and Join Object is User, Join Field is Id, and then add a Sub-Condition on the UserType field with type "Multiple", where you can enter the User Types that you want to allow.

Here's the XML for a basic test case:

<skuidpage unsavedchangeswarning="yes" showsidebar="true" showheader="true" tabtooverride="Attachment">   
   <models>
      <model id="Attachment" limit="100" query="true" createrowifnonefound="false" sobject="Attachment">
         <fields>
            <field id="Name"/>
            <field id="CreatedDate"/>
         </fields>
         <conditions>
            <condition type="join" value="" field="OwnerId" operator="in" enclosevalueinquotes="true" joinobject="User" joinfield="Id">
               <conditions>
                  <condition type="multiple" value="" field="UserType" operator="in" enclosevalueinquotes="true">
                     <values>
                        <value>CSPLitePortal</value>
                        <value>PowerPartner</value>
                     </values>
                  </condition>
               </conditions>
            </condition>
         </conditions>
         <actions/>
      </model>
   </models>
   <components>
      <pagetitle model="Attachment">
         <maintitle>
            <template>{{Model.labelPlural}}</template>
         </maintitle>
         <subtitle>
            <template>Home</template>
         </subtitle>
         <actions>
            <action type="savecancel"/>
         </actions>
      </pagetitle>
      <skootable showconditions="true" showsavecancel="false" searchmethod="server" searchbox="true" showexportbuttons="false" pagesize="10" createrecords="true" model="Attachment" mode="read">
         <fields>
            <field id="Name" allowordering="true"/>
            <field id="CreatedDate" allowordering="true"/>
         </fields>
         <rowactions>
            <action type="edit"/>
            <action type="delete"/>
         </rowactions>
         <massactions usefirstitemasdefault="true">
            <action type="massupdate"/>
            <action type="massdelete"/>
         </massactions>
         <views>
            <view type="standard"/>
         </views>
      </skootable>
   </components>
   <resources>
      <labels/>
      <css/>
      <javascript/>
   </resources>
</skuidpage>



(Edited)
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Out of curiosity, will there be support for Polymorphic field referencing in the models?

ie. Select TYPEOF Owner WHEN User THEN UserType END FROM Attachment
Photo of Zach McElrath

Zach McElrath, Employee

  • 49,056 Points 20k badge 2x thumb
These features are only available as part of the SOQL Polymorphism Developer Preview, which has been in Developer Preview for almost 3 years, so we have no plans to support these features until, if ever, Salesforce moves in the direction of making them Beta or GA.
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
3 years!!! Wow! At least there aren't too many use cases requiring it. Or am I wrong?
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Actually, considering your use case - wouldn't record level security on the custom object not be an option?  You could open up the custom object or its fields to broader rights than the Organizational Default for RLS,  but the default would push down to the Attachment.  Skuid respects RLS,  so if you were the author (Owner) of the attachment,  you could edit it,  but if you were not the owner - it would be read only. 

Use Salesforce for security.  Use Skuid for UI.  Together it makes a pretty awesome package. 
Thanks all.  We are relying on RLS to control which Attachments the Portal User gains access.  This is controlled by the Parent object of the Attachments.  Despite my rough prototype, which Zach did a nice job emulating, it would definitely be implemented under a parent record and controlled by RLS.

I likely needed to articulate clearly that the use case here is that a portal user should be able to toggle between attachments they've (or another portal user from their account) uploaded and those uploaded by an employee.

That said... I think we're there...

I found an article that referenced how to get at the SOQL being generated. (https://community.skuidify.com/skuid/topics/model-soql-generator-for-use-in-other-appexchange-apps-i...
)

I'm satisfied with the subquery option being the best performer so going that direction in order to be able to filter the records natively filtered by RLS with an additional layer of which attachments are portal uploaded vs. employee uploaded.

skuid.model.getModel('Attachment').getSOQL()
"SELECT ContentType,CreatedDate,CreatedById,CreatedBy.Name,LastModifiedDate,LastModifiedById,LastModifiedBy.Name,Name,Description,BodyLength,Id FROM Attachment WHERE (OwnerId in (SELECT Id FROM User WHERE (((UserType in ('CSPLitePortal','PowerPartner'))))))"

Thanks again.
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
All looks good. IF you have further questions, don't hesitate to ask...