how to join 2 different tables on one browse form

[oli]

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

[kev1n]

Hi,

See e.g. here: https://forums.nubuilder.cloud/viewtopic. ... OIN#p23805

[oli]

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

[kev1n]

You need to use JOINS.
https://www.w3schools.com/sql/sql_join.asp

[oli]

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

Here's the definition of the form:

Form_definition.jpg

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]

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.

[apmuthu]

If it is a readonly form, try using views.