Hi,
i have a question about calculated fields.
Use Case is: We'd like to cluster living-areas.
Therefore i need something like a calculated field thats selects for example Reagion1 if the zipcode in the linked field/module starts with 3.
Thanks!
Hi,
i have a question about calculated fields.
Use Case is: We'd like to cluster living-areas.
Therefore i need something like a calculated field thats selects for example Reagion1 if the zipcode in the linked field/module starts with 3.
Thanks!
You can use the SugarLogic function subStr(string, star, leng).
So, as per your scenario, we have:
ifElse(
equal(subStr($some_field, 0, 1), "3"),
// do something
// do something else
)
Regards
Hey,
thanks for the reply.
So i tried it and i use the following formular
ifElse(
isWithinRange(rollupSum($zipcode_short_c,"amount"),01,09),
"Sachsen",
ifElse(
isWithinRange(rollupSum($zipcode_short_c,"amount"),10,19),
"Brandenburg",
ifElse(
isWithinRange(rollupSum($zipcode_short_c,"amount"),20,29),
"Hamburg",
ifElse(
greaterThan(rollupSum($zipcode_short_c,"amount"),30),
"keine Region",
"0"
)
)
)
)
But there is a error:
Ungültige Formel
"Unexpected token I in JSON at position 0"
Any suggestions?
Thanks!
I got this working formula in my dev instance:
ifElse(
isWithinRange(rollupSum($opportunities, "amount"), 1 ,9),
"Sachsen",
ifElse(
isWithinRange(rollupSum($opportunities, "amount"), 10, 19),
"Brandenburg",
ifElse(
isWithinRange(rollupSum($opportunities, "amount"), 20, 29),
"Hamburg",
ifElse(
greaterThan(rollupSum($opportunities, "amount"), 30),
"keine Region",
"0"
)
)
)
)
You just need to replace the link name at the rollupSum formula.
Regards
Used your fourmula and replaced the fieldname.
still the same error
Unexpected token I in JSON at position 0
Used your fourmula and replaced the fieldname.
still the same error
Unexpected token I in JSON at position 0
So I believe there is an issue in your custom link/relationship.
I had tested in the default opportunities link under Accounts.
yeah,
so actually it's all in one module
so the field that is calculated (region_c) get's it's value from a field (zipcode_short_c) in the same module
is that maybe the reason?
Definitely yes!
So the formula may looks like that one:
ifElse(
isWithinRange($zipcode_short_c, 1 ,9),
"Sachsen",
ifElse(
isWithinRange($zipcode_short_c, 10, 19),
"Brandenburg",
ifElse(
isWithinRange($zipcode_short_c, 20, 29),
"Hamburg",
ifElse(
greaterThan($zipcode_short_c, 30),
"keine Region",
"0"
)
)
)
)
Regards
ok, and the zipcode_short_c must be a number probably?
because right now it is a textfield.
Certain it should be a number (either integer or float).
now it says: the formula must be boolean ..
i'm going crazy
the whole workaround.
all the fields are in the same module.
i use a related formula to get the first 2 digits of the zipcode.
then i'd like to use the formular to set a region as value defined by the first 2 digits.
zipcode it textfield - out of the box
short zipcode - i tried all number fields
Hi Lennart Gebauer,
I hear you on feeling crazy in situations like this.
In this scenario, you are working with multiple functions in multiple formulas across multiple fields and field types. Because the outputs of some functions or the data types of fields might not be the data type the function they are embedded in need, you can get confusing errors that are difficult to chase down when dealing with the whole.
Try replacing the field variables with the numbers saved in those fields and see if the formula still fails the same way, a different way, or works out. If it works, then the issue is in the field being called in the variable. Either the data on the database is not what you think is in that field, or the datatype of that field is not what is needed. If it fails the same way, it could be that one of the embedded functions is outputting a string in spite of the use of a number, and another function wrapped around it might be necessary to change the output of the embedded function.
If the above modifications do not lead to a solution, there are other options. You could break the larger formula into parts in a textfield and see what the functions/formulas are outputting when broken down into parts. This often reveals surprises to me.
If none of the above works, then it sounds like we will need the names, datatypes, and formulas in all relevant fields so we can build out a version on our ends and see if we can reproduce and narrow down the root cause along with you.
I hope this helps!
Hi Lennart Gebauer,
this requires a transformation of the textvalue into float.
The following formula will to the trick but requires a second field (that does not need to be in the layout)
It looks like this:
What did I do?
Initially I pulled the first two characters from the zip code field into the copyzip_c float field with this formula
subStr($billing_address_postalcode,0,2)
The field looks like this in studio
Then I added this formula in my Sales Region field. Most important is the "Sonstige" part to catch all not mathcing results like a zip code that is written like D-44892 etc.
ifElse(equal($copyzip_c,"10"),"City A",
ifElse(equal($copyzip_c,"20"),"City B",
ifElse(isWithinRange($copyzip_c,33,45),"Region A",
ifElse(isWithinRange($copyzip_c,51,58),"Region B",
ifElse(isWithinRange($copyzip_c,60,90),"Region C",
"Sonstige"
)))))
Works like a charm!
Viel Spaß damit :-)
Cheers
Björn Canales Pfisterer
Technical Support Manager
provalida GmbH
Hi Björn,
thanks for the detailed Help!
It works now and i see the troubleshooter.
Sometimes it's in the detail!
Have a good day
Lennart
Hey Patrick,
thanks for the words, sometimes it's quite difficult to get through the small details.
It helped as well for other projects.
Take care!
Lennart