Table Summary on MODEL_LOOKUP Formulas not working

  • 1
  • Problem
  • Updated 3 years ago
  • Not a Problem
Has anyone else run across this? 
Also, can't get them to show up properly on charts

Photo of Mansour

Mansour

  • 1,932 Points 1k badge 2x thumb

Posted 3 years ago

  • 1
Photo of Mansour

Mansour

  • 1,932 Points 1k badge 2x thumb
Some additional context: the MODEL_LOOKUP is looking up a REST external model and the formula is quite complex (looking up multiple models) - see below.


IF(((VALUE(MODEL_LOOKUP("GB1_1_200","callback_leads_count","account_id",VALUE({{GB1_Account_ID__c}})))+VALUE(MODEL_LOOKUP("GB1_1_200","reward_leads_count","account_id",VALUE({{GB1_Account_ID__c}}))))/VALUE(MODEL_LOOKUP("GB1_1_200","rewards_views","account_id",VALUE({{GB1_Account_ID__c}})))*100)>0,
((VALUE(MODEL_LOOKUP("GB1_1_200","callback_leads_count","account_id",VALUE({{GB1_Account_ID__c}})
))+VALUE(MODEL_LOOKUP("GB1_1_200","reward_leads_count","account_id",VALUE({{GB1_Account_ID__c}}))))/VALUE(MODEL_LOOKUP("GB1_1_200","rewards_views","account_id",VALUE({{GB1_Account_ID__c}})))*100)
,"")
+
IF(
((VALUE(MODEL_LOOKUP("GB1_200_400","callback_leads_count","account_id",VALUE({{GB1_Account_ID__c}})))+VALUE(MODEL_LOOKUP("GB1_200_400","reward_leads_count","account_id",VALUE({{GB1_Account_ID__c}}))))/VALUE(MODEL_LOOKUP("GB1_200_400","rewards_views","account_id",VALUE({{GB1_Account_ID__c}})))*100)>0,
((VALUE(MODEL_LOOKUP("GB1_200_400","callback_leads_count","account_id",VALUE({{GB1_Account_ID__c}})))+VALUE(MODEL_LOOKUP("GB1_200_400","reward_leads_count","account_id",VALUE({{GB1_Account_ID__c}}))))/VALUE(MODEL_LOOKUP("GB1_200_400","rewards_views","account_id",VALUE({{GB1_Account_ID__c}})))*100)
,"")
+
IF(
((VALUE(MODEL_LOOKUP("GB1_400_600","callback_leads_count","account_id",VALUE({{GB1_Account_ID__c}})))+VALUE(MODEL_LOOKUP("GB1_400_600","reward_leads_count","account_id",VALUE({{GB1_Account_ID__c}}))))/VALUE(MODEL_LOOKUP("GB1_400_600","rewards_views","account_id",VALUE({{GB1_Account_ID__c}})))*100)>0,
((VALUE(MODEL_LOOKUP("GB1_400_600","callback_leads_count","account_id",VALUE({{GB1_Account_ID__c}})))+VALUE(MODEL_LOOKUP("GB1_400_600","reward_leads_count","account_id",VALUE({{GB1_Account_ID__c}}))))/VALUE(MODEL_LOOKUP("GB1_400_600","rewards_views","account_id",VALUE({{GB1_Account_ID__c}})))*100)
,"")
+
IF(
((VALUE(MODEL_LOOKUP("GB1_600_800","callback_leads_count","account_id",VALUE({{GB1_Account_ID__c}})))+VALUE(MODEL_LOOKUP("GB1_600_800","reward_leads_count","account_id",VALUE({{GB1_Account_ID__c}}))))/VALUE(MODEL_LOOKUP("GB1_600_800","rewards_views","account_id",VALUE({{GB1_Account_ID__c}})))*100)>0,
((VALUE(MODEL_LOOKUP("GB1_600_800","callback_leads_count","account_id",VALUE({{GB1_Account_ID__c}})))+VALUE(MODEL_LOOKUP("GB1_600_800","reward_leads_count","account_id",VALUE({{GB1_Account_ID__c}}))))/VALUE(MODEL_LOOKUP("GB1_600_800","rewards_views","account_id",VALUE({{GB1_Account_ID__c}})))*100)
,"")
+
IF(
((VALUE(MODEL_LOOKUP("GB1_800_1000","callback_leads_count","account_id",VALUE({{GB1_Account_ID__c}})))+VALUE(MODEL_LOOKUP("GB1_800_1000","reward_leads_count","account_id",VALUE({{GB1_Account_ID__c}}))))/VALUE(MODEL_LOOKUP("GB1_800_1000","rewards_views","account_id",VALUE({{GB1_Account_ID__c}})))*100)>0,
((VALUE(MODEL_LOOKUP("GB1_800_1000","callback_leads_count","account_id",VALUE({{GB1_Account_ID__c}})))+VALUE(MODEL_LOOKUP("GB1_800_1000","reward_leads_count","account_id",VALUE({{GB1_Account_ID__c}}))))/VALUE(MODEL_LOOKUP("GB1_800_1000","rewards_views","account_id",VALUE({{GB1_Account_ID__c}})))*100)
,"")
+
IF(
((VALUE(MODEL_LOOKUP("GB1_1000_1200","callback_leads_count","account_id",VALUE({{GB1_Account_ID__c}})))+VALUE(MODEL_LOOKUP("GB1_1000_1200","reward_leads_count","account_id",VALUE({{GB1_Account_ID__c}}))))/VALUE(MODEL_LOOKUP("GB1_1000_1200","rewards_views","account_id",VALUE({{GB1_Account_ID__c}})))*100)>0,
((VALUE(MODEL_LOOKUP("GB1_1000_1200","callback_leads_count","account_id",VALUE({{GB1_Account_ID__c}})))+VALUE(MODEL_LOOKUP("GB1_1000_1200","reward_leads_count","account_id",VALUE({{GB1_Account_ID__c}}))))/VALUE(MODEL_LOOKUP("GB1_1000_1200","rewards_views","account_id",VALUE({{GB1_Account_ID__c}})))*100)
,"")
+
IF(
((VALUE(MODEL_LOOKUP("GB1_1200_1500","callback_leads_count","account_id",VALUE({{GB1_Account_ID__c}})))+VALUE(MODEL_LOOKUP("GB1_1200_1500","reward_leads_count","account_id",VALUE({{GB1_Account_ID__c}}))))/VALUE(MODEL_LOOKUP("GB1_1200_1500","rewards_views","account_id",VALUE({{GB1_Account_ID__c}})))*100)>0,
((VALUE(MODEL_LOOKUP("GB1_1200_1500","callback_leads_count","account_id",VALUE({{GB1_Account_ID__c}})))+VALUE(MODEL_LOOKUP("GB1_1200_1500","reward_leads_count","account_id",VALUE({{GB1_Account_ID__c}}))))/VALUE(MODEL_LOOKUP("GB1_1200_1500","rewards_views","account_id",VALUE({{GB1_Account_ID__c}})))*100)
,"")
+
IF(
((VALUE(MODEL_LOOKUP("GB1_1500_1700","callback_leads_count","account_id",VALUE({{GB1_Account_ID__c}})))+VALUE(MODEL_LOOKUP("GB1_1500_1700","reward_leads_count","account_id",VALUE({{GB1_Account_ID__c}}))))/VALUE(MODEL_LOOKUP("GB1_1500_1700","rewards_views","account_id",VALUE({{GB1_Account_ID__c}})))*100)>0,
((VALUE(MODEL_LOOKUP("GB1_1500_1700","callback_leads_count","account_id",VALUE({{GB1_Account_ID__c}})))+VALUE(MODEL_LOOKUP("GB1_1500_1700","reward_leads_count","account_id",VALUE({{GB1_Account_ID__c}}))))/VALUE(MODEL_LOOKUP("GB1_1500_1700","rewards_views","account_id",VALUE({{GB1_Account_ID__c}})))*100)
,"")
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Wow.  I thought I had written a complicated formula over the weeked.  I'm not at all suprized that column summaries don't play well with formula fields.  We'll take a harder look.. 
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Ok,  I can feel Zach glaring at me.  Of course summaries work... 

