Solved: Re: Get rid of unwanted zeros in Invoice output an...

Dinesh_Chandra1 · 01-06-2022 10:40 AM

There are lot of problems associated with Appsheet when computing calculations like invoices return unwanted ZEROS when the quantity is zero. (If we can stop this zero appear in quantity field we can solve this issue but since it is a number field, we need it to be a number field to apply simple math formulas)

I could not find any answer to solve this problem. Eg. If the quantity is zero, then the Total Price (Qty * Unit Price) =0 and the Invoice template will be filled with unwanted zeros.

Tried many ways including taken sync with spreadsheet (googlesheet) but unfortunately this do not work in my app as still it gives those unwanted zeros.
Spreadsheet it is working fine but not in the Appsheet.
If you have simple method or advice on to use the correct expression would be great help for all who have this simple problem.

These formulas are working fine in Google sheets where BK15= 0 (zero value) eg: Qty and following formula does not return “0” (zero value).

=IF(BK15>0,(BK15BL15),"")
and this also
=IF(BK18=0,"",(BK18BL18))

BK15 is Qty, BL15 is Unit Price and BM15 is Total Price.

When BK15 is zero there is no return value zero in goggle sheet but if use the same formulas in appsheet it gives all unwanted zero in computation and this is not what we want to appear in the invoice as it is not needed to show zero values for Totals.

This is another expression that works fine in the google sheet but its a real pity why can’t we get that in appsheet?

Here is the formula; =IF(BK15>0,(BK15*BL15),"")

In my Invoice Template there are 3 columns appear zeros when the quantity is zero. They are: Total Price, Discount and Discounted Price.

Hope you can help me with this as I can’t simply understand why these google sheet formulas are not compatible with appsheet?

BTW: I have also tried making 0;0;;@ numbering format in the appsheet number formatting and use that sync with appshet. There is no use still appsheet recognize zero value as zero and when multiply something with a zero value it gives zero values.

If we can generate an expression that do not take zero values in computation formulas we can resolve this I guess. But still can’t understand why appsheet do not offer same result in google sheet when the appsheet has been created with googlesheet?

Thanks.
Dinesh C.

Dinesh_Chandra1

Hi Graham,
Yes you have correctly identified that QTY column in number type actually do not save data on spreadsheet not as zero but as BLANK.
So essentially if that is the case that we need to treat this field as blank but in number types.

I found a way to solve that issue to avoid unwanted zero appear in your spreadsheet and such you would use in making PDF documents.

Here is the solutions that worked for me and I am share for those who wants to get rid of unwanted zeros in your PDF Quotations and other.

Column Structure
QTY * UNIT PRICE = Total Amount
Total Amount * Discount Rate = Discount
Total Amount - Discount = Total after Discount (Discounted Total)

Use below formula for Total Amount (Here all fields are number fields)

IF([ITEM19 QTY]=NUMBER(""),NUMBER(""),([ITEM19 QTY] * [ITEM19 UNIT PRICE]))

For Discount use bellow: (Here Discount rate is percentage and Total amount is number field)

IF([ITEM18 TOTAL PRICE]=NUMBER(""),NUMBER(""),([ITEM18 TOTAL PRICE]*[ITEM18 DISCOUNT RATE]))

Then use Discounted Amount below formula (there can be other formulas you can think of but since it has different expression, I prefer using this)

IF( [ITEM19 QTY]*1=0, NUMBER(""),[ITEM19 TOTAL PRICE]-[ITEM19 DISCOUNT] )

Cheers!!
Dinesh C.

View solution in original post

Rafael_ANEIC-PY

Hello @Dinesh_Chandraratne1, welcome to our community !

Not necesarily, you can insert logic into your template, so that when you encounter a “0” in an undesired place you can replace it with a “” instead.

You can use these to convert your numerical values to text with the desired amount of decimals, add currency symbols, apply conditional formating, etc.
It can look kinda ugly inside your template, but it gets the job done

An example of conditional formating in templates

You can make an expression that does the exact same thing in AppSheet, but it’s not the same as a google sheets formula, and it doesn’t have to be, it is recommended that you run all calculations inside your app when posible.

If you could share some information about your aplication column structure we could help you elaborate an equivalent expression.

