PublicAccountsView and user-defined field

R

Reinfried

I want to select a user-defined field with the BCM-View: PublicAccountsView

the Code for this view is:

SELECT EntryGUID,
ModifiedOn,
FullName,
WebPage,
Active,
BusinessAddressStreet,
BusinessAddressCity,
BusinessAddressState,
BusinessAddressPostalCode,
BusinessAddressCountry,
OtherAddressStreet,
OtherAddressCity,
OtherAddressState,
OtherAddressPostalCode,
OtherAddressCountry,
Email1Address,
Email2Address,
Email3Address,
BusinessTelephoneNumber,
BusinessFaxNumber,
OtherTelephoneNumber,
OtherFaxNumber,
PrimaryContactGUID,
Reserved0
FROM AccountsUnionView auv
WHERE
auv.IsDeletedLocally = 0 AND
auv.ModifiedOn =
(select MAX(ModifiedOn) FROM
(
SELECT MAX(ModifiedOn) ModifiedOn
FROM AccountsUpdatesTable
WHERE EntryGUID = auv.EntryGUID AND IsDeletedLocally = 0
UNION
SELECT MAX(ModifiedOn) ModifiedOn
FROM ContactMainTable
WHERE EntryGUID = auv.EntryGUID AND IsDeletedLocally = 0
) subQuery)

How can i modify this code to get a additional user-defined-field e.g.
accountgroup

ManyThanks

Reinfried
 
L

Luther

I want to select a user-defined field with the BCM-View:  PublicAccountsView

the Code for this view is:

SELECT EntryGUID,
        ModifiedOn,
        FullName,
        WebPage,
        Active,
        BusinessAddressStreet,
        BusinessAddressCity,
        BusinessAddressState,
        BusinessAddressPostalCode,
        BusinessAddressCountry,
        OtherAddressStreet,
        OtherAddressCity,
        OtherAddressState,
        OtherAddressPostalCode,
        OtherAddressCountry,
        Email1Address,
        Email2Address,
        Email3Address,
        BusinessTelephoneNumber,
        BusinessFaxNumber,
        OtherTelephoneNumber,
        OtherFaxNumber,
        PrimaryContactGUID,
        Reserved0
        FROM AccountsUnionView auv
WHERE
auv.IsDeletedLocally = 0 AND
auv.ModifiedOn =
(select MAX(ModifiedOn) FROM
(
        SELECT MAX(ModifiedOn) ModifiedOn
        FROM AccountsUpdatesTable
        WHERE EntryGUID = auv.EntryGUID AND IsDeletedLocally = 0
UNION
        SELECT MAX(ModifiedOn) ModifiedOn
        FROM ContactMainTable
        WHERE EntryGUID = auv.EntryGUID AND IsDeletedLocally = 0
) subQuery)

How can i modify this code to get a additional user-defined-field e.g.
accountgroup

ManyThanks

Reinfried
Click to expand...

PublicXxxViews are great if the fields you need to change are part of
the view. If you need user fields, they're not so useful.

The user fields are in a table like ContactUserFields, and it'll join
to ContactMainTable using the ContactServiceID columns in both? Note:
ContactMainTable and ContactUserFields, despite the "Contact" are also
used by Accounts and Opportunities.
 
Top