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

Best Answer

  • Misbah
    Answer ✓

    @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

Answers

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

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

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

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

  • Hi

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

    Please test

     

    regards,

    parmod

  • Thanks!!
  • Ok, I'll try to do that first
  • Ok thanks!