Dinesh_Chandra1

Hi Rafael,
Thanks for your feedback.
I did try some expressions with IF but it didn’t work for me.
Here are some of the expressions that I have used.

IF([ITEM1 QTY]>0,([ITEM1 QTY]*[ITEM1 UNIT PRICE]),"")

IF([ITEM1 QTY]=0,([ITEM1 QTY]*[ITEM1 UNIT PRICE]),"")

IF([ITEM1 QTY]=0,"",([ITEM1 QTY][ITEM1 UNIT PRICE]))
IF([ITEM1 QTY]>0,"",([ITEM1 QTY][ITEM1 UNIT PRICE]))

I have not tried IFS yet.

To your request on column structure I am attaching required here as my sheet is having a separate column for each item (product) as user experience on seeing products entered in down drag is better than dragging on horizontal plane.

Here is the extract of my column structure for your kind perusal.

ADVANCE PAYMENT RECEIPT STATUS ITEM1 ITEM1 DESCRIPTION ITEM1 UNIT ITEM1 QTY ITEM1 UNIT PRICE ITEM1 TOTAL PRICE ITEM1 DISCOUNT RATE ITEM1 DISCOUNT ITEM1 DISCOUNTED PRICE ITEM2 ITEM2 DESCRIPTION ITEM2 UNIT ITEM2 QTY ITEM2 UNIT PRICE ITEM2 TOTAL PRICE ITEM2 DISCOUNT RATE ITEM2 DISCOUNT ITEM2 DISCOUNTED PRICE ITEM3 ITEM3 DESCRIPTION ITEM3 UNIT ITEM3 QTY ITEM3 UNIT PRICE ITEM3 TOTAL PRICE ITEM3 DISCOUNT RATE ITEM3 DISCOUNT ITEM3 DISCOUNTED PRICE ITEM4 ITEM4 DESCRIPTION ITEM4 UNIT ITEM4 QTY ITEM4 UNIT PRICE ITEM4 TOTAL PRICE ITEM4 DISCOUNT RATE ITEM4 DISCOUNT ITEM4 DISCOUNTED PRICE ITEM5 ITEM5 DESCRIPTION ITEM5 UNIT ITEM5 QTY ITEM5 UNIT PRICE ITEM5 TOTAL PRICE ITEM5 DISCOUNT RATE ITEM5 DISCOUNT ITEM5 DISCOUNTED PRICE ITEM6 ITEM6 DESCRIPTION ITEM6 UNIT ITEM6 QTY ITEM6 UNIT PRICE ITEM6 TOTAL PRICE ITEM6 DISCOUNT RATE ITEM6 DISCOUNT ITEM6 DISCOUNTED PRICE ITEM7 ITEM7 DESCRIPTION ITEM7 UNIT ITEM7 QTY ITEM7 UNIT PRICE ITEM7 TOTAL PRICE ITEM7 DISCOUNT RATE ITEM7 DISCOUNT ITEM7 DISCOUNTED PRICE ITEM8 ITEM8 DESCRIPTION ITEM8 UNIT ITEM8 QTY ITEM8 UNIT PRICE ITEM8 TOTAL PRICE ITEM8 DISCOUNT RATE ITEM8 DISCOUNT ITEM8 DISCOUNTED PRICE ITEM9 ITEM9 DESCRIPTION ITEM9 UNIT ITEM9 QTY ITEM9 UNIT PRICE ITEM9 TOTAL PRICE ITEM9 DISCOUNT RATE ITEM9 DISCOUNT ITEM9 DISCOUNTED PRICE ITEM10 ITEM10 DESCRIPTION ITEM10 UNIT ITEM10 QTY ITEM10 UNIT PRICE ITEM10 TOTAL PRICE ITEM10 DISCOUNT RATE ITEM10 DISCOUNT ITEM10 DISCOUNTED PRICE ITEM11 ITEM11 DESCRIPTION ITEM11 UNIT ITEM11 QTY ITEM11 UNIT PRICE ITEM11 TOTAL PRICE ITEM11 DISCOUNT RATE ITEM11 DISCOUNT ITEM11 DISCOUNTED PRICE ITEM12 ITEM12 DESCRIPTION ITEM12 UNIT ITEM12 QTY ITEM12 UNIT PRICE ITEM12 TOTAL PRICE ITEM12 DISCOUNT RATE ITEM12 DISCOUNT ITEM12 DISCOUNTED PRICE ITEM13 ITEM13 DESCRIPTION ITEM13 UNIT ITEM13 QTY ITEM13 UNIT PRICE ITEM13 TOTAL PRICE ITEM13 DISCOUNT RATE ITEM13 DISCOUNT ITEM13 DISCOUNTED PRICE ITEM14 ITEM14 DESCRIPTION ITEM14 UNIT ITEM14 QTY ITEM14 UNIT PRICE ITEM14 TOTAL PRICE ITEM14 DISCOUNT RATE ITEM14 DISCOUNT ITEM14 DISCOUNTED PRICE ITEM15 ITEM15 DESCRIPTION ITEM15 UNIT ITEM15 QTY ITEM15 UNIT PRICE ITEM15 TOTAL PRICE ITEM15 DISCOUNT RATE ITEM15 DISCOUNT ITEM15 DISCOUNTED PRICE ITEM16 ITEM16 DESCRIPTION ITEM16 UNIT ITEM16 QTY ITEM16 UNIT PRICE ITEM16 TOTAL PRICE ITEM16 DISCOUNT RATE ITEM16 DISCOUNT ITEM16 DISCOUNTED PRICE ITEM17 ITEM17 DESCRIPTION ITEM17 UNIT ITEM17 QTY ITEM17 UNIT PRICE ITEM17 TOTAL PRICE ITEM17 DISCOUNT RATE ITEM17 DISCOUNT ITEM17 DISCOUNTED PRICE ITEM18 ITEM18 DESCRIPTION ITEM18 UNIT ITEM18 QTY ITEM18 UNIT PRICE ITEM18 TOTAL PRICE ITEM18 DISCOUNT RATE ITEM18 DISCOUNT ITEM18 DISCOUNTED PRICE ITEM19 ITEM19 DESCRIPTION ITEM19 UNIT ITEM19 QTY ITEM19 UNIT PRICE ITEM19 TOTAL PRICE ITEM19 DISCOUNT RATE ITEM19 DISCOUNT ITEM19 DISCOUNTED PRICE ITEM20 ITEM20 DESCRIPTION ITEM20 UNIT ITEM20 QTY ITEM20 UNIT PRICE ITEM20 TOTAL PRICE ITEM20 DISCOUNT RATE ITEM20 DISCOUNT ITEM20 DISCOUNTED PRICE

