Pull city names based on condition

[Chuku]

Hi,
I need to pull city names from property table.
Property_id in property_table is mapped to prop_id in worklog_table. If prop_id is not null then I need to pull the city name
If prop_id which is present in a worklog_table is null then I need to pull the city name but based on a field value(called prop_nbr in another table called Loan table)

How to proceed. Could anyone help

 

[PeterDimitrov]

Are you looking for a solution on the server side or in Crystal ?
If the server side works for you, create 2 queries and union them. Query 1 to return all the records with NON null prop_id in worklog_table joined with property_table. Query 2 to return all records with null prop_id in worklog_table joined with Loan table by prop_nbr. You can save the union of these qiueries as a view and base your report on it or directly create a comand.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[Chuku]

hi Peter,
Thanks for your reply. I am looking to pull the values in crystal using sub reports. My idea is to implement each condition in a separate sub report. Is this possible.

 

[codrutza]

Just help me understand. What fields do you use to link the tables when the prop_id is null?
- prop_id is not null:
property_table.Property_id = worklog_table.prop_id

- prop_id is null:
?.?=Loan.?

 

[PeterDimitrov]

Yes, I guess you can add 2 subreports, or even present the first part of the data in the main report and add one subreport. However this will require 2 calls to the database and you will need to format the subreports in exactly the same way . Even a small difference in the formatting may be visible on the screen and will cause the data to be exported in different columns in Excel. I cannot see any reason to do this using subreports except if you do not feel comfortable to create SQL queries directly.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[BettyJ]

Adding to PeterDimitrov's answer above. If you want both results to be displayed in the same column, right click the second subreport if you are using two subreports (or the subreport if you are using only one subreport), Format Subreport, in Suppress formula give a condition to suppress it when prop_id is not null.

 

[Charliy]

What tables are the City Names in?

You've said that the property number is in the Load table, but not which table the city name can be found in if you're trying to get it based on the property number.