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
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”, “”)
Hope this helps!
Hi Barry,
Thanks for the quick response! I have tried the name versus the Id. We do not have any names that are the same however.
I’m not savvy enough to know how to use the developer console to help with this issue. I did try entering the skuid.utils.modelLookup you gave me and entered a few names and it returned an “undefined” each time. Not sure what that means.
Thanks,
Chelsea
Hi Chelsea -
Good to know you don’t have any duplicate names. That said, I’d still strongly recommend switching to an Id if possible. In the future, you might end up with a new team member with the same name in which case the modelLookup could return the wrong data.
That said, given your current situation, using names should return the correct result.
In the situations where the data is incorrect (e.g. 186,966.91 & 45,238.60), is 186,966.91 a valid number somewhere in the Retainage model?
This one is difficult to troubleshoot without seeing a more complete sample but one other thing you could do - Add another UI only field to OpenInvoice that is a formula and uses modelLookup. Instead of returning the sumRetainageHeldc field, return the Owner field from Retainage. This will provide an way to see if it’s even finding the right row in the Retainage model and if not, which row (based on Owner name) its finding.
Hi Barry,
Thanks for your continued help! I tested showing the name using the Model_Lookup and this worked perfectly fine. Afterwards, I changed the grouping to Owner Id, as this totally makes sense to protect from people with the same name.
I’m going to dig into the numbers as you suggested, however, I did try adding the same retained amount field with a different Alias and it produces the same incorrect numbers as the Model_Lookup…
Now I am totally perplexed. How could the same field in the same model return different numbers?? (This would be the 2 columns on the right).
It appears my Model_Lookup field (left column) is pulling the same numbers as my new retained amount field. I have not changed anything to conditions or the like.
I tested adding the same aggregate field to a different model to make sure this was not messing up the model, but as long as I changed the Alias name it was totally fine.
Any ideas why this would happen? Thanks again.
I’m not going to try to answer all the question - but just want to further dig into Barry’s suggestion of using the console to see what is going on. This is an indespensable debugging tool because it can show you the raw data in a Skuid model (as welll as a number of other properties about the page).
Right click somewhere on your page and select “inspect element” to bring up the browser’s development tools. (I’m thinking about Chrome here, but there are similar tools in all Browsers)
Then click on the “console” tab of the developer tools.
In the console you can type any javascript instructions available to the page and push enter to execute. The easiest one is this:
skuid.model.map()
This will give you all the models on the page, and all thier properties. Expand and contract the triangles to get more detail.
I generally want a more granular view. So I type something like this.
skuid.$M('MyModelName').data
This gives me all the data in the model. (.soql gives the raw query. .conditions gives a list of the conditions and how they are working … )
This should give you better answers about what is going on in your page.
Thanks Rob! That was super helpful for digging into the numbers.
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!
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.
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
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”…
Great news Chelsea, glad you got it sorted out!
