Sep 12, 2021 05:12 AM
As you can see in the image, formula (price - discount + shipping charges) is returning 1 in first field as I have two values separated by commas that is 399,1076.
Is there any way to write this formula, so it takes into account units separated by commas and return the correct result.
Sep 12, 2021 05:57 AM
Hi Dhaval. Assuming you want to sum all the prices in the “Price” field before subtracting discount and adding shipping, I would use a “rollup” field, instead, that summed those prices for you.
Sep 12, 2021 06:29 AM
But I want Prices after deducting Discount.
To give a better idea refer image in which I have not used any formulas and have entered figures manually. So, let’s say price of one product is 399 and discount is 50, & another product’s price is 1076 and discount is 40. So I want result of (399-50) & (1076-40) i.e 349,1036. I want exact same results when I enter formula so that I don’t have to do calculation manually for the every record I add.
Sep 12, 2021 07:13 AM
Oh, I see. You want to end up with an array of invoice prices derived from other arrays. If the unit discount and the shipping is specific to each customer, then I really don’t know how to do this in Airtable. Hopefully, someone will but there’s not much array magic available in formulas.
Sep 12, 2021 07:18 AM
Math with lookup fields does not work the way you want.
You should use a junction table where you enter the discount for each item in the sale and calculate the discounted price. Then you can rollup all the discounted prices.
Sep 12, 2021 07:49 AM
I could do that but the real challenge is that I have listed multiple coupon codes on multiple e-commerce platforms (amazon, Noon, etc), so there is not specific discount code or rates for products. Additionally, I also sell my products on my website at full prices (no discount). So there would be a myriad types of invoices from where I’d be recording discounts. It could be 0, 50, 10, anything.
Sep 12, 2021 08:23 AM
I assume that you could use an automation, triggered on specific field updates, to run a script that iterate/calc through the arrays.