Condition on basic model to determine if Id of row is in grouping of aggregate model?

Apparently models are limited to two active ‘result of a subquery’ conditions.

I thought I’d get around this by creating a couple aggregate models, and then using ‘field from another model’ conditions to point to the agg models. But it doesn’t seem to work.


Let me explain the full situation:

I have the objects Patient_Case__c, Interaction__c, and Signature__c. Interaction__c is a child of Patient_Case__c, and Signature__c has a lookup to Patient_Case__c.

I want a table which shows the rows from Patient_Case__c where:

  1. At least one of the Interactions is a complete appointment. i.e. Id IN (SELECT Patient_Case_c FROM Interactions__c WHERE Interaction_Category__c = Appointment AND Status__c = Complete).
  2. None of the Interactions is a Scheduled appointment. i.e. Id NOT IN (SELECT Patient_Case_c FROM Interactions__c WHERE Interaction_Category__c = Appointment AND Status__c = Scheduled).
  3. And there is no medical chart signature. i.e. Id NOT IN (SELECT Patient_Case__c FROM Signature__c WHERE Signature_Type__c = MD Medical Chart AND Signature__c != null)

But that’s three subquery conditions.

I tried to create aggregate models on the Interaction__c object with a grouping on Patient_Case__c to get a list of the unique Patient Case Ids that had the conditions for 1 and 2. But it doesn’t seem that I can use a ‘field from another model condition’ to use those lists.

What’s the best way to structure this model?

Thanks!

Can you show a video of conditions that don’t work?

Are Interactions master-detail on Patient Cases? If so, you might want to consider Roll-Up Summary fields.

If that won’t work, I think if you go into the XML you can use aggregate models as sources for “Field from another model”.

Ben is right - you can add the aggregate field alias to your conditions in the XML. 

Roll-up fields are awesome for this. Totally forgot about their existence!

Between roll-ups and XML hacking, I’m all set. Thanks, gentlemen.

I have a somewhat similar situation. I want to display a list of the most recent records of an object (Call it ManyRelatives) grouped by the related record on a related object (Call it SingleRelative). This is NOT a parent-child relationship, so I can’t use Salesforce Rollup fields. It is a one-to-many relationship where SingleRelative is the one and ManyRelatives is the many. I can do this with an aggregate model, but I want to display more columns in my table. If I add these fields to group by, they display, but I then my group by is screwed up and too many records display. I tried creating an additional non-aggregate model on the same ManyRelationships object and put a condition on it where the record ID must be in a field on the aggregate model. I was able to do this declaratively and didn’t need to hack XML. Since the aggregate model only displays the records I want (the newest records), the condition on the non-agg model should restrict the query to only the newest records. However, if I add this condition then on page load I get the attached error message. Any ideas on how to accomplish this would be helpful. Thanks.

Raymond,

Could you build a table on the Single Relatives and use a Child Relationship field to pull in the fields you want to show from the Many Relatives?  You can pull in the fields and use some table html to format fields from Many Relatives.

Thanks,

Bill

Thanks for the response, Bill. I like the idea and it would work perfectly except that I need a way to display only the newest child of each parent. My long-winded post got way too confusing. I only used aggregate fields because you can use them to identify the newest child. I’m unaware of an condition in basic models that will identify “newest”. So, to restate my question, “is there a way to display only the newest child record of each parent in a table?” Thanks for your help!

Have you tried a child relationship with an order by and and limit 1?

No! But I should have. I think this will work. I need to be able to click through to the child but I should be able to create a hyperlink in the child template. Thanks!

Those Child Relationships!  A beautiful thing! 

I also recently had a similar situation come up that relates to this I believe. So far I haven’t been able to get it working: Basically, I’m already showing a list of tasks on an Account’s detail page that relate to the account and I also want to show tasks where WhatId is related to any of the Opportunities related to the Account. I figured I would use an aggregate model on the Opportunity for this and then group by Opportunity Id to get the list of Ids related to this AccountId I’m currently viewing. In a table I can see a list of all Opportunities related via the grouping field of Opportunity Id. So I want the tasks to be able to look at the grouping field of Ids that would grow over time. I tried to add a Task model condition to look at all Ids in the aggregate model listing the Ids of all related Opportunities in the system and couldn’t get the condition to work on field from another model by going into the xml and replacing a standard field with the grouping field alias. Should we in this case name the field alias anything like “idFieldAlias” and within the XML change the task model condition field from another model to be “idFieldAlias” and it should work? Thanks for any clarification you can provide!

Jeff,

Try this sub-query join on the Task object…

Thanks,

Bill

Hi Bill, Thanks for these screenshots. My post might not have fully explained the whole issue so my apologies, but I believe it relates to how this original question. In standard Salesforce you’re able to see on an Account other activities that are related to the Account for things like Opportunities for example all listed together in one section or table/related list. For one of our clients they want the ability to on an Account detail page where we already show all Tasks related to the Account in tables (split between open and closed tasks) to be able to in the same table/list show a mix of open tasks that are related to the account we are currently on and also any Opportunity that the account might be related to through the Opportunities AccountId field or one other lookup field on the Opportunity. In a sense they want the ability to see a roll-up into these lists of all related activity on the account in each list. From what I can tell with a sub-query join like you’ve shown we can’t add another condition that would show all tasks related to the account and then group the conditions (1 OR 2 for example) since grouping conditions with sub-queries doesn’t seem to work. With my clarification does this mean I’d have to look into Aggregate Opportunity model to get all Opportunity Id’s with ties back to the current Account I’m on when dealing with lots of data? Let me know if I missed the point of what you shared and thanks for your reply! Jeff

You might find this post helpful, Jeff… https://community.skuid.com/t/task-object-reference-the-account-of-a-contact-on-a-task

Jeff.  I didn’t read the full chain here.  But there may be a simpler option:   A task whose “whatID” is an opportunity is going to automatically get the AccountId of the opportunities account.  So if you look for tasks with a particular AccountId - this will include all those opportunity tasks. 

No problem, Rob. Thanks you guys for the feedback on my question. In most cases this would work and gets us almost there by showing all tasks based on AccountId. The only part this doesn’t work for is the fact that Opportunities can be related to another Account through a lookup field on the Opportunity from what we have built. In such a case an Account would technically be related to an Opportunity if they were just in the lookup field alone and we’d want to see that Opportunities tasks in the lists too. Is this possible with Skuid conditions?

Thanks Raymond, this brings me close but in my latest reply to Rob I explain what part I’m still trying to figure out. Thanks!

Jeff,

Showing this in a single table as you have found out is not supported using SOQL (Skuid as well). The only workaround that I have thought of is to create 2 models to bring in the Task data - one each based on the tasks you want to see.  Then create a script to ‘import’ one model’s rows into the other and then display the ‘merged’ model in your table.

Thanks,

Bill

Bill, Thanks for the message and clarifying this. I was just thinking I could probably just show another table of related tasks in a sub-tab setup within a tab, but may bug you about your idea if what I’m going to try doesn’t work and I have further questions on it! Thanks again!