Best Practice for large number of checkbox/bit attributes

I am working on a deployment of CRM 3.0 for my company and one of the
requirements is to track a large number of checkboxes (bit fields) for
contacts.  These are used to track skill sets, certifications, and
other similar data.  There are about 125 of these items and they could
add to this list over time.

Looking at this requirement from my ASP.NET background, I would easily
solve this with a lookup table of "skills" with all the skills listed
and allow the user to add skills and populate a "user_skills" table
based on what they added.  However, I just can't get my mind around how
this would be done in CRM.

I've tried creating a new custom entity named "Skills" with a "Skill
Name" primary attribute and added a relationship between it and
Contacts, but that doesn't allow me to choose from the list of skills
(only add to it).  I started to test the workaround to the many-to-many
limitation, but that doesn't seem to be the right direction either.
Plus none of the methods will allow me to use a simple checkbox
interface for fast data entry.

I'm not looking for a complete solution to this question, but rather
just some advise on how to think about this the CRM way.  One easy
solution (that keeps popping in my head each time I work on this) is
just to create 125 attributes in the Contacts entity and be done with
it, but that just seems like really _bad_ practice.

Thank you for your time,
Kale

0
kale
11/1/2006 11:36:46 PM
crm 35858 articles. 1 followers. Follow

3 Replies
576 Views

Similar Articles

[PageSpeed] 18

You need 2 entities (just like you would do if coding form scratch).  Then, 
if you want to avoid the CRM UI, you would need to develop a custom ASPX 
page to present this info in a checkbox format and then handle the API calls 
as needed.s

-- 

Matt Parks
MVP - Microsoft CRM


"kale" <kale.davis@gmail.com> wrote in message 
news:1162424205.964256.176580@h54g2000cwb.googlegroups.com...
I am working on a deployment of CRM 3.0 for my company and one of the
requirements is to track a large number of checkboxes (bit fields) for
contacts.  These are used to track skill sets, certifications, and
other similar data.  There are about 125 of these items and they could
add to this list over time.

Looking at this requirement from my ASP.NET background, I would easily
solve this with a lookup table of "skills" with all the skills listed
and allow the user to add skills and populate a "user_skills" table
based on what they added.  However, I just can't get my mind around how
this would be done in CRM.

I've tried creating a new custom entity named "Skills" with a "Skill
Name" primary attribute and added a relationship between it and
Contacts, but that doesn't allow me to choose from the list of skills
(only add to it).  I started to test the workaround to the many-to-many
limitation, but that doesn't seem to be the right direction either.
Plus none of the methods will allow me to use a simple checkbox
interface for fast data entry.

I'm not looking for a complete solution to this question, but rather
just some advise on how to think about this the CRM way.  One easy
solution (that keeps popping in my head each time I work on this) is
just to create 125 attributes in the Contacts entity and be done with
it, but that just seems like really _bad_ practice.

Thank you for your time,
Kale


0
Matt
11/2/2006 5:03:26 AM
Kale,
I think you are on the right track, and Matt is right, you need two
entities, as you have a M:M relationship here (One Contact can possess
many skills, each skill can be possessed by many Contacts.)

1) Create one entity called Skills.  There, you can add all of the
generic skills that any user can possess.  (Basically, think of this as
an admin populating a reference table, except that any user you give
the 'create skill' ability can do it at anytime.)

2) Create an entity called ContactSkills.  This is the intersection
table for this many-many relationship.  So, set it up that way with the
relationships in CRM:  Many ContactSkills to One Contact, and Many
ContactSkills to One Skills record.

3) Then modify the ContactSkills form to place both lookup values on
the form.  This will then let you add new ContactSkills to a Contact
record by populating both lookups, the Contact, and the Skill.

However - while this is probably "best practice" from a relational
purist perspective, you may indeed find that your users hate it...
This method requires a TON of clicks to set up one contact's set of
skills.  Using this data is also unfortunately more complex: , for
example, to "find me all users who have this skill", using Advanced
find, return all contacts, and then the users have to know to scroll
all the way down the list of fields, into the related entities, select
UserSkills, select the right one....  Much more painful that finding
that field on the Contacts list of fields...

