SOLVED - Calculated Field - Value from 2 Fields wether which one is filled

Discussion created by Lennart Gebauer on Mar 26, 2019
Latest reply on Mar 27, 2019 by Lennart Gebauer

Like • Show 0 Likes0
Comment • 6

Hi,

so far i have a calculated field that takes a value from one field and uses it.

Formular:

subStr($stage_customer_postalcode_c,0,5)

But i'd like to adjust the field that it's possible to take the information needed from 2 fields.

And it depends on which field is filled with a value to take that value.

Usecase is:

We have Module A and Module B with records of Users.

In the Case Section we Link records from Module A or B, depending of the which kind of Users they are.

Within the Case Module we use the zipcode (at the moment just Module A) of the user for Reporting. So we see which ticket comes from which Region.

The Point is a calculated Field that will get it's value from either connected record form Module A or connected record from Module B.

I hope it's clear what i like to do

Outcomes

Patrick McQueen

Mar 26, 2019 8:50 AM

Hi Lennart Gebauer,

I recently participated in this discussion that seems similar.

The description is not clear as there are references to fields and related modules and some dependencies pertaining to those.

Providing the database names and types of the fields, names of modules and relationships, all allow the community to better visualize the goal/use case for your inquiry and build/test something locally to ensure we're giving you an accurate and relevant response. I look forward to more details if the linked conversation does not address your need.

Actions

B_Canales @ Patrick McQueen on Mar 26, 2019 9:06 AM

Hey Patrick,

I talked to Lennart via PM and we found/tested a similiar formular like:

ifElse(
   greaterThan(
       strlen(
           related($notes,"plz_notes_c")
           ),0),related($notes,"plz_notes_c"),ifElse(
   greaterThan(
       strlen(
           related($calls,"plz_calls_c")
           ),0),related($calls,"plz_calls_c"),""))

On this you need to know that it is possible to attach more than one record to the 1:N relationship in one module but SugarCRM seems to take the value from the latest attached record which is fine in some scenarios. (have not checked if it is date created or date modified, to be tested later on)

Additionally the value from those relation will be taken that matches the if clause first. So the sort order of modules in the formular may be important.

last but not least the above formula allows to have the value not set on the related records.

Once this is completely solved I think Lennart Gebauer will share the final solution with the community :-)

Bests

Björn

2 people found this helpful

Actions

Patrick McQueen

@ B_Canales on Mar 26, 2019 9:38 AM

Hi Björn Canales Pfisterer,

I hear you on the n-to-n dilemma. The related() function in calculated fields is only meant for use in n-to-1 or 1-to-1 relationships.

Advanced Workflow or a custom logic hook could represent the least upfront development labor for this scenario.

If the customer finds it essential to use calculated field functions for this, then the creation of a new custom function could make sense, creatively combining the iteration logic like that found in:

include/Expressions/Expression/Date/MaxRelatedDateExpression.php

and/or

include/Expressions/Expression/Numeric/CountConditionalRelatedExpression.php

with the ability for the formula builder to pick the field to be returned found in:

include/Expressions/Expression/Generic/RelatedFieldExpression.php

2 people found this helpful

Actions

Lennart Gebauer @ Patrick McQueen on Mar 26, 2019 9:09 AM

Hi Patrick,

thanks for the advice.

Björn is helping me out.

It's quite complex to picture the situation and all the connected details.
But i will post an update as soon as we figured it out. At the moment it's quite close to the final solution.

Actions

Lennart Gebauer Mar 26, 2019 9:17 AM

So far Björn Canales Pfisterer helped to write the formula for the calculated field that get's it's information from either module A or B.
That works so far.

At the moment there is a problem by getting the value from the module B.

Probably the relationship between the modules cases/module B.

By now it was an N:N relationship and it was connected via an "connection"- field.

But after connecting the Case with the Module B record, it doesn't takes the value.

Actions

Lennart Gebauer Mar 27, 2019 6:56 AM

Jep,

it was a Problem with the Relationship.

The Cases Module needs an - N:1 - to the Module B

So,

if you have a Cases Module and you want to get Values from a connected record wether Module A or Module B or Module C, depending on which one is connected to Module Cases you need a Relationship ->

Cases - N:1 - Module A/B/C

After This you can generate a calculated field in the Cases Module with the following formula.

ifElse(
greaterThan(
strlen(
related($Module A,"Value from Module A")
),0),related($Module A,"Value from Module A"),ifElse(
greaterThan(
strlen(
related($Module B,"Value from Module B")
),0),related($Module B,"Value from Module B"),ifElse(
greaterThan(
strlen(
related($Module C,"Value from Module C")
),0),related($Module C,"Value from Module C"),"")))

