Using Row Item value to fetch data in another field

[sbastoll]

Hello there,

that’s my first serious contact with LO Base and software alike, so the question might be dump … I got a schema like that (just marking primary and foreign keys for simplicity):

  +- USER -+
  | id     |
  | …      |
  +--------+

  +- INVOICE -+  +- POSITION -+
  | id        |  | id         |
  | user      |  | name       | <= HOW to show that value
  +-----------+  +------------+

  +- LINE ---+
  | id       |
  | invoice  |
  | position |
  +----------+

I want to make a form to insert and view INVOICE LINES for a given user. So I started out and created a form hierarchy like that:

+-+ USER
    + user table to select
    +-+ INVOICE
        + invoice table to select
        +-+ LINE
            + line table to edit

My Problem is, that a LINE Item has a foreign key relation to POSITION. Right now the positioncolumn in the table shows the ID, but in best case that is a dropdown that offers all existings positions to be set by name.

How can I tell Base to lookup in another table? To do that in a query - which isn’t hard to write - I would need to access current row Item’s (LINE) position ID to fetch the appropriate data.

Is there a way to make that happen?

[RobertG]

Might be I didn’t understand right, but a list box will show “name” and will save “id” of table “POSITION”.
SELECT "name", "id" FROM "POSITION" ORDER BY "name" will be the code for such a listbox in form for table “LINE”.

[sbastoll]

Thanks for your reply! You have got it right. Does it mean that one always has to select both columns that are relevant for read and write - id and name in this case? But how does LO Base determine which field to use for display and which to use to reference the entities? In other words, what if the fields would by position_id and name, how would LO Base know how to wire up the references?

Does the id field need to be a simple Integer in order for this to work?

[RobertG]

@sbastoll

But how does LO Base determine which field to use for display and which to use to reference the entities?

Open the list box for editing.
Go to Data.
List content is SQL-code I posted.
Bound Field is set to ‘1’. Counting starts with ‘0’. So “name” is field ‘0’ and “id” is field ‘1’. Field ‘1’ will be saved. Field ‘0’ will be shown.

[sbastoll]

Thanks again for you explanation about the Bound Field! Still it is not working. Just to make sure that the LO BASE setup is correct, the Data Properties of the Field now look like that:

But there is another thing which might break it. The Positions Table is created in this way:

CREATE TYPE accounting.Position_ID AS (
  id integer,
  revision integer
);

CREATE TABLE accounting.Position
(
    id accounting.Position_ID NOT NULL PRIMARY KEY,
    name character varying(255) NOT NULL,
    unit accounting.unit NOT NULL,
    price money NOT NULL,
    tax numeric(2) NOT NULL DEFAULT 0,
    date accounting.current_time
);

So its primary key (id) isn’t an integer as usual. Can LO Base deal with that? Right now I do not get any error popping up, but neither is the right position name shown nor can I add new ones.

[RobertG]

Which database do you use? I have never used CREATE TYPE before. Doesn’t exist in internal HSQLDB.

You are using “accounting”.“position” - looks like PostgreSQL with schema name.

Now: Where is the table, which should save data in the field “position”? An example will help here.

[sbastoll]

I am using Postgres. Right now all the tables are in the accounting schema.

The DB looks like that:

-- just the necessary fields are shown 

CREATE TABLE accounting.User 
(
  id serial NOT NULL PRIMARY KEY,
  name character varying(255) NOT NULL UNIQUE
);

CREATE TABLE accounting.Invoice
(
  id serial NOT NULL PRIMARY KEY,
  "user"  integer NOT NULL REFERENCES accounting.User (id)
);


-- all lines from my post above



CREATE TABLE accounting.Line
(
  id        serial NOT NULL PRIMARY KEY,
  invoice   integer NOT NULL REFERENCES accounting.Invoice (id),
  position  accounting.Position_ID NOT NULL REFERENCES accounting.position (id)
);

I would like to do CRUD operations on the Lines Table.

[RobertG]

Have tested this. Have to delete the rows “price” and “date”. But I’m not able to add any value to table “Position” when entering the table GUI.

Why did you define a separate TYPE, which should be integer?

[sbastoll]

The reason for a position to have such a composite type is the ability to create newer revisions of an existing one - f. i. changes in Price. This way the id aka »Position Number« wont change event though the position itself changes over time. The List, from which users shall select a position for a new Line is generated like so:

CREATE OR REPLACE VIEW Latest_Position AS
SELECT
	*
FROM (
	SELECT
		*,
		MAX((Pos.id).revision) OVER (PARTITION BY (Pos.id).id) as "max_revision"
	FROM accounting.Position Pos
) Rnk
WHERE (Rnk.id).revision = Rnk.max_revision;

What yields a list of all position by each with its latest revision.

[RobertG]

And how do you add any value to “Position”?

Have tested with a simple example in PostgreSQL-description, only one type ENUM, not two types as you have set. This simple type will work…

If I design a database, where entries should be changed by time, I will add new rows with a new date. I would create another table instead for price and date.

[Villeroy]

The relation between invoices and their sold items is a many-to-many relation with a sales table mapping invoices to sold items looking like this in a relation window:

[Invoices] 1–n [Sales] n–1 [Items]

There is only one way to edit a many-to-many relation in a Base form.
Simple example where each person has many things: [Example] Relations reflected by list boxes in forms
An invoicing example: Apache OpenOffice Community Forum - [Example] Invoice Forms (without macros) - (View topic) (Items – Sold – Invioice)
https://ask.libreoffice.org/uploads/short-url/eaU0J7mCLggEdOiUgt3GoZog08x.odb is another example posted recently to this forum (Sales – Product – Prices)

The recipe for a filly editable many-to-many form:
Main form: One of the lists (invoices or products)
Subform: The linking table as a table control. The table control has one listbox column where each entry represents one item of the other list (product or invoice respectively)
In the first sample each person in the persons form shows a column of things in the yellow table control. Each thing is represented by a listbox. Likewise in the things form, each thing shows a column of persons in the orange table represented by a listbox.

[sbastoll]

And how do you add any value to “Position”?

INSERT INTO accounting.Position (id, name, unit, price, date) values
( (0, 0),  '…',       '…' ,  … ,  '…'),

[…] where entries should be changed by time, I will add new rows with a new date […]

Yeah that is possible as well. That’s one of my first data bases, so yes, may not be the best way to implement that. The idea to use a date for that is pretty cool, this way we can actually when a change did happen.

[sbastoll]

The relation between invoices and their sold items is a many-to-many relation

In my case not, line to position is one to many. Do mean that as an advice to model it like that?

[RobertG]

@sbastoll

INSERT INTO accounting.Position (id, name, unit, price, date) values
( (0, 0),  '…',       '…' ,  … ,  '…'),

This method seems to be impossible through Base GUI. Try to input through table view …

[sbastoll]

I guess so … I have just changed it to use an integer ID and date, it works fine and feels »more right« as well.