Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics CRM forum
Unanswered

Rollup Field: Count blank or specific value

Posted on by 685

Hi,

I have created a rollup field to count some records. I am running one of the filters on an option set field. I want it to count if the field is blank or if 2 of the values are selected. I thought I could do this by simply saying if the field value does not equal the two that I have selected , but it is not counting the blank fields. If I set an OR option to include in the count when the field is blank, then the OR operator then negates my other filters. 

I am surprised that by default blanks are counted. Is there a way I can achieve this?

Best wishes,

Richard

  • ashlega Profile Picture
    ashlega 34,473 on at
    RE: Rollup Field: Count blank or specific value

    You mean it does not work with those settings when you use "does not equal" condition as per the screenshot above (not the "equals" condition)?

    "No, your not quite getting what the issue is here. Thanks for trying thoug"

    Ah.. ok. Maybe you could add a screenshot then. That usually helps.

  • crmrsw15 Profile Picture
    crmrsw15 685 on at
    RE: Rollup Field: Count blank or specific value

    No, your not quite getting what the issue is here. Thanks for trying though.

  • ashlega Profile Picture
    ashlega 34,473 on at
    RE: Rollup Field: Count blank or specific value

    Here is an example

    35373.1.PNG

  • crmrsw15 Profile Picture
    crmrsw15 685 on at
    RE: Rollup Field: Count blank or specific value

    Yes that's what I thought to Alex, until I tested it.

  • crmrsw15 Profile Picture
    crmrsw15 685 on at
    RE: Rollup Field: Count blank or specific value

    I would have thought MS would have made it easier by counting blank values. e.g. if field 3 <> option 1, means a blank value should be counted but it doesn't count it. It only counts the fields that contain data and are not equal to option 1

  • ashlega Profile Picture
    ashlega 34,473 on at
    RE: Rollup Field: Count blank or specific value

    If you use "<>" with specific options, I think it will count blanks on Field 3

  • crmrsw15 Profile Picture
    crmrsw15 685 on at
    RE: Rollup Field: Count blank or specific value

    Thanks Alex. Yes that would work but I also need to count the blanks and that's where it falls over.

  • ashlega Profile Picture
    ashlega 34,473 on at
    RE: Rollup Field: Count blank or specific value

    If filed3 is an optionset, maybe you can do:

    Field 1 = X

    AND Field 2 = X

    AND Field 3 <> Option 3

    AND Field 3 <> Option 4

    AND Field 3 <> Option 5

    AND Field 3 <> Option 6

    etc

  • crmrsw15 Profile Picture
    crmrsw15 685 on at
    RE: Rollup Field: Count blank or specific value

    Sorry if I was confusing. The issue here is that it DOESN'T count blank fields unless I specifically say does not contain data.

    The rollup field I am trying to achieve is:

    Field 1 = X

    AND Field 2 = X

    (AND Field 3 DOES NOT CONTAIN DATA OR Field 3 = X or X)

    Because of the first 2 fields I cannot negate it to use OR. Not sure how I can make this one happen, any ideas would be welcome.

    Thanks.

    Richard

  • ashlega Profile Picture
    ashlega 34,473 on at
    RE: Rollup Field: Count blank or specific value

    Hi,

     it's either all "AND" or it's all "OR" in rollups.

     I suspect you might want to inverse the filter (Contains Data AND NOT equals Option 4 AND NOT Equals Option 5 AND Not Equals Option 6...). Although, I'm not sure if you actually want to count blanks or not (since you wrote that you want it to count blank but you are surprised it's counted)

      If you want more advanced rollup filters(though I don't think you need it in your scenario), you'll need something custom. For example, here is one option:

      http://www.itaintboring.com/dynamics-crm/lets-roll-up/

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,835 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,526 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans