Your best route here is using a calculated field using the InStr function and likely one additional calculated field, either the user-edited or perhaps a runtime parameter would be more useful.
You can build a HasSearchWord formula-based calculated field with this expression:
The enhancement will look like this:
Making SearchTerm a runtime parameter adds convenience in that Monarch will ask you for a value every time that you open this model and then apply your specified value to the model.
You can achieve a similar result with the user-edit field type, but it's much more work/hassle.
The final steps would be to filter your data to show only the relevant names, and then use that filter in a summary definition.
Shout if you have further questions.
If you need to summarize the data for different type of customer at the same time, for example Pizzerias, Restaurants, Bars etc. then you could try this approach.
You will first need to create a list of the different types of outlet that you need to summarize by so, create a calculated field called 'Lookup' and type in the words that you need to match:
Next you will need to check all of the words in the Customer Name to see if any of them find a match in your list.
You can use the LSplit() function to split the name into several parts and compare each part to the lookup list.
LSplit(,5," ",1) /code
This will split the Customer Name field whenever it sees a space in the name, into 5 parts. This example returns the first part (or first word) of the name.
By taking the first, second, third part etc. of the name and comparing each one to the lookup list, you can see if any of them match your list and if so, return the word that matches.
Create a new calculated field called 'Type and use something like this for the formula:
You can then summarize your data by the 'Type' field.
If you have lots of different types of outlet to summarize, this might not be the best way but, it does work.
Hope this is of use to you.
Is there a way to make Monarch decide to match similar name. In my customer list I can have different customer that have similitude between their name and I'd like to summarize those customer base on the similitude in their name. I have a long list and I can't enter the search word because I do not know all of them.
As Olly suggests, there isn't a simple way to do this fully.
How much can be done would probably depend on how similar the customer names are or how you define 'similar'.
If they all contain a location like (Hatford) (Claremont) etc. it's possible to extract that part of the name and find all of the duplicates.
If the names begin with the same words like 'Pizzeria Casa' it's possible to extract the first word, two words, three words etc. in a calculated field.
LSplit(,5," ",1)+LSplit(,5," ",2) /code
Would give the first two words of the name so, anything beginning with 'Pizzeria Casa' or 'House of' or 'Casa Del' etc.
If you then create a filter; on the Advanced Tab, under Duplicate Handling, set this to 'all duplicated rows' and select your new calculated field as 'specified key', you should just see any customers that appear to be duplicates based on the first two words of the name (if this is how you are classing them as similar).
It doesn't have to be the first two words, it could be the second and third word or first and third word but, you have to decide which parts of the name make one customer name similar to another customer name.
Rather than using a filter to identify duplicates, you could use the calculated field (containing parts of the name) as the input field for a Lookup calculated field. By importing all of the entries you would have a list containing the first two words of each customer name. You can then put an entry in the output field for any records that you identify as duplicates or near duplicates.
You can then filter on the new lookup field to show all records with duplicates based on the new lookup field.
There's a fair bit of manual editing and auditing to do but it may get you a little closer to what you want to do.
Hope this helps.
If it were me, and if this task was relatively important, I'd probably forego all of the formula approaches and create a lookup table (an Excel file) with only two, maybe three columns: Customer Number (if available), Customer Name, and Customer Category. Then decide what you want the categories to be, five or ten of them, whatever is necessary but not too long, and stick to exactly those category names when populating the list for each customer.
Then in the Monarch model I would add an external lookup to retrieve the proper customer category.
It's an investment in time, and requires some manual upkeep (for the customer category list), but it will be far more accurate than any formulaic approach would be.