So, your "125 checkbox" idea, while painful to think about from a
relational DB design perspective, might make for a better UI
experience.  Especially by being able to add your own tabs and sections
(esp, if the skills can be grouped, this would help the user find the
skill more readily), this might be a better approach.    And, as you
can get more than two columns on a form now, the UI I believe would be
vastly superior to the "proper relational" model...  And again,
searching for contacts with this set of skills is much easier with the
checkbox approach.

I suspect that if you were coding this from scratch, you might combine
these concepts:  create the two extra tables and the relationship, and
create a new entry into these tables when the user selects a
checkbox...  And you could even do this in post-callout code in CRM,
but you would still have to create the 125 checkboxes anyway, so the
benefit may not be worth the cost.  It would however give you a history
of when the skill was added, which you can't get from checkboxes...
(This type of idea, I believe, is what Matt was referring to when he
said to develop a custom ASPX page with checkboxes, and then code the
addition/deletion of UserSkills records in code.)

BTW, I hate checkboxes for this reason:  does the absence of a check
mean that you don't know if they have this skill or not, i.e., you have
not asked them yet, or does it mean that you asked, and they don't have
this skill?... At least with the multiple entity approach you can store
other fields on that m:m intersection record.  For example, you will
automatically get createdon/by and modifiedon/by, so that you can tell
which user "checked this skill off" when.  The other solution, if that
history is important to you, is to purchase c360's Audit product...
That will tell you which user checked off which check box when (and
perhaps more importantly, who unchecked it when??)

The other thing to consider is, are these really checkboxes?  For
example, for a certification, I would use a Date field, because you
probably want to know when they passed the test, as most certifications
expire over time...  Also, are there levels of proficiency for a skill
that means a picklist makes more sense?  (I.e., if one skill is "plays
chess", is that person a beginner, intermediate, master, or
grandmaster?)

Regardless of the implementation method, its always best practice to
spend time analyzing the future use of that data collection point
before you assume they are all checkboxes.

HTH,
Dave

Matt Parks wrote:
> You need 2 entities (just like you would do if coding form scratch).  Then,
> if you want to avoid the CRM UI, you would need to develop a custom ASPX
> page to present this info in a checkbox format and then handle the API calls
> as needed.s
>
> --
>
> Matt Parks
> MVP - Microsoft CRM
>
>
> "kale" <kale.davis@gmail.com> wrote in message
> news:1162424205.964256.176580@h54g2000cwb.googlegroups.com...
> I am working on a deployment of CRM 3.0 for my company and one of the
> requirements is to track a large number of checkboxes (bit fields) for
> contacts.  These are used to track skill sets, certifications, and
> other similar data.  There are about 125 of these items and they could
> add to this list over time.
>
> Looking at this requirement from my ASP.NET background, I would easily
> solve this with a lookup table of "skills" with all the skills listed
> and allow the user to add skills and populate a "user_skills" table
> based on what they added.  However, I just can't get my mind around how
> this would be done in CRM.
>
> I've tried creating a new custom entity named "Skills" with a "Skill
> Name" primary attribute and added a relationship between it and
> Contacts, but that doesn't allow me to choose from the list of skills
> (only add to it).  I started to test the workaround to the many-to-many
> limitation, but that doesn't seem to be the right direction either.
> Plus none of the methods will allow me to use a simple checkbox
> interface for fast data entry.
>
> I'm not looking for a complete solution to this question, but rather
> just some advise on how to think about this the CRM way.  One easy
> solution (that keeps popping in my head each time I work on this) is
> just to create 125 attributes in the Contacts entity and be done with
> it, but that just seems like really _bad_ practice.
> 
> Thank you for your time,
> Kale

0
Dave
11/2/2006 4:45:35 PM
I just posted a question regarding a similar circumstance.  Could I use this 
to create a list of "Marketing Events" and the contacts who attended them?  I 
think my post is titled "Entities with Multiple Entries - or Campaigns?  HELP 
Please."  I sure would appreciate your wisdom on this matter!
-- 
Mags
Just Another CRM User, sometimes completely frustrated, in St. Louis MO


"Dave Carr" wrote:

