I could not sum up, what I really want in the title. But here I am going to try to explain what is it about. I have switched from MS Access to Libreoffice Base because it is open source, free and platform independent. I really like Libreoffice and therefore try to solve my problems there. I could not find anything related in the documentations and also could not find exact same question (there were similar questions but did not work out for me).
Part of my database consists of 3 tables. Invoice table is linked one to many to Service (this is one line in the invoice remarking product involved and quantity). (Invoice.ID (1) -> Service.InvoiceID (n)) (n) = many, (1) = one
On the other hand I have another table for products which is like a catalog of products linked one to many to Service table. Product.ID (1) -> Service.ProductID (n) I have also created a table control linked to a query, which joins Service and Product tables. So I have following columns: Service.ID, Invoice.ID, Product.ID, Service.Quantity, Product.Name, Product.Description, Product.Price
I want to place a column with combobox into this table which is linked to Product table. So user sees product name and selects the correct product there but the Service.ProductID will be changed to the Product.ID of selected product. I suppose this is doable, however I can't seem to find any way.
Any help would be appreciated!
PS. I am also programming and could also write own Java application but I suppose this is unnecessary for an application of this kind.
I found the answer in OpenOffice forums.
To sum it up: Combobox is the wrong choice here, as it is like a auto-complete textbox. Listbox is used instead (which also looks like a combobox in the classical sense). Simply write a sql query to get the name (visible field) for index 0 and ID to index 1 (data field). And select the data field in the table to be changed. That's it!