Ui-only formula field on aggregate model.

  • 2
  • Question
  • Updated 3 years ago
  • Answered
We're currently using a bunch of javascript to make percentage calculations on an aggregate model.

I'd love to replace that with a ui-only formula field.

Here's my attempt at a formula:

{{TotalRecords}} / MODEL_LOOKUP('numberOfSchedulingLeads', 'TotalRecords', 'Id', '4')

The formula isn't returning anything. Is my syntax ok?

The fourth row of my model (Id=4) is the roll-up summary row. TotalRecords and Percentage are both COUNT(Id), and we're using javascript to change the values of Percentage to a percent of the column sum.

The ui-only field is called PercentUI, but isn't showing up in the model at all.


Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb

Posted 3 years ago

  • 2
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
This is a place where our documentation needs to be improved.  ( I don't think we have a tutorial on UI Only fields published yet...)

The syntax for lookup fields is as follows: 

MODEL_LOOKUP("ModelName","FieldNametoBeReturned","FieldNameToMATCHinCurrentModel",{{MatchingValueFromCurrentModel}})

The first three properties need to be in quotes. 
The last property is a data merge, so it needs to be in Braces. 

Hopefully this helps. 
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
So, if I want to return the value of the TotalRecords field from the numberOfSchedulingLeads model where Id = 4 (or actually, just get the value of {{TotalRecords}} from the last row of the model... how would I do that? can I do that with a model lookup function? Seems like I actually need a query of some kind?

The current model is the numberOfSchedulingLeads model... not sure if that makes a difference.
Photo of Anna Wiersema

Anna Wiersema

  • 10,890 Points 10k badge 2x thumb
Hey Ryan, this should be doable in Skuid. You create the formula field in the "aggregations" section of your aggregate model, and then just drag it in to your table.

What version of Skuid are you running? in some of the earlier Banzai versions you could only add aggregate model fields from the Add > Model Field(s) button on your table.

Also, make sure your table is set to the right model. Do either of these fixes work for you?
Photo of Ryan Denis

Ryan Denis

  • 478 Points 250 badge 2x thumb
Thanks Anna. Dragging and dropping did get the formula on to the table. I wasn't able to add it using the add model fields button. However my formula isn't working. It's showing up as 0.00 so I guess my formula is wrong. How do I reference the other aggregations in the formula? 
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Ryan, you can use merge syntax with the unique id that you give the other fields, i.e. {{sumMyOtherAggFieldc}} 
Photo of Ryan Denis

Ryan Denis

  • 478 Points 250 badge 2x thumb
Yea that was it. Dumb mistake. But this is huge so thanks for the help guys,.
Photo of Anna Wiersema

Anna Wiersema

  • 10,890 Points 10k badge 2x thumb
Not dumb!  Aggregate model field aliases can be tricky. Happy Skuidifying!
Photo of Anna Wiersema

Anna Wiersema

  • 10,890 Points 10k badge 2x thumb
Guys! I just (finally) published the ui-only fields tutorial. Pretty basic, but at least there's something! http://help.skuidify.com/m/models-conditions-filters/l/378313-ui-only-fields
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
The Staff of Gandalf for only $2,500! What a steal!!
Photo of Anna Wiersema

Anna Wiersema

  • 10,890 Points 10k badge 2x thumb
You'd be surprised what you can find on the internet ;)