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

How to delete orphaned records in a linked table?

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

  • How to delete orphaned records in a linked table?

    I have three tables (and I'm still on DBF)
    b_tasks
    b_task_2_file
    b_files

    The middle one is a many-to-many - associates file records with task records.

    b_tasks has a primary key of task_id, that's auto-increment

    My dilemma is that if we delete a task that already had an associated record in b_task_2_file, when we do a new task it uses that next number in line via the auto-increment, which then associates it with any leftover records in b_task_2_file. Basically, deleting a record in b_tasks is leaving orphans in b_task_2_file. I know I once saw a way to deal with that, but I can't remember even what kind of thing it was. Can somebody point me in the right direction? I assume this has to be a Callback of some sort?

    ophan_being_reused.png

    b_tasks.png

    task-2-file.png
    Wendy Welton
    Architect
    past & future Alphaholic - deliberately falling off the wagon!

    http://www.artformhomeplans.com/

  • #2
    Re: How to delete orphaned records in a linked table?

    I'm wondering how this happens, doesn't your next new b_task get the next autoincrement #?

    e.g. if you have items
    1
    2
    3
    4
    5

    and you delete 3, you get
    1
    2
    4
    5

    now if you add a new btask you should get 6.

    What am I missing?
    Peter
    AlphaBase Solutions, LLC

    [email protected]
    https://www.alphabasesolutions.com


    Comment


    • #3
      Re: How to delete orphaned records in a linked table?

      In your example, if I delete 3, the next record would indeed still be 6. But if I delete 5, the next record is then 5. That's when we run into trouble.
      Wendy Welton
      Architect
      past & future Alphaholic - deliberately falling off the wagon!

      http://www.artformhomeplans.com/

      Comment


      • #4
        Re: How to delete orphaned records in a linked table?

        Forgot about that scenario. Typically you would apply referential integrity in a set or in sql. You can code it of course, but... Otherwise can you use a unique key to link on that's not autoinc?
        Peter
        AlphaBase Solutions, LLC

        [email protected]
        https://www.alphabasesolutions.com


        Comment


        • #5
          Re: How to delete orphaned records in a linked table?

          Depending on the integrity you want to have of your data you could create a separate table in which you keep system/application wide preferences. One of the fields would be the last used ID for a given table. Instead of using an auto increment number in your table you'd then be using a calculated field with a user defined function that simply increments the systempreferences table field and then returns that value.
          Frank

          Tell me and I'll forget; show me and I may remember; involve me and I'll understand

          Comment


          • #6
            Re: How to delete orphaned records in a linked table?

            OK - so I'm hearing that there isn't some simple clear way to have it reach through and delete those records in the secondary table. I'm either going to

            1 - disallow delete, and make a field called Delete instead - then filter all displays for Delete <> .t. or
            2 - something else clever that I haven't thought of yet (and probably won't - 'cause I kind like my fake delete idea)

            Using a UUID type PK would work also, as Frank seems to suggest - I'm using those in several places and do know how to code that. I just want simple sequential numbering for my Task records.
            Wendy Welton
            Architect
            past & future Alphaholic - deliberately falling off the wagon!

            http://www.artformhomeplans.com/

            Comment


            • #7
              Re: How to delete orphaned records in a linked table?

              I have an auto increment in MySQL, and no matter what, if I delete 5, (the highest number in the list,) it will still make the next number 6. I was doing some testing that involved repeatedly inserting data, and then deleting it. My table contained no records the last time I did this, and the first number it entered into the record was over 140. So at least MySQL keeps track of the numbers used.

              Also, while I have not set up anything like this, you should be able to set rules in your database to automatically delete child records when a parent is deleted to prevent orphan records. I haven't set up anything like this in my database, so I don't know how to go about doing this, but I know it can be done. Involves using foreign keys. (Maybe a bad idea, but I have no foreign keys in my database.)

              Comment


              • #8
                Re: How to delete orphaned records in a linked table?

                Wendy I am running into the exact same problem in my police records management application. The user deletes the parent record, but not the child records. Next user creates a new parent record, and all the previous child records get linked to it, because they are sharing the same autoincrement linkingkey.

                Did you ever figure out a way to have one delete button or checkbox delete the parent record and all the child records?

                I know this can be done easily on the desktop side, but I don't see a way to do it on the web.
                Sergeant Richard Hartnett
                Hyattsville City Police Department
                Maryland

                Comment


                • #9
                  Re: How to delete orphaned records in a linked table?

                  I don't use dbfs but a way to do it would be put the code to delete the child records before the parent record is deleted in the CanDeleteRecord event of the parent grid that contains the record you want to delete. The mySql code would be something like delete from ChildTable where ChildTable.LinkingID = ParentTable.LinkingID have some code that returns success then go ahead and delete the parent record.

                  In the old VFP days you could set the table to have referential integrity that would cascade the deletes and the updates to the child records but that required a database container i don't think you have that with alpha.

                  With MySql I just set up a forein key and have the deletes cascaded in the child table. MySql handles all the work.
                  Win 10 64 Development, Win 7 64 WAS 11-1, 2, Win 10 64 AA-1,2, MySql, dbForge Studio The Best MySQL GUI Tool IMHO. http://www.devart.com/dbforge/mysql/studio/

                  Comment


                  • #10
                    Re: How to delete orphaned records in a linked table?

                    Thanks Frank. I believe Alpha does in fact have referential integrity, but it is only honored on the desktop side, whereas all my applications are web based.

                    Maybe Wendy has found a solution.

                    -- Rich
                    Sergeant Richard Hartnett
                    Hyattsville City Police Department
                    Maryland

                    Comment


                    • #11
                      Re: How to delete orphaned records in a linked table?

                      With MySql I just set up a forein key and have the deletes cascaded in the child table. MySql handles all the work.

                      this is true in Microsoft SQL too
                      MSQL since 2010
                      A5V11 since Feb 2012

                      Comment


                      • #12
                        Re: How to delete orphaned records in a linked table?

                        Rich

                        Actually allowing a delete with an autoincrement id will cause issues of the record with the largest id is deleted.

                        Alternative is to mark as deleted and filtered accordingly when displaying. ( show that it is deleted and allow an undelete.)

                        or use an alternate custom id system that keeps incrementing from a separate indicator.

                        If the parent is truely gone, create an inverted set and the orphans won't link to a parent and can be deleted by select where the parent id field is blank.
                        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.
                        "Make it as simple as possible, but not simpler."
                        Albert Einstein

                        http://www.iadn.com/images/media/iadn_member.png

                        Comment

                        Working...
                        X