Skip to Content
author's profile photo Former Member
Former Member

One to Many table join -- concat field per record grouped by id

Post Author: wm5

CA Forum: Formula

Hello,

I am using Crystal Reports XI and have two tables that have a one to many relationship and are joined by an JobID (number).

Below is a sample with relative fields for each table.

job_table: JobID (number), Manager (text), Status (text)

jobaudit_table : JobAuditID (number), JobID (Number), FormID (Number)

There is a one to many relationship with jobaudit_table having multiple records for each JobID in job_table.

I have created a Group Header using the job_table.JobID and suppressed the detail section.

In the group header for each JobID I display the JobID, Manager, Status. I also use the below formula to determine if any records in the jobaudit_table has a record where FormID = 90. If so, I display "Yes". If not, "No".

So my report currently looks like.

JobID Manager Status Audit Performed

1 Manager 1 Closed N

2 Manager 2 Closed Y

Here are the formula's I use to determine if any records in jobaudit_table contains a record where FormID = 90.

@ja90exists

if {jobaudit_table.FormID} = 90 then 1else 0;

if sum({@ja90exists},{job_table.JobID}) = 0 then "No"else "Yes";

Everything so far works fine. What I would like to do now is add a hyperlink to a script to view the job audit when in the above report the "Audit Performed" column is "Yes"

So Report is now:

JobID Manager Status Audit Performed

1 Manager 1 Closed N

2 Manager 2 Closed Y (hyperlink to view audit)

I cannot figure out how to gather the valid JobAuditIDs where FormID = 90 grouped by JobID to be used in the Group Heading section of the report.

Also, it is unlikely, but possible that more than one job_audit record exists with FormID = 90 per JobID. So, my hyperlink could look like http://mysite.com/viewjobaudit.aspx?jobid=[jobaudit_table.JobAuditID],[jobaudit_table.JobAuditID]; .

Thanks for any help. And if this post is not clear let me know and I will clarify.

wm5

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 30, 2008 at 05:04 PM

    Post Author: bettername

    CA Forum: Formula

    Although I can't think of a way to get multiple hyperlinks, this should be a start. It (should) hyperlink to the last job/audit in the group that formID of 90. Oh, I assumed that the hyperlink should have been xxxx...jobID,jobAuditID! 😊

    I think there may be a way of getting hyperlinks to every "90" record, but that will involve a subreport, so lets try this first...

    1 - everything from your group header to the group footer...

    2 - add a formula into the group header that says:

    whileprintingrecords;

    stringvar jobauditID="";

    stringvar jobID:=";

    3 - Then add a formula to the details section:

    whileprintingrecords;

    stringvar jobauditID;

    stringvar jobID;

    if {jobaudit_table.FormID} = 90

    then (jobID:=totext({job_table.job_id},0,""); jobauditID:=totext({jobaudit_table.jobaudit_id},0,"")

    4 - Finally, on your "Audit Performed" formula, have a conditional hyperlink that says:

    whileprintingrecords;

    stringvar jobauditID;

    stringvar jobID;

    if {@audit performed} = "Y" then http://mysite.com/viewjobaudit.aspx?jobid=jobID","+jobauditID

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 21, 2008 at 12:33 AM

    Post Author: wm5

    CA Forum: Formula

    bettername,

    Belated thanks for the help with the above. I got pulled onto another project and am just now getting back to my Crystal Reports project.

    The above did work with some tweaking; however, I ran into some other problems and started over. I read an article on creating an XML document from a stored procedure and then referencing the XML doc to create the report. This was much easier since I'm more familiar with SQL.

    Thanks again,

    wm5

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.