- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 26, 2020 07:33 PM
- We have a field called Products which is a link to our “Product” table
- We have a field called Cost which is a lookup field. (looks up the cost from the Product table)
- In our “ORDER” table, we have a field called Total Cost
In that Total Cost field we would like to multiply the Cost by the Qty (quantity) but if there is a value in the Misc Cost field then we would like to multiply that field by the Qty to calculate the Total Cost
Here’s the kicker…
Basically, we want to use the Misc Cost to override the Cost x Qty total, if there if something is entered in that field.
Usually a product is full price for us. Sometimes we get a massive discount on a certain quantity or we get some of a product for free. I still want to link to the existing product from our database, but I want to have this Override field and have my “Total Cost” field use the Misc Cost field, if it’s exists.
Sorry if I didn’t explain this very well. Any help here would be great!! :pray:
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 26, 2020 08:28 PM
Thank you so much @ScottWorld!
That formula was much further than I was ever able to get, so thank you! But when I use that formula and put a $0.00 in the Misc Cost field, the Total Cost field, I want that $0.00 to override the Cost which it was not doing.
After a bit of tinkering a friend helped me come up with this, which is exactly with what I need…
:white_check_mark: . SOLUTION…
IF({Misc COST}!=BLANK(),{Misc COST}*Qty,Cost*Qty)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 26, 2020 07:38 PM
That could be done with a simple formula:
IF({Misc Cost},Qty*{Misc Cost},Qty*Cost)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 26, 2020 08:28 PM
Thank you so much @ScottWorld!
That formula was much further than I was ever able to get, so thank you! But when I use that formula and put a $0.00 in the Misc Cost field, the Total Cost field, I want that $0.00 to override the Cost which it was not doing.
After a bit of tinkering a friend helped me come up with this, which is exactly with what I need…
:white_check_mark: . SOLUTION…
IF({Misc COST}!=BLANK(),{Misc COST}*Qty,Cost*Qty)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 27, 2020 03:15 AM
Ah, yes! Zero is the tricky one. Glad you figured it out! :slightly_smiling_face: