We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

ReportSmith 3.0 Linking

Ianah
Ianah asked
on
Medium Priority
328 Views
Last Modified: 2010-04-03
Please help...
 
I am using ReportSmith 3.0 and I am having problems
with a master/detail relationship.
 
I am using 3 Paradox tables:
 
BM - Broker Master file (Main table for report).
PT - Policy transaction (Detail file) - Index Broker Code.
PM - Policy Master file (Lookup File) - Index Policy Number.
 
I have linked the first two tables.
 
BM->Broker Code -------> PT->Broker Code
 
This works great, however I need to relate each PT record
to the last file PM to pick up names and addresses.
(The PM file is a large database +- 100 000 records!)
 
The link is a follows:
 
PT->Policy Number --------> PM->Policy Number
 
When I do this I have to wait for ages while it sorts
through what seems like the whole PM file (15-20mins)
this is unacceptable.  Do you know how I might
speed up the process or create a better link.
 
The PM file does have an index on the Policy Number field.
What I need is a one to one lookup on the Policy Number.
Some PT records may not have a Policy Number in which
case I want it to return a blank.
 
Thanking you in advance for your help.
 
Ian Hopewell
Hopewell and Associates
Business Information Systems.
 
e-mail: ianah@iaccess.za
Tel: 27-31-2075059
Fax: 27-31-2074868
Comment
Watch Question

Commented:
If you are good with SQL, you could press the EDIT SQL button and try to improve on Reportsmith's SQL, perhaps using a nested SELECT statement.

Alternatively, if the PT table is relatively small (lets say a few hundred to even a couple of thousand records), you may obtain an improvement by changing the report to use only two tables, the first being BM, and the second a table which you populate before running the report, which includes the fields from the PT table, plus the fields you need from PM table. Set a tcursor on the PM table, set its index to Policy number. Build the new table record by record by going through the PT table, and use findkey on the indexed cursor on the PM table to find the right record in the PM table. The report should then have very little work to do, since it will not involve the PM table at all.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.