how to join 2 different tables on one browse form

Questions related to using nuBuilder Forte.

how to join 2 different tables on one browse form

Unread postby oli » Sat May 15, 2021 5:07 pm

Dear All,

I have 2 different tables where I need to create one joint list of the data:

Tables:
contact (incl. the ID of the current Record) and the form where the list should be showed
plan (incl. plan_contact as the reletad contact)
order (incl. ord_contact as the related contact)


Now I need to collect all data of table plan where plan_contact = #RECORD_ID# (contact)
and of table order where ord_contact = #RECORD_ID# in one table or report.

How could this be achieved as effectively as possible?

Thanks in advance!

Greetings, Oli
oli
 
Posts: 70
Joined: Sat Mar 20, 2021 11:52 pm
nuBuilder Version: 4.5

Re: how to join 2 different tables on one browse form

Unread postby kev1n » Sat May 15, 2021 5:12 pm

kev1n
nuBuilder Team
 
Posts: 1763
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: how to join 2 different tables on one browse form

Unread postby oli » Sat May 15, 2021 5:41 pm

I'm not sure if this would help and honestly I don't know how to implement it.
What I need is something like:

SELECT *
FROM
order, plan
WHERE
order.ord_contact = #RECORD_ID#
AND
plan.plan_contact = #RECORD_ID#

By using such statement I get multiple lines per records
oli
 
Posts: 70
Joined: Sat Mar 20, 2021 11:52 pm
nuBuilder Version: 4.5

Re: how to join 2 different tables on one browse form

Unread postby kev1n » Sat May 15, 2021 6:05 pm

kev1n
nuBuilder Team
 
Posts: 1763
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: how to join 2 different tables on one browse form

Unread postby oli » Sun May 16, 2021 6:20 pm

I already tried different options to get the result showed in the form.

I found a SQL statement that works in phpMyAdmin:
Code: Select all
SELECT
auftrag.auftrag_id AS id,
auftrag.auf_label AS label,
auftrag.auf_starttag AS starttag,
auftrag.auf_fahrer AS fahrer
FROM auftrag WHERE auf_fahrer LIKE "%605350a35740b66%"
UNION ALL
SELECT
sf_tagesplan.sf_tagesplan_id AS id,
tagesplan.tplan_titel AS lable,
tagesplan.tplan_einsatztag AS starttag,
sf_tagesplan.sftp_fahrer AS fahrer
FROM sf_tagesplan
JOIN tagesplan ON tagesplan.tagesplan_id = sf_tagesplan.sftp_tagesplanid
WHERE tagesplan.tplan_typ = "Tagesplan" AND sf_tagesplan.sftp_fahrer = "605350a35740b66"


but now I don't know how to show all columns since the columns seems to be shifted (it's ok for table "auftrag" but for tabel "sf_tagesplan" I got the values for field "label" in column "fahrer".
This is how the current result looks like
results.jpg
results.jpg (110.38 KiB) Viewed 155 times


Here's the definition of the form:
Form_definition.jpg
Form_definition.jpg (204.58 KiB) Viewed 155 times


I also don't know how to filter by #RECORD_ID# ( in fields auf_fahrer (multi selected array) and sftp_fahrer)?

Any ideas?

Thanks in advance!
oli
 
Posts: 70
Joined: Sat Mar 20, 2021 11:52 pm
nuBuilder Version: 4.5

Re: how to join 2 different tables on one browse form

Unread postby oli » Mon May 17, 2021 7:18 pm

Since it was to complicated for me to join these tables, filter them by another one and show all data on a browse form I decided to implement the fucntionality by collecting all data in a separate temp table and show the results by creating a HTML table with JavaScript.
That works fine and gave me the flexibility I need.
oli
 
Posts: 70
Joined: Sat Mar 20, 2021 11:52 pm
nuBuilder Version: 4.5

Re: how to join 2 different tables on one browse form

Unread postby apmuthu » Thu May 20, 2021 2:17 am

If it is a readonly form, try using views.
apmuthu
 
Posts: 250
Joined: Sun Dec 06, 2020 3:20 pm
Location: Chennai, India, Singapore


Return to General

Who is online

Users browsing this forum: No registered users and 15 guests