ADVANCE P.R. APPROVED WS_001_IND_145 White Sugar - Indian ICUMSA 145 kg 1,000.00 105 105000 0.03 2,625.00 102,375.00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

ADVANCE P.R. PENDING DS_002_IRAN_GR1_B Dried Sprats - Iranian Grade 1_B kg 1,000.00 480 480000 0.03 12,000.00 468,000.00 RL_006_CAN_SM Red Split Lentils - Small - CANADIAN kg 500 250 125000 0 125000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

ADVANCE P.R. PENDING DS_004_THAI_GR1_NS Dried Sprats - Thailand Grade 1_No Salt kg 900.00 600 540000 0.03 13,500.00 526,500.00 RL_002_AUS_CUP_M Red Split Lentils CUP - Medium - AUSTRALIAN kg 500 275 137500 0 137500 RL_006_CAN_SM Red Split Lentils - Small - CANADIAN kg 400 250 100000 0 100000 WS_002_BRAZ_45 White Sugar - Brazil ICUMSA 45 kg 1000 120 120000 0 120000 CF-001_CHIN_125g Canned Fish - Small 125g - PRC Tin 600 185 111000 0 111000 RL_006_CAN_SM Red Split Lentils - Small - CANADIAN kg 400 250 100000 0 100000 WS_001_IND_145 White Sugar - Indian ICUMSA 145 kg 2000 105 210000 0 210000 CF-002_CHIN_425g Canned Fish - Large 425g - PRC Tin 500 340 170000 0 170000 DS_003_THAI_GR1 Dried Sprats - Thailand Grade 1 kg 500 605 302500 0 302500 RL_006_CAN_SM Red Split Lentils - Small - CANADIAN kg 6000 250 1500000 0 1500000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

