[Solved] Customizing A Boolean

[BAC94]

Hello, I fairly new to setting up databases. I'm trying to set one up for the shop I work in. I want to start tracking defects in the factory. One of the fields I want to have in my database is whether or not the defect was caused internally or by our supplier, so I figured that field should be a Boolean: either "Internal" or "Supplier". But when I choose "Yes/No [ BOOLEAN ]", my only options are "Yes" or "No" and it generates a check box when I open the database. Is there a way I can have a little drop-down list with those two options in it?

[UnklDonald418]

A boolean may not be the best choice.
I would recommend adding a field type VARCHAR(8) with a constraint.
When edited, entered and executed at Tools-SQL the following SQL code should add a column “Defect” to your table

Code: Select all

ALTER TABLE "YourTableNameHere" ADD COLUMN "Defect" VARCHAR(8) DEFAULT 'Supplier';
ALTER TABLE "YourTableNameHere" ADD CONSTRAINT "CK_DEFECT" CHECK("Defect" in ('Supplier', 'Internal'));

that is constrained to only accept the string values 'Internal' or 'Supplier'.

On a form add a listbox control for “Defect”
Right click on the listbox and select Control from the pop-up list to open the listbox properties dialog.
On the General tab
set Dropdown to Yes
set Linecount to 2
to set “List entries”, type Internal followed by a Shift-Return and then type Supplier
On the Data tab
set “Data field” to Defect
set “Type of list contents” to Valuelist

[UnklDonald418]

It just occurred to me that you could use a Boolean if it was connected to a a Group Box control with 2 option buttons.

To do that open your form in the Edit/Design mode, and select the Group Box control from the Form Controls menu.
You can add the control to your form using click and hold while you stretch out a box. The size isn't that important because it can be changed later.
Then a wizard should open on the screen. Type the name Internal in the name box and add it to the Option Fields list using the arrow button (>>), then do the same for Supplier.
Select Next to move to the Default Field selection wizard page. It would probably be best to use the default value it shows.
Select Next to move to the Field Values wizard page. It should show a value of 1 for Internal, which should work, but change the Suppliers value to 0.
Select Next to move the the Database Field wizard page. Select the name of your Boolean field from the drop down list.
Select Next to move to the Create Option Group wizard page. Type a name you want to use as a label for the Group Box.
Click Finish and Save your form.

[BAC94]

So I'm new to forms as well. I was thinking I would just enter the data into the table itself.

So, it looks like forms can restrict data, but not the table itself?

[Villeroy]

http://forum.openoffice.org/en/forum/do ... hp?id=2933
contains a form where you can choose type 0, 1 and 2 by means of a list box, 3 radio buttons or a numeric field. The same variations can be implemented for a boolean field with 2 possible values.

[UnklDonald418]

Forms allow numerous options for data display and entry while working directly in a table has very few options. Generally, entering and editing data directly in the table is only used for very simple tables/situations.
If you added the Defect column using the SQL code I posted earlier, and try to enter something other than Supplier or Internal into the table it will generate an error message. That occurs because the Constraint placed on the Defect column is stored with meta-data for the table, so it's available for any Database engine operation on that table. Without the constraint you could enter any 8 character string into that column, but I doubt it would be very useful to have something like "pink" or "Mary" appear in the Defect column.

With forms you can choose where on the screen to display the data, and you have all sorts of formatting options including character font, size and color. You can use form controls like list boxes, combo boxes and even option buttons to limit the what can be entered into a column, which eliminates some data entry errors. A form also allows data entry into more that one table on a single screen, simplifying and streamlining the data entry process.
Learning to design forms is an essential skill for creating RDBMS's (Relational Database Management Systems).
I usually use the Form Wizard to at least get my form design started. The wizard can even generate a form with a single subform. Using the basics generated by the wizard and then adding more controls/forms/subforms can save quite a bit of time.