Apache OpenOffice Community Forum

Posted: **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 194 times

Posted: **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.

Posted: **Sat Mar 23, 2019 12:56 am**

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.

Posted: **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

Posted: **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

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)

Posted: **Thu Apr 11, 2019 7:37 am**

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

Apache OpenOffice Community Forum

[Solved] Arbitrarily link tables, based on criteria

[Solved] Arbitrarily link tables, based on criteria

Re: arbitrarily link tables, based on criteria

Re: Arbitrarily link tables, based on criteria

Re: arbitrarily link tables, based on criteria

Re: Arbitrarily link tables, based on criteria

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