> Kale,
> I think you are on the right track, and Matt is right, you need two
> entities, as you have a M:M relationship here (One Contact can possess
> many skills, each skill can be possessed by many Contacts.)
> 
> 1) Create one entity called Skills.  There, you can add all of the
> generic skills that any user can possess.  (Basically, think of this as
> an admin populating a reference table, except that any user you give
> the 'create skill' ability can do it at anytime.)
> 
> 2) Create an entity called ContactSkills.  This is the intersection
> table for this many-many relationship.  So, set it up that way with the
> relationships in CRM:  Many ContactSkills to One Contact, and Many
> ContactSkills to One Skills record.
> 
> 3) Then modify the ContactSkills form to place both lookup values on
> the form.  This will then let you add new ContactSkills to a Contact
> record by populating both lookups, the Contact, and the Skill.
> 
> However - while this is probably "best practice" from a relational
> purist perspective, you may indeed find that your users hate it...
> This method requires a TON of clicks to set up one contact's set of
> skills.  Using this data is also unfortunately more complex: , for
> example, to "find me all users who have this skill", using Advanced
> find, return all contacts, and then the users have to know to scroll
> all the way down the list of fields, into the related entities, select
> UserSkills, select the right one....  Much more painful that finding
> that field on the Contacts list of fields...
> 
> So, your "125 checkbox" idea, while painful to think about from a
> relational DB design perspective, might make for a better UI
> experience.  Especially by being able to add your own tabs and sections
> (esp, if the skills can be grouped, this would help the user find the
> skill more readily), this might be a better approach.    And, as you
> can get more than two columns on a form now, the UI I believe would be
> vastly superior to the "proper relational" model...  And again,
> searching for contacts with this set of skills is much easier with the
> checkbox approach.
> 
> I suspect that if you were coding this from scratch, you might combine
> these concepts:  create the two extra tables and the relationship, and
> create a new entry into these tables when the user selects a
> checkbox...  And you could even do this in post-callout code in CRM,
> but you would still have to create the 125 checkboxes anyway, so the
> benefit may not be worth the cost.  It would however give you a history
> of when the skill was added, which you can't get from checkboxes...
> (This type of idea, I believe, is what Matt was referring to when he
> said to develop a custom ASPX page with checkboxes, and then code the
> addition/deletion of UserSkills records in code.)
> 
> BTW, I hate checkboxes for this reason:  does the absence of a check
> mean that you don't know if they have this skill or not, i.e., you have
> not asked them yet, or does it mean that you asked, and they don't have
> this skill?... At least with the multiple entity approach you can store
> other fields on that m:m intersection record.  For example, you will
> automatically get createdon/by and modifiedon/by, so that you can tell
> which user "checked this skill off" when.  The other solution, if that
> history is important to you, is to purchase c360's Audit product...
> That will tell you which user checked off which check box when (and
> perhaps more importantly, who unchecked it when??)
> 
> The other thing to consider is, are these really checkboxes?  For
> example, for a certification, I would use a Date field, because you
> probably want to know when they passed the test, as most certifications
> expire over time...  Also, are there levels of proficiency for a skill
> that means a picklist makes more sense?  (I.e., if one skill is "plays
> chess", is that person a beginner, intermediate, master, or
> grandmaster?)
> 
> Regardless of the implementation method, its always best practice to
> spend time analyzing the future use of that data collection point
> before you assume they are all checkboxes.
> 
> HTH,
> Dave
> 
> Matt Parks wrote:
> > You need 2 entities (just like you would do if coding form scratch).  Then,
> > if you want to avoid the CRM UI, you would need to develop a custom ASPX
> > page to present this info in a checkbox format and then handle the API calls
> > as needed.s
> >
> > --
> >
> > Matt Parks
> > MVP - Microsoft CRM
> >
> >
> > "kale" <kale.davis@gmail.com> wrote in message
> > news:1162424205.964256.176580@h54g2000cwb.googlegroups.com...
> > I am working on a deployment of CRM 3.0 for my company and one of the
> > requirements is to track a large number of checkboxes (bit fields) for
> > contacts.  These are used to track skill sets, certifications, and
> > other similar data.  There are about 125 of these items and they could
> > add to this list over time.
> >
> > Looking at this requirement from my ASP.NET background, I would easily
> > solve this with a lookup table of "skills" with all the skills listed
> > and allow the user to add skills and populate a "user_skills" table
> > based on what they added.  However, I just can't get my mind around how
> > this would be done in CRM.
> >
> > I've tried creating a new custom entity named "Skills" with a "Skill
> > Name" primary attribute and added a relationship between it and
> > Contacts, but that doesn't allow me to choose from the list of skills
> > (only add to it).  I started to test the workaround to the many-to-many
> > limitation, but that doesn't seem to be the right direction either.
> > Plus none of the methods will allow me to use a simple checkbox
> > interface for fast data entry.
> >
> > I'm not looking for a complete solution to this question, but rather
> > just some advise on how to think about this the CRM way.  One easy
> > solution (that keeps popping in my head each time I work on this) is
> > just to create 125 attributes in the Contacts entity and be done with
> > it, but that just seems like really _bad_ practice.
> > 
> > Thank you for your time,
> > Kale
> 
> 
0
Utf
1/24/2007 2:52:06 AM
Reply:

