tricky model condition required

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…

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.

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?

You might want to rethink your design, this sounds pretty kluge…

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.

This could work quite well so long as the treeaddress formula field can be queried against.

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” 
))))))

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.

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! 

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’))

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. 

Tasks, Phases and Jobs are all in the same table as different record types.

It started out as a necessity, but I’m starting to actually like it.

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.

Can conditions be entered via SOQL query manually? If not, is this ever going to be possible?

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.

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.)

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.

That’s a Salesforce SOQL limitation. 

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.