Form in Base: want fields to effect each other

[rymong]

I’m in archaeologist trying to develop a form of ceramic analysis. An ancient Maya ceramic vessel is attributed to:

A Ware, of which there are dozens

A Group, of which there are also dozens but limited by the Ware.

A Type, of which there are hundreds but limited by the Group.

A Variety, of which there are thousands but limited by the Type.

So, I would like to have fields in the form for Ware, Group, Type, and Variety where the options available for each successive field are determined by what has been put in the prior field.

Also, since there are so many options for each of these fields, drop down menus seem like they would be unwieldy (e.g. selecting your type from about 300 options). So, we are thinking of using code numbers, where once a code for a Ware is input, another field auto-populates with its name.

Apologies if this is asking too much or unclear.

[charlie.it]

Ciao, your ask is clear enaugh but I need your file database for my answers.

[rymong]

see above and thank you!

[rymong]

Thank you. I don’t have enough Karma to upload the database file with this message. Here is a link to it:

Ucanal Database.odb

Within the “Variety Form” I would like the fields Ware, Grupo, Tipo, and Varidad to autofill from the table “Ceramic Wares” when a specific WGTV# is entered.

For example, if I enter 01010201 in the WGTV# field, I’d like the “Ware” field to automatically fill “Uaxactun Sin Engobe”

and have the “Grupo” field fill in “Achiotes”, and the “Tipo” field fill in “Baldizon Impreso”, and the “Varidad” fill in “Mono.”

[Ratslinger]

Hello. You question seems simple enough but presents many challenges. The volume of items within the categories seems to make even "filtering’ the items a difficulty.

The only solution I can present is using a macro to locate each item in a category based upon the WGTV code. To demonstrate, I have a sample.

In the sample there is one form for demonstration. Contents: Name, WGTV code, Ware, Group, Type and Variety. To use, enter a Name (any); enter the eight digit WGTV code; when you TAB (or ENTER)to “Ware” field, a macro executes and using the first two digits of the WGTV code it displays the result; you must do the same for the remaining fields.

You MUST use the Tab or Enter key through the Ware, Group, Type and Variety fields. Using the mouse to select a field can create problems. The same is true if the WGTV field is changed - skipping the Tab Enter process for any of the four fields can leave erroreous results.

There are five tables. “ANCIENTITEM” is filled in by the form. “WARE” has a key of two digits (the first two in the WGTV code) and the description. “GROUP” has a key of four digits (the first four in the WGTV code) and a description. The first two digits represent the “WARE” and the last two digits represent the unique “GROUP” within the “WARE”. “TYPE” is similar with six digit key - 2 for WARE, 2 for GROUP and 2 for unique TYPE. “VARIETY” uses the full eight digits.

Even if you don’t want to use macros, maybe this will benefit someone else or even trigger a different thought by someone else.

As you may understand, there is limited data in the tables provided. Simple codes such as 01010101 and 02020101 and others will work. The code does provide default error text for information not fould in a table so any eight digit code will be accepted.

Sample: archaeology.odb

[rymong]

Ratslinger,

This looks absolutely perfect! Thank you so much. What is the best way to bring over the form, tables, and macros that you created to the database I have already set up?

Thank you again.

[Ratslinger]

In the main window when you open the .odb select the form, copy and then paste into your .odb. The same can be done with the tables provided you are using the database included with LO (HSQLDB). Macros are similar - copy and paste the code from one to another.

[rymong]

Ratslinger, not sure if you will see this. You helped me a great deal with my database last summer. I’m having a new issue, and I was wondering if you could help. I am trying to tie the different tables in the database together using Relationships, but I keep getting an error when I try to create one. Can you assist?

Error: Column types do not match in statement [ALTER TABLE “Lot Table” ADD FOREIGN KEY (“Lot”) REFERENCES “ANCIENTITEM” (“NAME”)]

[Ratslinger]

@rymong Yes I got the message. It is best if this is asked as a new question so others having the same problem will be able to find the solution easily. Be sure to include details about the fields in each table you are trying to join (data type, length).

Also, for others benefit, if an answer resolves your question (as I believe this one did), please click on the (upper left area of answer). This also provides others with the knowledge a question has been answered.

[Ratslinger]

@rymong Also include what DB you are using & type - embedded, split, external, built-in HSQLDB, in split HSQLDB 2.x, MySQL, PostgreSQL, etc.

[rymong]

I asked here: Creating relationships in Base

Thanks for responding so quickly!

[Ratslinger]

Most happy to help if possible.