Alpha Video Training
Results 1 to 10 of 10

Thread: trouble with global field reference in filter of append opperation

  1. #1
    Member
    Real Name
    Bill Standke
    Join Date
    Apr 2013
    Posts
    14

    Default trouble with global field reference in filter of append opperation

    I'm trying to append rows to a table adding only non-duplicates. I'm doing this by using field1 as a linking field in the transaction and the master and filtering rows of the master by the key associated with the current set of records I want to update. I keep getting an error says that I'm referencing a non-existent field. The xbasic generated looks like this
    where Project_id is a field in the target table ?? The expression was created with the expression builder...

    a_tbl = table.current()
    append.t_db = "e:\users\bill.standke\my documents\alpha five v11\rpd.dbf"
    append.m_key = "SUBJECT_AREA"
    append.t_key = "SUBJECT_AREA"
    append.m_filter = "Project_Id=var->gbProjectID "
    append.t_filter = ""
    append.type = "Unique, replace existing"
    append.m_count = 3
    append.m_case1 = "EITHER"
    append.m_field1 = "SUBJECT_AREA"
    append.m_exp1 = "@RPD->SUBJECT_AREA"
    append.m_case2 = "EITHER"
    append.m_field2 = "PROJECT_ID"
    append.m_exp2 = "Var->gbProjectID"
    append.m_case3 = "EITHER"
    append.m_field3 = "TIMECREATED"
    append.m_exp3 = "Var->gbTimeStamp"
    append.t_count = 0
    a_tbl.append()

  2. #2
    Member nehru's Avatar
    Real Name
    Nehru L. Albania
    Join Date
    Nov 2003
    Location
    Philippines, Manila, Q.C.
    Posts
    152

    Default Re: trouble with global field reference in filter of append opperation

    i have same problem using v11
    capture-20150815-223446.png

    pls. help ^_^

    this method is working in my v8
    Last edited by nehru; 08-15-2015 at 10:35 AM.
    "Knowledge without application is useless."

  3. #3
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,563

    Default Re: trouble with global field reference in filter of append opperation

    Quote Originally Posted by nehru View Post
    i have same problem using v11
    capture-20150815-223446.png

    pls. help ^_^

    this method is working in my v8
    Please show where and when the variable is dimmed.

    Any other code would be good too.
    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.

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

    Default Re: trouble with global field reference in filter of append opperation

    The proper syntax is
    append.m_filter = "Project_Id= " + s_quote(var->gbProjectID)
    Where the variable is character or numerical and the variable is of sufficient scope for the code to see it.

    As shown originally it should work if the variable is dimmed as global and numerical but best practice is to "expose" the variable by moving it out of the quotation marks.
    There can be only one.

  5. #5
    Member nehru's Avatar
    Real Name
    Nehru L. Albania
    Join Date
    Nov 2003
    Location
    Philippines, Manila, Q.C.
    Posts
    152

    Default Re: trouble with global field reference in filter of append opperation

    capture-20150816-140649.png

    created in the operations tab
    the variable vORDNUM is set to GLOBAL in a button before the append operation
    ive try STAN's advice but no luck

    it was running in v8 but not in v11

    ty for helping ^_^

    default xbasic code for append


    DIM Append as P

    a_tbl = table.open("orders det")
    append.t_db = "lnkpas_sms_orders_copy"
    ON ERROR GOTO ERROR16082015162011335


    append.m_key = "alltrim(Order_No)+alltrim(Item_Code)"
    append.t_key = "alltrim(Loc_Code)+alltrim(Item_Code)"
    append.m_filter = "Order_No = Var->vORDNUM"
    append.t_filter = ""
    append.type = "Unique, replace existing"
    append.m_count = 10
    append.m_case1 = "EITHER"
    append.m_field1 = "ITEM_CODE"
    append.m_exp1 = "@LNKPAS_SMS_ORDERS_COPY->Item_Code1"
    append.m_case2 = "EITHER"
    append.m_field2 = "ITEM"
    append.m_exp2 = "@LNKPAS_SMS_ORDERS_COPY->ITEM"
    append.m_case3 = "EITHER"
    append.m_field3 = "UNIT_CODE"
    append.m_exp3 = "@LNKPAS_SMS_ORDERS_COPY->UNIT_CODE"
    append.m_case4 = "EITHER"
    append.m_field4 = "UNIT"
    append.m_exp4 = "@LNKPAS_SMS_ORDERS_COPY->UNIT"
    append.m_case5 = "EITHER"
    append.m_field5 = "DISPLAY"
    append.m_exp5 = "@LNKPAS_SMS_ORDERS_COPY->DISPLAY"
    append.m_case6 = "EITHER"
    append.m_field6 = "COST"
    append.m_exp6 = "@LNKPAS_SMS_ORDERS_COPY->COST"
    append.m_case7 = "EITHER"
    append.m_field7 = "SELLING"
    append.m_exp7 = "@LNKPAS_SMS_ORDERS_COPY->SELLING"
    append.m_case8 = "EITHER"
    append.m_field8 = "COMPANY_CODE"
    append.m_exp8 = "@LNKPAS_SMS_ORDERS_COPY->COMPANY_CODE"
    append.m_case9 = "EITHER"
    append.m_field9 = "DATETIME_ENTRY"
    append.m_exp9 = "@LNKPAS_SMS_ORDERS_COPY->DATETIME_ENTRY"
    append.m_case10 = "EITHER"
    append.m_field10 = "Order_No"
    append.m_exp10 = "@LNKPAS_SMS_ORDERS_COPY->Loc_Code"
    append.t_count = 0

    a_tbl.append()


    GOTO CONTINUE16082015162011335
    ERROR16082015162011335:
    ON ERROR GOTO 0
    ui_msg_box("Error","Error running Append Operation"+crlf()+error_text_get())
    END
    CONTINUE16082015162011335:
    a_tbl.close()
    'If the Operation is run from within a Form or Browse, then refresh the window
    if is_object(topparent.this) then
    if topparent.Class() = "form" .or. topparent.class() = "browse" then
    topparent.Refresh_layout()
    end if
    end if
    Last edited by nehru; 08-16-2015 at 05:54 AM.
    "Knowledge without application is useless."

  6. #6
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,563

    Default Re: trouble with global field reference in filter of append opperation

    Quote Originally Posted by nehru View Post
    ive try STAN's advice but no luck
    Did you try Stan's code in the operation builder or as an xbasic script?

    Stan's option is for an xbasic script. It will work there.
    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.

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

    Default Re: trouble with global field reference in filter of append opperation

    ppend.m_key = "alltrim(Order_No)+alltrim(Item_Code)"
    append.t_key = "alltrim(Loc_Code)+alltrim(Item_Code)"
    append.m_filter = "Order_No = Var->vORDNUM"
    tell us the data types stored in "Order_No", "Item_Code", "Loc_Code", "Item_Code", and "vOrdnum". Your code suggests that the first four fields are "CHARACTER" data, but the way you've written the last line vORDNUM is numeric. Is that what you intend?

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

    Default Re: trouble with global field reference in filter of append opperation

    Bill,

    I have been thinking about your question, and have decided I don't understand your goal. If you succeed in applying a filter to the master table and then append "unique, replace existing" you may be creating lots of duplicates in the master table. When the operation tries to identify whether a transaction record is unique, its "view" of the master table should NOT be limited by a filter. Otherwise, transaction records that don't match the filtered view of any records in the master will be appended, right? Some or all of those may duplicate OTHER records in the master, lying outside the range of your filter.

    I'd argue a more typical approach would filter records in the transaction table, not the master.

  9. #9
    Member nehru's Avatar
    Real Name
    Nehru L. Albania
    Join Date
    Nov 2003
    Location
    Philippines, Manila, Q.C.
    Posts
    152

    Default Re: trouble with global field reference in filter of append opperation

    tnx for the help guys

    my main goal is to append the transaction table to the master table (filtered) <unique, replace existing>
    this is working on A5V8 using default operation append

    first i will try all your advice then i'll post the result

    more power to all of us ^_^

    the Var->ORDNUM is character (10)
    capture-20150816-140649.pngcapture-20150817-161801.pngcapture-20150817-161830.png
    Last edited by nehru; 08-17-2015 at 04:54 AM.
    "Knowledge without application is useless."

  10. #10
    Member nehru's Avatar
    Real Name
    Nehru L. Albania
    Join Date
    Nov 2003
    Location
    Philippines, Manila, Q.C.
    Posts
    152

    Default Re: trouble with global field reference in filter of append opperation

    TOM CONE JR is correct ^_^

    i've create a field ORDER_NO in the transaction table
    IT WORKS!

    in my obervation, when filtering the master table with a field1 and a global variable; the field1 must be also exist in the transaction table
    the message error we discuss above is for the transaction table not in the master (funny) hmm but i place it in under the master filter not in the transaction filter

    anyway problem is solve

    tnx to the effort guys
    tnx for the help
    "Knowledge without application is useless."

Similar Threads

  1. S.O.S! Rookie in trouble doing an Append
    By sgerber in forum Alpha Five Version 11 - Desktop Applications
    Replies: 10
    Last Post: 08-25-2012, 12:56 AM
  2. Trouble getting reference to parent grid field
    By rjackson@arach.net.au in forum Application Server Version 11 - Web/Browser Applications
    Replies: 7
    Last Post: 07-25-2012, 12:26 PM
  3. Global reference in expressions
    By MikeData in forum Alpha Five Version 10 - Desktop Applications
    Replies: 13
    Last Post: 10-19-2010, 08:12 PM
  4. Reference and Filter 1st Char of data field
    By Rudy16 in forum Alpha Five Version 9 - Desktop Applications
    Replies: 2
    Last Post: 10-03-2009, 04:05 PM
  5. Trying to reference Global Variables
    By JBall in forum Alpha Five Version 7
    Replies: 15
    Last Post: 05-07-2008, 07:46 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
  •