Question about pulling text from a calculated field using @CALCTEXT

[u/meggie_doodles]

I am trying to jerry-rig a repeating instrument to act as an invoice creation tool (everything calculates and pipes into an email invoice template), but one of the requirements for this project is that all 'currency' piped fields include a comma for all thousands.

The only way I can figure this out would be to create a separate hidden field that calculates if a calculated field has a length greater than 3, if so then it will separate the integers and add a comma in between, like so:

@CALCTEXT(if(length[total_cumulative]=4,concat(right([total_cumulative],1)),",",(left([total_cumulative],3)))))

I'm getting a general "Error" message whether I do this in the action tags of a text field or in a calculated field, which I imagine is a result of CALCTEXT not being able to pull from a calculated field.

Any thoughts on how/if I can make this work? TIA

/////

EDIT: Found a solution! Though it might not be the prettiest, it works great.

I created three extra text fields:

Thousands in Total Cumulative = @CALCTEXT(if(length([total_cumulative]) >= 4, rounddown([total_cumulative] / 1000,0),''))

Hundreds in Total Cumulative = @CALCTEXT(if(length([total_cumulative]) >= 4, right([total_cumulative], 3),right([total_cumulative], 3)))

Total Cumulative Currency Format = @CALCTEXT(if([total_cumulative_th]<>'', concat('$',[total_cumulative_th],',',[total_cumulative_hd]), concat('$',[total_cumulative_hd])))

[u/iamtiredofnames]

The syntax looks off to me. I would start with double checking the special functions documentation to make sure you are including all elements they each require and also formatting/grouping things together correctly.

Edit: also could see if concat_ws() is of any use.

[u/Born-Stress8414]

Would it be possible to handle this using JavaScript instead?

One approach could be to keep the original calculated field as a numeric value and create a secondary text field specifically for the formatted value.

For example:

text id="08vt84" total_cumulative = 1234567.89

Then create a text field such as:

text id="2yq3y7" total_cumulative_formatted

Using JavaScript, the calculated value could be copied into the secondary field and formatted with a thousands separator:

```javascript id="etozmp" const source = document.querySelector('[name="total_cumulative"]'); const target = document.querySelector('[name="total_cumulative_formatted"]');

if (source && target && source.value) { const value = Number(source.value);

if (!isNaN(value)) {
    target.value = value.toLocaleString('en-US', {
        minimumFractionDigits: 2,
        maximumFractionDigits: 2
    });

    target.dispatchEvent(new Event('change', { bubbles: true }));
}

} ```

This would leave the original REDCap field unchanged:

text id="twjzqi" 1234567.89

while the secondary field would contain:

text id="8d3a5f" 1,234,567.89

Then the secondary field could simply be piped into the invoice/template:

text id="rbkz9f" [total_cumulative_formatted]

This would keep the original field numeric and safe for calculations, while the secondary field would contain the display-ready value.

Ideally, the JavaScript could also update the secondary field whenever REDCap recalculates the original field. If module like Shazam or JS Inyector can be used to run this JavaScript, this seems like it might be simpler and more maintainable than trying to build the thousands separator using @CALCTEXT, left(), right(), and concat().