This is a 2 part question. I am using a LOOKUP_MODEL UI-only formula field on a simple Model (“Reps”) to pull in aggregations from an aggregate Model (“InboundCallsByRep”).
1. Is it possible to use IF logic in a formula containing LOOKUP_MODEL?
It seems like it should be, but I can’t make it work.
My MODEL_LOOKUP formula (returns blank if there are no aggregations for that rep):
MODEL_LOOKUP(“InboundCallsByRep”,“countId”,“ownerId”,{{Id}})
What I tried:
IF(MODEL_LOOKUP(“InboundCallsByRep”,“countId”,“ownerId”,{{Id}})==‘’,0,MODEL_LOOKUP(“InboundCallsByRep”,“countId”,“ownerId”,{{Id}}))
I also tried ==0
What eventually worked – a second formula field with the IF logic for display
IF({{InboundCalls}}==‘’,0,{{InboundCalls}})
InboundCalls is the first MODEL_LOOKUP formula field
2. Is it possible to use formula field != 0 as a Condition?
Using the MODEL_LOOKUP formula and the second IF formula, I was trying to add a Condition to exclude the zeroes. I tried != 0, != None - Blank Value, >0… no luck.
My final question is can you order a Model or sort a column on a formula field (in my case, my MODEL_LOOKUP formula), but I think the answer is “not without a snippet”, based upon this article: https://community.skuid.com/t/table-allow-ordering-for-ui-only-formula-fields
Thanks for any help you can offer.
- try wrapping the the model lookup in VALUE()
2) Have your formula field return text that you can use in a condition. For example, if the value of a the formula = 0, then “Zero”. You should then be able to set up a condition that excludes any records where the formula field equals “Zero”.
3) I believe you must use a snippet.
Raymond,
Thanks for your response.
1) I tried VALUE(). It did not return 0, just blank as before. Have you gotten this to work somewhere?
2) I tried your text formula field, which worked. However, the Condition on the Model didn’t work. I tried !=, does not contain, and does not start with. Have you gotten this to work somewhere?
3) I used the snippet from the post I mentioned above. Works well.
Thanks.
