How to create a Single Record Report with join clause

Questions related to nuBuilder Forte Reports and the Report Builder.

How to create a Single Record Report with join clause

Postby jhe » Tue Jul 24, 2018 3:59 pm

Hello,

I need to create a single record report that will print the details of a client. My problem is, I can't find how to display the joined table of my query.

for example this sql...

SELECT * FROM Clients
JOIN dependents ON dependents.client_id = Clients.client_id
WHERE client_id = '#client_id#'


Is there any way to do that?
jhe
 
Posts: 4
Joined: Thu Jul 05, 2018 7:56 pm

Re: How to create a Single Record Report with join clause

Postby admin » Thu Jul 26, 2018 12:36 pm

jhe,

You can use a record created with the SQL Builder instead of a table.

https://wiki.nubuilder.net/nubuilderforte/index.php/Reports#Table


Steven
admin
Site Admin
 
Posts: 3108
Joined: Mon Jun 15, 2009 9:53 am

Re: How to create a Single Record Report with join clause

Postby jhe » Thu Jul 26, 2018 12:54 pm

Thanks for Reply Admin,

I am using SQL builder to generate report. The problem is, my tables have a 1 to many relationship.

For example:

Client Table

ID-NAME
1-CLIENT A
2-CLIENT B

--------------------

Dependent Table
ID-CLIENT ID-NAME
1-1-CLIENT A DEPENDENT 1
2-1-CLIENT A DEPENDENT 2
3-1-CLIENT A DEPENDENT 3
4-2-CLIENT B DEPENDENT 1
5-2-CLIENT B DEPENDENT 2

------------------


how can i display that in a single record report when i choose to print client a record with its dependents?
jhe
 
Posts: 4
Joined: Thu Jul 05, 2018 7:56 pm

Re: How to create a Single Record Report with join clause

Postby admin » Thu Jul 26, 2018 1:20 pm

jhe,

I'm not sure what you mean by "Single Record Report".

Your query...

SELECT * FROM Clients
JOIN dependents ON dependents.client_id = Clients.client_id
WHERE client_id = '#client_id#'


... may return more than one record.

Steven
admin
Site Admin
 
Posts: 3108
Joined: Mon Jun 15, 2009 9:53 am

Re: How to create a Single Record Report with join clause

Postby jhe » Thu Jul 26, 2018 2:53 pm

Hello Admin,

hmmm. You are right Admin.

How about i want something like this report?

-----------------------------------
CLIENT NAME: CLIENT A

DEPENDENTS:
1 DEPENDENTS 1
2 DEPENDENTS 2
3 DEPENDENTS 3

------------------------------------

reports like personal information. Is that possible?
jhe
 
Posts: 4
Joined: Thu Jul 05, 2018 7:56 pm

Re: How to create a Single Record Report with join clause

Postby admin » Fri Jul 27, 2018 10:14 am

jhe,

Yes, it is.

When using the Report Designer, if you create a Group on CLIENT NAME to sort and a Header to place the CLIENT NAME in.

https://wiki.nubuilder.net/nubuilderforte/index.php/Report_Designer#Group_Properties


Steven
admin
Site Admin
 
Posts: 3108
Joined: Mon Jun 15, 2009 9:53 am

Re: How to create a Single Record Report with join clause

Postby jhe » Fri Jul 27, 2018 2:52 pm

Hello Admin,

what about the dependents list? where can i put it?

and what if i have another one? character reference list?
jhe
 
Posts: 4
Joined: Thu Jul 05, 2018 7:56 pm

Re: How to create a Single Record Report with join clause

Postby admin » Thu Aug 16, 2018 7:28 am

jhe,

Sorry but I don't understand your question.

Steven
admin
Site Admin
 
Posts: 3108
Joined: Mon Jun 15, 2009 9:53 am

Re: How to create a Single Record Report with join clause

Postby nc07 » Tue Jun 04, 2019 11:52 am

Hi, I am new to this forum and have started using nubuilder forte for a few weeks now which awesome.

I have a form and I have created a report using SQL. I would like to generate a report(certificate) based in the current opened record. I have tried #RECORD_ID# filter but it seems not working; it outputs all record.
nc07
 
Posts: 21
Joined: Tue Jun 04, 2019 11:35 am

Re: How to create a Single Record Report with join clause

Postby Janusz » Wed Jun 05, 2019 12:39 am

Hi, please find enclosed short example how to join current record with the report.

https://drive.google.com/open?id=1p0sfH ... 4RMsG9vlIb
Janusz
 
Posts: 220
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Next

Return to Reports