Feb 17, 2019 12:53 AM
I have a column named “Weights” with values like “1.0” or “2.2” that I use for basic item ordering.
x.0 = default item
x.1 = first optional item
x.2 = second optional item
eg:
1.0 - Include
2.0 - Include
3.0 - Include
3.1 - Exclude
3.2 - Exclude
4.0 - Include
5.0 - Include
I’m using a Rollup field to add up prices, but it’s including all the optional items in the sum, where I only want the default items included.
My thought was "Create a formula field that outputs the Total Price field, but only if the far-right character of the value of the Weights column was a zero, if it’s anything other than zero, then set the price to “0” instead… Then create a rollup field on that “Total Prices Without Optional Values” column…
Can’t figure out how to get RIGHT() to show me the right-most character of the Weights field so far…
Feb 17, 2019 01:35 PM
IF(RIGHT(Weights,1)=0,{Total Price},BLANK())
The first thing in the RIGHT function is the field you’re wanting to look at - Weights. The second thing is how many characters from the right you want it to look at - one from the right. So we’re retrieving the rightmost character of the Weights field with (RIGHT(Weights,1)
This is all wrapped in an IF statement: If the rightmost character in weights is = 0, print the Total Price. If the rightmost character is anything but 0, then print nothing.
Is this just about what you wanted?
Feb 17, 2019 05:46 PM
Thanks!
I tried briefly just using RIGHT(Weights, 1) just to see if that works, and I get an error… So I think maybe I’m trying to do a text operation on a numeric field… Could be I need to use a FLOOR or something
Feb 17, 2019 06:43 PM
Hmmm, maybe I’m missing something, but that small difference actually makes it a much more complicated formula. The question becomes how to make a formula that only accepts whole numbers. Looking through the functions, I can’t see anything that would make that very easy.
If I were you, I would convert your Weights field to a text field and just use the text formula I gave you. If you still need the Weights formatted as a number for something else, you can convert it using VALUE(Weights) and print that to a new field for anything you need to do with it.
Feb 18, 2019 02:05 PM
You can force any number to be a string by adding it to another string, even an empty one. So while using RIGHT(Weights, 1) won’t work on a numerical value, RIGHT("" & Weights, 1) will work. That will let you keep your Weights field numeric rather than changing it to a text field.
Jun 17, 2019 10:21 PM
Genius! This worked for me! Thanks!