The field will show the Value of the Module that is connected to the Module Cases.

I used this for ZipCodes therefore the fields are formatted as "floating-point-number"

Hope it helps.
Cheers

Actions

6 Replies

Patrick McQueen Mar 26, 2019 8:50 AM
Hi Lennart Gebauer,

I recently participated in this discussion that seems similar.

The description is not clear as there are references to fields and related modules and some dependencies pertaining to those.

Providing the database names and types of the fields, names of modules and relationships, all allow the community to better visualize the goal/use case for your inquiry and build/test something locally to ensure we're giving you an accurate and relevant response. I look forward to more details if the linked conversation does not address your need.
Like • Show 1 Like1
Actions
- B_Canales @ Patrick McQueen on Mar 26, 2019 9:06 AM
  Hey Patrick,
  
  I talked to Lennart via PM and we found/tested a similiar formular like:
  
  ifElse(
     greaterThan(
         strlen(
             related($notes,"plz_notes_c")
             ),0),related($notes,"plz_notes_c"),ifElse(
     greaterThan(
         strlen(
             related($calls,"plz_calls_c")
             ),0),related($calls,"plz_calls_c"),""))
  On this you need to know that it is possible to attach more than one record to the 1:N relationship in one module but SugarCRM seems to take the value from the latest attached record which is fine in some scenarios. (have not checked if it is date created or date modified, to be tested later on)
  
  Additionally the value from those relation will be taken that matches the if clause first. So the sort order of modules in the formular may be important.
  
  last but not least the above formula allows to have the value not set on the related records.
  
  Once this is completely solved I think Lennart Gebauer will share the final solution with the community :-)
  
  Bests
  Björn
  2 people found this helpful
  Like • Show 1 Like1
  Actions
  - Patrick McQueen @ B_Canales on Mar 26, 2019 9:38 AM
    Hi Björn Canales Pfisterer,
    
    I hear you on the n-to-n dilemma. The related() function in calculated fields is only meant for use in n-to-1 or 1-to-1 relationships.
    Advanced Workflow or a custom logic hook could represent the least upfront development labor for this scenario.
    If the customer finds it essential to use calculated field functions for this, then the creation of a new custom function could make sense, creatively combining the iteration logic like that found in:
    include/Expressions/Expression/Date/MaxRelatedDateExpression.php
    and/or
    include/Expressions/Expression/Numeric/CountConditionalRelatedExpression.php
    with the ability for the formula builder to pick the field to be returned found in:
    include/Expressions/Expression/Generic/RelatedFieldExpression.php
    2 people found this helpful
    Like • Show 2 Likes2
    Actions
- Lennart Gebauer @ Patrick McQueen on Mar 26, 2019 9:09 AM
  Hi Patrick,
  
  thanks for the advice.
  Björn is helping me out.
  It's quite complex to picture the situation and all the connected details.
  But i will post an update as soon as we figured it out. At the moment it's quite close to the final solution.
  Like • Show 0 Likes0
  Actions
Lennart Gebauer Mar 26, 2019 9:17 AM
So far Björn Canales Pfisterer helped to write the formula for the calculated field that get's it's information from either module A or B.
That works so far.

At the moment there is a problem by getting the value from the module B.
Probably the relationship between the modules cases/module B.

By now it was an N:N relationship and it was connected via an "connection"- field.
But after connecting the Case with the Module B record, it doesn't takes the value.
Like • Show 0 Likes0
Actions
Lennart Gebauer Mar 27, 2019 6:56 AM
Jep,

it was a Problem with the Relationship.

The Cases Module needs an - N:1 - to the Module B

So,
if you have a Cases Module and you want to get Values from a connected record wether Module A or Module B or Module C, depending on which one is connected to Module Cases you need a Relationship ->

Cases - N:1 - Module A/B/C

After This you can generate a calculated field in the Cases Module with the following formula.

ifElse(
greaterThan(
strlen(
related($Module A,"Value from Module A")
),0),related($Module A,"Value from Module A"),ifElse(
greaterThan(
strlen(
related($Module B,"Value from Module B")
),0),related($Module B,"Value from Module B"),ifElse(
greaterThan(
strlen(
related($Module C,"Value from Module C")
),0),related($Module C,"Value from Module C"),"")))

The field will show the Value of the Module that is connected to the Module Cases.
I used this for ZipCodes therefore the fields are formatted as "floating-point-number"

Hope it helps.
Cheers
Like • Show 0 Likes0
Actions

SOLVED - Calculated Field - Value from 2 Fields wether which one is filled

Outcomes

Related Content

Recommended Content