FIND formula not working

KevinMcQuillen
Contributor

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!

25 REPLIES 25
DaanishSoomar
Certified Master Anaplanner

Re: FIND formula not working

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.

KevinMcQuillen
Contributor

Re: FIND formula not working

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"  

 

KevinMcQuillen_0-1616018948392.png

 

DaanishSoomar
Certified Master Anaplanner

Re: FIND formula not working

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! 

Misbah
Moderator

Re: FIND formula not working

@KevinMcQuillen 

 

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

DaanishSoomar
Certified Master Anaplanner

Re: FIND formula not working

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? 

Misbah
Moderator

Re: FIND formula not working

@DaanishSoomar 

 

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

KevinMcQuillen
Contributor

Re: FIND formula not working

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.

DaanishSoomar
Certified Master Anaplanner

Re: FIND formula not working

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. 

 

DaanishSoomar
Certified Master Anaplanner

Re: FIND formula not working

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?