How Can I Format A Text Field As A Dollar?

[Bangalore]

How Can I Format A Text Field As A Dollar?

Can someone help with this?

[casGary]

Hi Bangalore,

Can you describe it in more details.

What kind of DataPage do you use, which field is that, what would you like it to look like?

[pavitra2410]

This script will apply to submission forms when user enters a value and it automatically formats into $X,XXX.XX. The field that will store this value much be a Text(255). Add an HTML block and paste the following script. Replace FIELDNAME with the field name from your table.

 

<script>

function formatAsDollars(el) {

el.value = el.value.replace(/[^\d]/g,'').replace(/(\d\d?)$/,'.$1').replace(/^0+/,'').replace( /\d{1,3}(?=(\d{3})+(?!\d))/g , "$&,");

el.value = el.value ? '$' + el.value : '';

}

document.getElementById( 'InsertRecordFIELDNAME' ).onkeyup = function() {

formatAsDollars(this);

}

document.getElementById("InsertRecordFIELDNAME").onchange= function() {

formatAsDollars( this );

}

</script>

[CavyEnthuasist]

Hi.

 

Thanks for this, CaspioEnthusiast, it is helpful. Can you tell me how to change it so the text box formats input as a whole dollar amount? (either allowing only whole number into the text box or allowing only 2 zeros after the decimal point).

Gracias

[Jan]

Hi CavyEnthuasist,

 

I hope this script allows only whole number:

<script>
function formatAsDollars(el) {
el.value = el.value.replace(/[^\d]/g,'').replace(/(\d\d?)$/,'$1').replace(/^0+/,'').replace( /\d{1,3}(?=(\d{3})+(?!\d))/g , "$&,");
el.value = el.value ? '$' + el.value : '';
}
document.getElementById( 'InsertRecordno_spaces' ).onkeyup = function() {
formatAsDollars(this);
}
document.getElementById("InsertRecordno_spaces").onchange= function() {
formatAsDollars( this );
}
</script>

[Elena]

Is there a way to amend the code so that it works on Report Details page?

I tried to replace "InsertRecord" with "EditRecord" and it's not working for me.

 

Assistance is appreciated, thank you so much in advance.

[bbewley]

How do you amend the JS to allow for this to apply to multiple fields that need to be formatted as currency?

[kpcollier]

I could also use a currency formatting option for multiple fields. Having a field type and/or format set to currency in the wizard doesn't work for JS computed numbers. 

[Vitalikssssss]

Hi @bbewley, @kpcollier,

You may try using this JS which should go to the Footer:

<script type="text/javascript">

function formatAsDollars(el) {
el.value = el.value.replace(/[^\d]/g,'').replace(/(\d\d?)$/,'$1').replace(/^0+/,'').replace( /\d{1,3}(?=(\d{3})+(?!\d))/g , "$&,");
el.value = el.value ? '$' + el.value : '';
}

let fields = ["FIELD_1", "FIELD_2"]; //specify your fields here

fields.forEach(element => {
    element = "InsertRecord" + element; // replace "InsertRecord" with "EditRecord" for Details/Single Record Update DP 
    document.getElementById(element).onkeyup = function() {
        formatAsDollars(this);
    }
    document.getElementById(element).onchange= function() {
        formatAsDollars(this);
    }
});
</script>

Pay attention to comments.

Hope this helps.

Vitalikssssss

[bbewley]

Worked perfectly!  Thanks Vitali!

[kpcollier]

I couldn't get it to work. Maybe it is because I the values in the fields I am trying to format are involved in custom js calculations? I am taking an inputted number, multiplying by a few different percents, and the total of that is the value to these fields I am trying to format. They still spit out a number with 3 digits after the decimal point and no ' , ' after 3 digits. 

To see the custom js calculations, go here. Vitaliks had also helped me with some bugs in that code.

[JMR21]

On 1/3/2020 at 8:18 AM, Vitalikssssss said:

You may try using this JS which should go to the Footer:

<script type="text/javascript">

function formatAsDollars(el) {
el.value = el.value.replace(/[^\d]/g,'').replace(/(\d\d?)$/,'$1').replace(/^0+/,'').replace( /\d{1,3}(?=(\d{3})+(?!\d))/g , "$&,");
el.value = el.value ? '$' + el.value : '';
}

