FIND formula not working
Hello,
We have an line item composed of all the Sales Reps associated with an account (some only have 1, some have multiple). This line item is text formatted. This line items is called "Sales Reps"
We then have a second text-formatted line item, which is a text field, called "name," and the functionality we are looking for is when we type the name of the rep in the text field, our line item with the FIND formula in it will return the number of accounts that rep is associated with. Currently, the formula is only returning zeros, and we cannot figure out why.
The formula looks like this:
FIND(name, Sales Reps)
We know that the text syntax matters, and we used the Compare formula to verify that the model is recognizing the names match between line items when we input the name in the "name" line item.
I'm wondering if there is a consideration we are overlooking, as it appears in theory the formula should be working?
Thanks!
Answers
-
Hey @KevinMcQuillen,
Thanks for your question. I believe I understand the context of what you are trying to do.
For your module, you are looking to create a system, where if you type in the name of the Sales Rep, you want to return the number of accounts that Sales Rep is assigned to. The FIND function behaves a bit differently than what you may be anticipating, little different than Excel. The FIND function is used to identify text string characters and the character location of them in a line item. So your function is currently returning 0 because it does not match the text string that is being searched.
Just so I understand the nuances/context a bit better, are you able to provide a screenshot of the blueprint view of this module? And the formula you have currently?
I have a few different ideas as to how we can approach this. Seeing your blueprint view will confirm for me which direction we can take this. There are a few different functions such as LOOKUP that we might be able to leverage here depending on your setup.
Look forward to your reply.
0 -
Thank you for the response!
Interesting - looks like I completely misinterpreted the purpose of the Find formula. We have experimented with using LOOKUPs, but I would be interested to hear your alternatives.
Attached is the blueprint mode - for the previous post I simplified line item names - the line item we want to be working is "# of Opportunities on Team"
1 -
Sure no problem @KevinMcQuillen! This helps open up a few more possibilities.
Few more clarifying questions:
1) Are you able to provide a screenshot of where "PROPERTIES: ALL OPPORTUNITIES.Opportunity" is located? The blueprint view for that line item (assuming it is a line item based on syntax, or if list property, screenshot of that)?
2) Depending on your answer this may open up routes using either SUM or LOOKUP. The L6 Sales Rep field is what we may potentially use as a reference for the SUM or LOOKUP formula, but depends on the dimensionality of the Opportunity line item and the setup of that module.
Look forward to your reply. I have a playground model mirroring your setup where I can play with some options once I have some context!
1 -
Well if you need the count of accounts Sales Rep is assigned to use SUM function
Text Line Item = Free Text
List Formatted Line item = Find Item(List, Text Line Item)
Account = 1
Number of Accounts = Account[SUM: List Formatted Line Item] -----Dimensioned by Sales Reps
0 -
Yup good call @Misbah , that’s what I was thinking as well regarding the SUM function!
Would you have to create the find item formula? As the L6 Sales rep field already exists as a property which is already list formatted? This would be a duplicate line item possibly. From PLANS point of view, perhaps it would be best to use the existing property?
Unless the goal is to use a flat list for employees. Thoughts?
1 -
PLANS does not recommend creating Properties in the list 🙂 Well there are exceptions to it but I don't see the need to use that exception here. But yeah to your point if there is already a line item then we can use that instead of creating a new one
Misbah
1 -
Sorry I should have elaborated on our complications, as there are additional considerations. We have our L6 Sales Reps list, and each rep is the owner of an account. However, in addition, some Reps are listed as "team members" on other accounts, and for reporting purposes their projected revenue should include the accounts they are the direct owner of, as well as the accounts they are listed as a team member. The problem is, that the L6 Sales Rep list is imported from Salesforce as Last Name, First Name, and the Account Team Member line item is imported as First Name, Last Name.
Our first step here is simply wanting to identify how many accounts a Sales Rep is a Team Member on, however we are unable to use the SUM function because the "Team Member" data is imported into a text-formatted line item, and the text field we want to match is also text-formatted. And we cannot Lookup on the L6 Sales Rep list because it is Last Name, First Name and therefore doesn't match with First Name, Last Name.
I know this quite a lot to intake - but yeah we're here because we are struggling to find solutions
Because the line items in question have data which comes from imports, they are not based on syntax in the Properites: All Opportunities module, so screenshots of that module won't be of much help.
1 -
Okay got it. @KevinMcQuillen
All of that context helped make everything clear as to the goal that you are trying to achieve. I must say though this may seem very complex, it is not impossible!
I have enough inspiration to play with this in my playground. Give me a few hours I will get back to you with an update of a possible solution. I see a potential path forward here. Going to build out a mock version of this in my playground which will help me communicate the steps needed more clearly.
Question while I am exploring, is there a unique ID for Account Team Member?Here are a few thoughts going through my head- I need to poke holes myself through these steps.
1. Step 1 will be importing the Account Team Members into a Flat List, using ISFIRSTOCCURRNCE.
2. Step 2 will be in a System Module with Account Team Members, converting list format to text. Then converting names from First Name, Last Name to Last Name, First Name using string math.
(Caution- For testing I will go ahead and do this. However, this kind of string math is not recommended in Anaplan, it is advised to be done in a Data Hub at minimum and ideally via ETL so the Data arrives cleanly into your planning model).
3. Then once converted, doing a FINDITEM on L6 Sales Reps using the new First Name, Last Name column. Essentially mapping Account Team Members to L6 Sales Reps.
4. Having a mapping table unlocks a lot of power for you to use downstream in your Reporting, because then you can do all sorts of SUMS/LOOKUPs across dimensions.
I believe all of this hinges on a unique ID being available. Tagging @JaredDolich @Misbah here- if you can please take a stab at this one and confirm if the path I am going down above is in the right direction.
Again @KevinMcQuillen I need to play with this I will update you when I find some time to build this in my playground.
While even if the above steps do work, I would long-term reccommend revisitng your data imports/loads at some point and seeing if it is possible to get cleaner data to load into Anaplan as that will help facilitate and ease the nature of the processing power required to do these kind of text string calculations and mapping excercises.
0 -
Also are you able to provide a screenshot as to how the "Account Team Member" line item appears, perhaps the blueprint view of that module where the data is being imported?
0 -
Thank you for all your help!!
Note*: "Opportunity" = Account (I used "account" in the descriptions here b/c it is more digestable)
To your question, unfortunately, when the Opportunity Team Members are brought in, the are brought directly through an import into the line item - so they do not have an unique identifier specific to the Opportunity Team Member line item - but those same individuals do have a Unique Identifier in the L6 Sales Rep list, though again that list is formatted Last Name, First Name, and thus the unique ID would be as well
Like I said, it is originally imported from Salesforce - so there is no syntax per below...
However here is how it is referenced in another module if that helps..?
Thanks again!!
0 -
Sure that is helpful to know @KevinMcQuillen. Got it Account Team Members=Opportunity Team Members.
I know that the Opportunity Team Members field is imported, the reason I was curious to see the Blueprint, was not for the formula (since there is none for imported fields) but rather the dimensionality of the module Opportunity Team Members is loaded into. Can you please take another screenshot of the entire blueprint view where the Opportunity Team Members line item is located?
That would be much appreciated.
0 -
The entire module is too large to fit in one screenshot lol (over 150 line items) but here is the pivot so you can see the dimensionality - just a ton of line items based off an "all opportunities" list that gets refreshed every night via salesforce imports
0 -
Got it. Last question regarding screenshots. Are you able to send me a screenshot of your General Lists? @KevinMcQuillen
I want to see how your L6 Sales Reps Rollup via hierarchy and the positioning of All Opportunities? Is All Opportunities the top of your hierarchy?
0 -
Separate Hierarchies...
Sales Hierarchy
Opportunity Hierarchy
And sorry I also just noticed there is an opportunity team member list, except it is a numbered list and is not being referenced by anything
0 -
Okay @KevinMcQuillen, I think I have all the information I need. This will require some creativity. I need to go to the drawing board and test out some ideas, do see some paths forward.
I will get back to you later today with an update on my progress. This will take a little bit of time to construct.
0 -
Thank you!!
1 -
Hello @KevinMcQuillen,
I do have an update for you. I played around with this for a while in my playground enviornment and reached a few conclusions. I can see why this has been a struggle as it became messy for me very quickly.
What is not possible: there is no route that I found that can add the Revenue for an L6 Sales Rep + Supporting Team Player. In other words, I cannot add Sally Johnson's Revenue ($300) as an "Owner" in Account A + Sally Johnson's Revenue as a "Supporting" Team Member on Account B ($500) and get $800. This is because Anaplan has no way of linking the Sally Johnson (the L6 Rep) to Sally Johnson (the Supporting Team Member) unless Sally Johnson has a Unique ID that comes with the Imported Name that matches the Unique ID in the L6 List.
I can convert both Sally Johnson's to Text, and say Sally Johnson (text) = Sally Johnson (text), but I cannot do a FINDITEM and search for Sally Johnson just on Text against a numbered list. The reason for this is the System would not know how to handle duplicates and unfortunately this is a limitation of a Numbered List. You can however, do this against a non Numbered List. The issue you run into there is there could be 2 employees with the same name and therefore makes the non-Numbered List not possible either.
My strongest reccommendation: see if you can work with the Salesforce team providing the data, or through some other Data Warehouse, ETL tool, etc. see if you can do some transformation to the Imported Supporting Team Member Name and retrieve the Unique ID which matches the L6 Sales Rep List. This is likely the only solution you have if you want to add L6 Sales Revenue (owner) + Supporting Team Member Revenue (Supporting Team).
What is possible: It is possible to showcase Suppporting Revenue separate from Owner Revenue. You can even criss-cross the dimensionality to show Owner Revenue and Supporting Revenue together in the same page. What you again cannot do is add Supporting Revenue + Owner Revenue.
-It is possible to do string math and convert a name that looks like (First Name, Last Name) and convert it using string math to (Last Name, First Name).
1) To do this you need the following Line Items:
Length of Name= LENGTH(Imported Supporting Opportunity Team Member)
Find Space = FIND(" ", Imported Supporting Opportunity Team Member)
First Name = LEFT(Imported Supporting Opportunity Team Member, Find Space-1) *note -1 only neccessary if there is a ","
Last Name = RIGHT(Imported Supporting Opportunity Team Member, Length of Name-Find Space)
Calculated Name= Last Name & " " & First Name
2) In your Opportunities Data Module, you can drive the creation of a separate Supporting Team Members List. Then add a Supporting Team Members List Formatted Line Item in your Opportunities Data Module. The code would have to be L6 Owner Code (that would connect Supporting Team Members to L6 Sales Rep).
3) Then in a CAL01 Module (Supporting Team Members x Line Items- Supporting Revenue)
Supporting Revenue = Opportunities Data Module.Revenue[SUM: Opportunities Data Module.Supporting Team Members]
Supporting Team Member= ITEM(Supporting Team Members List)
Code= CODE(Supporting Team Member)
Owner= FIND ITEM(L6 Sales Rep, Code) *Note this will return the Owner of the Opportunity not link it to the same person in the L6 List.
4) And another CAL02 Module (L6 Sales Rep x Line Items- Owner Revenue)
Owner Revenue= Opportunities Data Module.Revenue[SUM: Opportunities Data Module.Supporting Team Members]
L6 Sales Rep= ITEM(L6 Sales Rep List)
Code= CODE(L6 Sales Rep)
Supporting Team Member= FIND ITEM(Supporting Team Members List, Code) *Note this will return the Supporting Team Members for the same Opportunity not Link the same 2 people together.
5. Output Module- L6 Sales Rep x Supporting Team Members x Line Items
Owner Revenue = CAL02: Owner Revenue[SUM:
IF This is my Source Data:
You can See John Smith and Annie intersection = 500, but again what you cannot do is say John Smith Owner (500) + John Smith Supporting (300) = 800.
Just to reiterate I do not think what I showed you can DO is going to be of much help ultimately, I believe this is a data source issue and needs to be handled upstream. Anaplan cannot retrieve Names against a Numbered LIST without a Unique ID.
2 -
Nice write up @DaanishSoomar - brilliant!
1 -
Wow, thank you very much for this! I agree, the ideal solution would be adjusting the data imports, but what you've shown is very helpful!
1 -
@KevinMcQuillen - Awesome! Glad to help. Hope this gives you some inspiration and a better sense of the path forward on which you want to embark on.
Good luck with your Anaplanning!
0 -
Nice piece of work @DaanishSoomar
On your output why can't we use SUM on both of these line items to aggregate the data for Owners and team members
If this is the data then my formula should be Revenue[SUM: Resource Line item 1] +Revenue[SUM: Resource Line item 2]
Assuming: Both of these line items have same Resource list in formatting.
1 -
@Misbah - haha, if only the world was perfect, that would have made this so easy. But even though it appears that the names in both columns are the same, they are actually 2 different lists.
As mentioned in my earlier posts, the Supporting team members does not have a unique ID therefore we had to create a separate list. The only common link between the Supporting Team Member and Owner is the Account they are both tied to. So I can link an Owner to a Supporting Team Member. But what I cannot do is link the Owner to themself in the other list without a unique employee ID (which is why @KevinMcQuillen was finding this difficult as well).
Hence my recommendation of seeing if Salesforce team, ETL team or Warehouse team etc. can help match Supporting Team Members with a unique ID that matches the unique Employee ID in the L6 Sales Rep list.
If anyone else sees a different path forward without a unique ID, more than happy to explore/entertain but I ultimately think that is a blocker for the requirement of adding revenues together. The closest I was able to come was crisscrossing dimensionality but that may be too sparse.0 -
A little bit lost as to why do we need another list for Team Members. Let's say if we have Team members without Unique ID getting into Anaplan as Text Format as First Name, Last Name - we convert it to Last Name, First Name and the use FINDITEM on Existing Resource List. Wouldn't that help, I know I am missing something but just want to make sure if this can be solved , feel you are very close.
1 -
@Misbah - I would 100% agree if the data supported what you are highlighting. But we cannot do a FINDITEM on text against a numbered list. If it was a non-numbered list I think what you are suggesting works. What if you want to search for “John Smith” but in the Numbered list you have multiple “John Smith’s”. I tried the FINDITEM but this did not work for me as Anaplan cannot detect the names against a numbered list.
In a perfect world you would not need a separate list. You would have unique ID for the Supporting Team Member that matches the L6 employee ID.
But without the unique ID, the Supporting Team Members would need to be a separate list.
0 -
@Misbah - this is what happens when I try to use FINDITEM against the list
@JaredDolich @KevinMcQuillen @Misbah - I am attaching the way I have my plaground setup, I would be curious to see what you all can come up with as alternatives if in case you want to try this in your own model.
Again I currently do not see a path forward that would allow me to add L6 Sally Johnson (L6 Sales Rep) ( Revenue 300) + Supporting Team Member (Text) (Revenue 100) = Total Revenue L6 Sales Rep Sally Johnson = 400 without a unique ID for the Supporting Team Member.
0
