You are viewing limited content. For full access, please sign in.

Question

Question

Forms currency field inserting decimal

Forms
Updated January 17, 2019
asked on January 17, 2019

We have a process where on a Form, a purchase order total is pulled when a user keys in a purchase order number.

The field on the form is of course a currency field.  I have tired it with and without the thousands delimiter

When the amount is pulled over from the database, Forms is treating the comma as a decimal?

 

Data is stored as a varchar in SQL.

This seems to happen on any amount with a comma in it.  Wondering why the form doesn't take the comma as is, especially since I am using the delimiter.  

 

Thanks,

Chris

0 0

Replies

replied on January 17, 2019 • Show version history

I'd say the problem is that the data type is varchar; this seems related to language/region-based functionality because in some places the period and comma are used in reverse for numeric values.

For example,

US - $1,000.00

Brazil - $1.000,00

Typically, numeric values are not stored with a "thousands" delimiter, so it may just be that both the period and comma are viewed as a decimal separator with a string/varchar value.

As a workaround, you could attach the lookup to a hidden Single Line field, then in your Currency Field use a function to automatically copy the value. Like so,

=VALUE(LookupField)

That way, you can pull the varchar value as-is and the value function will clean it up before putting it into the currency field (just replace LookupField with the variable name for your hidden field).

1 0
replied on January 17, 2019

I figure I could do some kind of workaround but just had not seen something like this before.  I have done quite a few currency table lookups (even ones I specifically remember being varchar) and have not had this issue.

0 0
replied on January 17, 2019 • Show version history

It may be a bug, or something with the configuration, but I'd say it is best to avoid connecting a string/varchar lookup directly to a numeric or currency field anyway.

Another option would be to create a view in SQL that sanitizes the values on the database side, then you don't have to worry about what is being sent to Forms.

For example,

SELECT CAST(REPLACE([currency],',','') AS decimal(18,2)) AS [amount]
FROM [myTable]

However, you still run the risk of the view breaking if someone slipped a non-numeric value into that column.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...