Skip to main content
G
Gregory5FBA
Participating Frequently
September 1, 2020
Answered

Data formats in forms

  • September 1, 2020
  • 3 replies
  • 632 views

I am using a form as part of a document portfolio to distribute and recieve the completed forms...I am exporting the document portfolio as a CSV so I can summarize data

 

I have a field (Field 3) that calculates a percentage from the numerator (Field 1) and the denominator (Field 2), which are the variables (they will always be integers) so I assign them a number format with 0 decimals

If I assign Field 3 as "None" it shows the number and many decimal places 

Field 1: 60

Field 2: 70

Field 3: 1.16666666667 (and yes I realize the numerator is larger than the denominator in this example, nonetheless the issue is the same)

If I assign Field 3 as "Number, 0 decimals" it returns a number:

1 (rounded down I suppose)

If I assign Field 3 as a "Percentage" it returns:

116.67%

In all three cases: if you click into the field (in preview/data entry mode) it returns:

1.16666666667

I suspect the 1.16666666667 value is the one that is exported into the CSV...while not a big issue, I can write a function in Excel to round and convert the entire column into a percentage value or just use the value with the understanding of what a percent is (the value * 100)...but I would rather the value be exported as a value of 1-100, no decimals or fractions thereof

 

To arrive at the calculation the javascript is:

var numerator = +getField("NrShpIDReqWithID").value;
var denominator = +getField("TotalSheepReqID").value;
if (denominator!==0){event.value=numerator / denominator;} else {event.value="";}

 

I didn't write this script, and with limited knowledge of javascript, I am using it because it worked for the calculation from a previous form...maybe there is a better script with a parameter that defines how I would like the value exported (1-100 integer value)

 

Any help is appreciated...TIA

 

 

 

 

This topic has been closed for replies.
Correct answer ls_rbls

Use the custom calculation script like this:

 

var numerator = this.getField("NrShpIDReqWithID").value;
var denominator = this.getField("TotalSheepReqID").value;

if (denominator!==0) {event.value= util.printf("%.0f",  (numerator / denominator)*100); }
if ((numerator=="")||(denominator =="")) event.value="";

3 replies

G
Gregory5FBAAuthor
Participating Frequently
September 2, 2020

This script works so far, Thanks!

ls_rbls
Community Expert
ls_rblsCommunity ExpertCorrect answer
Community Expert
September 1, 2020

Use the custom calculation script like this:

 

var numerator = this.getField("NrShpIDReqWithID").value;
var denominator = this.getField("TotalSheepReqID").value;

if (denominator!==0) {event.value= util.printf("%.0f",  (numerator / denominator)*100); }
if ((numerator=="")||(denominator =="")) event.value="";
try67
Community Expert
try67Community Expert
Community Expert
September 1, 2020

Change:

event.value=numerator / denominator;

To:

event.value=Math.round((numerator / denominator)*100);

G
Gregory5FBAAuthor
Participating Frequently
September 2, 2020

The script returned a value of 100...the script below worked...Thanks anyway

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices