calculated field - if value starts with .. select this: SOLVED

Hi Lennart Gebauer 

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

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 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

Would be glad if you can mark this thread as solved for helping others searching for a solution :-)

Grüße

Björn