Page 1 of 1

[Solved] Arbitrarily link tables, based on criteria

PostPosted: Fri Mar 22, 2019 5:53 am
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 53 times

Re: arbitrarily link tables, based on criteria

PostPosted: Fri Mar 22, 2019 5:14 pm
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.

Re: Arbitrarily link tables, based on criteria

PostPosted: Sat Mar 23, 2019 12:56 am
by UnklDonald418
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.

Re: arbitrarily link tables, based on criteria

PostPosted: Sat Mar 23, 2019 4:30 pm
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

Re: Arbitrarily link tables, based on criteria

PostPosted: Sat Mar 23, 2019 10:38 pm
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   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)

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

PostPosted: Thu Apr 11, 2019 7:37 am
by dreamquartz
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