Phrase Group

Discussions related to Crystal Reports development.

Moderator: Lisa Ennis

Post Reply
ckootg
Posts: 122
Joined: Fri Jan 06, 2012 6:10 pm

Phrase Group

Post by ckootg »

In Cameron, how do I get the Phrase Group for a Phrase?

In Boylan,

Code: Select all

FROM dbo.Phrase AS P
INNER JOIN dbo.zrefPhraseGroup AS zPG ON P.PhraseGroupID = zPG.ID
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: Phrase Group

Post by Lisa Ennis »

You can get the phrase type by joining from the pfm.Phrase table to the pf.OrderItemType table.

pfm.Phrase.Type#=pf.OrderItemType.ID
pf.OrderItemType.Name=’DeedEasement’
pf.OrderItemType.Name=’DeedException’
pf.OrderItemType.Name=’DeedRestriction’
pf.OrderItemType.Name=’Instruction'

If you need to use the type to include/exclude certain phrase types, use the Name of the type instead of the numeric code as the code numbers can change but the Names will not. For example

Code: Select all

SELECT *
FROM pfm.Phrase AS P
INNER JOIN pf.OrderItemType AS OIT ON P.Type#=OIT.ID
WHERE OIT.Name='Instruction'
Lisa Ennis
Senior Report Developer
SoftPro
ckootg
Posts: 122
Joined: Fri Jan 06, 2012 6:10 pm

Re: Phrase Group

Post by ckootg »

I'm actually looking for the grouping a phrase belongs to.

Image

These are the values from the zref.PhraseGroup table in Cameron

Code: Select all

ID	Code	Description
1	NULL	
2	BuyerTen	Buyer Tentative Statement
3	BuyerFin	Buyer Final Statement
4	BorrTen	Borrower Tentative Statement
5	BorrFin	Borrower Final Statement
6	SellTen	Seller Tentative Statement
7	SellFin	Seller Final Statement
8	BuySellTen	Buyer/Seller Tentative Statement
9	BuySellFin	Buyer/Seller Final Statement
10	BuyTenFC	Buyer Tentative Statement (fee conversion)
11	BuyFinFC	Buyer Final Statement (fee conversion)
12	EstStmt	Estimated Statement
13	BuyTen2nd	Buyer Tentative 2nd Statement
14	BorrTen2nd	Borrower Tentative 2nd Statement
15	BuyFin2nd	Buyer Final 2nd Statement
16	BorrFin2nd	Borrower Final 2nd Statement
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: Phrase Group

Post by Lisa Ennis »

The code below should get you what you need. Please let me know if you have any other questions.

Code: Select all

SELECT 
    P.Code, 
    P.[Text], 
    IG.NameID AS GroupNameID, 
    zPG.Code AS GroupCode,
    zPG.[Description] AS GroupDescription
FROM 
    pfm.Phrase AS P
    LEFT OUTER JOIN pfm.InstructionGroup AS IG ON p.RootId#=IG.RootId# AND P.Id# BETWEEN IG.Id# AND IG.LastId#
    LEFT OUTER JOIN zref.PhraseGroup AS zPG ON IG.NameID=zPG.ID
GO
Lisa Ennis
Senior Report Developer
SoftPro
ckootg
Posts: 122
Joined: Fri Jan 06, 2012 6:10 pm

Re: Phrase Group

Post by ckootg »

Thank you. That worked.
Post Reply