Alpha Video Training
Results 1 to 6 of 6

Thread: Retrieve values from another table

  1. #1
    Member
    Real Name
    Mark Hajny
    Join Date
    Jul 2009
    Posts
    5

    Default Retrieve values from another table

    I must have my search terminology wrong because I cant find this answer and it is probably an easy thing to do.

    I have a customer table, with state tax code in it. When I go to create an invoice for this customer, I pull down a combo box and pick my customer. Once I do this, I need the state tax code in the invoice header file to be updated with the state tax code from the selected customer.

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Retrieve values from another table

    That's called a lookup. There are several ways to use lookups depending on what you want to do with the result.
    There can be only one.

  3. #3
    Member MikeData's Avatar
    Real Name
    Mike Data
    Join Date
    Nov 2008
    Posts
    923

    Default Re: Retrieve values from another table

    Is the code A tax amount?
    If so, all you have to do is add more fill-in choices to your dropdown box.

  4. #4
    Member
    Real Name
    Mark Hajny
    Join Date
    Jul 2009
    Posts
    5

    Default Re: Retrieve values from another table

    Thanks to all who helped. I solved it by coding the following in the OnChange event of my customer drop-down record list:

    :frmInvoiceHeader:state_tax_code.value = lookup("customer.dbf","Customer_Id="+:frmInvoiceHeader:customer_id.value,"State_tax_code")
    :frmInvoiceHeader:city_tax_code.value = lookup("customer.dbf","Customer_Id="+:frmInvoiceHeader:customer_id.value,"City_tax_code")
    :frmInvoiceHeader:other_tax_code.value = lookup("customer.dbf","Customer_Id="+:frmInvoiceHeader:customer_id.value,"Other_tax_code")
    :frmInvoiceHeader:Terms_days.value = lookup("customer.dbf","Customer_Id="+:frmInvoiceHeader:customer_id.value,"Due_days")


    This works. But if there is a better way please advise.

  5. #5
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,620

    Default Re: Retrieve values from another table

    Quote Originally Posted by markh28 View Post
    I must have my search terminology wrong because I cant find this answer and it is probably an easy thing to do.

    I have a customer table, with state tax code in it. When I go to create an invoice for this customer, I pull down a combo box and pick my customer. Once I do this, I need the state tax code in the invoice header file to be updated with the state tax code from the selected customer.
    Have you tried a field rule lookup and assigned values from the lookup table like the example from AlphaSports attached?

    Note that when a product is picked the retail price is placed in the invoice_items price field.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  6. #6
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,198

    Default Re: Retrieve values from another table

    What Al wrote gets you there as directly as possible. Even so, I would like to make this one comment. Your code uses lookup() 4 times. This opens a table 4 times. If you are needing to gather several fields of record data, I would recommend opening the table once and getting all the data with that one opening:

    Code:
    ' get customer_id selected
    	dim vcid as C
    	vcid=topparent:customer_id.value
    
    ' open table filtered to the customer_id and fill in the form fields 	dim t as p
    	t=table.open("customer")
    	query.filter="Customer_Id="+quote(vcid)
    	query.order=""
    	xi=t.query_create()
    		topparent:state_tax_code.value = t.State_tax_code
    		topparent:city_tax_code.value = t.City_tax_code
    		topparent:Other_tax_code.value = t.Other_tax_code
    		topparent:Terms_days.value = t.Due_days
    	xi.drop()
    	t.close()
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

Similar Threads

  1. Get Values from another Table
    By vellj001 in forum Alpha Five Version 9 - Desktop Applications
    Replies: 4
    Last Post: 05-19-2009, 12:36 PM
  2. How do I retrieve values
    By Mike Reed in forum Application Server Version 8
    Replies: 2
    Last Post: 01-24-2009, 09:14 PM
  3. MySQL Active link table - displaying values of a table in a listbox
    By Slap in forum Alpha Five Version 9 - Desktop Applications
    Replies: 4
    Last Post: 09-18-2008, 10:06 AM
  4. calculating values from another table
    By foodminer in forum Alpha Five Version 8
    Replies: 3
    Last Post: 03-22-2007, 06:35 PM
  5. Linking values from table 1 to table 2
    By Tom Sullivan in forum Alpha Five Version 4
    Replies: 8
    Last Post: 04-18-2000, 01:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •