problems with formula.

[pehoma]

Hello to all.
I try to create a formula for a URL field. The Idea is to get a specific record from a public database (land registry). I have to create the URL by referring to another field value. So far so good. But I also need to get the first two characters from that field and also the third to fifth caracter of the field. It's a bit complicated.

The first reference is in field 'refcatastral': 123456UF52PLG
For the URL (in field 'Catastro') I need the complete value of the field, and also the first two digits (12) and also the third to fifth digit (345).

Is there a possibility to get it. Here is the formula like it is so far:

catastro=string\concatenate('https://www1.sedecatastro.gob.es/CYCBienInmueble/OVCConCiud.aspx?UrbRus=R&RefC=', refcatastral, '&esBice=&RCBice1=&RCBice2=&DenoBice=&from=OVCBu sq ueda&pest=rc&RCCompleta=', refcatastral, '&final=&del=29&mun=900')

Where right now at the end of the URL is '29' should be the first two digits of the field 'refcatastral'
Where right now at the end of the URL is '900' should be the 3rd to fifth digit of the field 'refcatastral'

Maybe there is also a possibility to change the visible field value by adding a href to the formula so that instead of the long URL only a word is displayed in the detail view of ESPO?

Thanks for your help.

[shalmaxb]

To give the URL a short name you can use Textbox (with markdown) or Wysiwyg with normal link/name combination. For the URL you may create a normal textfield, to which you reference in the formula.
Though I did not get, what you want to achieve by formula? Only concatenate? If it is that, then you could create some fields, where your parts to be concatenated are saved and create the formula from that point.
If I would know the "ingredients" and the desired result I perhaps could help more.

[pehoma]

Hello shalmaxb, thanks for your reply and your help. I already thought about creating some hidden fields with the parts which will be concatenated in the final URL. But how can I get the part of a value from a field?
Example:
input field 'reference' is 29900GFV5265KI525

I want to get the first two digits of the value from 'reference' to be stored in the hidden field 'part1', in this example '29'.
Then I need the third to fifth digit of the value in another hidden field: in this example '900'

[shalmaxb]

This: https://docs.espocrm.com/administrat...tringsubstring

[esforim]

Yep, look like you solve the initial idea of getting formula, once you combine these substring you should be able to get it. Lastly to make it look clean you don't want a long URL you can convert it in a link. If it a text field you can create something like this, it will turn into a clickable URL

[Click Me for Land Registry Information](https://www1.sedecatastro)

the [ ] is the name that will be display, the ( ) is the URL

[pehoma]

Thanks to all. I got it resolved with this helpful posts.

[shalmaxb]

post your solution please, could help others