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

  • 1
  • Question
  • Updated 3 years ago
  • Answered
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!
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb

Posted 3 years ago

  • 1
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Can you show a video of conditions that don't work?
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,470 Points 10k badge 2x thumb
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".
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Ben is right - you can add the aggregate field alias to your conditions in the XML. 
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Roll-up fields are awesome for this. Totally forgot about their existence!

Between roll-ups and XML hacking, I'm all set. Thanks, gentlemen.
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
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.
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
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
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
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!
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,470 Points 10k badge 2x thumb
Have you tried a child relationship with an order by and and limit 1?
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
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!
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Those Child Relationships!  A beautiful thing! 
Photo of Jeff Rutter

Jeff Rutter

  • 1,162 Points 1k badge 2x thumb
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!
Photo of Jeff Rutter

Jeff Rutter

  • 1,162 Points 1k badge 2x thumb
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
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
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. 
Photo of Jeff Rutter

Jeff Rutter

  • 1,162 Points 1k badge 2x thumb
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?
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
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
Photo of Jeff Rutter

Jeff Rutter

  • 1,162 Points 1k badge 2x thumb
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!
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
You might find this post helpful, Jeff......
https://community.skuidify.com/skuid/...
Photo of Jeff Rutter

Jeff Rutter

  • 1,162 Points 1k badge 2x thumb
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!
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
Bill points out using two separate models to return the records you want. Have you looked at "Adopt Rows" action. This allows you to adopt the rows of one model on ObjectA into another model on ObjectA. It may work in this case. You could set up two models on the Task Object, then adopt the rows of one into the other or adopt the rows of both into a third model. Once all the rows have been adopted into a single model, you can display them all in a single table. I have never done this, but it is worth a look.
(Edited)
Photo of Jack Sanford

Jack Sanford, Champion

  • 8,322 Points 5k badge 2x thumb
I have two aggregate models and I want to use a condition that limits one aggregate model to only those record IDs that are in the other, is this possible?

I tried a condition of Record Id is IN the Id Field from my parent model, but I get an error that the Id field is not coming in on my parent Aggregate model. 

Next I tried hacking the XML so the condition is Record Id IN the 'countdistinctId field from my parent model, but I get an error that there is not record a Id that = 12 (12 is the number of rows in my parent aggregate model).

My use case is I have an aggregate model that pulls in fields from several other aggregate models in a single table, because I need basically a matrix table that shows columns of an aggregate value for each calendar month. That's working, using UI-Only fields with Model_Lookup formulas.

The tricky part is that I also want to use table filters on this table. Right now, filters only act on the values from my main model, and not the other models.

I was thinking I could use a model action that triggers when my parent model is requeried, and the actions are set a condition on each of the other models and requery them. 

Open to other ways of achieving this, like if there's some way to say, "if this filter is activated on my parent model, then set the same filter on my child models and give them the same value." Maybe a snippet could do something like that?

Thanks for any advice! Let me know if posting code would help.
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
Neat project. My only thought is instead of using actual table filters, locate a navigation component above the table. Nav components are buttons that do whatever you want so you could set one of the navigation items to activate a condition and requery 5 models at the same time.
If you need the ability to get filter suggestions like a table filter set to automatic would provide, you may be able to accomplish this with the search component. The search component could be set to search the model fields that you want to filter on, then activate conditions on models when the selection is made (I believe). This two wound have to be inserted above the table instead of in its header like an actual table filter, but with some wrappers and formatting, you could probably make it look pretty nice.
Photo of Jack Sanford

Jack Sanford, Champion

  • 8,322 Points 5k badge 2x thumb
Great ideas!!! Will post if I get something working. 
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Have you seen Pat's genius idea here:  https://community.skuidify.com/skuid/topics/cross-model-linked-conditions   It might be what you are looking for
Photo of Jack Sanford

Jack Sanford, Champion

  • 8,322 Points 5k badge 2x thumb
Yes! I think that will do it. Should work the same for Ag to Ag cross models. 
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Yes, I regularly use pat's cross model linked conditions with AG models. Works like a charm. :)