Human readable custom input field for an IBAN number?

[ThomasB]

I need to store an IBAN number and I already have a custom varchar field to which I have also added a pattern to validate the number.
This works, but it would be nicer if I could enter the number and display it in a human readable form.

For example, I currently have to add the number like this, which is hard to read: DE07123412341234123412

It would be much nicer if I could enter the number in packs of 4 and 2.

DE07 1234 1234 1234 1234 12

Perhaps it would be possible to remove the spaces from the input and then validate and save the result.

But how to display the number again with spaces?

Is there also a way to override the pattern error mesaage? For non-technical persons a regex pattern error message is pretty confusing.

[Kharg]

Hi Thomas,

there’s an extension by Eblasoft called Mask Field

https://www.eblasoft.com.tr/espocrm-extension-page/espocrm-mask-field

before buying contact eymen-elkum using the chat on the website, as I think the website gives you the old version and to get the new one you have to use his Support Portal.

[espcrm]

That extension is your best bet. There no other alternative at the moment.

It got feature request twice (1 by me and 1 by another user).

[ThomasB]

Thanks for the replies.

It definitely looks like a nice add-on. Just not sure how it would work with regex validation.

When I tried to program something, I tried to remove the spaces from the input string with a "Before Save Custom Script" and had a RegEx pattern in the field definition. However, the field was evaluated before the script was executed and did not work.

I now have an "API Before Save Script" that seems to work, but is not as sophisticated as the add-on. It strips the blanks from the input and then validates with a RegEx.
In case of an error, a message is thrown.

With this solution I can at least copy and paste IBANs with spaces or type them with spaces to check them visually.


iBAN = varchar field

iBAN = string\replace(iBAN, ' ', '');

ifThenElse(
string\test(iBAN, '(^(?:((?:IT|SM)\d{2}[A-Z]{1}\d{22})|(NL\d{2}[A-Z]{4}\d{10})|(LV\d{2}[A-Z]{4}\d{13})|((?:BG|GB|IE)\d{2}[A-Z]{4}\d{14})|(GI\d{2}[A-Z]{4}\d{15})|(RO\d{2}[A-Z]{4}\d{16})|(MT\d{2}[A-Z]{4}\d{23})|(NO\d{13})|((?:DK|FI)\d{16})|((?:SI)\d{17})|((?:AT|EE|LU|LT)\d{18})|((?:HR|LI|CH)\d{19})|((?:DE|VA)\d{20})|((?:AD|CZ|ES|MD|SK|SE)\d{22})|(PT\d{23})|((?:IS)\d{24})|((?:BE)\d{14})|((?:FR|MC|GR)\d{25})|((?:PL|HU|CY)\d{26}))$)'),
iBAN = iBAN,
$checkfailed = true
     );

if ($checkfailed == true && iBAN != '') {
    recordService\throwBadRequest("IBAN is incorrect.");
}​

Maybe there is a better way to do it, but at least it works. And I'm not really a programmer.

[espcrm]

Very cool.

[Kharg]

I think you can make your regex even better as the first 2 characters are always the country initials

[ThomasB]

Maybe. I copied them from the https://regex101.com/ library. But each country has different lenght in digits. E. g. Norway 15 and Germany 20

See: https://www.iban.com/structure

[espcrm]

Which setting do you use for Regex101? Most of my Regex work with the default setting in Reg101, but some it only work with Reg101 but not in EspoCRM.

[ThomasB]

Oh, and this is just a very simple check. It only checks the country code and length and does not perform a checksum check.

I found this php library: https://github.com/globalcitizen/php-iban

But that is far beyond my capability to include that.

Maybe it would be an idea for some programmers to provide such an extension for SEPA checks (BIC, IBAN) etc.

[item]

Hi,
i think another solution is front-end : https://github.com/arhs/iban.js
it'seems this library can handle "formating" (with space or -)

[item]

Not perfect, but work for me..

if iban is valid, then the record is saved silent with human readable (bban)
if not, a confirm dialog show.
Maybe someone with front-end skill make it better on event keypress or ... and not on save.

download : https://github.com/arhs/iban.js
put iban.js in client/custom/lib/iban.js

For Account
client/custom/Espo/Custom/Resources/metadata/clientDefs/Account.json

{
    "recordViews": {
        "detail": "custom:views/account/record/detail"
    }
}​ 

field iban = ibanNumber in my case.

client/custom/src/views/account/record/detail.js

