crystal syntax for the "where" clause

[tomparko]

The tables we are using are as follows:

Contact Log
Clog_entity_link
People_Lookup

Contact Log links to Clog_entity_link which in turn links to People_Lookup.

We would like to know the crystal syntax for the "where" clause or perhaps somebody knows a better way of structuring the formula. We know the formula shown below doesn’t work but gives an idea of what we have been trying - DONT LAUGH!!! PLEASE HELP

if {CONTACT_LOG.LOG_TYPE} = "I" then {PEOPLE_LOOKUP.FULLNAME_SFM} else
{PEOPLE_LOOKUP.FULLNAME_SFM} where {CLOG_ENTITY_LINK.IS_FROM} where = "T"

 

[lupins46]

Please explain in words what you are trying to do.
The first line looks clear but I don't see what the second line is trying to do.

 

[Madawc]

That's not how you do things in Crystal. Crystal has a 'flow', a fixed cycle that you have to slot your own code into. It isn't a full programing language: that's the price you pay for a software tool that can be used to produced a nice-looking report very quickly.

Do it as follows:
a) Link the three tables - this produces a 'row', data drawn from each of the three. If you still want the row even when data from one table is missing, use a 'left outer' link.
b) You may have to include 'People_Lookup' twice, once linked via Clog_entity_link and once direct to Contact Log.
c) Use 'Record Selection' to exclude 'rows' that definitely don't have anything you want. Probably {CLOG_ENTITY_LINK.IS_FROM} = "T", but be sure CLOG_ENTITY_LINK has a 'left-outer' link.
d) Use a formula field to choose the correct source for the data you want to display. Maybe something like

if {CONTACT_LOG.LOG_TYPE} = "I" then {PEOPLE_LOOKUP.FULLNAME_SFM} else
{PEOPLE_LOOKUP_1.FULLNAME_SFM}

Note that PEOPLE_LOOKUP_1 is the second or 'alias' version, assumed to be linked via CLOG_ENTITY_LINK.

Madawc Williams (East Anglia, UK)

 

[AyJayEL]

Tom this looks like EMS (An education Database) to me. Tell me what you want to show and I might be able to help you. Why do you want to do it? Is this for a letter or a report. Are you using the formulae in a text field or is it to group by? Like Lupin I don't understand what you are trying to with the second line.

Learn something new every day *:->*
AyJayEl

 

[tomparko]

Thank you all for the help. Its good to have help on hand!!

From the advice by using alias we have found a solution to our problem.

Keep an eye out - I will no doubt post other queries in the future.

Tom