Search code examples
libreoffice-base

Add record with ID, selecting NAME from another table

Can anyone, please, help me with Libreoffice Base form creation? I have the following tables:

Tables And I'm trying to add a form to enter new RESOURCES record with the following fields: [RESOURCE_NAME], [CURRENCY_NAME] and [AMOUNT]. But after 10+ tries I have not succeeded. I have tried adding it via wizard, selecting RESOURCES as main form and CURRENCIES as subform and vice versa. I have tried VIEWS and forms based on them. These tries only gave to me or no possibility to enter new record, either creation of the new CURRENCY.

I don't need to create new currency via this form, I only want to enter new Resource (only enter once, not to modify, not to delete). Since I don't want to remember all the ID's I want to select currency name via DropDown list.

Can anyone provide instructions about how to do it, please?

Thanks.

Solution

  • You do not need a subform for this - just create your form document with RESOURCES as the main form (only form).

    You will need a listbox to enter the currency item. A listbox has two fields, a display field and a field that is saved in the table. You will set it up to display CURRENCY_NAME and store CURRENCY_ID.

    When you create a listbox, the wizard that pops up may get you what you want. If the wizard falls short:

    • Make sure the form document is open in design mode: on the "Form Controls" toolbar, the leftmost/topmost icon of a pencil with a triangle should be depressed. If this icon is grayed out, close your document, right-click on its name and choose "Edit".
    • Right-click on the listbox and choose "Control"; this will open the properties window
    • On the tab "Data" change the "Type of list contents" to "Sql"
    • In the field "List content" enter SELECT "CURRENCY_NAME", "CURRENCY_ID" FROM "CURRENCIES" ORDER BY "CURRENCY_NAME"
    • The Bound Field should default to 1. If it isn't 1, change it to 1.
    • Close the properties window and save your form. It should work as you want now.

    If you want a listbox inside a tablegrid: after you create the table, with the form in edit mode, right-click on the column name you want to change and choose "Replace with" and then "listbox".

    Edited to include comment by OP about bound field needing to be 1