Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Need help with deleting 3rd child records while deleting the parent

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Need help with deleting 3rd child records while deleting the parent

    Hello:

    I was able to delete the 2nd child record that matches with the parent but I was not able to delete the information that matches the product table.

    With the delete button I would like to delete all record associated with the ID.

    Thanks.
    Mark
    Attached Files

  • #2
    Re: Need help with deleting 3rd child records while deleting the parent

    Mark,

    On your button code, you did not command to delete 3rd table. you did only "sub" table.
    your key Id were set as "N" type, I think you should use "C" type. I will not write the code for you, but the direction what to do.

    parentform.commit()
    tbl_src= table.current(1)
    if tbl_src.pid = pid.value
    tbl_dest = table.get("sub") '<== you only said the 1 table. did not point the 3rd table.
    tbl_dest.delete_range("Pid = "+Pid.value)
    end if
    tbl_src.delete_record()
    parentform.Resynch()


    'I do this way
    'Find the parent Id value
    vParId = parentform:pId.value
    'or table method

    dim tSub as p
    tSub = table.get("sub") 'or table.current(2)
    'Find the matching records
    'How to? -- See help to know detail
    'If found the records then
    '<tbl>.delete_range() 'will delete all
    'see <tbl>.delete or <tbl>.delete_range

    dim tPrice as p
    tPrice = table.get("price") 'or table.current(3)
    'same thing as above

    'Refresh form and

    end

    Comment


    • #3
      Re: Need help with deleting 3rd child records while deleting the parent

      Mark,

      also, Study about "Primary key and Foreign key".

      Comment


      • #4
        Re: Need help with deleting 3rd child records while deleting the parent

        This solution is probably not what most would use.

        Code:
        parentform.commit()
        prc_list = table.external_record_content_get("sub","alltrim(str(id))","id","pid = "+pid.value)
        tbl_src= table.current(1)
        tbl_prc = table.get("price")
        for each prc_id in prc_list
        tbl_prc.delete_range("id = "+prc_id.value)
        next
        tbl_dest = table.get("sub")
        tbl_dest.delete_range("Pid = "+Pid.value)
        tbl_src.delete_record()
        parentform.Resynch()
        There can be only one.

        Comment


        • #5
          Re: Need help with deleting 3rd child records while deleting the parent

          John
          Your method is made simpler by leaving the primary delete for last
          then all you need is
          Code:
          dim tbl as P
          dim a_tbl as P
          dim b_tbl as P
          
          a_tbl = table.current(2)
          a_tbl.delete_range()
          b_tbl = table.current(3)
          b_tbl.delete_range()
          
          tbl = table.current()
          tbl.change_begin()
          tbl.delete()
          tbl.change_end(.T.)

          Comment


          • #6
            Re: Need help with deleting 3rd child records while deleting the parent

            Ray,

            Thank you.
            Beginner is required the step by step. I am a beginner and prefer the easy way to understand.

            Comment


            • #7
              Re: Need help with deleting 3rd child records while deleting the parent

              Stan, I like your approach.

              I've taken the liberty of adding explanatory comments so folks can get the benefit of your work
              without having to study the example data set presented in this thread.

              Please let me know if I've got anything cross-wise.

              Code:
              'Stan Mathews example deleting linked child and grandchild table records
              
              'This runs from a button on the form
              
              'Main is primary table
              '    Sub is one to many child, linked on Pid fields
              '        Price is one to one grandchild (linked to child) linked on Id fields
              
              
              'save any pending edits
              parentform.commit()
              
              ' build crlf delimited list of "Id" field values from the Sub table, ordered by ID, but
              '      only include Id's if they are in records that contain a Pid that matches
              '      the Pid in the Main table record displayed in the form.  
              prc_list = table.external_record_content_get("sub","alltrim(str(id))","id","pid = "+pid.value)
              
              
              tbl_src= table.current(1)    'get pointer to the primary table in the set supporting the form
              
              tbl_prc = table.get("price") 'get pointer to the grandchild table
              
              
              'delete Price table records by looping through the list of Id's
              '   and for each Id in the list delete any records that contain an ID that matches the current id in the list
              '   NOTE: in the next line the term "prc_id" is an arbitrary placeholder variable that
              '          refers to each item in the list, one at a time.  To get the "value" of that
              '          item you use the "value" property of the placeholder variable.  i.e.  "prc_id.value"
              for each prc_id in prc_list  'Loop through the list of id's
              tbl_prc.delete_range("id = "+prc_id.value)
              next
              
              
              tbl_dest = table.get("sub") 'get pointer to the Sub table 
              
              ' now delete all the records in the Sub table that have the same Pid field
              '     value as appears in the Main table record currently displayed in the form
              tbl_dest.delete_range("Pid = "+Pid.value)
              
              ' and finally, using the pointer to the primary table in the set, delete
              '    the current Main table record
              tbl_src.delete_record()
              
              ' resynch the form as the script ends
              parentform.Resynch()
              Last edited by Tom Cone Jr; 03-23-2013, 05:55 AM.

              Comment


              • #8
                Re: Need help with deleting 3rd child records while deleting the parent

                Tom,

                Nice explanation. The only thing I can think to add is in this line

                tbl_prc.delete_range("id = "+prc_id.value)

                prc_id.value refers to the value property of the prc_id pointer variable established in the for each header line. Since prc_id is not a form or browse field it should be clear that prc_id is not a form or browse object but it won't hurt to explain the derivation.

                Ray,

                Another good approach but don't you need to reverse the sequence?

                a_tbl = table.current(2)
                a_tbl.delete_range()
                b_tbl = table.current(3)
                b_tbl.delete_range()

                If you delete the child records first I don't see how the delete range on the grandchild table will know which records are referenced as they have been orphaned. It may be that you have tested your script and it does work but it seems counterintuitive to me.
                There can be only one.

                Comment


                • #9
                  Re: Need help with deleting 3rd child records while deleting the parent

                  A complete oversight on my part Stan.
                  Absolutely, thanks for checking.
                  I had tested it with table(2) and then just repeated the incantation.

                  Comment


                  • #10
                    Re: Need help with deleting 3rd child records while deleting the parent

                    Errata.
                    In case anyone tries my sample code and misses Stan's valid point, do it in the following order.
                    Code:
                    dim tbl as P
                    dim a_tbl as P
                    dim b_tbl as P
                    
                    b_tbl = table.current(3)
                    b_tbl.delete_range()
                    a_tbl = table.current(2)
                    a_tbl.delete_range()
                    
                    tbl = table.current()
                    tbl.change_begin()
                    tbl.delete()
                    tbl.change_end(.T.)
                    Last edited by Ray in Capetown; 03-23-2013, 02:21 AM.

                    Comment


                    • #11
                      Re: Need help with deleting 3rd child records while deleting the parent

                      Thanks, Stan,

                      I've tweaked the explanation a bit and agree that its now more complete.

                      -- tom

                      Comment


                      • #12
                        Re: Need help with deleting 3rd child records while deleting the parent

                        If you delete the child records first I don't see how the delete range on the grandchild table will know which records
                        If the set is
                        Parent
                        ===>Child
                        =======>Grandchild


                        (1) Get the Key Id from Parent table
                        vKeyId = parentform:KeyId.value

                        (2) Find the matching records from child table
                        tChild = table.current(2)
                        'find the matching records and get the Child Pk before delete it
                        'loop the records
                        vChildId = tchild.Child_id.value

                        (3) Find the matching records from Grand child table.
                        tGrand = table.current(3)
                        'Find the matching records with vChildId
                        tGrand.delete_range()

                        (4) Delete the child table here
                        tChild.change_begin()
                        tChild.delete()
                        tChild.change_end(.t.)
                        End Loop

                        (5) I do not want to delete the parent records.

                        Comment


                        • #13
                          Re: Need help with deleting 3rd child records while deleting the parent

                          Correct, but that's not what Ray's script as posted originally did. I agree that deleting the grandchild records first is mandatory.
                          There can be only one.

                          Comment


                          • #14
                            Re: Need help with deleting 3rd child records while deleting the parent

                            I found a glitch on this.

                            When I press the "Delete" button it deletes records correctly BUT being on the same form the PID (auto increment filed) does not show auto incremeted value.

                            parentform.commit()
                            Code:
                            prc_list = table.external_record_content_get("sub","alltrim(str(id))","id","pid = "+pid.value)
                            tbl_src= table.current(1)    'get pointer to the primary table in the set supporting the form
                            tbl_prc = table.get("price") 'get pointer to the grandchild table
                            for each prc_id in prc_list  'Loop through the list of id's
                            tbl_prc.delete_range("id = "+prc_id.value)
                            next
                            tbl_dest = table.get("sub") 'get pointer to the Sub table 
                            tbl_dest.delete_range("Pid = "+Pid.value)
                            tbl_src.delete_record()
                            parentform.Resynch()
                            Stan, what do you mean by we need to add "tbl_prc.delete_range("id = "+prc_id.value)". where?

                            Thanks all. I was busy with other things.

                            Comment


                            • #15
                              Re: Need help with deleting 3rd child records while deleting the parent

                              My comment to Tom concerning the remarks he added.

                              The only thing I can think to add is in this line ........ (an explanation).
                              There can be only one.

                              Comment

                              Working...
                              X