Alpha Video Training
Results 1 to 2 of 2

Thread: Need to Lookup and Pick 2 Value from a table

  1. #1
    Member
    Real Name
    Bruce Roberson
    Join Date
    Mar 2011
    Posts
    88

    Default Need to Lookup and Pick 2 Value from a table

    I am trying to see how to do a post operation based on the scenario below. I don't care if its a post operation or not but I need to bring in a rate and an exemption type.

    I have a transactional file with four fields in question for the sake of discussion: Property, Sales Month, Tax Rate, Exemption Type
    The sales month uses a six character representation of the year and month, ie YYYYMM.
    Call this Table1, record 1 initially has these values for the first record.

    Property Sales Month Tax Rate Exempt Type
    Property1 201601

    I need to pull in the tax rate and the exempt type out of Table2 by first matching the value from Property in each table, and by looking up the tax rate and exempt type in table 2 from each of the effective months below. So the correct answer is to match up 201601 as being greater than 201509 but less than 201602, so the Tax Rate should be .035 and the Exempt Type should be 11 in this case.

    In another table (call it Table2), I have records similar to below:
    Property, Eff Month, Tax Rate, Exemption Type

    The Eff Month has the same 6 digit character set for year and month, also YYYYMM.
    So, table2 could have records like this.

    Property Eff Month Tax Rate Exempt Type
    Property1 201412 0.075 Null
    Property1 201505 0.053 5
    Property1 201509 0.035 11
    Property1 201602 0.053 5


    Thanks for your help....

    Bruce

  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: Need to Lookup and Pick 2 Value from a table

    A post operation, replacing, should do it with both fields to post having the condition salesmonth <=effmonth.

    Each property matching record will replace the taxrate and exempttype fields until the last one where salesmonth <=effmonth is found, the rest of the property matching records will be skipped.

    I changed the field names you indicated because you don't have fields named Sales Month, Tax Rate, Exempt Type, or Eff Month. Why not use the real names?
    There can be only one.

Similar Threads

  1. How to randomly pick records from a table and display on a list?
    By JetLi in forum Mobile & Browser Applications
    Replies: 1
    Last Post: 05-06-2016, 09:16 AM
  2. Table lookup issue with image reference file being stored in child table of a set. Weird or what?
    By preston2 in forum Alpha Five Version 11 - Desktop Applications
    Replies: 6
    Last Post: 01-08-2016, 10:26 AM
  3. Lookup with dynamic filter in child table using field from parent table
    By Fisher in forum Application Server Version 11 - Web/Browser Applications
    Replies: 4
    Last Post: 11-11-2011, 08:06 AM
  4. Force users to use Pick List-Lookup's
    By cavj1 in forum Application Server Version 10 - Web/Browser Applications
    Replies: 4
    Last Post: 11-03-2009, 05:00 PM
  5. filtering a table lookup based on a field in the current table.
    By flord in forum Alpha Five Version 9 - Desktop Applications
    Replies: 3
    Last Post: 07-25-2008, 04:22 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
  •