Table lookups and fields that are not stored?

[Rose Edwards]

Hi all!

As threatened, here's another question from me. I've tried reading the resources that are available to me (Manuals, "Xbasic for Everyone" (for Alpha Five Version 3), the learn alpha site, many of the messages in this forum and the code archive), but have not found how to handle what I thought would be a fairly straightforward task, given the power of Alpha Five. Of course, if I have missed it in the reading, please be sure to tell me where it is to be found!

I am writing an application to track where we are storing financial records. I need to keep track of the kind of record a particular item is, so that I know how long the record needs to be stored. I want to link items and categories with a numeric ID field, so that I can make the ID be an autoincrementing field. I do *not* want to show the users the ID field, as it means nothing to them. I figured that it would be straightforward to have a table lookup of the text field naming the category populate a dropdown list to show the user, while my program stored the ID in my item record. I've been flailing away in the documentation and Field Rules (and other places) most of today and some of yesterday, and not gotten any closer to the solution. What am I missing? I know that I am beginning with this, so don't worry about explaining things at too low a level for me.

For a little more concrete detail, here is an extract of relevant details from my database design:

Table structure for Category.dbf

Name              Type    Length
--------------    ------  ------
ID                  N          8
Name                C         80


Table Structure for Item.dbf

Name              Type    Length
--------------    ------  ------
ID                  N          8
Description         C         80
CategoryNumber      N          8

Thanks!

Greg

[Jim Chapman]

RE: Table lookups and fields that are not stored?

Hello Greg,

>>I figured that it would be straightforward to have a table lookup of the text field naming the category populate a dropdown list to show the user, while my program stored the ID in my item record. I've been flailing away

[Rose Edwards]

RE: Table lookups and fields that are not stored?

Hi Jim,

I wasn't clear enough. The only thing that I want to store from the category table in the item table is
category->ID. The description in the item table is not the category name.

If the category table contains:

ID  Name
 1  Canceled Checks
 2  Cash Disbursements
 3  Auditors Records

I want to show the user a dropdown list with "Canceled Checks", "Cash Disbursements", and "Auditors Records". If the user then chooses "Canceled Checks", all I want to put into the item record is the number 1. The Description field in the Item table is for more information about what kind of Canceled checks, like "For recipients whose names begin N-U".

As to why I want to use numeric fields for my IDs, there are several reasons. First, I can make them autoincrementing, so that I don't have to write code to assign them and keep them unique. Second, they are (or can be - I don't yet know just how A5 stores things) more compact to store on disk. Third, it is likely that they can be compared faster. Granted, the latter two reasons are not as important in this application, since it will be a smallish one, anyway, and disk space is so darn cheap anymore.

I'll keep plugging away.

[Jim Chapman]

RE: Table lookups and fields that are not stored?

Hello,

Ok, just do it like I posted earlier, only in the field rule lookup definition, don't fill-in the decription, just display the name from the category table. You can tell Alpha to display a fields contents in the lookup, but you don't have to do anything with the value you display.

Good luck,
Jim

[Tom Cone Jr]

RE: Table lookups and fields that are not stored?

Greg,

Alpha Five's autoincrement rule handles text string numerics just fine. I invariably set my idnum fields to char type, but stuff them with things like '01001'. If this were the value in the last record, the next record added to the table would have an autoincrment value of '01002'.

I don't believe the dBase format permits numeric fields to be stored in binary. I think the char equivalents are written to the field when the record is saved. So I don't think the disk storage requirements would be different if a field were numeric, 10 or if it were character, 10.

-- tom

[Rose Edwards]

RE: Table lookups and fields that are not stored?

OK, I had to work on other things for a while, but I am back on this for the next week (then the job disappears under me :-( ANyway, I can make it almost work the way that I envisioned, following your directions, Jim. Thanks for them!

One more question - is it possible to not show the ID field that is being used to link the tables? I don't have them in a set because I know that I read somewhere that it was not necessary to do so for lookups of this form. What I was hoping to be able to do was not let the users see that I had a link field at all.

Failing that, is it simple to cause the looked up description to show on a form? Ideally this would be done with minimal xbasic, since I don't think that I can expect my successors in this position to be programmers.

I would like to be able to have a screen something like this:

Item Description: 
Category:

Where the user can enter the item description and choose a value for item.category from the list in the category table, then have the category name that was chosen be displayed on the form. (The things in are the values stored in the database.) Is this feasible, or am I dreaming?

Thanks again!

[Rose Edwards]

RE: Table lookups and fields that are not stored?

Ack! I clearly didn't think my "post in HTML" strategy through completely, seeing as how I chose to use examples that would get interpreted as unknown tags.

Let's try the sample screen again, with the descriptions in brackets, that should show as I intended, instead:

Item Description: [item.description]
Category: [item.category] [category.name]

Hopefully, this example will be a little clearer! I have for example, "[item.description]" where I want to look up the item's description from the database.

Greg