<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16441" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>Some Background:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>We are a publicly funded (government/university) R&D
organisation. Our engagements and collaborations with industry drives
our R&D directions and provides pathways for commercialisation. So my
company is not quite the classic target customer for vtiger or
Sugar.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>Our key needs:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>1) A single definite list of Contacts and Companies
(Accounts).</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>2) Mail outs (news and invitations) to filtered lists of
Contacts. (Filtering on combinations of multi-select combo boxes).
The ability to slice and dice our Contacts in different ways is
essential.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>3) Track industry engagements/meetings/agreements
(Opportunities).</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>4) Nice to be able to track which Companies/Contacts have
attended various events we hold, e.g. technology showcases, business briefings,
short courses. (A bit of a kludge, we are experimenting with using Sugar
Projects for this.)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>5) Nice to get the CRM to do document management. We
store important meeting Notes and Emails under Opportunities. (Document
management is not perfect; we would like to find an attached document by text
search.)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>(BTW this would be a really nice feature for the
future, that is an indexing system (similar to Copernic or Google Desktop)
that indexes Notes and attachments in the database in the background. With
this we could use the CRM to do our document management. That is, you
could find a meeting note under an Opportunity (for example), or find it by a
'free text' search.)</FONT></SPAN></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007></SPAN><SPAN
class=875462011-11062007><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>Anyway, regarding Sugar vs vtiger...</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>Some of the keys issues for us were around Contact
management. (We have about 1400 Accounts and 2700
Contacts)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>Some of Sugar's features that were important to
us.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>* Flexible export: export of a filtered selection (really
useful as workaround to any lack of functionality, e.g. filter, then export to
Excel, then do an email to the list.)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>* Duplicate Management</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2> - detection of duplicates.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2> - merge of duplicates (really nice, though it
has bugs still :-( )</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2> (We will have lots of contributors to the
database. Avoiding and managing duplicates is really
important.)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>* Mass Update and Mass Delete</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>(Nice to manage large numbers of Contacts - but needs to be
used carefully.)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>Some of vtiger's advantages: </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>* better, more flexible, advanced filtering for List
Views.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>* Tags (we really liked this)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>* HTML editing of Notes (not that important to us but
nice).</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=875462011-11062007><FONT face=Arial
color=#0000ff size=2>* Ajax (not that important to us)</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=875462011-11062007><FONT face=Arial color=#0000ff size=2>Our IT
department liked that Sugar was more established (with a larger support base?)
and therefore more likely to be around in the long term. (This is probably
a bit unfair, but that was their perception.)</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT><FONT face=Arial color=#0000ff
size=2></FONT><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=875462011-11062007><FONT face=Arial color=#0000ff size=2>That's
about it,</FONT></SPAN></DIV>
<DIV><SPAN class=875462011-11062007><FONT face=Arial color=#0000ff
size=2>Ciao,</FONT></SPAN></DIV>
<DIV><SPAN class=875462011-11062007><FONT face=Arial color=#0000ff
size=2>Neil</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
vtigercrm-developers-bounces@lists.vtigercrm.com
[mailto:vtigercrm-developers-bounces@lists.vtigercrm.com] <B>On Behalf Of
</B>Jorge Torres<BR><B>Sent:</B> Monday, 11 June 2007 11:57<BR><B>To:</B>
vtigercrm-developers@lists.vtigercrm.com<BR><B>Subject:</B> Re:
[Vtigercrm-developers] Storage of Multi-Select Items forQuicker MYSQL
Searching<BR></FONT><BR></DIV>
<DIV></DIV>Neil,<BR><BR>It is clear you like vtiger, I have one question, what
made your company choose Sugar instead of vtiger?, so sooner someone can make
vtiger offer such things.<BR><BR>Regards,<BR><BR>Jorge<BR><BR>
<DIV><SPAN class=gmail_quote>On 6/10/07, <B class=gmail_sendername>Neil
Temperley</B> <<A
href="mailto:neil.temperley@exemail.com.au">neil.temperley@exemail.com.au</A>>
wrote:</SPAN>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">Dear
Developers,<BR><BR>In the end my company went with Sugar, but I still keep an
eye on vtiger<BR>because I love you guys :-).<BR><BR>Anyway here is an idea
that I will post on a Sugar forum, but I'd thought<BR>I'd post it here first
so that if you choose to adopt it you can't be <BR>accused of copying
:-). While I haven't checked the vtiger code for a while<BR>I
suspect this issue is applicable to you
too.<BR><BR>-----------------------------------<BR><BR>Storage of Multi-Select
Items for Quicker MYSQL Searching <BR><BR>Consider a custom multi-select field
with items:<BR>cat<BR>dog<BR>horse<BR>bobcat<BR>cattle<BR><BR>An example of a
selection of the above is stored in the database as a string<BR>like this
(ignore the ""): <BR>"cat#,#horse#,#bobcat#,#cattle"<BR><BR>And if "cat" is
the only item the string saved looks like:<BR>"cat"<BR><BR>This storage scheme
has some problems when it comes to doing an SQL searches <BR>on strings
formatted this way. To search for uniquely for "cat" we have
to<BR>search for four different possibilities: (The MYSQL search string is
shown<BR>in ""):<BR><BR>1) cat at the beginning of the multi-select database
string, "cat#%", <BR>2) cat in the middle, "%#cat#%",<BR>3) cat at the end,
"%#cat", and finally,<BR>4) cat by itself, "cat".<BR><BR>If there are 1000
records and none have "cat" selected, the search needs to <BR>check all four
conditions on each record before rejecting each as
'not<BR>matching'.<BR><BR>However if we choose to store the selection
differently we only need to<BR>search for one match not four matches per
record. <BR><BR>Choosing '#' as the bounding character, we store the
multi-selected items as<BR>a string like this instead (ignore the
""):<BR>"#cat##horse##bobcat##cattle#"<BR><BR>And if "cat" is the only item
the string saved looks like: <BR>"#cat#"<BR><BR>The advantages of this system
are:<BR><BR>* Now the one MYSQL search "%#cat#%" will match "cat" regardless
of where it<BR>is in the string or whether it is the only item.<BR><BR>* A
side benefit is that is possible to delete (or replace) the "cat"
item<BR>using a simple PHP string operation, e.g.<BR>str_replace( "#cat#", "",
"#cat##horse##bobcat##cattle#" ); <BR><BR>* Also MYSQL searches are simpler --
if/when you ever need to do a search<BR>using a database admin
tool.<BR><BR>Note that to search for 'no selection' you need to check for ##
or an empty<BR>string.<BR><BR>I suggest the best way to implement the change
is with dedicated explode()<BR>and implode() functions and using a minimum of
hardwired strings. Examples<BR>are given below.<BR><BR>that's
all,<BR>regards and good luck, <BR>Neil<BR><BR><BR>Sample Code...<BR><BR>/*
##########################################################
*/<BR>define("MULTIPLE_SELECT_BOUNDING_CHAR",
'#');<BR>define("MULTIPLE_SELECT_BOUNDING_CHAR_RE", '\#'); // Char for
regexps. <BR><BR>/**<BR>* Formats a string of multi-select items for saving to
a database.<BR>*<BR>* @param array $multiple_select_array<BR>* @return string
formatted multi-select string<BR>*<BR>*/<BR>function MultipleSelectImplode(
$multiple_select_array ) { <BR> $bounding_char =
MULTIPLE_SELECT_BOUNDING_CHAR;<BR> return
$bounding_char .
implode("$bounding_char$bounding_char",<BR>$multiple_select_array) .
$bounding_char;<BR>}<BR><BR>/**<BR>* Splits a formatted string of multi-select
items into an <BR>array. Returns an empty array if there are no multi-select
items.<BR>*<BR>* @param string $multiple_select_string<BR>* @return array
multi-select array<BR>*<BR>*/<BR>function MultipleSelectExplode(
$multiple_select_string ) {
<BR> $bounding_char =
MULTIPLE_SELECT_BOUNDING_CHAR;<BR> $bounding_char_re =
MULTIPLE_SELECT_BOUNDING_CHAR_RE; // Char
for<BR>regexps<BR> // Remove $bounding_char at
each end of the string:<BR> $trimmed_string
= preg_replace( array("/^\s*$bounding_char_re/",
<BR>"/$bounding_char_re\s*$/"),
'',<BR> $multiple_select_string
);<BR> if ( empty($trimmed_string) )
{<BR> // (An explode() here
would return a one element array with empty
<BR>element):<BR> return
array();<BR> }<BR> return
explode("$bounding_char$bounding_char", $trimmed_string);<BR>}<BR>/*
##########################################################
*/<BR><BR>_______________________________________________ <BR>Reach hundreds
of potential candidates - <A
href="http://jobs.vtiger.com">http://jobs.vtiger.com</A><BR></BLOCKQUOTE></DIV><BR><BR>
<P><FONT size=2>No virus found in this incoming message.<BR>Checked by AVG Free
Edition.<BR>Version: 7.5.472 / Virus Database: 269.8.13/840 - Release Date:
08/06/07 15:15<BR></FONT></P>
<P><FONT face=Arial size=2></FONT></P></BODY></HTML>