Details about this thread
  • State Not Answered
  • +1 person also asked this people also asked this
  • Replies 8 replies
  • Subscribers 322 subscribers
  • Views 2573 views
  • Users 0 members are here

Calculated fields with clauses

Frederik Lundberg

I am setting up sugarCRM for a real estate administration company..

I have used the accounts module for buildings and apartments..
Each apartment is a member of the building they are in..

I have made a calculated field (count($members)) to show how many apartments fall under one building.. (this works)

Each apartment has a status field (dropdown) that is either 'Rented' or 'Available', I would like to count how many apartments are rented in each building.

In SQL it would look something like this:
SELECT *
FROM Building1
WHERE status = 'Rented'

How do I make this in sugar?

  • 0 Sugar Employee Badge

    Hi Frederik Lundberg,

    I would address this with a formula like this:

    countConditional($members,'status',array('Rented'))

    This works as expected for me:

    countConditional($members,"account_type",createList("Customer","Investor"))

    I hope this helps!

  • 0 in reply to Patrick McQueen

    Hi Patrick McQueen,

    I tried this already with the account_type list in SugarCRM Enterprise 8.3.0 (demo environment)

    When I replace the field name with the variable it seems to accept this but 'Customer' is an issue in any combination (looks like lists are not working here:

    With "array()"

    Without "array()"

    Next try with double quotes the formula is accepted

    but ends in an error 500 on opening accounts

    With "array()" and double quotes

    Looks like we have a bug here or formulas with dropdowns doesn't work.

    Any idea or should we raise a bug report?

    Bests

    Björn

  • 0 Sugar Employee Badge in reply to Björn Canales Pfisterer

    Hi Björn Canales Pfisterer,

    I apologize my earlier advice was inaccurate.

    This works as expected for me:

    countConditional($members,"account_type",createList("Customer","Investor"))

    1. You are correct the $field_name, not 'field_name' works here.

    2. array() is not a function of calculated fields.

    There are defects in the help text description for the countConditional() function. We have in our records Defect 80244 outlining this and communicating the workaround.

  • 0 in reply to Patrick McQueen

    Negative,

    the formular is accepted and counts the (all) related records but the count is not correct as it does not respects the dropdown value.

    I tried it with "account_type", $account_type and related($members,"account_type")

    The count does not change.

    Bests

    Björn

    read next post.

  • 0 Sugar Employee Badge in reply to Björn Canales Pfisterer

    Hi Björn Canales Pfisterer,

    My experience in a hosted 8.3.0 Enterprise instance works as expected. I am sorry to read it is not working for you as described.

    My test included:

    1. The formula is exactly as above.

    2. Relate an Account to 3 Member Accounts.

    3. One account was Customer. The other two were Analyst

    4. The field populated with a 1 upon save.

    5. I opened one of the Analyst accounts and changed it to Investor

    6. Upon refreshing the parent account, the field now read 2.

  • 0 in reply to Patrick McQueen

    Patrick McQueen, forget my last post, your solution works like a charm!

    I'm a giddy goat, forgot to re-check the "calculated value" after C&P your formula which had been deactivated for unknow reason (probaby after I saved a not working formula)

    Frederik Lundberg I think we are done here and found a solution

    Bests

    Björn

  • 0 in reply to Patrick McQueen

    Thanks for the replies.. I tried to use the formula you posted here and edited it to fit my data, then it would look like this?:

    countConditional($members,"account_type",createList("apartment","empty"))

    Given the account_type = apartment, and the condition that status_c = empty..

    This just gives me the same as count($members), since it does not take into account that the apartment has to be available before it counts them..

    Is there any other way to make this work? I do not just need to count apartments in a building, but apartments with a condition.. In this case, empty apartments

  • 0 in reply to Patrick McQueen

    Does this work?

    countConditional($members,and("account_type",createList("apartment"),"status_c",createList("empty")))

    Have not tested it and am not sure if add() works here. Maybe Patrick McQueen can help.

    *Your formular only counts accounts that have the account type = apartment or empty 

    Bests

    Björn