Page 1 of 1
how to join 2 different tables on one browse form
Posted: Sat May 15, 2021 9:37 am
by 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
Re: how to join 2 different tables on one browse form
Posted: Sat May 15, 2021 9:42 am
by kev1n
Re: how to join 2 different tables on one browse form
Posted: Sat May 15, 2021 10:11 am
by 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
Re: how to join 2 different tables on one browse form
Posted: Sat May 15, 2021 10:35 am
by kev1n
Re: how to join 2 different tables on one browse form
Posted: Sun May 16, 2021 10:50 am
by 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!
Re: how to join 2 different tables on one browse form
Posted: Mon May 17, 2021 11:48 am
by 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.
Re: how to join 2 different tables on one browse form
Posted: Wed May 19, 2021 6:47 pm
by apmuthu
If it is a readonly form, try using views.