List of CRM tables?

Hi All,

I'm just starting out altering some SQL queries in excel to make the reports
more relevant and find myself wanting to pull data from two tables. Does
anybody know where there is a list of tables for reference of failing that,
which table the property opportunity.partneridname sits in?

Thanks

D

Darren
Thu Feb 22 13:12:43 CST 2007

For opportunity, you can look into opportunitybase and
opportunitybaseextension table. But you can use the view
"opportunity".

Hope this helps.

Darren Liu
Crowe Chizek and Company
http://www.crowecrm.com


On Feb 22, 12:23 pm, "Dempsey J" <xfo...@hotmail.com> wrote:
> Hi All,
>
> I'm just starting out altering some SQL queries in excel to make the reports
> more relevant and find myself wanting to pull data from two tables. Does
> anybody know where there is a list of tables for reference of failing that,
> which table the property opportunity.partneridname sits in?
>
> Thanks
>
> D

Daniel
Thu Feb 22 20:40:08 CST 2007

Try this URL
http://<your_crm_server>/sdk/list.aspx

"Dempsey J" <xfon99@hotmail.com> wrote in message
news:KclDh.36117$Fm2.31775@newsfe1-gui.ntli.net...
> Hi All,
>
> I'm just starting out altering some SQL queries in excel to make the
> reports more relevant and find myself wanting to pull data from two
> tables. Does anybody know where there is a list of tables for reference of
> failing that, which table the property opportunity.partneridname sits in?
>
> Thanks
>
> D
>

MattNC
Thu Feb 22 21:38:08 CST 2007

You may find these links helpful:
Database diagrams for CRM 3.0
http://www.microsoft.com/downloads/details.aspx?familyid=DA8D5048-615E-43C0-AB31-8FB5DA70216B&displaylang=en

CRM SDK
http://www.microsoft.com/downloads/details.aspx?FamilyID=9c178b68-3a06-4898-bc83-bd14b74308c5&DisplayLang=en

The SDK has a report writers section that is helpful if you are going to try
your hand at SQL Reporting Services reports for CRM.
--
Matt Wittemann, CRM MVP
http://icu-mscrm.blogspot.com


"Dempsey J" wrote:

> Hi All,
>
> I'm just starting out altering some SQL queries in excel to make the reports
> more relevant and find myself wanting to pull data from two tables. Does
> anybody know where there is a list of tables for reference of failing that,
> which table the property opportunity.partneridname sits in?
>
> Thanks
>
> D
>
>

MD
Thu Feb 22 21:50:08 CST 2007

Hey,

The field you're looking for can be found in either the Opportunity view or
the FilteredOpportunity view. I recommend using the Filtered view as it's
supported by MSFT and you have more user friendly columns to query. For eg,
it has "StatusCodeName" column for your Status Reason string value instead of
the integer value-- which save you a Left Join on the StatusMap table in your
query.)

Unfortunately, you will need to create a Left Join on the FilteredStringMap
view to get the String values for all of your system and custom picklist
attributes.

You could, however, create a couln in Excel using the CHOOSE() formula to
assign these string values, but they'd be hard coded and you'd need to
manually update you formulas should picklist items be added or deleted. The
FilteredStringMap view will, of course, update dynamically.

I've created many custom Excel reports, OLAP cubes, graphs and pivottables
from the FilteredOpportunity view, so if you'd like further assistence, let
me know...

HTH,



"Dempsey J" wrote:

> Hi All,
>
> I'm just starting out altering some SQL queries in excel to make the reports
> more relevant and find myself wanting to pull data from two tables. Does
> anybody know where there is a list of tables for reference of failing that,
> which table the property opportunity.partneridname sits in?
>
> Thanks
>
> D
>
>