Alpha Video Training
Results 1 to 5 of 5

Thread: Creating new records from old records

  1. #1
    VAR Pat Bremkamp's Avatar
    Real Name
    Pat Bremkamp
    Join Date
    Apr 2000
    Location
    Oregon, USA
    Posts
    2,645

    Default Creating new records from old records

    I used to know how to do this, but I've forgotten the trick!

    I need to use some records in a table to create additional new records in the same table. So, I filtered the table to get the records I need, then created two pointers to that table, one to read through the old records and one for the new records. Then, I set up a while ... end while loop to read the old records. Works fine, except that the while .not. tbl.fetch_eof() test appears to get to the end of the old records and then continue on with the new records. As a result, I get duplicate new records.

    Odd thing is, I don't get an infinite number of new records...if I read one record, I get two duplicates. If I read 7 records, I get three duplicates...very strange. Any suggestions?

    Pat

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

    Default Why duplicate

    Pat,

    Send you script so we can see it.

    On a different note, why don't you use a copy operation, copy the old records to a Temp table, and then append them back to the Master table?

    Mike W

  3. #3
    VAR Pat Bremkamp's Avatar
    Real Name
    Pat Bremkamp
    Join Date
    Apr 2000
    Location
    Oregon, USA
    Posts
    2,645

    Default Here 'tis

    Mike, thanks for responding.

    The picture attached shows the form. It is a dialog form. My app includes lists of people, companies, places, and things, and this form allows the user to duplicate an existing list either to add to an existing list or to a new list. The code is:

    Code:
     
    '==== get the copy from list Id and name
    vFromListID=left(vCopyFrom,8)
    '==== get copy to list ID
    if left(vCopyTo,8)="99999999"
     '==== if new list
     tbl1=table.open("[PathAlias.ADB_Path]\lists",FILE_RW_SHARED)
     tbl1.enter_begin(.t.)
     tbl1.Li_name=alltrim(vNewListName)
     tbl1.Li_entid=session.__protected__En_Id
     tbl1.Li_entname=session.En_Name
     tbl1.Li_date=date()
     tbl1.enter_end(.t.)
     vToListId=tbl1.Li_id
     vToListName=alltrim(vNewListName)
     tbl1.close()
    else
     '==== if existing list
     vToListId=left(vCopyTo,8)
     vCopyFrom=alltrim(vCopyFrom)
     vToListName=right(vCopyFrom,len(vCopyFrom)-8)
    end if
    '==== copy user field labels <<== this part works fine
    if convert_type(vCopyUDF,"L")=.t.
     '==== copy from
     tbl2=table.open("[PathAlias.ADB_Path]\lists",FILE_RW_SHARED)
     Indx2=tbl2.order("Li_Id","Li_EntId="+s_quote(session.__protected__En_Id))
     tbl2.fetch_find(vFromListID)
     '==== copy to
     tbl3=table.open("[PathAlias.ADB_Path]\lists",FILE_RW_SHARED)
     Indx3=tbl3.order("Li_Id","Li_EntId="+s_quote(session.__protected__En_Id))
     tbl3.fetch_find(vToListID)
     tbl3.change_begin()
     tbl3.Li_pe_ul01=tbl2.Li_pe_ul01
     tbl3.Li_pe_ul02=tbl2.Li_pe_ul02
    
     
     ...and so on...
     
     tbl3.change_end(.t.)
     tbl2.close()
     tbl3.close()
    end if
    '==== copy people      <<== this is where I have trouble
    if convert_type(vCopyPe,"L")=.t.
     '==== copy from
     tbl2=table.open("[PathAlias.ADB_Path]\li2pe",FILE_RW_SHARED)
     Indx2=tbl2.order("","Li_Id="+s_quote(vFromListID))
     tbl2.fetch_first()
     '==== copy to
     tbl3=table.open("[PathAlias.ADB_Path]\li2pe",FILE_RW_SHARED)
     '==== copy all from tbl2
     while .not.tbl2.fetch_eof()
      tbl3.enter_begin(.t.)
      tbl3.Li_Id=vToListID
      tbl3.Li_name=vToListName
      tbl3.Pe_Id=tbl2.Pe_Id
      tbl3.Li_name=vToListName
      tbl3.Pe_Name=tbl2.Pe_Name
      tbl3.En_Id=session.__protected__En_Id
      tbl3.Li2pe_F01=tbl2.li2pe_F01
      ... and so on ...
      tbl3.enter_end(.t.)
      tbl2.fetch_next()
     end while
     tbl3.close()
     tbl2.close()
    end if
    '==== copy companies
    and so on for the companies, places and things...

    Thanks,
    Pat

  4. #4
    VAR Pat Bremkamp's Avatar
    Real Name
    Pat Bremkamp
    Join Date
    Apr 2000
    Location
    Oregon, USA
    Posts
    2,645

    Default

    OK, I got it. i just got a count of the records and used a For ...Next loop instead of a While ...End While loop.

    sometimes the easy things are the hardest to see!

    Pat

  5. #5
    Former Alpha Employee Edward Larrabee's Avatar
    Real Name
    Edward Larrabee
    Join Date
    Nov 2002
    Location
    New Hampshire
    Posts
    1,385

    Default

    The way to use a WHILE loop when reading through a table is:

    Code:
    tbl = table.open("mytable")
    tbl.fetch_first()
    
    while .not. tbl.fetch_eof()
        ...do stuff
        tbl.fetch_next()
    end while
    
    tbl.close()

Similar Threads

  1. Replies: 7
    Last Post: 03-03-2006, 01:07 PM
  2. Creating Child Records
    By Bob Flanagan in forum Alpha Five Version 5
    Replies: 4
    Last Post: 06-18-2004, 09:39 AM
  3. Estimate Records to Contract/Invoice Records
    By Sean Kennedy in forum Alpha Five Version 5
    Replies: 8
    Last Post: 04-26-2004, 07:00 AM
  4. Need help creating script to add records
    By joannristau in forum Alpha Five Version 5
    Replies: 5
    Last Post: 10-01-2002, 11:37 AM
  5. Creating Multiple Records
    By Sean Tucker in forum Alpha Five Version 4
    Replies: 2
    Last Post: 02-06-2002, 09:35 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
  •