Alpha DevCon 2018
Results 1 to 7 of 7

Thread: Field Rule lookup() not working?? Looking up character field from another table

  1. #1
    Member
    Real Name
    JoAnn Ristau
    Join Date
    Jul 2001
    Location
    Texas
    Posts
    188

    Default Field Rule lookup() not working?? Looking up character field from another table

    I created a field rule to look up the period_name field from one table based on date fields (converted to serial) in the table. Here is the code:

    LOOKUP("gl_periods.dbf","Docdateserial>=S_Start_Dt.and.docdateserial<=S_End_Dt","period_name")

    For example, the the fields used for dates in gl_periods are serial (i.e, s_start_dt = 20110102 and s_end_dt = 20110128). The field in the row in the detail table (docdateserial) contains the serialized date for 01-10-2011 or 20110110 .

    When I try to recalc the field rules, it errors out with:

    No such field.

    The field names for each table are correct in the lookup statement.

    I hope someone has some ideas and can help me get this field rule to work. Thanks!!
    Land of the Free, Because of the Brave
    Support our US Military

  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: Field Rule lookup() not working?? Looking up character field from another table

    Code:
    s_start_dt = "20110102" 
    s_end_dt = "20110128"
    
    
    ? "Docdateserial>=S_Start_Dt .and. docdateserial<=S_End_Dt"
    = "Docdateserial>=S_Start_Dt .and. docdateserial<=S_End_Dt"
    
    ? "Docdateserial>="+quote(S_Start_Dt)+" .and. docdateserial<="+quote(S_End_Dt)
    = Docdateserial>="20110102" .and. docdateserial<="20110128"
    Whole function

    Code:
    LOOKUP("gl_periods.dbf","Docdateserial>="+quote(S_Start_Dt)+" .and. docdateserial<="+quote(S_End_Dt),"period_name")
    If the start and end values are variables you should use

    Code:
    LOOKUP("gl_periods.dbf","Docdateserial>="+quote(var->S_Start_Dt)+" .and. docdateserial<="+quote(var->S_End_Dt),"period_name")
    There can be only one.

  3. #3
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,300

    Default Re: Field Rule lookup() not working?? Looking up character field from another table

    If Stan's syntax is mysterious, take a close look at the examples here:

    http://wiki.alphasoftware.com/Writing+Expressions

  4. #4
    Member
    Real Name
    JoAnn Ristau
    Join Date
    Jul 2001
    Location
    Texas
    Posts
    188

    Default Re: Field Rule lookup() not working?? Looking up character field from another table

    Thanks, however I still get the no such field error.
    I'm attaching some screenshots that may help.
    Land of the Free, Because of the Brave
    Support our US Military

  5. #5
    Member
    Real Name
    JoAnn Ristau
    Join Date
    Jul 2001
    Location
    Texas
    Posts
    188

    Default Re: Field Rule lookup() not working?? Looking up character field from another table

    I started out using the following code, which I use to find the current period_name, as a guide, replacing date() with date fields from pfl_detail table. As soon as I change date() to a date field from the table, it shows 'invalid or incomplete expression' in the field rule editor.

    LOOKUP("gl_periods.dbf","dtos(Start_Date_Dt)<=dtos(date()).and.dtos(End_date_dt)>=dtos(date())","Period_name")

    The above code works in the field rule editor, returning the JAN-11 period name.
    Land of the Free, Because of the Brave
    Support our US Military

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

    Default Re: Field Rule lookup() not working?? Looking up character field from another table

    When creating a field rule for the pl_name_order field in the pfl_detail table, Alpha can see the docdateserial field, not the s_start_dt or s_end_dt fields. The lookup, since it is based on the gl_periods table, knows what you mean by s_start_dt and s_end_dt.

    Code:
    lookup("gl_periods","s_start_dt >= "+quote(Docdateserial)+" .and. s_end_dt <="+quote(Docdateserial),"Period_name")
    In this usage Docdateserial refers to the value in the docdateserial field of the current record.

    The general construction of a lookup() is

    lookup("sometablename","somefield_in_sometablename = something","someotherfield")

    in your expression somefield_in_sometablename was in the wrong table.
    There can be only one.

  7. #7
    Member
    Real Name
    JoAnn Ristau
    Join Date
    Jul 2001
    Location
    Texas
    Posts
    188

    Default Re: Field Rule lookup() not working?? Looking up character field from another table

    Ah, now it makes sense. Thanks so much! It is now working... :-)
    Land of the Free, Because of the Brave
    Support our US Military

Similar Threads

  1. Field rule Lookup not working in 1764-5103
    By Tbaker in forum Alpha Five Version 8
    Replies: 5
    Last Post: 09-05-2007, 10:51 AM
  2. Field Rule (Lookup table) problem
    By cadmen in forum Alpha Five Version 5
    Replies: 3
    Last Post: 09-04-2002, 07:04 AM
  3. Field Rule Table Lookup
    By Peter.Greulich in forum Alpha Five Version 4
    Replies: 9
    Last Post: 03-30-2002, 08:48 AM
  4. Field Rule Table Lookup
    By JayMoskovitz in forum Alpha Five Version 4
    Replies: 4
    Last Post: 10-18-2001, 06:20 AM
  5. Field Rule Table Lookup
    By Peter.Greulich in forum Alpha Five Version 4
    Replies: 4
    Last Post: 10-09-2001, 11:32 AM

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
  •