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
[Solved] Arbitrarily link tables, based on criteria
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
[Solved] Arbitrarily link tables, based on criteria
Last edited by Hagar Delest on Thu Apr 11, 2019 7:55 am, edited 1 time in total.
Reason: tagged solved
Reason: tagged solved
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: arbitrarily link tables, based on criteria
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)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Volunteer
- Posts: 1544
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Arbitrarily link tables, based on criteria
If understand you correctly, the basic query would be something like
If you want to limit the results to a single "PIID" add a where clause with a parameter.
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
but for that to work you would need to add a row to "TABLE3" with a "CID" value of 0.
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"
Code: Select all
WHERE "PIID" = :Enter_PIID_Number
To resolve that issue you could add a COALESCE function
Code: Select all
JOIN "TABLE3" ON COALESCE("TABLE2"."FKCID",0) = "TABLE3"."CID"
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: arbitrarily link tables, based on criteria
It is unknown when a similar project will be entered.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.
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.
Re: Arbitrarily link tables, based on criteria
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:
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)
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>))
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
[SOLVED] Re: Arbitrarily link tables, based on criteria
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
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.