Data representing another data

[LarrySanders]

I'm working on creating a database program.

I want a data to represent another data but I couldn't figure it out.

For instance, I want word A to represent 9 and B represent 7
So when adding a combo box, options should be A and B but when you select B, it enters as 7.

How can I do it?

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).

[Villeroy]

What you want is a list box. viewtopic.php?f=100&t=40444
[Example] Relations reflected by list boxes in forms

[UnklDonald418]

List/Combo boxes will do that. List boxes limit the user to selecting from a list, Combo boxes expand that and also allow new entries.
I uploaded a little demonstration that will show how list boxes would work.
There are two tables, one for Words and another for Selections.
The Table_Words is just a list of words each with a unique ID.
The Table_Selections is a list of ID's of words selected from the Words table
Then there is Form_Selections. It shows a list of words selected from the Words table using a list box.
You can add new selections or change existing selections.
If you open the form in the Edit mode and right click on the column Words and select Column you will get the Properties: List box dialog. If you look at the Data tab you will see that it makes use of QueryWords to supply data to the list box.
Each time you select a word the form displays the selected word, but it actually stores the value found in the Bound field column which in QueryWords is "ID".

[LarrySanders]

I didn't get away with this

MyListBox [name of the query SELECT "Field A" AS "Visible", "Primary Key" AS "Bound" FROM "Your List" ORDER BY "Visible"]

I have 2 columns at my query; Jersey Type and Type Name. The name of query is Jersey Type.

I want Type Name to be visible and Jersey Type as the other one. How will I place it?

[Villeroy]

I didn't get away with this

MyListBox [name of the query SELECT "Field A" AS "Visible", "Primary Key" AS "Bound" FROM "Your List" ORDER BY "Visible"]

This is not the name of a query. It is the query itself.
Upload your document.

[MTP]

Maybe this is what you are looking for?

Code: Select all

SELECT "Type Name", "Jersey Type" FROM "Jersey Type" ORDER BY "Type Name"

[LarrySanders]

I have a teamgear.dbf which is used by the game (NBA Live 2004)

There is a column named JERSEYTYPE. This column is restricted by numbers.

I want to make a toolkit for DBF files of the game, for personal use.

There are 8 types
0 - Home
1 - Away
2 - Alternate
3 - Classic Home
4 - Classic Away
5 - Classic Alternate
6 - Practice Away
7 - Practice Home

You can enter numbers only in JERSEYTYPE column of teamgear.dbf

In my toolkit, I want to be able to select it by selecting the name, not the number. When I click "Home" at the combo box, 0 will apply.

[Villeroy]

List box. It is a list box. And your question has been answered a hundred times in depth. Just read and download the expample files with tons of working list boxes putting numbers by choosing text.

[UnklDonald418]

Since you didn't provide us with a copy of your document, I uploaded an expanded version of my demonstration database. Look at the form TeamGear, it has a column JERSEYTYPE, which contains the list box, and displays the text for the Jersey Type, and I also included a column Jersey Type Num so you can see what is actually stored in the table TeamGear.

You can enter numbers only in JERSEYTYPE column of teamgear.dbf

By the way Open Office Base files have an odb extension not dbf.

[Villeroy]

By the way Open Office Base files have an odb extension not dbf.

His odb might be connected to some nother program's dBase directory.

I have a teamgear.dbf which is used by the game (NBA Live 2004)

There is a column named JERSEYTYPE. This column is restricted by numbers.

I want to make a toolkit for DBF files of the game, for personal use.

Makes no difference here. Base does not support relations between dBase tables on the backend level but you can represent relations on form level by means of list boxes and subforms.
Be careful, though. Always work with a copy of the files. I'm not sure about the dBase version of the built-in dBase driver. It could be dBase3. Other programs may not accept dBase3 files when they expect dBase4. ANd then there are other dBase files other than *.dbf

[LarrySanders]

Not at all... hmm. That's not list box, I'm sure.

Sorry for my English, I guess I failed to fully explain what I want to do.



This is a data about teams.dbf.

You see options as Eastern Conference, Western Conference etc. This identifies the team's conference. But in teams.dbf, they are expressed in numbers. I guess Eastern Conference was 0 and Western Conference was 1. Let's assume, if it's not

When you click Western Conference there the data is entered as 1. When you click Eastern Conference the data will be entered as 0. Vice versa. If you want your team to be at Western Conference, you have to write 1.

If I succeed to make this, my job will be WAY easier

[RoryOF]

From a listbox you can return the position of the selected name. In OO BASIC
SelectItemPos(short) is the number of the selected item; it is read-only, and -1 if nothing is selected.
SelectedItem (string) is the text of the highlighted entry, read-only.

[Villeroy]

Add another table mapping the names to numbers.
Fill the list box with the content of that extra table, text in first column, number in second column, ordered by text.

[LarrySanders]

how will I do the mapping?

[Villeroy]

Create a new table with 2 fields. One field for the numbers and the other for the descriptive strings (max lenght 100 or so).
Open the table, enter descriptions and numbers.

[LarrySanders]

From a listbox you can return the position of the selected name. In OO BASIC
SelectItemPos(short) is the number of the selected item; it is read-only, and -1 if nothing is selected.
SelectedItem (string) is the text of the highlighted entry, read-only.

hmm

how can I use these functions?

[Villeroy]

menu:Insert>New table in design view...
Add one field name of type integer and another field name of type Text(VARCHAR) with sufficient max. length.
Save the new table design under some table name.
Double-click the new table and enter values.