Construct database of stored property

Discuss the database features

Construct database of stored property

Postby davo » Sat Oct 09, 2021 8:31 am

HELP PLEASE!!

I am trying (in vane) to construct a database of stored property called ASSETS.

All the ASSETS are stored in BOXES.

BOXES are numbered 1,2,3, etc and A,B,C, etc.
Each BOX contains multiple ITEMS.
Each BOX has one CATEGORY of ITEMS.
Each BOX is located in one LOCATION.

I have created Tables for each of the above i.e. BOX NUMBER, CATEGORY, LOCATION, ITEM.
I have created a form with drop-down lists for BOX NUMBER, CATEGORY, LOCATION, ITEM.

However, I still can't get things to work. When I start to fill out the form, nothing appears to get saved.

I think I need several Relationships, but I keep getting error codes when trying to establish them.

The following error is displayed when trying to add records via the Form:

SQL Status: 23000
Error code: -177

Integrity constraint violation - no parent SYS_FK_142 table: Box Number in statement [INSERT INTO "Assets" ( "Box Number","Category","Item","Location") VALUES ( ?,?,?,?)]
OpenOffice 4.3.1 on Windows 10 Pro
davo
 
Posts: 3
Joined: Sat Oct 09, 2021 8:02 am

Re: Base

Postby Villeroy » Sat Oct 09, 2021 5:07 pm

davo wrote:BOXES are numbered 1,2,3, etc and A,B,C, etc.

is A,B,C etc. a category or just an arbitrary box label?

davo wrote:Each BOX is located in one LOCATION.

Is this a fixed location or can the box move to another location next day?

davo wrote:Each BOX has one CATEGORY of ITEMS.

So category is an item's attribute, but is it also an attribute of a box? If some box has items of category A, could it also be filled with items of another category or is the box for A items exclusively?

Can there be more than one of the same item in a box?
Does each item belong into its particular box or can it be distributed across many boxes?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30156
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Construct database of stored property

Postby UnklDonald418 » Mon Oct 11, 2021 5:55 pm

If I understand you correctly,
Since each Box has one location and contains one category, the Box table should have fields for BoxName, Location_ID and Category_ID and of course an ID field which is the Primary Key with AutoValue is set to Yes.
Items are located in Boxes so, the Items table will need fields ItemName, Box_ID, and againan ID field for the Primary Key with AutoValue is set to Yes.

Box_ID in the Item table and ID in the Box table form the one to many relationship.
Items can get their category and location from their associated Box.

Create a form with Box on the MainForm and Items on the SubForm using the relationship above.
Since you have a one to many relationship, the SubForm should use a Table control.
You will need list boxes for Category_ID and Location_ID on the MainForm and one for Box_ID on the SubForm.
You should then be able to enter a Box on the MainForm and select Category and Location.
Then on the table on the SbuForm enter Items associated with the Box on the MainForm. If set up properly the Box_ID will be automatically entered on the SubForm.

This assumes a table of Locations and another of Categories
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.8 & LibreOffice 6.4.7.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1410
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Construct database of stored property

Postby John_Ha » Mon Oct 11, 2021 6:37 pm

The 476 page LibreOffice Base Guide is an excellent reference and much more comprehensive than the OOo 54 page Chapter 8 - Getting Started with Base
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 8658
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Construct database of stored property

Postby davo » Sun Oct 17, 2021 10:30 pm

Villeroy.

A,B,C and 1,2,3 are Box labels.

LOCATION is fixed.

CATEGORY is a unique attribute of each BOX.

Yes, there can be more than one ITEM in the same BOX.

Each ITEM is resident in one BOX at any one time. An ITEM can be moved to another BOX, and the Database will change to reflect this change.
OpenOffice 4.3.1 on Windows 10 Pro
davo
 
Posts: 3
Joined: Sat Oct 09, 2021 8:02 am

Re: Construct database of stored property

Postby UnklDonald418 » Mon Oct 18, 2021 12:26 am

See if this example does what you are wanting.
Attachments
Demo56_Assets.odb
(22.43 KiB) Downloaded 9 times
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.8 & LibreOffice 6.4.7.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1410
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA


Return to Base

Who is online

Users browsing this forum: No registered users and 5 guests