Hello,
I've designed a solution for one of my clients which is in the financial industry (stock market specifically), and I'm having a problem with one of their requests.
My client uses MS CRM 4.0 for keeping track of what sectors (banking, construction etc.), companies (e.g. Citibank, AIG) or finanical reports (Daily Market Report, Financial Sector Report etc.) each contact is interested in, and we use that data to send them articles, reports, or stock tips by email regarding the sectors or companies they have selected.
So I created new entities to keep track of this, and it is something like this:
- An entity called "Sector", which is also the parent entity of "company"
- An entity called "Company" (these are actually stock codes, not the same as "account")
- An entity called "Research Services"
All of these entities have N-N relationships with "Contact".
The logic is this: A contact may be interested in the banking sector, not interested in the construction sector, but interested in a single company X that is in the cons. sector. He may also be interested in our financial research report and daily market report. So we link all of these companies, sectors or services with that specific contact.
And let's say we have a stock tip about company X. We do a mailing using the quick campign to all contacts who are interested in company X, plus all contacts who are interested in the construction sector in general, plus contacts who subscribed for our "Contruction Research Report Service".
Now the problem:
My client wants me to implement an advanced contact search functionality. This functionality will allow them to do searches that go something like this in writing:
"Find all contacts who are interested in the banking sector, or are interested in company Y or company Z, or have subscribed to our Financial Report Service."
- As you may guess, this can't be done in advanced search, because there is more than one related entity, so you can't group them with "OR".
- Using the marketing list entity to search one-by-one for these criteria and merging the contacts that way doesn't work, because it is static. Basically you have to create a new marketing list each time new contacts are added or old contacts sign up for new reports.
- Querying the database using FetchXML doesn't work because of the same reason as no.1.
I guess the only option I have left is creating an ASP.NET application, designing an interface for the search (which will be very hard, considering all the different combinations) and querying the database with dynamically created SQL statements. The problem is, the results have to be shown in the normal CRM Grid. So I can't just use a normal ASP.NET Gridview for it. My client wants to be able to use all the functionality of the CRM Grid, just like you would get when you do an advanced search.
What are your ideas and solutions? Would really appreciate the help.
Thank you in advance.
Kutay Taskin