let fields = ["FIELD_1", "FIELD_2"]; //specify your fields here

fields.forEach(element => {
    element = "InsertRecord" + element; // replace "InsertRecord" with "EditRecord" for Details/Single Record Update DP 
    document.getElementById(element).onkeyup = function() {
        formatAsDollars(this);
    }
    document.getElementById(element).onchange= function() {
        formatAsDollars(this);
    }
});
</script>

Pay attention to comments.

Hope this helps.

Vitalikssssss

@Vitalikssssss

I used this to edit a single record on the details page and it works perfect for my first record entry but then it doesn't work on the rest of the records that follow.  Do I need to add something else or place it differently?  I tried in the footer as well as inserting an HTML block before and after my "Price" field.  Thanks in advance for the help!

[Vitalikssssss]

Hi @JMR21,

The above code needs to be updated since now all Caspio Datapages have AJAX and special event handler "DataPageReady".

You may try the following updated JavaScript:

<script>

document.addEventListener('DataPageReady', assignEvent);

function formatAsDollars(el) {
el.value = el.value.replace(/[^\d]/g,'').replace(/(\d\d?)$/,'$1').replace(/^0+/,'').replace( /\d{1,3}(?=(\d{3})+(?!\d))/g , "$&,");
el.value = el.value ? '$' + el.value : '';
}

function assignEvent(){

let fields = ["Field_1", "Field_2"]; //specify your fields here

fields.forEach(element => {
    element = "InsertRecord" + element; // replace "InsertRecord" with "EditRecord" for Details/Single Record Update DP 
    document.getElementById(element).onkeyup = function() {
        formatAsDollars(this);
    }
    document.getElementById(element).onchange= function() {
        formatAsDollars(this);
    }
});

}

</script>

Regards,

vitalikssssss

[JMR21]

@Vitalikssssss Oh my gosh!  You're amazing!  Thank you soooo much!  This worked perfectly!!

[JKSGT]

@Vitalikssssss Hi, I have a few text fields in my data page and tried your code (below) but I'm not getting any results. Am I missing something? Many thanks 

 

<script>

document.addEventListener('DataPageReady', assignEvent);

function formatAsDollars(el) {
el.value = el.value.replace(/[^\d]/g,'').replace(/(\d\d?)$/,'$1').replace(/^0+/,'').replace( /\d{1,3}(?=(\d{3})+(?!\d))/g , "$&,");
el.value = el.value ? '$' + el.value : '';
}

function assignEvent(){

let fields = ["Stock", "Content"]; //specify your fields here

fields.forEach(element => {
    element = "EditRecord" + element; // replace "InsertRecord" with "EditRecord" for Details/Single Record Update DP 
    document.getElementById(element).onkeyup = function() {
        formatAsDollars(this);
    }
    document.getElementById(element).onchange= function() {
        formatAsDollars(this);
    }
});

}

</script>

 

 

[Vitalikssssss]

Hi @JKSGT,

What Datapage type do you use? 

Screenshot from a browser console (Windows: Ctrl+Shift+I | Mac: Cmd + Shift + I) would help to understand your case better.

Regards,

vitalikssssss

[JKSGT]

Hi, I think I'm ok on this front now. Thank you for responding.

[JMR21]

@Vitalikssssss

I'm doing another page related to this same topic...

I have a datapage that calculates a summary of purchases, credits, discounts, payments, and net due.  In the course of this, I have had to make my "sale credit, volume discount, and repeat buyer discount" fields as TEXT FIELDS in the datapage elements so that the rules will function correctly and hide any of these sections that are NOT present.  The rules are working correctly now that I have made them text fields but I can't get my js to work to format them as currency in the report page.  I tried the above coding in my footer but it doesn't work.  Any ideas?  Here are some screen shots:

------------------------------------------------------------

My js in footer of the datpage:

<script>

document.addEventListener('DataPageReady', assignEvent);

function formatAsDollars(el) {
el.value = el.value.replace(/[^\d]/g,'').replace(/(\d\d?)$/,'$1').replace(/^0+/,'').replace( /\d{1,3}(?=(\d{3})+(?!\d))/g , "$&,");
el.value = el.value ? '$' + el.value : '';
}

