Libreoffice Base - how to have a help desk database with two tables

[DiscoDucky]

Hello,

I am basically trying to do the following:

1. One Table is the customer table, with name, address, etc
2. The second table is the issue/problem table, with the issue, comments, last update, open/closed, etc

I want to be able to access it, through a form or otherwise, so that the first column is linked to the customer table’s last name/first name

like

C1:last name(and first name if possible) C2: Issue C3: Comments(and the other columns from the second table)

So I can basically add entries through a single form or table, choose the customer from a drop down, type the issue, and have some other columns that function as drop downs(such as status:open/closed, etc).

This would be fairly easy to do in a sharepoint list, but I’m trying to do it in some free way that can be used offline.

[RobertG]

Table for “issue” should contain a separate primary key, because it might be you need more than one row for a customer in the issue-table.
Then: Table issue will need a field, which could save the value of primary key from customer. This might be a Integer field.
In the form you could connect a listbox to this integer field. Listbox could show the last name and first name and could save the value of the primary key of customer table in issue table.
Here is one example how to fill listbox with the right data:
https://books.libreoffice.org/en/BG73/BG7301-IntroductionToBase.html#toc32

[Villeroy]

You never link anything to a name. Tables are linked through primary keys and foreign keys which are ID numbers.
The following SQL code (menu:Tools>SQL) …

create table issues(
    descr longvarchar, 
    ts timestamp default current_timestamp, 
    cid integer not null, 
    id integer identity, 
    foreign key (cid) references customers(id)
);
create table messages(
    txt longvarchar,
    ts timestamp default current_timestamp, 
    issueid integer not null, 
    id integer identity, 
    foreign key (issueid) references issues(id)
);

… generates this database with two one-to-many relations:

Each issue belongs one customer.
Each message belongs to one issue.
The timestamp fields (TS) add the current timestamp to every new record.
one2many.odb (3.2 KB)