Find an item from a list with a property that isn't code

lauren.levine
lauren.levine
in Modeling

I saw the post about Find List Item based on a property, but the solution was the create a number list where the property for the Finditem was the code. We can not have it as a code, and they have similar role ups so the can not both be used as dimensions on a line item. How can I find the list item?

Tagged:

Best Answer

  • PaulRitner
    PaulRitner
    Answer ✓

    Hi,

     

    This is a tough one, but we did something similar before at a client in D.C.

     

    Without a code, there will be limits to what you can do, but I think there are ways to obtain consistently solid formulaic alignment (that will require review & approval).  

     

    However, consider the following idea:

     

    1. Using the Activity List, create a line item that isolates the description (and/or possibly other unique properties) of the requested new SKU as a text string that can be compared to the properties getting created in the (external system that the SKU code gets created in)
      1. Only include Activity List items that lack SKU code!  this is important because we only want to address new values that need a SKU.
      2. At the end of this string, append a comma & the numbered list item number (i.e. Activity list's NAME value)
      3. For a given activity requiring a SKU code, the string might look like the following text:  "MyNewSKUDescriptionAndPossiblyOtherProperties,#234"
      4. Its important that there be an expectation that "MyNewSKUDescriptionAndPossiblyOtherProperties" is sufficiently unique.. if you there is no reasonable expectation of this, then just stop reading now.
    2. Create a single TEXTLIST string of the above text strings.  Note that I'm assuming that the textlist string of all Activities that you need to assign a SKU number to will not exceed the 10,000 character TEXTLIST length limit.
      1. If new SKU's get requested daily and the average text length is 100 characters (i.e. description & Numbered list item number), then you can create 100 new SKU's per day without exceeding the limit
      2. In any case, even if you exceed the limit, you can simply run the process multiple times (and the TEXTLIST string should decrease in size as we match up the SKU's and assign the SKU ID.
    3. I hope I didn't lose you yet.
    4. Next step is to Create a module using the SKU List.  In this module, we will:
      1. create a line item to bring in the TEXTLIST value above.  We'll call this item NewSKUList.
      2. Create a line item to mimic the "MyNewSKUDescriptionAndPossiblyOtherProperties" part (no TEXTLIST here, just the basic search string for sku's). Let's call this line item SKUsearchString
      3. Create a line item named MatchStartPosition (format=Number).  Insert a formula to search the NewSKUList string using the FIND function: FIND(SKUsearchString,NewSKUList,1)
        1. If the search fails to return a value, then this is an existing SKU that is already mapped/availabe to the Activity list 
        2. If the search returns a value, then we have aligned an SKU with an Activity that requested a new SKU
      4. Now lets define the boundaries of our numbered list item#'s:
        1. Create a line item named NumberedListItemStart (format as Number)
          1. Insert a formula that says something like:  If MatchStartPosition=0 then 0 else FIND(SKUsearchString,",",MatchStartPosition)+1
        2. Create another line item named NumberListItemEnd (format as number)
          1. Insert a formula that says something like:  If MatchStartPosition=0 then 0 Else FIND(SKUsearchString,",",NumberedListItemStart )
        3. Create another line item named NumberListItemLength (format as number)
          1. Insert a formula like:  NumberListItemEnd-NumberedListItemStart
      5. Add a line item (formatted as text) named "Activity Numbered List Item"
        1. Insert a formula something like:  If MatchStartPosition=0 then BLANK else Mid(SKUsearchString,NumberedListItemStart,NumberListItemLength)
        2. This is our Numbered List Item!  Yay!
      6. Now we have a little workflow/review/bulk load:
        1. Create a filtered view that shows which SKU's in the SKU list align with Activity items that requested a new SKU.  Place this on a new sku review dashboard.
        2. If approved by a reviewer, add a process to the dashboard wherewe can bulk load the related SKU Codes into the "SKU Code" field in the Activity list module where a new SKU was requested.
          1. This should be a reasonably low risk event because we can only find Activity numbered list items that need a SKU code.
          2. Make sure the import process (which I called a bulk load above) uses a filtered view that only includes identified Activity List items.
        3. After the bulk load runs, the filtered view showing aligned SKU's should be empty... unless we exceeded the 10,000 character TEXTLIST string length... then we need to repeat the review process and run the bulk update process again.
          1. There is an exception here:  If there are multiple Acitivity Items that use the same newly requested SKU, only one can get fixed per iteration.  In this case, just run the process repeatedly until things clear up.
        4. You might want to include a checkbox in your review to ignore/skip selected items (if it is determined that the alignement logic is invalid).  I would expect pretty good results here, particularly if we are talking about a handful of SKU's per cycle (and not hundreds or thousands).
          1. Alternatively, if something appears amiss during the audit, you can manually clean up those SKU assignments.
        5. It goes without saying that this process needs to be adequately tested 🙂

    If you implement this approach, please let me know how it goes.  

     

    Also, if you can do this and you're interested in employment, please give me a call... you've passed the entrance test.  (really, no kidding here)

     

    Paul

Answers

  • PaulRitner
    PaulRitner

    Hi,

     

    Can you provide an example? 

     

    Paul

  • lauren.levine
    lauren.levine

    So we have 2 list, one Activity and the other is SKU. They both role up to Business Unit and are numbered list. When you create a new Activity you can either select an already existing SKU or say you want a SKU to be made in an external system. The Activity will have everything needed for the SKU in Properties except for SKU code, the SKU code is created outside of Anaplan. When the new SKU is imported into Anaplan, it needs to then be updated in the Activity automatically, but how do you find the SKU in the activity if you don't have the code. The Uniqie Property they do load is Anaplan generated Event number which is loaded in the SKU list. That would be the roperty needed to find the SKU for the Activity

  • lauren.levine
    lauren.levine

    Paul,

     

    I tried your solution and got it to work! But, since we are using the anaplan generated number as the unique field instead some kind of description, we are going to be using a another list. When they update the non activity list, it will also update this 2D list where its name is the Activity and it has the property to link it to the list we are loading. Then we are using a line item for finditem that will take the activity and find the New list then a lookup to the property. 

     

    Thanks,

    Lauren

Categories