Skip to main content

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.

  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. 

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.

Check out this post: https://community.skuid.com/t/filter-on-count

View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings