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

lauren.levine

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?

PaulRitner

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