RE: Fun with SQL and CRM Help needed by Naria
Naria
Mon Sep 24 11:28:03 PDT 2007
Is it possible to populate a picklist with items from a SQL database
(actually Select statement on CRM content)?
Can someone point me in the right direction, if its even possible?
Thank you!
"MD" wrote:
> Hey,
>
> 3 things may be at work:
>
> 1.) You created (or are creating) the rule while concurrently creating the
> new data fields. Workflow does not update (or refresh) the list of available
> (published) custom attributes on the fly. So if you're not seeing the fields
> that you created at all, this may be the cause.
> a) Save your rule
> b) Shut down the WF Manager
> c) Make sure you've published your changes to both the Account and Contact
> entities Re-open the WF Manager, your fields should now be available.
>
> 2.) Difference in data types. Similar to mapping attribute values, you can
> only dynamically update certain data fields based on the Parent's datatype.
> Don't quote me on this, but I believe the following fields can be updated
> based on the preceeding parent data types:
>
> Lookup --> Lookup
> Picklist --> Picklist
> Bit --> Bit, Text
> Text --> Text
> DateTime --> DateTime, Text
>
> So if you Account has a Text field, it cannot dynamically update a DateTime
> Field or Picklist field. Best thing to do is make both custom fields the same
> datatype.
>
> 3.) Update Direction. By this I mean that the Dynamic Value feature works on
> a oneway street, from the Parent (Account) to the Child (Contact) entity, not
> the reverse. You can't dynamically update the Account based on data from the
> Contact.
>
>
> If you can't solve it from the above 3 suggestions, let me know...
>
> "mindylynn" wrote:
>
> > Hi MD:
> >
> > your suggested rule was most helpful- thanks!
> >
> > However i am having difficulty getting this rule to register with custom
> > attributes i have created on both the contact and account entities. Any
> > suggestions? Is it not possible to map custom fields in the rule?
> >
> > Cheers-
> >
> > "MD" wrote:
> >
> > >
> > > An easier, supported, non-programatic way to accomplish this is using CRM
> > > Workflow.
> > >
> > > On the CRM Server, open the Workflow Manager.
> > > Create a new Manual Workflow rule for the CRM Contact enitity.
> > > Call it something like "ADMIN - Update Address Name".
> > > From the Common tasks area, choose Insert Action --> Update Entity.
> > > In the Update Entity screen:
> > > In the "Enitity:" picklist, leave the default enitity as "Contact".
> > > In the "field:" picklist, select "Address 1: Name".
> > > Choose the "=" Operator
> > > Click the Lookup button for the Field Value (on the right)
> > > In the Select Value screen:
> > > Click on the "Dynamic Value" option button.
> > > In the "Entity:" picklist, select "Account"
> > > In the "Field:" picklist, select "Account Name"
> > > Click OK to save and close the Select Value window.
> > > Click OK to save and close the Update Entity action.
> > > From the Common tasks area, choose Insert Action --> Stop.
> > > Keep the default option ("Complete") and click the OK button.
> > > Save and Activate the Workflow rule.
> > >
> > > Now that you have this rule, you can apply it to all of your contacts, in
> > > bulk. Best way to apply this is to an Advanced find view, that filters
> > > Contacts where the Address Name does not contain data. Set your number of
> > > records per view page to 250 (the maximum amount). and apply the Workflow
> > > rule to all the records on that page. As the Workflow rule applies the
> > > Account name to the Contact's Address Name field, these records will be
> > > removed from the view. Keep selecting all the records on the page until there
> > > are no records left.
> > >
> > > Then of course, delete the Workflow rule. This takes a bit of time, but in
> > > the end it's the fastest way to make this change.
> > >
> > > "KenCraft" wrote:
> > >
> > > > Ok. I've been tasked with setting the "Address Name" for every contact
> > > > equal to the name of the parent company.
> > > >
> > > > I know I can write an SQL query to do this, I just can't figure out
> > > > where the relationship is between contacts accounts and the
> > > > customeraddressbase table. So, heres what I need to do:
> > > >
> > > > Open Contact, Get Contacts Parent Customer Name, Put Parent Customer
> > > > Name into Address Name for the contacts main address.
> > > >
> > > > The database is relatively new, we just finished a very botched import
> > > > from ACT! not to long ago. this is the latest "Fix" that I've been
> > > > asked to work on.
> > > >
> > > > I can see that the Address is stored in "CustomerAddressBase" but I
> > > > don't know how it is linked to the ContactBase or AccountBase tables.
> > > >
> > > > Any ideas?
> > > >
> > > > Thanks,
> > > >
> > > > Ken
> > > >
> > > >