Newbie Help Question

[baggster55]

Hi all - just started using AppSheet, and pulling my hair out about an problem:

I have create an invoice form that has a field called “Invoice VAT.” This is set up as a Decimal, and should be calculated based on a VAT Rate (which is a percentage referenced from another table) and a Total Amount, which is a Number entered in the form by a user…

My problem is that the “Invoice VAT” field will not show a Decimal. For instance, if the actual amount should be 2.50, the “Invoice VAT” field shows 3. If the actual amount is 0.35, the “Invoice VAT” field shows 1.

Just to double-check, I set the “Invoice VAT” field to equal whatever the user selects from the VAT Rate dropdown, but it still doesn’t show the decimal. If the User selected 21% from the VAT Rate drop-down, the “Invoice VAT” shows 1.00.

I really cannot figure out what is going on here.

For reference, for the “Invoice VAT” I have the following Type Details set up:
Numeric Digits: 2
Maximum Value: blank
Minimum Value: blank
Increase/decrease step: blank
Decimal digits: 2

Thanks in advance!

[hru]

baggster55:

User selected 21% from the VAT Rate drop-down

I assume this is an Enum type column with base type percent?

[baggster55]

It’s actually a REF type, pointing at a table where I keep VAT rates.

[hru]

And the column type of VAT rates is a decimal type?
is it coming from some-kind of calculation?

[baggster55]

The VAT rate column type is a Percentage (in the original table where its stored). It’s not calculated, its just typed in.

[Steve]

Click through for a likely fix:

Fix for expression giving 0% or missing decimal part Tips & Tricks 💡

Trying to divide two numbers to get a percentage but always getting 0? Trying to divide two numbers but losing the numbers after the decimal point? TRY THIS Convert one or both of the values in your division expression to a Decimal value, either by: (1) wrapping the value in the expression with the DECIMAL() function; (2) multiplying the value in the expression by 1.0; or (3) changing the column’s type to Decimal if suitable to the app. FOR EXAMPLE If computing a percentage from two Number co…

[WillowMobileSys]

baggster55:

Total Amount, which is a Number entered in the form by a user…

Why wouldn’t Total Amount be a Decimal?

[WillowMobileSys]

I played with this a little. See the results in the image below. Make sure your column capturing the result is Decimal.

It is ok if the user input value is of NUMBER type (the column SomeNumber in the image).

Below, the VAT fields both use the same calculation. One is set as a NUMBER type while the other is set as a DECIMAL type.

[baggster55]

Thanks all for your help. I’m still not getting the right answer, so I have decided to change things up a little to make things easier to troubleshoot.

First, User can now select “VAT Rate” from ENUM. ENUM values are 21.00% and 0.00%, and are set to Percentage as data type.

“Invoice Amount” is now a Decimal.

I am trying to calculate VAT Amount. Field is set as Decimal. My formula is [Invoice Amount]*[VAT Rate].

No matter what values I choose for VAT rate, or what amount I put in Invoice Amount, the VAT Amount shows 0.00.

I really have no idea what’s going on here, this should be super simple.

[baggster55 · Accepted solution]

Ok so as I figured it was a stupid mistake. For the ENUM values, I typed in 21% thinking that since I set the field as a Percentage it would know that I meant 21%, or 0.21. However, it looks like it didn’t recognise 21% as a number at all.

I decided to see what happened if I just typed 21. I though since I set the field as percent may it would see 21 as 21% or 0.21, but I saw that it was interpreted as 2100%. So then I just typed 0.21 in the ENUM field and lo and behold it works.

So stupid mistake, although AppSheet could be clearer in how the Percentages should be entered.

[hru]

I thought you were at this point when asked you the first question about base type percent. Actually this is not a stupid mistake, but you need to take care for the next issue for doing math with decimal as guided by Steve in his post.

You may close this post with your answer as acceptable answer and just post another question whenever you like.