[Solved] Arbitrarily link tables, based on criteria

Discuss the database features

[Solved] Arbitrarily link tables, based on criteria

Postby dreamquartz » Fri Mar 22, 2019 5:53 am

Hi All,

I am looking for guidance concerning to the following (see attachment).

Because projects are entered in a random order, I cannot seem to find a way to arbitrarily link the contents of one table to an other under certain circumstances.

I have a table that holds: PIID, Project#, and DateOfIssue.
A second table holds: PersonID, FKPIID, and FKCID.
A third table holds: CID, FA, FB, SA, and SB.

Project information is entered in table 1.
Persons are entered via a subform in table 2.
Under Project# 2019066001 and 2019066002, there needs to be a relation with a third table, based on the number of people entered in the project.

Explanation about Project#: Project code 66 in 2019 started with sequence 2019066001 and is followed by 2019066002.

For Project# 2019066001 and PID: 9020, it should be made possible to retrieve the information in the 1st record of the third table, being: FA: 6; FB: 3; SA: 4; SB: 1
For PID: 9078, it should be FA: 2; FB: 8; SA: 6; SB: 3
This is to continue to PID: 9080; FA: 2; FB: 11; SA: 12; SB: 9.

For Project# 2019066002, the relationship with the third table should start at CID: 1 all the way to CID: 12.

My quest is to link e.g.:
PIDD: 1641; Project#: 2019066001; DateOfIssue: 02-08-2019; PID: 9020; FKPIID: 1641; FA: 6; FB: 3; SA: 4; SB: 1.
It should be done based on a Query, and not hard coded, because that would limit the application of the principle.

Thanks in advance,

Dream
2Tables.pdf
(18.14 KiB) Downloaded 40 times
Last edited by Hagar Delest on Thu Apr 11, 2019 7:55 am, edited 1 time in total.
Reason: tagged solved
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 710
Joined: Mon May 30, 2011 4:02 am

Re: arbitrarily link tables, based on criteria

Postby eremmel » Fri Mar 22, 2019 5:14 pm

This is a rather technical question. You give some table definitions by listing field names but fail to reference them consistently in our story, so I do not understand.
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

Re: Arbitrarily link tables, based on criteria

Postby UnklDonald418 » Sat Mar 23, 2019 12:56 am

If understand you correctly, the basic query would be something like
Code: Select all   Expand viewCollapse view
SELECT "PIID","Project#","DateOfIssue","PersonID","CID","FA","FB","SA","SB"
FROM "TABLE1"
JOIN "TABLE2" ON "TABLE1"."PIID" = "TABLE2"."FKPIID"
JOIN "TABLE3" ON "TABLE2"."FKCID" = "TABLE3"."CID"

If you want to limit the results to a single "PIID" add a where clause with a parameter.
Code: Select all   Expand viewCollapse view
WHERE "PIID" = :Enter_PIID_Number

But the data from 1625, 1628 and 1642 will be absent from the results because "FKCID" for those records contain NULL.
To resolve that issue you could add a COALESCE function
Code: Select all   Expand viewCollapse view
JOIN "TABLE3" ON COALESCE("TABLE2"."FKCID",0) = "TABLE3"."CID"

but for that to work you would need to add a row to "TABLE3" with a "CID" value of 0.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1213
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: arbitrarily link tables, based on criteria

Postby dreamquartz » Sat Mar 23, 2019 4:30 pm

eremmel wrote:This is a rather technical question. You give some table definitions by listing field names but fail to reference them consistently in our story, so I do not understand.

It is unknown when a similar project will be entered.
Multiple users are entering projects at different locations.

Every time a similar project is entered it is also unknown how many people (PID) will be linked to it.
It could be 5, 12, 101 or any number.
The moment the first person is added to the project, they are to be linked to CodeID (CID) 1, the next person to CID: 2 etcetera.
The information (FA, FB, SA, SB) should be retrievable.

Dream
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 710
Joined: Mon May 30, 2011 4:02 am

Re: Arbitrarily link tables, based on criteria

Postby eremmel » Sat Mar 23, 2019 10:38 pm

You can do that with help of a macro. When you have a form to couple a person to project you can for that person find the FKCID with a query like:
Code: Select all   Expand viewCollapse view
SELECT CID
FROM TABLE3
WHERE CID IN ( SELECT MIN(ID) FROM TABLE3  WHERE CID > (SELECT coalesce(MAX(FKCID),-1) FROM TABLE2 WHERE FKPIID = <project-code-from-form>))

There is risk of double assigned values for FKCID in case of concurrency, but that can be prevented by making a unique index on TABLE3 on the fields (FKPIID, FKCID)
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

[SOLVED] Re: Arbitrarily link tables, based on criteria

Postby dreamquartz » Thu Apr 11, 2019 7:37 am

I used the suggestion you made a while back ( see: https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=69523#p310211).
I created a view of the information that needed to be counted and linked the results to the tables in question.
Based on user input, the results are used to fill fields on the documents.

Dream
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 710
Joined: Mon May 30, 2011 4:02 am


Return to Base

Who is online

Users browsing this forum: No registered users and 3 guests