define('custom:views/account/record/detail', ['views/record/detail'], function (Dep) {

    return Dep.extend({
        
        actionSave: function () {

            let ibanNumber = this.model.get('ibanNumber');
            if (ibanNumber){
                if (IBAN.isValid(ibanNumber) ){
                    this.model.set('ibanNumber', IBAN.printFormat(ibanNumber, ' ') );
                    Dep.prototype.actionSave.call(this);
                 }else{
                    this.confirm({
                    message: this.translate('Are you sure you want to save Account with bad iban?'),
                    backdrop: 'static',
                    confirmText: this.translate('Confirm'),
                    }).then(() => {
                        this.model.set('ibanNumber', IBAN.printFormat(ibanNumber, ' ') );
                        Dep.prototype.actionSave.call(this);
                    });
                 }
            }else{
                Dep.prototype.actionSave.call(this);
            } 
        },
        
        _initInlineEditSave: function () {
            this.listenTo(this.recordHelper, 'inline-edit-save', (field, o) => {
                let ibanNumber = this.model.get('ibanNumber');

                if (field === 'ibanNumber' && ibanNumber && !IBAN.isValid(ibanNumber) ){
                    this.confirm({
                        message: this.translate('Are you sure you want to save Account with bad iban?'),
                        backdrop: 'static',
                        confirmText: this.translate('Confirm'),
                    }).then(() => {
                        this.model.set('ibanNumber', IBAN.printFormat(ibanNumber, ' ') );
                        this.inlineEditSave(field, o);
                    });
                }else{
                    this.inlineEditSave(field, o);
                }
                
            });
        },

    });
});  ​ 

custom/Espo/Custom/Resources/metadata/app/client.json

{
    "scriptList": [
        "__APPEND__",
        "client/custom/lib/iban.js"
    ],
    "developerModeScriptList": [
        "__APPEND__",
            "client/custom/lib/iban.js"
    ]
}​ 

[ThomasB]

For Account
client/custom/Espo/Custom/Resources/metadata/clientDefs/Account.json

Is the path correct?

From the directory layout this seems to be more correct: custom/Espo/Custom/Resources/metadata/clientDefs/Account.json

Just trying to test your approach.

[item]

Hi,
i have now i more nice solution, more usuable and all entity ready
i will post solution tonigth

[ThomasB]

Ok, not sure if I did something wrong. but the test iban: DE91100000000123456789 always says its invalid. After saving, the IBAN number is formatted, so I think the modifications I did are working.

Waiting for your nicer solution now.

[item]

In : /client/custom/res/templates/fields/iban-number/detail.tpl

{{#if isNotEmpty~}}
        <a
            role="button"
            data-action="copyToClipboard"
            class="pull-right text-soft"
            title="{{translate 'Copy to Clipboard'}}"
        ><span></span></a>
    {{value}}
{{~else}}
{{#if valueIsSet}}<span class="none-value">{{translate 'None'}}</span>{{else}}
<span class="loading-value">...</span>{{/if}}
{{/if}}​ 

in
/client/custom/src/views/fields/iban-number.js

define('custom:views/fields/iban-number', ['views/fields/varchar'], function (Dep) {

    return Dep.extend(/** @lends module:views/fields/varchar.Class# */{
        
        detailTemplate: 'custom:fields/iban-number/detail',

        setup: function () {
            Dep.prototype.setup.call(this);
            this.events['click [data-action="copyToClipboard"]'] = () => this.copyToClipboard();
        },

        afterRender: function () {
            Dep.prototype.afterRender.call(this);

            let renderedValue = this.model.get(this.name);
            
            if (IBAN.isValid(renderedValue)){
                 this.$el.find('[data-action="copyToClipboard"]').addClass('fas fa-check');
                this.$el.removeClass('text-muted');
            }else{
                this.$el.find('[data-action="copyToClipboard"]').addClass('fas fa-ban');
                this.$el.addClass('text-muted');
            }
            this.$el.on('change', (e) => {
                let value = this.model.get(this.name);
                if (IBAN.isValid(value)){
                    this.$el.removeClass('text-muted');
                    this.model.set( this.name , IBAN.printFormat(value, ' ') );
                }
            });
        },
    });
});​ 

in sample ibanNumber field :
/custom/Espo/Custom/Resources/metadata/entityDefs/Account.json

{
    "fields": {
        "ibanNumber": {
            "type": "varchar",
            "maxLength": 20,
            "options": [],
            "isCustom": true,
            "view": "custom:views/fields/iban-number"
        }
    }
}​ 

And from previous post :
same :
custom/Espo/Custom/Resources/metadata/app/client.json
and same :
download : https://github.com/arhs/iban.js
put iban.js in client/custom/lib/iban.js

result :

[item]

because i have not a good skill.
i have try to have in color "error or warning or success" the icon.. but not success.
so i have success in "text-muted"

[Kharg]

Shouldn’t be that hard to color it. If I find the time I will make an extension as a new field if you don’t mind.

[item]

Find Kharg,

i have a little changed, so the valid icon is hidden after 3 secondes.. so it's not a christmas lighting
only bad is "red icon"

if (IBAN.isValid(renderedValue)){
this.$el.find('[data-action="copyToClipboard"]').addClass('text-success fas fa-check');
this.$el.removeClass('text-muted');
setTimeout(() => {
this.$el.find('[data-action="copyToClipboard"]').addClass('hidden');
}, 500);
}else{
this.$el.find('[data-action="copyToClipboard"]').addClass('text-danger fas fa-ban');
this.$el.addClass('text-muted');
}

[ThomasB]

"maxLength": 20,

The max valid length for SEPA IBAN is 31 (Malta). Non Sepa Russia is 33. So I think this field should be at least 31 long?

[item]

Hi,
no no... i just put sample my field ibanNumber..

you just need add this line in your fields :

"view": "custom:views/fields/iban-number"

in my case, this is a random length... and we use always iban from belgium.