tricky model condition required

  • 1
  • Question
  • Updated 4 years ago
  • Answered
I have two objects I'm working with.

The first is a custom task object I'm using that can reference other tasks as parents. Tasks are assigned a "Tab" value of 0-9 for which level they belong to in the Gantt Chart tool I'm using. Tab 0 = Jobs, Tab 1 = Phases, Tab 2+ = Phase or Task.

The second is a custom equipment object with a junction table to the tasks table. So many pieces of equipment can be assigned to many tasks.

When working with a task I would like to be able to list records in the equipment object based on whether or not it has been assigned to one of the parent job/phases the task is a descendant of.

Tasks can be at level 2 through to 9 and I've created a Popup w/ a page include for the Task Detail in order to easily replicate it across each level.

Now, how can I have a model that can look throughout each level of relationship possible a task can have. For example, a simple task can be the child of phase "Rip and Replace" which is child job "Replace Shingles". But a task can also be the child of phase "Phase whatever1" which is child of phase "Phase whatever2" which is child of phase "Phase whatever3" which is child of phase "Phase whatever4" which is child of phase "Phase whatever5" which is child of phase "Phase whatever6" which is child of phase "Phase whatever7" which is child of phase "Phase whatever8" which is child of job "Job WTF!".

I'm supposing I can create all nine possible conditions with OR conditional logic set between each with "Deactivate this condition" for "If no row in Source Model, then...".

Will this break the query when a condition simply isn't possible as the relationship doesn't exist when working with a task with Tab value of 2 when trying a condition that would only exist if the tab value for the task was 9. ie. grand-child vs grand-grand-grand-grand-grand-grand-grand-grand-child?

Yeah .... 
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb

Posted 4 years ago

  • 1
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
I'm pretty sure that SOQL will break if you try to extend the condition back 9 generations in one step within a subquery.  (Show me equipment that is in the junction object where the task goes back 9 generations...)

But I think you could create a set of secondary models that bring in the equipement junction records for the grandparent tasks, and then create a final equipment model that shows the equipment that is contained in each of those secondary models (Using Or groupings, and "Deactivate this condition" settings if nothing is in the secondary models). 

Sounds like an interesting challenge.  But I think Skuid is up to it....
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Yup. I broke it. See video.



Another thing, in the video I referenced the "Assigned_Measurement" object but I meant to reference "Job_Phase_Assigned_Measurement". So, I am pointing to the right object for the model.
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Ok. I think I've something I could use. A formula field called "TreeAddress".

Id & "." & Parent__r.Id & "." & 
Parent__r.Parent__r.Id & "." & 
Parent__r.Parent__r.Parent__r.Id & "." & 
Parent__r.Parent__r.Parent__r.Parent__r.Id & "." & 
Parent__r.Parent__r.Parent__r.Parent__r.Parent__r.Id & "." & 
Parent__r.Parent__r.Parent__r.Parent__r.Parent__r.Parent__r.Id & "." & 
Parent__r.Parent__r.Parent__r.Parent__r.Parent__r.Parent__r.Parent__r.Id

Can I use this field to query against?
Photo of Moshe Karmel

Moshe Karmel, Champion

  • 8,646 Points 5k badge 2x thumb
You might want to rethink your design, this sounds pretty kluge...
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Ok. This SOQL Works. How do this into the Skuid Model Condition?

SELECT Measurement__r.Name,Measurement__r.Value__c,Measurement__r.Dimension__c,Id FROM Job_Phase_Assigned_Measurement__c WHERE (Task__r.Id in ('a1po0000000ByEC','a1po0000000Bwkz','a1po0000000BsZP'))

'a1po0000000ByEC','a1po0000000Bwkz','a1po0000000BsZP' is created using a formula field but it is getting changed at runtime.

So close!

See pic.

Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
This could work quite well so long as the treeaddress formula field can be queried against.
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Can I use the Global Merge function urlencode or urldecode to get the value to be 'a1po0000000ByEC','a1po0000000Bwkz','a1po0000000BsZP' in the query by replacing the ' with %27 in the formula field?

