I've managed to get Ui-only formula fields to work using aggregations within the same model, but am having trouble pulling in a number from a different model.
My goal is to show an an average, by dividing a count from one model by a distinct count from another. I think I should be able to do this with a model_lookup in a Ui-only formula field.
Here's what I have, please let me know if there's something I am doing wrong, or if I'm running into the limitations of Skuid/salesforce.
Thanks in advance for any help!
My goal is to show an an average, by dividing a count from one model by a distinct count from another. I think I should be able to do this with a model_lookup in a Ui-only formula field.
Here's what I have, please let me know if there's something I am doing wrong, or if I'm running into the limitations of Skuid/salesforce.
Thanks in advance for any help!
-
918 Points
Posted 4 years ago
-
2,250 Points
Rob Hatch, Official Rep
-
44,968 Points
I have a suspicion that your UI only field is not actually getting populated. Put that field in a template, or build a table to see if you are actually getting values.
I think your model lookup function is returning a string, which is not working in your divide by calculation. You might wrap the model lookup section in a "Value()" Function.
I think your model lookup function is returning a string, which is not working in your divide by calculation. You might wrap the model lookup section in a "Value()" Function.
-
918 Points
Thanks, Rob.
I tried wrapping the formula with VALUE() functions:
VALUE({{TotalCancelsByMonth}}) / VALUE(MODEL_LOOKUP("MonthlyActiveClients","countdistinctCoaches","cmSnapshotDatec",{{cmEndDatec}}))
But it still won't return anything. I'm not sure if I'm using the model_lookup function correctly. I think the first two arguments are correct, but I'm not sure about the last two. Both models are grouping data at the calendar month level.
I tried wrapping the formula with VALUE() functions:
VALUE({{TotalCancelsByMonth}}) / VALUE(MODEL_LOOKUP("MonthlyActiveClients","countdistinctCoaches","cmSnapshotDatec",{{cmEndDatec}}))
But it still won't return anything. I'm not sure if I'm using the model_lookup function correctly. I think the first two arguments are correct, but I'm not sure about the last two. Both models are grouping data at the calendar month level.
Rob Hatch, Official Rep
-
44,968 Points
Here is a little help on the four arguments of the model lookup formula.
Arg 1: Model ID of what you are looking up against.
Arg 2: Field id of the value that you want to return into your UI only field. (in this case - in an aggregate model, you use the alias fieldname)
Arg 3: Field id in the Model from Arg 1 that is used to match against.
Note - Arguments 1 - 3 need to be in quotes.
Arg 4: Field from the model you are building the UI Only model on that you want to pass over in order to retrieve. This argment is usually in our merge syntax.
So... this is what you are telling skuid to do: Retrieve the count of coaches from the MonthlyActiveClients model when the SnapshotDate month is the same as the EndDate month from the current model.
Does that make sense?
Arg 1: Model ID of what you are looking up against.
Arg 2: Field id of the value that you want to return into your UI only field. (in this case - in an aggregate model, you use the alias fieldname)
Arg 3: Field id in the Model from Arg 1 that is used to match against.
Note - Arguments 1 - 3 need to be in quotes.
Arg 4: Field from the model you are building the UI Only model on that you want to pass over in order to retrieve. This argment is usually in our merge syntax.
So... this is what you are telling skuid to do: Retrieve the count of coaches from the MonthlyActiveClients model when the SnapshotDate month is the same as the EndDate month from the current model.
Does that make sense?
-
918 Points
Thanks for the detailed explanation!
That makes sense to me. However, it's perplexing, because I think I've set up my model_lookup correctly.
I think the issue must be in the Arg 4, because I don't see any room for error in the first 3. Can you explain more "This argment is usually in our merge syntax" statement?
That makes sense to me. However, it's perplexing, because I think I've set up my model_lookup correctly.
I think the issue must be in the Arg 4, because I don't see any room for error in the first 3. Can you explain more "This argment is usually in our merge syntax" statement?
Matt Sones, Champion
-
32,370 Points
Josh, What is cmEndDatec?
It may be that you're passing a date that doesn't exactly match.
It may be that you're passing a date that doesn't exactly match.
-
918 Points
Hi Matt,
Thanks for the assistance!
cmEndDateC is the grouping on my 'CancellationsThisYear' model. It's a 'date' field in salesforce, that I applied the calendar month "Function" to in the grouping of the model.
I've attached images of the groupings and the native fields in salesforce.
In my mind, grouping a date field by calendar month should return the same thing as any other date field grouped by calendar month. Am I missing something here? I've used a template within a table to display the data and both cm date fields process fine.
Thanks for the assistance!
cmEndDateC is the grouping on my 'CancellationsThisYear' model. It's a 'date' field in salesforce, that I applied the calendar month "Function" to in the grouping of the model.
I've attached images of the groupings and the native fields in salesforce.
In my mind, grouping a date field by calendar month should return the same thing as any other date field grouped by calendar month. Am I missing something here? I've used a template within a table to display the data and both cm date fields process fine.
Matt Sones, Champion
-
32,370 Points
Yep. It seems like the formula you have should be working.
-
2,250 Points
This is silly, but I think I found a way to make it work.
Create a new UI-Only field that shows the value of your close date in a number format. Do that for both fields in the models you are connecting via the MODEL_LOOKUP function. Then, when writing the formula, use the Values to connect the two instead of the actual fields.
Screenshots below may help.
Create a new UI-Only field that shows the value of your close date in a number format. Do that for both fields in the models you are connecting via the MODEL_LOOKUP function. Then, when writing the formula, use the Values to connect the two instead of the actual fields.
Screenshots below may help.
-
918 Points
THANK YOU!
It works flawlessly. I appreciate the creative solution. Had you run into a similar issue? Wondering if this is an isolated problem with dates and date functions.
Thanks to all three of you for helping solve this problem.
It works flawlessly. I appreciate the creative solution. Had you run into a similar issue? Wondering if this is an isolated problem with dates and date functions.
Thanks to all three of you for helping solve this problem.
(Edited)
-
2,250 Points
Ran into it this morning, which made me think of your post. It seems to be specific to aggregate models.
Rob Hatch, Official Rep
-
44,968 Points
Kudos to Mansour. I think we're going to have to crown him as king of the formula fields.
Skuidward Tentacles (Raymond), Champion
-
18,336 Points
This post was very helpful. Thank you to all who contributed!
-
8,900 Points
I am sure I have something in the wrong order?
The UI-field is on the LeadPerson Model and I am trying to pull information in from Entity_Person. I have the Entiry_Person loading first.
Here is what I have in a formula field returning text:
MODEL_LOOKUP("Entity_Person","View_Person__c",
"Entity_ID__c",
{{Lead_Referring_Entity_ID__c}})
Thanks in advance,
Bill
The UI-field is on the LeadPerson Model and I am trying to pull information in from Entity_Person. I have the Entiry_Person loading first.
Here is what I have in a formula field returning text:
MODEL_LOOKUP("Entity_Person","View_Person__c",
"Entity_ID__c",
{{Lead_Referring_Entity_ID__c}})
Thanks in advance,
Bill
Josh Merritt
I think the problem lies in my model_lookup formula. I tried wrapping both parts in VALUE() functions but it's still not returning anything.
Aggregating the field at the chart level did not work and querying the model on page load did not seem to do anything either.