New Contributor

## Property in List using a formula

I have a unique key list which is a standard naming convention of 12345_123456_123456.

I would like to use a formula to identify the last 6 digits that are not 000000 or 0000002 and create a list subset of those.

Can you help me figure out the formula for that? Thanks!!

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

## Re: Property in List using a formula

I would write it this way

RIGHT('[List Name]'.[Property], 6) <> '000000' OR  RIGHT('[List Name]'.[Property], 6) <> '000002'

Reasons: Your condition will process faster if you don't write IF ELSE statements with Boolean here.

Also Since Subsets are a miniscule part of the whole list TRUEs will be fewer than Falses - Planual Rule number 2.02 -17

I will also not hardcode the number to 6, instead will try to keep it dynamic by utilizing FIND, RIGHT & LENGTH functions.

Thanks,

Misbah

10 REPLIES 10
Contributor

## Re: Property in List using a formula

I think this might work:

IF RIGHT('[List Name]'.[Property], 6) = '000000' OR  RIGHT('[List Name]'.[Property], 6) = '000002' THEN FALSE ELSE TRUE

Might get you started in the right direction at least.

Certified Master Anaplanner

## Re: Property in List using a formula

Hi,

I think the above is the correct start, you can then import into your list module using your new line item to populate the subset for the list member. Alternatively, if you wanted a new list completely you could replace the TRUE FALSE conditions with RIGHT('[List Name]'.[Property], 6)  and "" then import this into a new list.

New Contributor

Ok thanks!

Moderator

## Re: Property in List using a formula

I would write it this way

RIGHT('[List Name]'.[Property], 6) <> '000000' OR  RIGHT('[List Name]'.[Property], 6) <> '000002'

Reasons: Your condition will process faster if you don't write IF ELSE statements with Boolean here.

Also Since Subsets are a miniscule part of the whole list TRUEs will be fewer than Falses - Planual Rule number 2.02 -17

I will also not hardcode the number to 6, instead will try to keep it dynamic by utilizing FIND, RIGHT & LENGTH functions.

Thanks,

Misbah

Moderator

## Re: Property in List using a formula

To add, why have this as a list property and not in SYS Module?  By having this logic in a module, it will keep the logic in one SYS module where others will know to look for such data, b) you will have to create a subset action to set the subset which can be in this SYS Module (talks to maintenance) and c) this/these line items will show up in the model map whereas list properties don't.

Please try to stay away from using List Properties.

Thanks,

Rob

New Contributor

## Re: Property in List using a formula

Ok, I'll try to do that first
Contributor

## Re: Property in List using a formula

@Misbah

I would use AND instead of OR in this instance. As it is written, the formula will return TRUE each time even if last digits are 000000 or 0000002.

Contributor

## Re: Property in List using a formula

Hi

I also agreed on the use of AND instead of OR in this instance.