Similar Artilces:

Auto number and save
Hi is it possible to have an incremental numer in a cell ie invoice number also to create macro to save this number as the file name. So every time i enter data in the form it gives it a unique number and save it with that number Mark Mark The following macro saves the workbook under the name of the contents of cell A1 in the same path as the original file. Note that this message may wrap the code over 2 lines. The macro consists of the Sub line, the End Sub line, and only one line in between. Sub SaveTest() ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & Rang...

40 column receipt does not show serial number when reprinting
When a transaction is compete on a 40 column receipt, it shows the serial number. When I reprint it it does not show the serial number on a 40 column reprint or a full page. Is there a way to fix it so it shows serial number on all reprinted receipts? ...

Hide Columns Using Numbered References
I'm trying to hide columns in a sheet using references to "numbered columns" instead of "lettered" columns, but I can't figure out the correct syntax. Someone please help. Thanks Sub TestCount() Dim MyCount MyCount = Range("A1").Value * 2 + 3 MyEndRange = 256 Columns("MyCount:MyEndRange").Hidden = True End Sub Columns(MyCount & ":" & MyEndRange).Hidden = True jrew23@yahoo.com wrote: > > I'm trying to hide columns in a sheet using references to "numbered > columns" instead of "l...

The best photographer
He is the best famous photographer.. http://club.cyworld.com/dcnomad ...