ADVANCE P.R. PENDING WS_002_BRAZ_45 White Sugar - Brazil ICUMSA 45 kg 1,000.00 120 120000 0.00 120,000.00 DS_001_IRAN_GR1 Dried Sprats - Iranian Grade 1 kg 2000 470 940000 2.50% 23500 916500 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

ADVANCE P.R. PENDING DS_002_IRAN_GR1_B Dried Sprats - Iranian Grade 1_B kg 1,000.00 480 480000 0.00 480,000.00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

I am also attaching some sample Quotations generated for you to understand what fields that I required to populate the Quotation Template.
One without zeros is when I entered full datas in 10 rows when it had only 10 products. (and also those data did not directly generated from the App but copied from google sheet as first I did quotation generation from google sheet)
The other sheet is the present status that I am in now with unwanted zeros.

Thanks again for giving me suggestions.
Dinesh C.

QUOTATION - PII for Jaykay Marketing Services Pvt Ltd dated 1_4_2022.pdf|attachment (133 KB)

QUOTATION - 1004 - Cosmecx Cons for Cosmecx Consortrium Pvt Ltd dated 1_6_2022.pdf (130 KB)

Steve

Why would you expect AppSheet to behave the same as a spreadsheet?

Dinesh_Chandra1

Hi Steve,
Thanks for your feedback.
I am not expecting AppSheet to behave same as spreadsheet but want to know overcome this problem somehow.
If you know a way, please do advice me.
Thanks.
Dinesh C.

Marc_Dillon

Try this:

NUMBER("")

Dinesh_Chandra1

Hi Mark,
you mean to have NUMBER("") for QTY?

Marc_Dillon

Spreadsheet formula:

IF( A1=0 , "" , A1*A2)

Spreadsheet cells are not type-constrained, so you can put a blank string into them no problem.

Appsheet columns are type-constrained, you can’t put a text string into a Number column. To put a blank value into a Number column, use NUMBER(""). Or, as a full expression:

IF( [qty]=0 , NUMBER("") , [qty]*[price] )

Dinesh_Chandra1

