Model_Lookup Not Returning Correct Numbers

  • 1
  • Question
  • Updated 3 years ago
I'm attempting to use the Model_Lookup function on 2 aggregate models. The first model is OpenInvoice that groups by owner and sums the invoice amount. The second model is Retainage that groups by owner and sums the retained amount. I had to create a different model for Retainage because it has different conditions.

I am trying to use the Model_Lookup on the Open Invoice model to show the Retained amount. The formula is returning numbers, but when I compare a table of the Retainage model to the Model_Lookup UI-Only field in the OpenInvoice model some of the owner's retained amounts are different.

The Model_Lookup formula is: MODEL_LOOKUP("Retainage", "sumRetainageHeldc", "OwnerNames", {{Owner}})

Where OwnerNames = grouping by owner name on the Retainage model and Owner = grouping by owner name on the OpenInvoice model.

This is the comparison of what is returned:


I've verified that the right hand column, which contains a table of the Retainage model is indeed correct. Does anyone have any ideas as to why some lines will return correct numbers and others do not?

Any help is much appreciated!
Chelsea
Photo of Chelsea Curtis

Chelsea Curtis

  • 1,300 Points 1k badge 2x thumb

Posted 3 years ago

  • 1
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Hi Chelsea - The first thing that comes to mind is that it appears you are using a "String" field (Owner).  Possibly there are "Owner"s with the same name?  I'm not sure and would have to test but I'm guessing model lookup finds the first match.  Ideally, you would be looking up against an Id field instead of a name/string field.  Where the numbers do not match, correspond what number is returned and determine where in the source model it is coming from.  That might clue you in to what is going on.

One other thing you can do if you are familiar with the browser developer console is open up the console and execute the modellookup directly in there.  Try various scenarios to see what results you get.  It might help narrow down the problem.

In a developer console, input the following and hit enter:
skuid.utils.modelLookup("Retainage", "sumRetainageHeldc", "OwnerNames", "<putanameinhere>")

Hope this helps!
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Hi Chelsea -

The info Rob provided will give you good visibility to the data in the models and the models properties.  Unfortunately, it's likely going to take some further sleuthing to figure out why modelLookup and/or aggregates are not working as you expect.

- Were you able to identify anything from looking at the data itself?
- Do you have any conditions applied in the model itself and/or possibly in the table that you are displaying this in that would be influencing the result?

I can't think of anything outside of that that might be causing the behavior you are seeing.  It could be a configuration issue in the way you have things set-up or a bug in Skuid, difficult to say at this point.

If I were tackling this myself, I would try to simplify things as much as possible.  If you could come up with a similar scenario on stock objects (e.g. Accounts, Users, Contacts, Opportunities, etc.) you could mimick what you are doing in your page with standard objects.  From there, you could create a simple page and post it here so that others (including myself) could look at to see what is going on.  Unfortunately, given your objects/fields are custom, there is no way to really reproduce the scenario on this end.  

Short of that, possibly you could grant login access and someone on the Skuid team could look at it up close.

Sorry I can't be of more help.  If you can create a sample page I'd be happy to take a closer look.  Hope this helps some!
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Hi Chelsea -

One other thing I thought of that you'll want to check.   On the model itself, there is a property called "Max # records".  You'll want to make sure that the value for this is empty in order to ensure ALL records are being retrieved.  It's possible you have a value on this property in which case you're numbers would SUM incorrectly since not all the data is being included.
Photo of Chelsea Curtis

Chelsea Curtis

  • 1,300 Points 1k badge 2x thumb
Barry,

Can't tell you how much I appreciate your help! I started working through your suggestions and using the developer console as Rob outlined.

I found that the column I checked as the correct data, the retained amount in the right column, was actually pulling from a different field. So the reason the 2 aggregates pulling the same field in my last post were different is because the older aggregated field pulled a different field "Retainage_Balance__c".

The strange part is that I checked the api names for both aggregations and the field it was pulling and they were both pulling from the same "Retainage_Held__c" field...

So in short, the Model_Lookup was not the issue at all. I thought I was supposed to be pulling the "Retainage_Held__c" field based on what was being returned, however it was supposed to be the "Retainage_Balance__c".

I'm not sure why this aggregated field said the api name it was using was "Retainage_Held__c" but was pulling "Retainage_Balance__c". But I'll let it go now that it's working properly!

Thanks again,
Chelsea
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Since you were using aggregate models,  the names used in Skuid to pass data back and forth is the "alias" value.   I bet that when you look at the aggregate model field,  you will find that the alias was "Retainage_Held__c"....
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Great news Chelsea, glad you got it sorted out!