Property in List using a formula

iWonder

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

Misbah

@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

EarlC

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.

AWhitworth

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. 

rob_marshall

@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

Alexandru.Rosca

@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.

parmod.kumar2

Hi

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

Please test

 

regards,

parmod

iWonder

Thanks!!

iWonder

Ok, I'll try to do that first

iWonder

Thanks!

iWonder

Ok thanks!