Hi Mark,
I found a way to resolve that and the formulas are shared here.
Thanks for your help and guidance.
Now I have a clean spreadsheet without zeros.
(If I could not do this properly with expression, I would have to think of making an Appscript function and use onChange function to change those zeros.
Cheers!!
Dinesh C.

Dinesh_Chandra1

Thanks Mark,
I will try that and will give you the feedback.

Dinesh C.

Dinesh_Chandra1

Hi Mark,
That works fine…!!
Thanks for your advice and that word NUMBER and “” placed in () made a huge difference to the formula and now it do the expected function.
So in all this type of situations anyone can use this.
IF( [qty]=0 , NUMBER("") , [qty]*[price] )

Dinesh C.

Dinesh_Chandra1

I am sorry to celebrate early.
IF( [qty]=0 , NUMBER("") , [qty]*[price] )
That expression also did not work.

ITEM1	ITEM1 DESCRIPTION	ITEM1 UNIT	ITEM1 QTY	ITEM1 UNIT PRICE	ITEM1 TOTAL PRICE	ITEM1 DISCOUNT RATE	ITEM1 DISCOUNT	ITEM1 DISCOUNTED PRICE	ITEM2	ITEM2 DESCRIPTION	ITEM2 UNIT	ITEM2 QTY	ITEM2 UNIT PRICE	ITEM2 TOTAL PRICE	ITEM2 DISCOUNT RATE	ITEM2 DISCOUNT	ITEM2 DISCOUNTED PRICE	ITEM3	ITEM3 DESCRIPTION	ITEM3 UNIT	ITEM3 QTY	ITEM3 UNIT PRICE	ITEM3 TOTAL PRICE	ITEM3 DISCOUNTED PRICE	ITEM4	ITEM4 DESCRIPTION	ITEM4 UNIT	ITEM4 QTY	ITEM4 UNIT PRICE	ITEM4 TOTAL PRICE	ITEM4 DISCOUNTED PRICE	ITEM5	ITEM5 DESCRIPTION	ITEM5 UNIT	ITEM5 QTY	ITEM5 UNIT PRICE	ITEM5 TOTAL PRICE	ITEM5 DISCOUNTED PRICE	ITEM6	ITEM6 DESCRIPTION	ITEM6 UNIT	ITEM6 QTY	ITEM6 UNIT PRICE	ITEM6 TOTAL PRICE	ITEM6 DISCOUNTED PRICE	ITEM7	ITEM7 DESCRIPTION	ITEM7 UNIT	ITEM7 QTY	ITEM7 UNIT PRICE	ITEM7 TOTAL PRICE	ITEM7 DISCOUNTED PRICE	ITEM8	ITEM8 DESCRIPTION	ITEM8 UNIT	ITEM8 QTY	ITEM8 UNIT PRICE	ITEM8 TOTAL PRICE	ITEM8 DISCOUNTED PRICE	ITEM9	ITEM9 DESCRIPTION	ITEM9 UNIT	ITEM9 QTY	ITEM9 UNIT PRICE	ITEM9 TOTAL PRICE	ITEM9 DISCOUNT RATE	ITEM9 DISCOUNTED PRICE	ITEM10	ITEM10 DESCRIPTION	ITEM10 UNIT	ITEM10 QTY	ITEM10 UNIT PRICE	ITEM10 TOTAL PRICE	ITEM10 DISCOUNTED PRICE
WS_001_IND_145	White Sugar - Indian ICUMSA 145	kg	1,000.00	105	105000	0.03	2,625.00	102,375.00						0		0	0						0	0						0	0						0	0						0	0						0	0						0	0						0		0						0	0
DS_002_IRAN_GR1_B	Dried Sprats - Iranian Grade 1_B	kg	1,000.00	480	480000	0.03	12,000.00	468,000.00	RL_006_CAN_SM	Red Split Lentils - Small - CANADIAN	kg	500	250	125000		0	125000						0	0						0	0						0	0						0	0						0	0						0	0						0		0						0	0
DS_004_THAI_GR1_NS	Dried Sprats - Thailand Grade 1_No Salt	kg	900.00	600	540000	0.03	13,500.00	526,500.00	RL_002_AUS_CUP_M	Red Split Lentils CUP - Medium - AUSTRALIAN	kg	500	275	137500		0	137500	RL_006_CAN_SM	Red Split Lentils - Small - CANADIAN	kg	400	250	100000	100000	WS_002_BRAZ_45	White Sugar - Brazil ICUMSA 45	kg	1000	120	120000	120000	CF-001_CHIN_125g	Canned Fish - Small 125g - PRC	Tin	600	185	111000	111000	RL_006_CAN_SM	Red Split Lentils - Small - CANADIAN	kg	400	250	100000	100000	WS_001_IND_145	White Sugar - Indian ICUMSA 145	kg	2000	105	210000	210000	CF-002_CHIN_425g	Canned Fish - Large 425g - PRC	Tin	500	340	170000	170000	DS_003_THAI_GR1	Dried Sprats - Thailand Grade 1	kg	500	605	302500		302500	RL_006_CAN_SM	Red Split Lentils - Small - CANADIAN	kg	6000	250	1500000	1500000
WS_002_BRAZ_45	White Sugar - Brazil ICUMSA 45	kg	1,000.00	120	120000		0.00	120,000.00	DS_001_IRAN_GR1	Dried Sprats - Iranian Grade 1	kg	2000	470	940000	2.50%	23500	916500						0	0						0	0						0	0						0	0						0	0						0	0						0		0						0	0
DS_002_IRAN_GR1_B	Dried Sprats - Iranian Grade 1_B	kg	1,000.00	480	480000		0.00	480,000.00						0		0	0						0	0						0	0						0	0						0	0						0	0						0	0						0	0.00%	0					0	0	0

Dinesh_Chandra1

I think this problem needs more attention from experienced experts.

graham_howe

As @Marc_Dillon mentioned, Appsheet columns are type-constrained, you can’t put a text string into a Number column. To put a blank value into a Number column, use NUMBER(""). So I wonder if the reverse applies, that where you have nothing in a quantity cell in the spreadsheet, this is also not being interpreted as 0. Try the following to force the qty field to be evaluated as a number before checking it is equal to 0:

IF( [qty]*1 = 0 , NUMBER("") , [qty]*[price] )

Dinesh_Chandra1