Double lookup in field rules?

[michaelklein]

Hi all.

I've created a set using 2 external tables, inventory.dbf and barcodes.dbf, which are used by another, non-Alpha5 system, and an invoice detail table, invoicedet.dbf which I have created inside Alpha5. The invoicedet table links to the barcodes table via a barcode field. The barcodes table links to the inventory table via a codenum field. I have created an embedded browse based on the invoice detail table with columns for Barcode, Item Description, Qty, and Price. When the user scans a bar code, I need to lookup the barcode table, then use the codenum found there to lookup the inventory table. Is there a way to do this double lookup in an embedded browse using field rules? Ideas anyone?

Thanks.
Michael

[Stan Mathews]

Re: Double lookup in field rules?

Wouldn't that be a "nested" lookup? I'm thinking a calculated field instead of a table lookup.

Build the first lookup to get the codenum. Use that expression in defining a lookup to the inventory table.

[michaelklein]

Re: Double lookup in field rules?

Stan,
Sorry, but I don't quite understand . A calculated field for which part?

The customer wants to enter/scan a barcode directly into a column of the embedded browse, or select an inventory item from a drop-down list, then display the associated product description in another column, and finally save the barcode and codenum into the invoicedet.dbf table.

Could you point me to a sample of what you're proposing?
Thanks.

[Stan Mathews]

Re: Double lookup in field rules?

I didn't understand the or part. I thought they wanted to scan a barcode and have that automatically lookup the inventory item and have the inventory item lookup the codenum. Or something like that.

So the first part is a table lookup somewhat like is defined for the invoive_items table in AlphaSports. After that lookup you want to "transfer" the barcode and codenum to the invoicedet table? The transfer could be done with a posting field rule if you are finding some existing record. If you are creating a new record with the transfer you could code the onsaverecord event to write to the invoicedet table.

[Stan Mathews]

Re: Double lookup in field rules?

Attached is a simple table with two fields. Unzip it to your AlphaSports directory and add it to the database. Edit the field rules for the invoice_items table, Events, Record Events, OnSaveRecord. Paste in this code.

tbl_cur = table.current()
tbl_dest = table.open("a")
tbl_dest.enter_begin()
tbl_dest.P_id = tbl_cur.product_id
tbl_dest.inv_no = tbl_cur.invoice_number
tbl_dest.enter_end(.T.)
tbl_dest.close()

save the field rules and exit the editor.

Open the Invoice form and make some new entries or change the product via the lookup in the items browse. Alternatively open the default browse for the invoice_items table and make some changes via the lookup.

Your changes will cause new entries in the "a" table recording the new product_id chosen and the invoice_number of the record edited.

[michaelklein]

Re: Double lookup in field rules?

Stan,
I'm sorry it's taken me a few days to take a look at what you sent. Although it accomplishes part of what I'm trying to do, my key issue is getting the embedded browse to work the way I need it to. To make things a bit clearer, here are the table definitions:

Bar codes table
Barcode, C, 14
Codenum, C, 6

Inventory table
Codenum, C, 6
Description, C, 30
Price, N, 6.2
Size, C, 8
Qtypercase, N, 4

Invoice detail table
Invoicenum, C, 10
Codenum, C, 6
Qty, N, 5
Price, N, 6.2

I have attached an image of the browse as well: LPDashBrowse.PNG

As you can see, there is a Barcode column (unused currently) and a ProductID column. I need to either...

1. Scan the barcode into the barcode column, then lookup the Barcodes table to get the associated codenum, then lookup the Inventory table using the codenum, and finally fill-in the Product, Size, and Qty/Case columns

OR

2. Have them use the Product ID drop-down (currently a lookup in the field rules for the invoicedet table) and fill in the rest as in #1 above.

I currently have option 2 above working, but would like to add the ability to scan a barcode if the user wants. Am I clear as mud yet ?

I guess what I'm asking is once I scan the bar code into the browse column, how do I get the Product, Size, and Qty/Case columns filled in given that I have to read 2 tables to get them and field rules only lets me do a lookup on a single table?

Thanks again.
Michael