A few things to look into. 
1. Are your formula fields really coming into your models as numbers (or currency) or are they strings? 
2. If you put a 0 in all the rows that are currently empty does it work? 
3. Or maybe you can turn off the "count blanks in summaries" propoerty. 
4. Looks like a multi currency org.  Is this a currency field?  Try in a non MC org,  or change the output to double not currency. 

Or maybe your formula....
Photo of Mansour

Mansour

  • 1,932 Points 1k badge 2x thumb
1. Yes, all are numbers, and the output is a percent
2. Nope, tried that too.
3. Didn't work
4. Yes, this is a multi currency org but none of the fields being used are currencies, all are numbers and percentages, and the percentages are not derived from currencies
5. I tried this with a much smaller formula ((a+b)/c)*100 and it still didn't work.

This is also impacting charts. 
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Mansour,  I think somthing else is going on here.  I can't see them fail.   What version of skuid are you on?  Have gotten the latest version from Skuidify.com/skuidreleases ? 

If that's blank, would you mind giving us login rights to your org so we can take a look at what is going on?  Send an email to support@skuidify.com  with the following information:
1. Org ID
2. Skuid Page   (maybe where your simple formula is first)
3. Field and summary table location where you are seeing the failure

We'll see what's going on...

Thanks. 
Photo of Mansour

Mansour

  • 1,932 Points 1k badge 2x thumb
Thanks Rob, just sent the details!