Property in List using a formula

Highlighted
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!!

10 REPLIES 10
Highlighted
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.

Highlighted
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

Re: Property in List using a formula

Ok thanks!

Highlighted
Master Anaplanner/Community Boss

Re: Property in List using a formula

@iWonder 

 

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

Highlighted
Master Anaplanner/Community Boss

Re: Property in List using a formula

@iWonder ,

 

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

Highlighted
New Contributor

Re: Property in List using a formula

Ok, I'll try to do that first
Highlighted
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.

Highlighted
Contributor

Re: Property in List using a formula

Hi

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

Please test

 

regards,

parmod

Highlighted
New Contributor

Re: Property in List using a formula

Thanks!