Page 1 of 3

How to create a Single Record Report with join clause

PostPosted: Tue Jul 24, 2018 3:59 pm
by jhe
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?

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

PostPosted: Thu Jul 26, 2018 12:36 pm
by admin
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

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

PostPosted: Thu Jul 26, 2018 12:54 pm
by jhe
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?

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

PostPosted: Thu Jul 26, 2018 1:20 pm
by admin
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

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

PostPosted: Thu Jul 26, 2018 2:53 pm
by jhe
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?

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

PostPosted: Fri Jul 27, 2018 10:14 am
by admin
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

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

PostPosted: Fri Jul 27, 2018 2:52 pm
by jhe
Hello Admin,

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

and what if i have another one? character reference list?

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

PostPosted: Thu Aug 16, 2018 7:28 am
by admin
jhe,

Sorry but I don't understand your question.

Steven

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

PostPosted: Tue Jun 04, 2019 11:52 am
by nc07
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.

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

PostPosted: Wed Jun 05, 2019 12:39 am
by Janusz
Hi, please find enclosed short example how to join current record with the report.

https://drive.google.com/open?id=1p0sfH ... 4RMsG9vlIb