Alpha Video Training
Results 1 to 16 of 16

Thread: Deleting multiple browse records

  1. #1
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Deleting multiple browse records

    Hi All

    I have a stock order form that contains a browse with all of the parts details for that order.

    This form is built on a set based on two tables, data_stock_order and data_stock_order_parts, with a one to many connection linked by the order id (field name or_id).

    I'm looking to create a button within the form that deletes all of the parts in the browse.

    I tried using the below code, but it only deletes the highlighted parts record.

    Code:
    topparent:Control_browse1.delete_record()
    Any advise on how to best build this button?

  2. #2
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,369

    Default Re: Deleting multiple browse records

    What happens if you highlight the records you want to delete and then do it?
    Option 2 is to delete the parent and that will take the children with it if you have set Referential Integrity ON.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  3. #3
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Deleting multiple browse records

    Quote Originally Posted by Ted Giles View Post
    What happens if you highlight the records you want to delete and then do it?
    Option 2 is to delete the parent and that will take the children with it if you have set Referential Integrity ON.
    Hi Ted

    I can create a button that will delete whatever browse record that is highlighted.

    Other then turning on the Referential Integrity, is there a way to modify the button to delete all related child records?

  4. #4
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,369

    Default Re: Deleting multiple browse records

    Quote Originally Posted by ADCO Service View Post
    Hi Ted

    I can create a button that will delete whatever browse record that is highlighted.

    Other then turning on the Referential Integrity, is there a way to modify the button to delete all related child records?
    I meant selecting the records by hlding down the Up Arrow (Upper Case arrow) and draging the mouse over the records or using down arrow (navigation arrow) to highlight them and then deleting.
    Plan B would be to delete the parent. Is there any reason to keep the parent?
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  5. #5
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,369

    Default Re: Deleting multiple browse records

    I'm losing the plot!
    Put a logical field in the table populating the browse - say .t. or .f.
    Default to True
    Put a button on the form which changes the true to false.
    Delete all records where Child ID = Parent ID AND logical field = False.

    So, pushing the button updates all child records for this order and you can then run a delete.
    You will still be left with the parent though.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

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

    Default Re: Deleting multiple browse records

    Code:
    'get the linking field value from the parent table
    dim tp as c
    tp=table.get("data_stock_order")
    
    'open the child table filtered to the linking field and loop through deleting the records
    dim tc as p
    tc=table.open("data_stock_order_parts")
    xi=tc.order("","or_id="+quote(tp.ord_id))
    tc.fetch_first()
    while .not. tc.fetch_eof()
    tc.change_begin()
    tc.delete()
    tc.change_end(.t.)
    end while
    tc.close()
    
    parentform:refresh_layout()
    or

    Code:
    'get the linking field value from the parent table
    dim vfilter as C
    dim tp as c
    tp=table.get("data_stock_order")
    vfilter="or_id="+quote(tp.ord_id)
    
    'open the child table and delete the range of records defined  by the filter for linking field value
    dim tc as p
    tc=table.open("data_stock_order_parts")
    tc.delete_range(vfilter)
    tc.close()
    
    parentform:refresh_layout()
    FROM THE DOCUMENTATION:
    Note : Do not use <TBL>.DELETE_RANGE()in a multi-user environment where other users may lock records within the range.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  7. #7
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Deleting multiple browse records

    Thanks Mike!

    Your code worked really nicely.

    Sorry for the late reply.

  8. #8
    Member
    Real Name
    Renny Jacob
    Join Date
    Mar 2012
    Location
    Dubai
    Posts
    117

    Default Re: Deleting multiple browse records

    How do I go through all records (in a loop) of the browse and change a field value

    Journal_frm:browse1.Fetch_First()
    Journal_frm:browse1:row_deleted.value=.t.
    Journal_frm:browse1.Fetch_Next()
    Journal_frm:browse1:row_deleted.value=.t.
    Journal_frm:browse1.Fetch_last()
    Journal_frm:browse1:row_deleted.value=.t.
    Journal_frm:browse1.Refresh_Browse()

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

    Default Re: Deleting multiple browse records

    row_deleted is a field in the browse?

    There is no .fetch_eof() method for a browse so you'll have to operate on the table shown in the browse. See Mike's first code section above and modify it with your table names.

  10. #10
    Member
    Real Name
    Renny Jacob
    Join Date
    Mar 2012
    Location
    Dubai
    Posts
    117

    Default Re: Deleting multiple browse records

    Stan,
    What about Fetch_last()

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

    Default Re: Deleting multiple browse records

    .fetch_last moves to the last record in the browse. It can't be used for checking whether you are trying to fetch past the end of the browse.

    That said, trying to fetch past the end of the browse doesn't throw an error like trying to fetch past the end of a table.

    Code:
    parentform.Browse1.activate()
    parentform:Browse1.fetch_first()
    for qx = 1 to 100
    	parentform:Browse1.change_mode()
    	parentform:Browse1:row_deleted.value=.t.
    	parentform:Browse1.fetch_next()
    next qx
    might be inefficient but should work if you never have more than 100 child records in the browse.

    You could use A5_GET_RECORDS_IN_QUERY() to count the child records and feed the result to the for loop.

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

    Default Re: Deleting multiple browse records

    Oops,
    rereading this thread, I see that the first lines in my code are wrong, as I have dimmed the pointer variable as C, and should be P
    Code:
    'get the linking field value from the parent table
    dim tp as P
    tp=table.get("data_stock_order")
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  13. #13
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: Deleting multiple browse records

    This works on all records in a browse
    Code:
    p=obj("browse1")
    p.Fetch_Last() ' to enable getting the number from the browse row number
    nrecs=p.Get_Viewport_Row()
    p.Fetch_First()
    for i=1 to nrecs 'instead of while fetch_last()
    	browse1:ledger.value = "SALES"
    	p.Fetch_Next()  'Fetches the next record.
    next
    In the case of the row being deleted I will look at a variation on this.
    Last edited by Ray in Capetown; 11-15-2012 at 03:09 AM.

  14. #14
    Member
    Real Name
    Renny Jacob
    Join Date
    Mar 2012
    Location
    Dubai
    Posts
    117

    Default Re: Deleting multiple browse records

    Thank you Ray, That is what exactly I was looking for.
    Only issue is, it adds an extra blank row in the browse.

    Renny

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

    Default Re: Deleting multiple browse records

    Try for i=1 to nrecs-1 ?

    Or try this to replace the p.fetch_next()

    if i<nrecs
    p.fetch_next()
    end if
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  16. #16
    Member
    Real Name
    Renny Jacob
    Join Date
    Mar 2012
    Location
    Dubai
    Posts
    117

    Default Re: Deleting multiple browse records

    Quote Originally Posted by Mike Wilson View Post
    if i<nrecs
    p.fetch_next()
    end if
    Perfect, this did the trick.
    Thank you once again.

Similar Threads

  1. deleting multiple rows in the default browse problem
    By martinwcole in forum Alpha Five Version 10 - Desktop Applications
    Replies: 2
    Last Post: 07-08-2010, 08:32 PM
  2. Deleting Multiple Records in Embedded Browse
    By enstorms in forum Alpha Five Version 9 - Desktop Applications
    Replies: 4
    Last Post: 02-21-2009, 10:34 PM
  3. Deleting records in embedded browse
    By DoubleShufty in forum Alpha Five Version 9 - Desktop Applications
    Replies: 10
    Last Post: 01-20-2009, 03:43 PM
  4. Problem with deleting records within a Browse
    By matrixxrnd@aol.com in forum Alpha Five Version 7
    Replies: 4
    Last Post: 12-30-2005, 11:49 AM
  5. Deleting Multiple Records
    By davidab in forum Alpha Five Version 5
    Replies: 5
    Last Post: 01-09-2004, 06:58 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
  •