[Solved] Arbitrarily link tables, based on criteria

Discuss the database features
Post Reply
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

[Solved] Arbitrarily link tables, based on criteria

Post by dreamquartz »

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 185 times
Last edited by Hagar Delest on Thu Apr 11, 2019 7:55 am, edited 1 time in total.
Reason: tagged solved
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: arbitrarily link tables, based on criteria

Post by eremmel »

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
W11 22H2 (build 22621), LO 7.4.2.3(x64)
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Arbitrarily link tables, based on criteria

Post by UnklDonald418 »

If understand you correctly, the basic query would be something like

Code: Select all

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

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

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.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: arbitrarily link tables, based on criteria

Post by dreamquartz »

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 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Arbitrarily link tables, based on criteria

Post by eremmel »

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

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
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

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

Post by dreamquartz »

I used the suggestion you made a while back ( see: 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 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Post Reply