[Solved] Construct database of stored property

[davo]

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 ( ?,?,?,?)]

[Villeroy]

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?

Each BOX is located in one LOCATION.

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

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?

[UnklDonald418]

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

[John_Ha]

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

[davo]

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.

[UnklDonald418]

See if this example does what you are wanting.