LOOKUP_MODEL UI-Only Formula Fields - IF logic and Conditions

  • 1
  • Question
  • Updated 1 year ago
  • Answered
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/skuid/topics/table-allow-ordering-for-ui-only-formula-fields

Thanks for any help you can offer.
Photo of Michael Pierce

Michael Pierce

  • 544 Points 500 badge 2x thumb

Posted 1 year ago

  • 1
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
1) 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. 
Photo of Michael Pierce

Michael Pierce

  • 544 Points 500 badge 2x thumb
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.