2

I'm building a form for entering credit/debit transactions, so I have two tables in PostgreSQL

accounts
    id      INTEGER PRIMARY KEY
    name    VARCHAR(128)

ledger
    id      INTEGER PRIMARY KEY
    credit  INTEGER NOT NULL REFERENCES accounts
    debit   INTEGER NOT NULL REFERENCES accounts
    date    DATE NOT NULL
    amount  NUMERIC(8,2)
    text    VARCHAR(128)

I have built a form with a single table control, listing accounts.name, and a subform that references that control for selection.

Inside the subform, there is another table control, showing date, credit, text and amount after matching debit against the selected entry in the master.

So far, that works, however the credit column has the numeric foreign key data inside a numeric field. I'd like to have a dropdown field that allows me to select an account in its place.

Is that possible, or should I go for a fallback solution with a readonly table populated from a query, and separate edit fields in a subsubform?

Simon Richter
  • 28,572
  • 1
  • 42
  • 64
  • `credit INTEGER NOT NULL REFERENCES accounts` -->> `credit INTEGER NOT NULL REFERENCES accounts(id)` , and similar for debit. – wildplasser Mar 06 '14 at 00:32
  • @wildplasser, that already works (at least Postgres automatically uses the primary key if no columns are specified). The main problem is doing appropriate UI in LibreOffice. – Simon Richter Mar 06 '14 at 10:17

1 Answers1

2

The functionality that works in LibreOffice is a listbox. What is tricky is that the individual fields on the grid/table control are not displayed in the form navigator. However, you can convert the type of field on the grid to a listbox by going to edit mode and right-mouse clicking on the header row/column selector of the grid. In the dropdown menu, select "replace with" and "list box". Now that field/column in the grid is a list box.

Then open the "control" dialog and click again on the appropriate header/column selector in the grid, which you just changed to a listbox. Go to the "data" tab. Under "type of list contents" select "Table" and "List content" select the foreign key table. With the "bound field" having a value of "1", the dropdown list will display the first column of the list source, and it will use the value of the second column to populate the data source displayed in the listbox control.

For a better aesthetic, in the control box, "General" tab, "Dropdown" option select "Yes".

The steps above will provide a functionality mostly indistinguishable in the UI from a familiar combobox. You also can try the "combobox" functionality directly in LO, but support for that field type is not as strong.

Doug0
  • 331
  • 1
  • 8