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?
I have two objects I’m working with.
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 & “.” &
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.
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?
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”/>
- Took out the operator.
- 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.
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.