DLookup Alternative?

[kcmark]

I built a report in Access that used a variety of dlookup commands to extract specific data, and it worked very effectively. I am creating an updated version of this report in Crystal 10 using a SQL Server database. Unfortunately, Crystal does not appear to have a dlookup function, and am struggling with figuring out how I am going to get the old 'dlookup' data pulled onto the report. Here is my current plan/status:

I have created the main report, and a subreport. The subreport contains the source data relating to the old dlookup commands. The subreport contains 1 key and 2 additional lookup fields that I need to transfer to the main report. When I preview the subreport all the data is visible and accurate. I created 3 shared variables on the main report (1 for the key and 2 for the fields I need to pass from the subreport to the main report) as follows:

Shared StringVar NM_Cust_Name;
Shared StringVar NM_Cust_No;
Shared StringVar NM_Cust_Contract; <---- this is the key

In the subreport I created the same variables and assigned values as follows:

Shared StringVar NM_Cust_Name := {RM00101.CUSTNAME}
Shared StringVar NM_Cust_No := {SVC0601V.CUSTNMBR}
Shared stringvar NM_Cust_Contract := {SVC0601V.CONTNBR}

What I am trying to do is to have the main report look up the key on the subreport and return the matching lookup values. However, I cannot get any data to transfer from the subreport to the main report.

I know this is so simple, but I have beat my head against the wall long enough - time to swallow my pride and get the expert's advice...

Thanks in advance for your help...

 

[lupins46]

Make sure the subreport is positioned at the beginning of your main report (before the mainreport needs to use any of the subreport values)

 

[dgillz]

From what you are describing, you do not need a subreport or for that matter a formula. Just link the 2 tables in the report by Cust_no, and drag the desired data fields onto the report.

If I am missing something, please elaborate.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[kcmark]

The subreport is placed in the report header, so think I am ok there.

In this case, the tables cannot be linked (I inherited a very poorly designed table structure).

Thanks gain for the feedback...

 

[lbass]

Why can't they be linked? Are they from two different datasources? You refer to the main report looking up the key on the subreport and then returning matching values, implying that at least you have a field on the main report and a field on the subreport that match (contract number). If so, then the simplest method would be to insert a gorup on contract number in the main report, move the subreport to a group section, and link the subreport to the main report on the matching field.

-LB

 

[kcmark]

Thanks for your reply.

The key on the main report is a calculated field and not part of the table structure.

 

[lbass]

You can link a formula from the main report to a field in a subreport. Perhaps you should share the specifics (formula contents) of the key field in both main and subreports.

-LB

 

[kcmark]

On the subreport the formula for the key is as follows:

WhilePrintingRecords;
Shared StringVar NM_Cust_Contract := {SVC0601V.CONTNBR}

This populates the subreport with the appropriate contents

On the main report the formula for the key is as follows:

WhilePrintingRecords;
Shared StringVar NM_Cust_Contract := IIf({@Source_Type}="Support-NM",Mid({GL20000.DSCRIPTN},6,10),"")

This formula populates the main report with the appropriate values for the key. This gets a little confusing, but I will try and explain. The NM_Cust_Contract field only applies to certain line items in the report. This formula checks to see if the NM_Cust_Contract field needs to be populated. If it does then it extracts the appropriate information from the source field, otherwise it leaves the field blank.

Thanks again for your help on this!

 

[IdoMillet]

There's a good chance that the formula used in the main report could be created as part of a SQL Server View. You would then be able to link that View to the other table(s) and gain significant speed (compared to using subreports).

hth,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

Change your main report formula to (iif doesn't work consistently, I've found):

If {@Source_Type}="Support-NM" then
Mid({GL20000.DSCRIPTN},6,10) else ""

Then try using this formula as the linking field directly to {SVC0601V.CONTNBR} (with no variable). This assumes that they are both stringfields. If {SVC0601V.CONTNBR} is a number, then convert it by using:

totext({SVC0601V.CONTNBR},0,"")

and then use this formula to link to. If the main report formula is not available for linking, then please supply the contents of {@Source_Type}. You should, as a rule, always supply the contents of any formulas or nested formulas, when posting, as they can affect the suggested solutions.

Please note that the subreport must be moved to the group section when using this linking.

-LB