Skip to main content

I have a Model called Parent with zero or three records, with with an “Allowed” number field in salesforce.


I have a Model called Child, related to the Parent model as you would expect. For each row in the Parent model there may be up to a dozen or so rows in the child model. The Child model has a “Used” number field in salesforce.


I want to display a table of the Child model with a ui-only formula field that shows a “Remaining” value.


Suppose the data in Parent looks like this:

Row | Name | Allowed

1 | A  | 10

2 | B | 5


And the data in Child looks like this:

Row | Parent | Used

1 | A | 2

2 | A | 1

3 | A | 5

4 | B | 4
5 | A | 12


Is there any way I can write a ui-only formula so that I can display a table on the Child model that looks something like this?

Row | Parent | Used | Remaining

1 | A | 2 | 8 (=10-2)

2 | A | 1 | 7 (=8-1)

3 | A | 5 | 2 (=7-5)

4 | B | 4 | 1 (=5-4)
5 | A | 5 | 0 (=MAX(2-5,0))

Merry Christmas Matt!  This should be pretty trivial to do as follows:

1.  Make sure your Parent Model loads before your Child 
2.  Add a Model Lookup UI only formula field to your child model.  Formula will be somthing like this:  MODEL_LOOKUP(‘Parent’,‘Allowed’,‘Name’,{{Parent}})

More details in the doc here:  https://docs.skuid.com/latest/en/skuid/models/ui-only-models-fields.html#referential-data-access

3.  Add a second formula field that subtracts Used from the value of the field in step 2… 

I think that should be it. 

Rob,


I started with exactly what you suggested, but with that process the Child model rows don’t take each other into account.


To simplify, I’m looking for a formula that will basically do this:


Parent Allowed] - ]Sum of Used from all Preceding Child Rows on Same Parent] - ]Used from Current Row].


I can get Parent Allowed and the Used from Current Row easily.


How can I get the “Sum of Used from all Preceding Child Rows on Same Parent”?


To Clarify:


Row | Parent | Used | Sum of Used from all Preceding Child Rows on Same Parent

1 | A | 2 | 0 (no preceding rows on same parent)

2 | A | 1 | 2 (from row 1)

3 | A | 5 | 3 (from rows 1 and 2)

4 | B | 4 | 0 (no preceding rows on same parent)

5 | A | 5 | 8 (from rows 1 , 2 and 3)

Ahh yes.  That is a different story.  And not so trivial. 

Building the running talley of used items in the child model is going to be tricky.  You might be able to create a different model that aggregates the child records - summing the used values and grouping by parent.  That would give you a total to work with - but wouldn’t give you the motion you want to see row by row. 

I was thinking about using the model index - 1 to get values from previous rows,  but IIRC you can’t use concatenation to construct merge syntax on the fly.   You’d also have to do some conditionals to ignore the previous row if the parents were different,  and that requires your order to be very particular.  (Brittle brittle). 

Maybe code is the answer here?  I’m not seeing anything else.   Sorry. 

Once again - Merry Christmas! Enjoy the last few days of banging on Skuid in 2018…

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings