[Solved] Value of a cell to be reported in another table

Creating and using forms

[Solved] Value of a cell to be reported in another table

Postby tlazzari » Mon Nov 13, 2017 9:40 am

contact_id porting.png
Where I would like the data to be
Dear All,

I have created a simple db with three tables: companies, contacts and contact_company. The final goal is to be able to create a form to input the company name in the company table, the name of the contact in a company in the contacts table and the contacts pecentage ownership of each company in the company_contact table.

In table companies: pk is company_id
In table contact_company: pk is contact_company_id, contact_id is FK, company_id is a FK
In table contacts: pk is contact_id
(see attachment)

I created a Form called Company, which has a subform called contacts_company, which has a subform called contacts (see attachment).
I connected all the master and foreign links but it does't work as I would like to infact, I would like the field contact_id in the table contact_company to be automatically populated when I fill up the contact name. (see attachment)

Does anybody have any advice?

Thanks
Tommaso
Attachments
oob_table.png
oob form structure.jpg
Last edited by tlazzari on Thu Nov 16, 2017 6:14 am, edited 1 time in total.
OpenOffice 4.1.0 on Ubuntu 14.04
tlazzari
 
Posts: 3
Joined: Mon Nov 13, 2017 8:41 am

Re: Value of a cell to be reported in another table

Postby tlazzari » Mon Nov 13, 2017 12:47 pm

Just one more point, I have a lot of contacts and companies therefore, let the user choosing from a dropdown list would not be feasible.
Thanks
Regards
Tommaso
OpenOffice 4.1.0 on Ubuntu 14.04
tlazzari
 
Posts: 3
Joined: Mon Nov 13, 2017 8:41 am

Re: Value of a cell to be reported in another table

Postby tlazzari » Thu Nov 16, 2017 6:14 am

For those who are interested I solved the issue thanks to a similar solution in an old threat.

The code that did the magic is the following:

Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****

Option explicit
Sub update

DIM oForm2 AS OBJECT
DIM oID AS OBJECT
DIM oForm1 AS OBJECT
DIM oIDCloned AS OBJECT
DIM sID AS STRING
DIM sIDCloned AS STRING
DIM oIDcell AS OBJECT
DIM lNameCol

oForm2 = thisComponent.Drawpage.Forms.Form.Form1.getByName("Form2")
oIDcell = oForm2.getColumns.getByName("contact_id")
sID = str(oIDcell.Value)

oForm1 = thisComponent.Drawpage.Forms.Form.getByName("Form1")
oIDCloned = oForm1.getColumns.getByName("contact_id")

lNameCol = oForm1.findColumn("contact_id")

IF sID <> "" THEN
   oForm1.updateString(lNameCol, sID)
ENDIF

oForm1.updateRow

oForm1.reload

End Sub


Cheers
OpenOffice 4.1.0 on Ubuntu 14.04
tlazzari
 
Posts: 3
Joined: Mon Nov 13, 2017 8:41 am

Re: Value of a cell to be reported in another table

Postby charlie.it » Thu Nov 16, 2017 11:37 am

Thanks @tlazzari, for a complete sharing why do you not even place the database file?
charlie
macOS 10.12 Sierra: Open Office 4.1.5 - LibreOffice 6.2

http://www.charlieopenoffice.altervista.org
User avatar
charlie.it
Volunteer
 
Posts: 320
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy


Return to Forms

Who is online

Users browsing this forum: No registered users and 0 guests