Filter then select then insert a field

[Sabrena]

Hi:

I'm new to Crystal and I'm trying to select certain fields (in SQL2000) from a dbase (which contains all data from another (Retailease) application) and then I need the field to display on the report. Without going into too much detail about the design of the dbase and tables, the field needs to be "filtered" and then the value needs to appear on the report......

The table looks like this:

UDFieldID LeaseID UDFieldNameID FieldValue
3 279 2 Zellers
4 281 2 Sears
5 283 3 02/01/2003
6 284 5 Yes
8 290 6 Cadillac

What I want to happen is all fields with the FieldNameId = 6 for their value to appear on the report.

I hope I have been clear enough on my question.

Thanks,

Sabrena

 

[synapsevampire]

Try:

Select Report->Edit Section formula->Record:

{UDFieldNameID} = 6

-k

 

[Sabrena]

I tried it and got the message "a number, currency amount, boolean....etc. is expected here" at the beginning of the code.

What I did try is the following code but it is returning every record under that ID (I'm guessing that I would have to link this to another table that is in the report). (I'm trying to get the correct Property Manager to coincide with its Shopping Centre.)

Anyhow, code I'm using is as follows:

whilereadingrecords;
stringvar update;
IF isnull ({UserDefinedFields.UDFieldNameID}) then update := "" else ({UserDefinedFields.FieldValue})

I have already stated in the Select Expert the following (also, the above and below have been done in a sub-report):

{UserDefinedFields.UDFieldNameID} = 25

Sabrena

 

[synapsevampire]

OK, I get it.

It sounds like you're going to need a subreport.

Insert->Subreport

Join this subreport to the main report based on the field and then further filter your data. But this is a wild guess.

More detail is a good thing. Generally it's best to post the types and versions of the software involved (Crystal, SQL Server, and you're using dbase???), table layouts, sample data and expected output.

-k

 

[Sabrena]

Gee, how can you tell I'm new?!!!

Anyhow, I'm using Crystal 8.5, SQL2000 & a program called Retailease "RL" (my company is a retailer (with different divisions) and RL contains all the terms of our leases and pays our rents on our store locations to landlords, calculates percentage rent payments, etc., etc. and basically the back end of the program is a bunch of dbases which are cross linked like crazy).

What I have been asked to do is create a report from the RL program which will list all of our store locations and the pertinent lease terms, i.e. expiry date, store area, minimum rent......A lot of this information that is required for the report is contained in a table called UserDefinedFields (as noted above) containing things like orgininal opening dates of the stores, sales information, property managers of the centres, etc. and the way the table seems to have been built is that each section (which displays as a tab in the RL program) is identified with a UDFieldNameID and then the information which is displayed in the RL program is contained in the UDFieldValue field.

I believe that I do need to link the subreport to the main report and do the further filtering as you noted but that's where I'm getting lost......linking the Property Manager (which is in the UserDefinedFields table) to the Shopping Centre (in the Locations table under Name).

Layouts are as follows:

UserDefinedFields Table

UDFieldID LeaseID UDFieldNameID FieldValue

Locations Table

LocationID Name Address1 City Province PostalCode Country Region LocationTypeID LandlordID LessorID PropertyManagerID

Thanks for your help