Alpha Video Training
Results 1 to 11 of 11

Thread: update a child table

  1. #1
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default update a child table

    I have a customer table... (doesn't almost everyone?)
    and a child table.. customer items...
    c_customers.dbf with serial id, cus_id
    c_customer_items.dbf with serial id, unit_table_I_D
    and a linking field unit_cus_I_D

    due to the complexity of the sets, I need to seed the customer items table with a dummy record for all of the customer records who do not (yet) have an item record.
    (Lack of item record is due to the lack of info about some of the customers. This will be resolved over time as the customers repeat..)

    In order to complete appointments and schedules tables, each customer has to have at least one record which contains 'adders' to be posted to appts and/or scheds.....

    Basically, I understand how to open the customer table, 'grab' the id # and then make a new record in the cus item table...
    What I don't know how to do is the check for an existing record under that customer....

    If someone could give me a place to research or a basic outline of what must be done to accomplish this, I would appreciate it immensely.

    D

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

    Default RE: update a child table

    Richard,

    A quick way to do this would be to use Key_Exist() to determine if an index key is present in the child table's serial id index.

    A slow way would be to open another instance of the child table, set the index to serial id, and do a fetch_find(). If successful you know you have a record, if fails you know you don't.

    -- tom

  3. #3
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default RE: update a child table

    Ok thanx
    I have to 'run thru' the customer table and check each cus id for a key in the customer item table....

    thought I'd use something like:

    while .not. eof
    if .not. item_cus_id = cus_id
    fill in the dummy record
    else
    next record in cus table
    end while

    gonna take a bit of 'mental exercise' and research, but I think I can come up with the code to make it work.....

    thanx again
    D

  4. #4
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default RE: update a child table

    oooooooooops
    I misread what you said...
    I can't use the serial id # in the cus_item table, I have to use the linking field, which matches the cus_id #....
    Got the basic idea, tho, so I'll work on it over the weekend...

    See ya later
    D

  5. #5
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default RE: update a child table

    Probably can use exist() rather than key_exist()....
    that way I can check the linking field in the child table by field name....
    with both tables open, I can use the cus_id to check against the existence of unit_cus_I_D = to cus_id in the child table....
    thanx for the 'point in the right direction'!!
    D

  6. #6
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default RE: update a child table

    Here's the first try at the table update.
    I don't want to try it until I get some idea of how 'good' or 'bug free' it is....
    It compiled error free but.....???

    Any comments will be appreciated.
    D

    'dim all the variables
    'dim shared test_id as C

    'first open table c_customers.dbf
    'next open table c_customer_items.dbf
    tbl1="c_customers"
    tbl2="c_customer_items"
    tbl1.open()
    tbl2.open()
    ' table.c_customers_fetch_first()
    tbl1.fetch_first()
    while .not. tbl1.fetch_eof()
    ' test_id = cus_id
    if exist (tbl1.cus_ID,"c_customer_items.dbf","unit_cus_I_D")
    tbl1.fetch_next()
    else
    tbl2.new_record()
    tbl2.unit_cus_I_D=tbl1.cus_id
    tbl2.unit_name="XXXXXX"
    tbl2.groom_load=2
    tbl2.bathe_load=1
    tbl2.board_load=1
    tbl2.groom_price=35
    tbl2.bathe_price=15
    tbl2.board_price=10
    tbl2.commit(.t.)
    tbl1.fetch_next()
    end if
    end while
    'update indexes
    'xxxxx.update()
    dim tbl as P
    dim idx as P

    tbl = tbl2
    idx = tbl2.index_open(A_DB_CURRENT_PATH + "customer_temp.cdx")

    'Index update requires exclusive access to the table.
    'If you do not have exclusive access, an error is generated.

    on error goto error_handler
    idx.update()
    end

    error_handler:
    ui_msg_box("Error","Index was not updated. Cannot get exclusive access to table", UI_STOP_SYMBOL)
    resume next



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

    Default RE: update a child table

    Richard,

    the general idea looks about right.

    1) don't you want resume 0 instead of resume next?

    2) The sequence used to open the two tables looks wrong.

    tbl1="c_customers" 'assigns char string to var
    tbl2="c_customer_items" 'ditto
    tbl1.open() 'syntax wrong ????
    tbl2.open() 'syntax wrong ????

    Check the docs for correct syntax on

    Table.open()

    3) Your script needs to clean up after itself. In its present for it would leave the two tables open (once you fix the table.open() statements. Remember to close them both before your script ends.

    4) Use debug(1) to step through the script

    -- t

  8. #8
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default RE: update a child table

    OK, I now see that!
    next crazy idea....
    if I open a form that is based on a set containing both of these tables, then they are both automatically opened...?
    If I resync the form after each 'fetch' (action or not) would I be able to see the records being displayed on the form.???? Albeit maybe too fast to read...????
    This, to me, would be good cuz I could see that the script is working, and then later, just open the form 'hidden'. This would also assure that all the field rules are in use during the formation of new records....
    D

  9. #9
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default RE: update a child table

    Well the debugger has driven me crazy long enough...
    No matter what I do, I keep getting 'variable mis-match' on the first line of the code... and it doesn't seem to matter if I change the code.... Even a 'dim' statement gives me the error....
    Goin' nuts,
    looking into it
    D

  10. #10
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default RE: update a child table

    finally got it to work and it did fine -- in a way....
    for every customer that DID NOT HAVE a customer item entry, I was able to insert a new record in the customer item table with 5 fields filled in.... these 5 fields did not have any data in them at the time of the 'update'...
    However, I tried to change the data in 3 other fields and got the 'subelement cannot be assigned to' error... The fields are numeric.

    Sooooooooo
    I re-wrote the script using tbl2.change_begin(), etc and re-ran the script.... got the same error....

    Earlier, I had run an update operation to 'insert a calculation' into these same three fields. Is there some kind of conflict here...

    the update script is below....
    D


    'Date Created: 08-Oct-2004 09:16:53 PM
    'Last Updated: 09-Oct-2004 05:30:05 PM
    'Created By : Administrator
    'Updated By : Administrator

    dim tbl2 as p

    tbl2=table.open("c_customer_items")

    tbl2.fetch_first()
    while .not. tbl2.fetch_eof()
    tbl2.change_begin()
    tbl2.unit_groom_load=3
    tbl2.unit_bathe_load=1
    tbl2.unit_board_load=1
    tbl2.change_end()
    tbl2.commit()
    tbl2.fetch_next()
    end while
    end

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

    Default RE: update a child table

    The "tbl.commit()" is not needed and is probably the thing generating the error.

    Read the text explaining how change_begin() and change_end() work. You'll find that commit() is not necessary. In fact, it's a method of form objects, not table objects.

    -- t

Similar Threads

  1. Parent table based on query of child table.
    By edward.mattison@scbhn.org in forum Alpha Five Version 5
    Replies: 1
    Last Post: 02-25-2005, 04:14 PM
  2. Update child records
    By Mary Nickerson in forum Alpha Five Version 6
    Replies: 12
    Last Post: 02-09-2005, 12:49 PM
  3. Update Record Operation - child table field
    By C Croscutt in forum Alpha Five Version 5
    Replies: 4
    Last Post: 02-04-2003, 04:42 AM
  4. Posting Parent table fields to Child table
    By Jimmie1234 in forum Alpha Five Version 5
    Replies: 1
    Last Post: 09-11-2002, 01:41 PM
  5. UPDATE CHILD
    By Peter Lear in forum Alpha Five Version 4
    Replies: 9
    Last Post: 11-06-2000, 03:27 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
  •