Skip to main content

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…


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?


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?


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

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


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.

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.

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.


Genius! This worked for me! Thanks!

Reply


Terms & Conditions