New entries in many-to-many subform

Creating and using forms

New entries in many-to-many subform

Postby bongbang » Mon Jul 31, 2017 1:25 am

Objective
I'm implementing a database to track receipts (and shipments, among other things) of unique devices. A device may be received many times (provided that it's shipped out between the receipts), and a receipt can include many devices. Often, the received devices are already in the database. Sometimes, however, a batch of brand-new devices are received from the factory that need to be entered into the database for the first time. I hoping to be able to create new device entries for those such devices by simply logging the receipt, instead of having to create new device entries first and then put them on a receipt entry. My main motivation for this database (the first such project ever for me) is to reduce redundancy, which not only wastes time and often leads to errors.

Work so far
I use the following schema (truncated to stay on point):

Code: Select all   Expand viewCollapse view
device
====
id (PK)
model_id (FK)
serial_number

model
====
id (PK)
model_name

receipt_device
====
receipt_id (FK) (These two together constitute PK.) 
device_id (FK)

receipt
====
id (PK)
receive_on
origin

(I'm leaving out the shipment-related tables for simplicity's sake.)


With the use of list boxes in the subform to the receipt form, I'm able to view received devices by model_name and serial_number, as desired.

Problems
1. I can't add a brand-new device to a receipt. I can only choose from devices already in the database.
2. Even when I'm choosing an existing device for a receipt, my current solution is clunky. Because the underlying data for each subform item is the device_id, the list box contains lots of repeated model names. For example, typing "a" would pull up "AA, AA, AA, AA, AA, AA, ... " instead of the more sensible "AA, AB, AC, ..."

Questions
1. Is it possible for me to add brand-new devices in the receipt form? (This would require creating not only new entries in the "receipt_device" table, but also the "device" table as well.)
2. If that's not possible, I can imagine creating a new special form for receipt of brand-new devices, but I'm not sure how exactly. Would this work?
3. How can I make choosing from an existing device more user friendly. I think using model_id and serial_number as (composite) primary key would help here, but might bring up unforeseen problems since the use of a surrogate key as I have is usually recommended in this situation. Is there a better solution?

Thank you.

PS I have read Villeroy's informative post: Relations reflected by list boxes in forms, but I don't think it answers my questions.
LibreOffice 5.3.6.1. Windows 10. MySQL 6.3.
bongbang
 
Posts: 8
Joined: Tue May 23, 2017 2:08 am

Re: New entries in many-to-many subform

Postby Villeroy » Mon Jul 31, 2017 3:52 pm

S I have read Villeroy's informative post: Relations reflected by list boxes in forms, but I don't think it answers my questions.

1) Just add an additional main form (not subform) where you can enter the required information for a new device (set property "new records only"). After that, you can refresh the list box of your data form without leaving the current record. This is what the second refresh button on the navigation toolbar does.
https://forum.openoffice.org/en/forum/d ... p?id=21160 (genres to which you can assign movies and add new movies, movies to which you can assign genres and add new genres).

2) "AA, AA, AA, AA, AA, AA, ". This deserves a separate table of models with distinct model names and IDs.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26753
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: New entries in many-to-many subform

Postby bongbang » Mon Jul 31, 2017 11:24 pm

Thanks, Villeroy. I will look into your solution (1) as soon as I get a chance. However, your point (2) puzzles me. I already have a table with distinct model names and ID (see schema in original post), which I join to the device table in order to be able to see model names at all. However, the "AA, AA, AA, AA, AA, AA, ..." seems the inevitable by-product of using "device id" as the identifier in the subform. I don't see how "model id" can be used here instead. If there's a way, could you please elaborate? Thank you.
LibreOffice 5.3.6.1. Windows 10. MySQL 6.3.
bongbang
 
Posts: 8
Joined: Tue May 23, 2017 2:08 am


Return to Forms

Who is online

Users browsing this forum: No registered users and 0 guests