function assignEvent(){

let fields = ["tblCustomerInfo_SaleCreditUsed", "tblCustomerInfo_VolumeDiscount_dollars", "tblCustomerInfo_RepeatBuyerDisct_dollars"]; //specify your fields here

fields.forEach(element => {
    element = "InsertRecord" + element; // replace "InsertRecord" with "EditRecord" for Details/Single Record Update DP 
    document.getElementById(element).onkeyup = function() {
        formatAsDollars(this);
    }
    document.getElementById(element).onchange= function() {
        formatAsDollars(this);
    }
});

}

</script>

--------------------------------------------------------------

This is what the report looks like without the currency formatting:

 

 

[sandy159]

Hello @JMR21,

I noticed from your screenshot that Currency fields are on the 'Search and Report Wizard - Configure Details Page Fields' screen.

If you are using the script on the Details page, you may want to replace "InsertRecord" with "EditRecord" in the code.

On the 'Search and Report Wizard - Configure Results Page Fields' you may just change the Formatting of the fields so they will be displayed with the '$' sign.  

Hope this helps!

Feel free to update this thread if you have further questions.

Regards

[JMR21]

@sandy159Thank you for your response.  I had previously tried the Edit Record without luck but I tried it again as you suggested.  It still doesn't work.  Also, I have to leave the fields as text fields because I have rules created and the rules are only working when they are formatted as text fields.  Any other ideas?  Thanks!

 

[Vitalikssssss]

Hi @JMR21,

This screenshot confuse me a bit because highlighted fields set to "Display only", however, in your screenshot they set as TextFields (inputs).

I am not able to replicate the issue with the information provided. Perhaps, you can export the Datapage with dependencies (without data) and attach it to your response, so I could have a closer look.

Regards,

vitalikssssss

[vanderLeest]

I tried to implement all the suggestions above in my scenario, and after quite of bit of trial and error
the script below on a Details page of a Report shows the edited values in dollars while you type in Txt help fields.
BeforeFormSubmit these text values are transformed in numbers and stored in the currency fields (to allow for calculations)

It's not straightforward but it is useful workaround for something that should have been fixed in Caspio vanilla years ago

<script type="text/javascript">

document.addEventListener('DataPageReady', assignEvent);

function formatAsDollars(el) {
el.value = el.value.replace(/[^\d]/g,'').replace(/(\d\d?)$/,'$1').replace(/^0+/,'').replace( /\d{1,3}(?=(\d{3})+(?!\d))/g , "$&,");
el.value = el.value ? '$' + el.value : '';
}
function assignEvent(){
let fields = ["BudgetTxt", "SATotalTxT"]; 
fields.forEach(element => {
    element = "EditRecord" + element;
    document.getElementById(element).onkeyup = function() {
        formatAsDollars(this);
    }
    document.getElementById(element).onchange= function() {
        formatAsDollars(this);
    }
});

}

document.addEventListener('BeforeFormSubmit', function (event) {

  document.getElementById("EditRecordSubAwardTotal").value = 
    document.getElementById("EditRecordSATotalTxT").value.replace(/$|,/g, "");

  document.getElementById("EditRecordBudget").value = 
    document.getElementById("EditRecordBudgetTxt").value.replace(/$|,/g, "");

});

</script>

[JMR21]

Thank you!  I will definitely try this out!

[Kurumi]

Hi! You may also check this updated post:

 

[futurist]

Just to add, if you also wish to validate value entered in an input field to make sure that it follows this format: www.websitename.com

Then you can use this code:

 

<script type="text/javascript">

document.addEventListener('BeforeFormSubmit', function(event) {

var inputField = document.querySelector('#InsertRecordNameofField');

  const value = inputField.value;
  const regex = /^www\.[a-zA-Z0-9-]+\.[a-zA-Z]{2,}$/;

if (!regex.test(value)) {
event.preventDefault();
alert('Invalid Submission!');

} else {

document.form["caspioform"].submit();

}

});
</script>

Make sure to replace "NameofField" with the actual name of the field that you want to validate before submitting.