hello i want add discount virtual column in order datiles table

[Hassan_Tanna]

hello i want add discount virtual column in order datiles table so how i can do that  ! 

order details table

[Joseph_Seddik · Accepted solution]

I thought so 🙂

How to calculate shipping fees? I don’t know, what is your criteria? We can help on the expression but first how do YOU calculate it on paper?

If you mean how to add it to the total, then just add 

+ [Shipping fees]
to the end of your Total column expression. 

[Joseph_Seddik · Accepted solution]

Do you mean how can you calculate the total?

In that case please show the expression you currently have in the Total Column. 

[WillowMobileSys]

you'll need to be more detailed in what you need.  

[Hassan_Tanna]

i want when i  make a order choose between list of discounts 10% or 20 % of total sales 

i want make a discount of the total

[ea_9000]

this is how added it to my appthis field as percentage

i have discount and discountsum columns

SUMIF('Order Details'!C[-4]:C[-4],RC[-5],'Order Details'!C[-1]:C[-1]) * (1-(RC[4]))

you can selct your discount percentage

configure as you wish

[Hassan_Tanna]

emad thank you so much i will try it 🙂 

[ea_9000]

Hello Hassan,

this what i did :

1-add a column discount

2-change "Total Cost" Spreadsheet formula to :SUMIF('Order Details'!C[-4]:C[-4],RC[-5],'Order Details'!C[-1]:C[-1]) * (1-(RC[4]))

(1-(RC[4])) id the discount column R1C1 format.

cheers

Emad

[Hassan_Tanna]

Hello emad @ea_9000  I have another question if you can help me I forget to put columns for shipping fees and i want do now virtual column for shiitake fees I think it will be in order table or order details table but what I should put inside the app formula ! How it’s will work ! 

[Joseph_Seddik]

Hello Hassan, 

All the needs you have described here should be in normal columns not virtual ones. You cannot use virtual columns to collect user input, like for example discount and fees. Virtual columns can be used for calculations based on other existing values in the row, and they have their limitations and drawbacks. 

What you need is to physically add a new column in the sheet, then do a Regenerate Structure for your table in AppSheet. 

 

[Hassan_Tanna]

thank you @Joseph_Seddik  this first time i know i can add normal column from regenerate structure 🙂 ok 

i add now now shipping fees in order details table so how now  calculate the shipping fees with total cost 

order details table

[Joseph_Seddik · Accepted solution]

I thought so 🙂

How to calculate shipping fees? I don’t know, what is your criteria? We can help on the expression but first how do YOU calculate it on paper?

If you mean how to add it to the total, then just add 

+ [Shipping fees]
to the end of your Total column expression. 

[Hassan_Tanna]

thank you so much its work now :))

[Hassan_Tanna]

i have another question  i already a added discount as  normal column in order details table but what will be equation or app formula expression to work 

[Joseph_Seddik · Accepted solution]

Do you mean how can you calculate the total?

In that case please show the expression you currently have in the Total Column. 

[Hassan_Tanna]

Yeah wantcalculate the total so what should i do exactly !!

 

 

[Hassan_Tanna]

@Joseph_Seddik So should i add something in the total column! 

[Joseph_Seddik · Accepted solution]

Thank you.

First let's talk please about something that is necessary to take into account.

There is one important notion in math expressions that is: operator precedence. It means which operators (+, -, *, /) get calculated before which. Multiplication (*) and division (/) operators are always evaluated before addition (+) and subtraction (-). 

Example: 

4 + 5 * 3 = 19,  not 27

That is because of the higher precedence of multiplication over addition. Thus, the 5 * 3 term will be evaluated first and results in 15, which will then be used (as operand) in 4 + .. so it will be 4 + 15 = 19.

If you want the 4 + 5 part to be evaluated first, then multiplied by 3, then you have to use the parentheses ( ) which have a higher precedence than all other math operators. 

(4 + 5) * 3 = 27 = 9 * 3

Now back to your expression: 

[Product ID].[Price] + [Shipping fees] * [Quantity]

What you are doing here is multiplying the quantity by the shipping fees only, and adding the price of only one product. So if you have for example an order with 3 items costing 150 EGP each, and a shipping cost of 20 EGP, your expression will calculate as this:

150 + 20 * 3 = 150 + 60 = 210 EGP, which is cheaper than the cost of the 3 items without shipping 🙂 

Thus, if you want to have a fixed shipping fee per order, then your expression should be: 

[Product ID].[Price] * [Quantity] + [Shipping fees] = 150 * 3 + 20 = 450 + 20 = 470 EGP.

Otherwise, in case  you want to add the shipping fees for every item in the order, then you should write the expression like this:

([Product ID].[Price] + [Shipping fees]) * [Quantity] = (150 + 20) * 3 = 170 * 3 = 510 EGP

Now let's speak about the Discount, for this we'll assume that you charge a fixed shipping fee per oder, so your expression is: 

[Product ID].[Price] * [Quantity] + [Shipping fees] 

Similarly, you have two cases here. First, if you want to apply the Discount only to the product price not the shipping fees, then, assuming a 20% discount, your expression should be: 

[Product ID].[Price] * [Quantity] * (1 - [Discount]) + [Shipping fees] 

= 450 * (1 - 20%) + 20 = 450 * 0,8 + 20 = 360 + 20 = 380 EGP.

Note that we had to put (1 - [Discount]) between parentheses so that it gets calculated before multiplying the result by the item's price and quantity. 

On the other hand, if you want to apply the discount to the total value, including the shipping fees, then your expression should be :

([Product ID].[Price] * [Quantity] + [Shipping fees]) * (1 - [Discount])

= (150 * 3 + 20) (1 - 20%) = (450 + 20) (1 - 20%) = 470 * 0.8 = 376 EGP

[Hassan_Tanna]

you are a king really thank you for everything i'm very happy and glad because you helped me and gave me from your time everything work good now really thank you i appreciated your help for me 🙂 🙂 

[Hassan_Tanna]

@Joseph_Seddik  Could you help me please 

Need help How to add county code in phone column or how is work i want add +2 before the phone number like +201020808070

you can suggest for me the better solution 

 

[Joseph_Seddik]

ازيك يا حسن 🙂

If you would only have Egyptian phone numbers, so you'll always need to add a "+2" then you can just use the following expression:

"+2" & [phone]

Please note that Egypt's country code is "20" NOT "2", so the prefix to add is "+20" NOT "+2". In Egypt as in many other countries, phone numbers especially mobile begin with 0, so adding "+2" should give an acceptable result most of the time.

However, this is NOT the case in all countries, so the correct way should be to affix the correct country code ("20" in case of Egypt) after removing the left most zero if exists from the phone number. 

Therefore, the correct expression to use should be:

"+20" & IF( 
  STARTSWITH([phone], 0),
  RIGHT([phone], LEN([phone]) -1),
  [phone]
)

If you need to include all country codes for all countries in your app, tell me. In this case, tell me also what is the user's display language of your app.