Calculated fields with clauses

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!

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

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.

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.

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.

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

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

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