ie. {{#urlEncode}}{{TreeAddress}}{{/urlEncode}}
or
{{#urlEDecode}}{{TreeAddress}}{{/urlDecode}}

Where TreeAddress is:
"%27" & Id & "%27" & "," & "%27" & Parent__r.Id & "%27" & "," & 

IF(ISBLANK(Parent__r.Parent__r.Id),"","%27" & Parent__r.Parent__r.Id & "%27" & 
IF(ISBLANK(Parent__r.Parent__r.Parent__r.Id),"","," & "%27" & Parent__r.Parent__r.Parent__r.Id & "%27" & 
IF(ISBLANK(Parent__r.Parent__r.Parent__r.Parent__r.Id),"","," & "%27" & Parent__r.Parent__r.Parent__r.Parent__r.Id & "%27" & 
IF(ISBLANK(Parent__r.Parent__r.Parent__r.Parent__r.Parent__r.Id),"","," & "%27" & Parent__r.Parent__r.Parent__r.Parent__r.Parent__r.Id & "%27" & 
IF(ISBLANK(Parent__r.Parent__r.Parent__r.Parent__r.Parent__r.Parent__r.Id),"","," & "%27" & Parent__r.Parent__r.Parent__r.Parent__r.Parent__r.Parent__r.Id & "%27" & 
IF(ISBLANK(Parent__r.Parent__r.Parent__r.Parent__r.Parent__r.Parent__r.Parent__r.Id),"","," & "%27" & Parent__r.Parent__r.Parent__r.Parent__r.Parent__r.Parent__r.Parent__r.Id &"%27" 
))))))
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Woot Woot! Wizardry at work!!!!

One formula on my task to make the TreeAddress. BLND_TreeAddress__c

Another formula in my junction table to make the ID for the Job/Phase the measurement is assigned to. Job_Phase_ID__c

So, my formula works with a tweak to the operator of the condition to be "in" via XML. The "in" operator isn't allowed in the page builder when trying to compare a field value to a single specified value.

Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
While I don't disagree with Moshe about the Kludge level of this.. I am impressed that you were able to get this working.  

Woot Woot indeed! 
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Is there any better way to do this than this HACKJOB I've done!

Not sure how this worked yesterday, but it certainly didn't this morning. Found another way to do this.

I create the following ancestor list text      in ('ID_Here','ID_Here')       with a formula field. I then hacked the XML condition as follows.

<condition type="modelmerge" field="Task__r.Id" operator=""  mergefield="BLND_TreeAddress__c" novaluebehavior="noquery" model="TDPUCurrentTask" enclosevalueinquotes="false"/>

1. Took out the operator.
2. Changed enclosevalueinquotes to false.

Bam! I get the following SOQL. The underlined bit is my formula. :S

SELECT Measurement__r.Name,Measurement__r.Value__c,Measurement__r.Dimension__c FROM Job_Phase_Assigned_Measurement__c WHERE (Task__r.Id in ('a1po0000000Bwkz','a1po0000000BsZP'))
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Are Tasks Child records under the Job_phase_assigned_Measurment object?  If this is the case I think you will need to create a subquery condition that only shows measurments where there are Tasks that are in the list provided in your formula. 
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Tasks, Phases and Jobs are all in the same table as different record types.
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
It started out as a necessity, but I'm starting to actually like it.
Photo of Ben Hubbard

Ben Hubbard, Employee

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

I wouldn't rely on this working in future versions of Skuid.  You shouldn't have been able to do this in the first place.  There's a bug we need to fix with model merges.
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Can conditions be entered via SOQL query manually? If not, is this ever going to be possible?
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Or is what I'm doing supposed to be possible using Models? My tasks object refers to itself of parents. This allows for a dynamic level of ancestry. So a task can have multiple ancestor tasks. The object has record types Job, Phase & Task.

My measurements object uses a junction object to assign to tasks in a many to many fashion. I'm wanting to query all assigned measurements to tasks that are an ancestor of any level to the current task.
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Hi Pat, I don't think we have any plans to let users enter arbitrary SOQL manually as conditions.  Especially when you allow merging in data, you run into some pretty thorny issues pretty quickly.  I would go back to your approach you had when you made the video.  I think you need to make sure your source model has the fields that you're specifying as your "merge field" in your conditions.  Basically, for each level, you need to check the box adding that field to the model, and then traverse the relationship to get to the next level.  I would try adding your 9 or conditions one at a time and see how deep you can get before salesforce complains. (It may not.)
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
K. Well .... that's a better solution. Only thing is that I can only get up to 5 levels up the tree. Is that a Skuid or Salesforce limitation? A total of six levels isn't too bad if I want this to be done without hacking the XML.

Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
That's a Salesforce SOQL limitation. 
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Another random thought you could try.  Since your formula field seemed to work fine, you could create 9 formula fields and put those in the OR statement.  That way you wouldn't be directly traversing those relationships in the SOQL.  Might work, might not.