Best text books / manuals for MS Office (intermediate-Advanced use
What are the best (most highly reccomended) text books / instruction manuals you can get for Access, Excel, PowerPoint, Publisher, & Word and also VBE? I consider myself to be Quite proficient in Excel......Can produce a decent document in Publisher.......Can "Get by" in Word....Not enough knowledge of Access......Only used PowerPoint once....... I started using BASIC (the programming language) back in the '80s, so I have some knowledge of VBE (Formulas & syntax are quite simular from what I see now). I have created several Excel projects with Macros bu...

Howto read/write SQL-Server ntext-Attributes with CRecordset using ODBC
Hello, is there any possibility to read/write chunks of data from/to a ntext attribute (SQL-Server using ODBC, not ADO) to avoid problems caused of not enough memory? Thx, K. Konrad ...

Where is the best place to put notes?
We are new to MSCRM and are not used to all of the possible places that you can enter notes. Has anyone created a structure for their company as to what notes should be entered where? Wendy, There are many ways to handle notes. You have accurately identified that notes are kind of disjointed. What you might consider doing is creating a central notes depository in Sharepoint and then adding a customization with an inline frame that shows the Sharepoint notes repository in several different parts of CRM. In general, you have not provided enough information on what you are trying to ac...

auto number conversion error
I am trying to convert a database from 97 to 2000 and am getting some ambiguous auto number results. The last auto number from prior to the conversion is 120 and now when I enter a new record after conversion the numbers start at 1291848529 and continue from here. I have a backup to try and convert again, but the same thing keeps happening. Is there something I can do to fix this so the next # will be 121 either in the converted database or somehow through the coversion process? Please, any help? These new #'s are way too large. Thanks, Bridget ...

publisher 2003 files becoming large for no reason
I am accustomed to my publisher files being 5 mb, max. But suddenly, as I work on this newsletter, I am dealing with a 58 mb file. I reduced the resolution of my photos, but it still is 51 mb. Are there any other tips I can try? Ultimately, this file will be emailed as a PDF, but I want to keep that PDF under a MB, if possible. And in order to print it as a PDF, I have to drive to work and use that computer, which as Acrobat on it - I don't have it here! So I can't play with it to see what it does... so I am hoping to get some help on reducing the size. Besides, my comput...

what is the best way to resize test that "best fits" the text box
I just want to know how to resize text to fit the text box. Select the text and reduce the font size until it suits your requirements. There is no automatic function for this. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "utahjohn" <utahjohn@discussions.microsof...

I want to delete a large attachment from a meeting item.
I have a meeting item in my calendar that has a large attachment. I want to delete the attachment and keep the meeting item. I can't edit the meeting item. Office 2007. Thanks. What happens when you select the attachment and hit Delete? Save & Close to keep the change or cancel the save if you change your mind of deleting the attachment. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net ...

Best way to forecast individual sales territories?
I have a spreadsheet with twelve months of sales data per territory (100 territories.) I need to forecast the next three months. The product being sold is just moving from launch phase to high growth phase, so it's still growing pretty quickly. What is the best method to forecast each individual territories' sales over the next three months? ...

Hindi Number Format
Is ther an equivalent in Access for a for a number format available inExcel .NumberFormat = "[$-2000000]#0,000.00"so that I can have Hindi numerals in a reports.when I use Numeral shapes to context or system or National it is notgiving me the desired result.How I can apply this in a Report so that the field will be formatedusing the above way?Thanks...

Formula For Sorting Part Numbers
I have Excel 2000. Sheet1 of my workbook has: * Part numbers in column D * Primary bin location in column F * Secondary bin location(s), if any, in columns I, J, K, L * Each part number and corresponding bin location(s) are listed on the same row and there are no duplicate part numbers I need help with a formula (to be inserted in Sheet2) that does the following: * List all the part numbers and bin locations (primary + secondary) if there is an "X" in column AB of any part number listed in Sheet1 * All the information for a part number should be on a single row * T...

Total number?
hi, I want to display a total number for all the number in series, how to do that? please advice. thanks. Hi What is 'number in serias'? <someone@js.com> wrote in message news:%23jz07DtkKHA.2132@TK2MSFTNGP05.phx.gbl... > hi, I want to display a total number for all the number in series, how to > do > that? please advice. thanks. > > Sum() all number but only the group/category. How to do that? "Uri Dimant" <urid@iscar.co.il> wrote in message news:OZlsMQ2kKHA.3792@TK2MSFTNGP02.phx.gbl... > Hi > What is 'num...

OWA/OMA Protection
<No response from other forum> Hello, I have a Exchange 2003/OWA frontend that is protected with RSA two factor authentication inside a Checkpoint Firewall. The RSA part works fine and everything is good. Now I buy a Motorola Droid and need to set up the Exchange Sync and the RSA poses a problem. If I remove the RSA protection the Droid connects to the OWA and syncs well. Calls to RSA says it will not work with a Droid so I'd have to get rid of the RSA authentication. The question is: it looks like if I want to use a Android 2.0 based Droid phone to sync with outlook...

Add hyphens to a number
In one column, each cell has a 16 digit number. I need to put hyphens between 4 different numbers in each cell. Each cell/number will have the hyphen in the same place. I was successful doing this once I believe through Format\Cells\Number\Custom but I did not write down the steps how to do it. Any clues? Thanks texasphil -- texasphil ------------------------------------------------------------------------ texasphil's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32615 View this thread: http://www.excelforum.com/showthread.php?threadid=541305 You didn&#...

Word Count when saving large documents
On some large documents, but not all, there is a verrrry long delay when saving the document as it repeatedly recalculates the number of words in the document. Is there a way to turn off the Word Count function in such documents?? Thanks. -- Cyndie Browning Software Support Specialist GableGotwals Tulsa, OK Hi Cyndie, Getting the word count is almost instantaneous - and doesn't rely on the save process for an update. If you're working over a network, the *network* may be the cause of the poor performance. -- Cheers macropod [Microsoft MVP - Word] "...

Math functions on non number data?
is there a way to convert data that is in fraction format to number format to be able to perform math functions without changing the original data type? For instance, change 23 1/2 * 34 3/4 to 23.5*34.75 to come up with 816.625. The result does not need to be in fractional format. thanks for any assistance, Dave DDP I presume you have all of that multiplication in an single cell (otherwise you would simply be able to multiply whatever two cells contain them)? If so, try this formula... =LEFT(A1,FIND("*",A1)-1)*MID(A1,FIND("*",A1)+1,99) -- Rick (MVP - Excel) &qu...

Deleting Large Public Folders
I have multiple PFs for Job Openings e-mails. I mistakenly forgot to set limits for one of the folders. The folder has grown to 7Gbs. I have disabled mail to it but can't access the folder to delete the mail. Any ideas on how to get rid of the contents or delete the folder? Both the admin console and Outlook freeze On Thu, 16 Mar 2006 09:20:27 -0800, EDEN <EDEN@discussions.microsoft.com> wrote: >I have multiple PFs for Job Openings e-mails. I mistakenly forgot to set >limits for one of the folders. The folder has grown to 7Gbs. I have disabled >mail to it but c...

Make invisible with a checkbox
Hello, I have a simple form in which I have a checkbox that makes a text field visible when the checkbox is checked (The text field is not bound). My problem is that when I switch to the next record the checkbox gets updated along with the record but the text field does not change along with the checkbox; if the next record has the checkbox unchecked the text box will still stay visible. Here is what code I have: Private Sub Check19_AfterUpdate() Me![Text22].Visible = Me!Check19 Me![Attachment29].Visible = Me!Check19 End Sub Private Sub Form_Current() Me![Text22].Vis...

Numbering a spreadsheet
Hello Excel Experts! I have a spreadsheet that is numbered 1-105, but because of column headers the numbers in my spreadsheet do not coincide with the excel row numbers. Oftentimes, I have to delete rows, but I want the original numbering to stay intact. For example, today, if I delete the row I've numbered as 35, I have to go through and re-number rows 36-105. Is there anyway to avoid manually re-entering the information so that it automatically corrects itself when a row is deleted. Look here: http://j-walk.com/ss/excel/usertips/tip035.htm -- Greeting from the Gulf Coast! http://my...

Losing space character value when writing attribute
Hi, Wonder if anyone can help me on this, In DotNet2.0 I've been quite happily using the WriteAttributeString method of XmlWriter object, but have run into a problem when trying to output a space character as the value of an attribute. Prototype of the method I am using is XmlWriter.WriteAttributeString (String, String) in the following fashion: xmlTextWriter.WriteStartElement("Rule"); xmlTextWriter.WriteAttributeString("Separator", " "); xmlTextWriter.WriteEndElement(); Which results in the following XML: <Rule Separator="" />...

Checkboxes?
Is there a way to do checkboxes in a column within excel? Eric Hoffma -- Message posted from http://www.ExcelForum.com Is there a way to insert checkboxes in place of a cell and do this for whole column at the same time? Eric Hoffma -- Message posted from http://www.ExcelForum.com This adds checkboxes from the Forms toolbar to a worksheet: Option Explicit Sub addCBX() Dim myCBX As CheckBox Dim myCell As Range With ActiveSheet .CheckBoxes.Delete 'nice for testing For Each myCell In ActiveSheet.Range("B1:B600").Cells With myCell ...

Report Ignoring negative numbers
I have two columns. One is Company name and the other is positive and negative numbers. I need to group by company name. I also need to group by the pos/neg numbers (ignoring the neg sign) and excluding orphans. I have tried using the ABS, but it changes the neg to a positive. How can I get it to ignore, but still display the neg number. i.e. $134.00 -$134.00 $134.00 $122.14 $122.14 -$122.14 If I had only one $155.00 or -$164.00, etc. I do not want it to show up on my report. Display your number field as is but group on a copy of the f...