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

Many to Many relationships

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

    Many to Many relationships

    Hi all,

    In A4V6 it was possible to create a many to many relationship through an intersection table. The example was many library books with the same name, being lent to many library subscribers.

    How is this achieved in A5v7?

    Cheryl has had a go at it, I've had a few sleepless nights, (sad I know), trying to create such a link.

    Anyone got any ideas?
    See our Hybrid Option here;
    https://hybridapps.example-software.com/


    Apologies to anyone I haven't managed to upset yet.
    You are held in a queue and I will get to you soon.

    #2
    Re: Many to Many relationships

    Hi Ted,

    I am no expert on A5 (but I'm trying). Nonetheless, I think I see how to do what you describe as your goal if you're asking how to handle this organizationally (functionally). I can't speak to elegant ways Alpha may make this easier than what I describe.

    My suggestion should work for any version of Alpha, since it is a generalized approach.

    Assume you have one table of library books ("books") and one table of library patrons ("patrons"). If you create a table of checked-out books ("check-outs"), you can link check-outs to books and patrons such that your function is performed.

    This can be done by creating a books table where each entry represents a book title rather than a book copy, and each book title has a unique key. Thus, each entry in books may represent more than one copy of that book.

    Additionally, each entry of the patrons table represents a unique person, and has a unique key.

    Finally, the check-outs table represents each instance of a patron checking out a particular book. Thus, the check-outs table contains the unique key of the book being checked out (the key corresponding to the book's entry in the books table) and the unique key of the patron checking the book out (the key corresponding to the patron's entry in the patrons table).

    The relationship between the patrons and check-outs is 1-patron-to-many-checkouts (each patron could check-out any number of books). The relationship between the books and check-outs is one-book-to-many-checkouts (each book title could be checked-out any number of times). The end result is that many patrons can each check-out many books, which is what you desire.

    In effect, what I've described is the intersection table that you said A4V6 could create automatically. If you're willing to create this table manually (once, as described above), populating it presumably would be as easy as A4V6.

    I hope that helps. Let me know if I've misunderstood what you're asking for.

    Regards,
    Dave

    Comment


      #3
      Re: Many to Many relationships

      Ted;
      I'm not sure of your purpose in the many-to-many set, but here is a thought I throw out, which may address your need.

      How about 2 (one-to-many) sets? The first set might be your "book borrowers" table linked to the "books" table, the second set would have the "books" table as the base, linked to the "book borrowers" table.

      Good luck!
      Duncan

      Comment


        #4
        Re: Many to Many relationships

        Hi Ted,

        This should be able to be done by the same method used in A4v6. I have a many to many relationship with people and Groups. The link is Group to Groupx to People. The groupx table is the table with a People id and a group id. Group to Groupx is a one to many and Groupx to People is a one to one matching on first record.

        Hope this helps.
        Dan

        Dan Blank builds Databases
        Skype: danblank

        Comment


          #5
          Re: Many to Many relationships

          Hi Ted,

          I use the same method that Dan has described. Attached is an example of same. The form is built on a set that has two many to many relationships combined. There are two more sets that just reverse the tables in order to get a different view of the data.
          Tim Kiebert
          Eagle Creek Citrus
          A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

          Comment


            #6
            Re: Many to Many relationships

            Thanks everyone - problem still exists.

            I still can't show all books for all borrowers on one screen.

            Best I can do is show all borrowers for ONE book ID, or all books for ONE borrower ID.

            Ho Hum!

            Still I have given the problem to an Access self confessed genius (Ha! Ha!) who assures me it is possible - I can see smoke and the wheels coming off from here!
            See our Hybrid Option here;
            https://hybridapps.example-software.com/


            Apologies to anyone I haven't managed to upset yet.
            You are held in a queue and I will get to you soon.

            Comment


              #7
              Re: Many to Many relationships

              Ted, do you have a sample you could upload with a clear description of what you need. I am not clear on what "show all books for all borrowers on one screen" exactly means.
              Tim Kiebert
              Eagle Creek Citrus
              A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

              Comment


                #8
                Re: Many to Many relationships

                Where should I send it? I have a sample and also a Datflow schema.

                Ted
                See our Hybrid Option here;
                https://hybridapps.example-software.com/


                Apologies to anyone I haven't managed to upset yet.
                You are held in a queue and I will get to you soon.

                Comment


                  #9
                  Re: Many to Many relationships

                  You can attach here to a message using the manage attachments feature while replying.
                  Tim Kiebert
                  Eagle Creek Citrus
                  A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

                  Comment


                    #10
                    Re: Many to Many relationships

                    I still can't show all books for all borrowers on one screen.
                    Go to the table books, and show all books checked.

                    Comment


                      #11
                      Re: Many to Many relationships

                      Ted, there's a similar example that's been prepared for you already. It's the Students and Classes databases in the Templates folder in your Alpha Five program directory. Any student can have multiple classes. Any class can have multiple students. An intermediate table is used in two different set structures to provide different views of the data.

                      Comment


                        #12
                        Re: Many to Many relationships

                        It sounds like you are linking on a book id and showing all users for that one book. If this is your issue, You probably need a second field for "Title ID". Then all books with the same title will have the "same" id field. So then you would establish your link based on that.

                        I sort of think you have some more thinking to do about what you want and how to set this up. I don't know that a many to many link is really your best solution. A book can only be checked out by one user at a time. So basically you do a search based on a title. All the books with that title display and should show the user's id field. Then I would probably include a calc field in the same browse to do a lookup of the user's name based on the user's id.

                        Just a thought.
                        Dan

                        Dan Blank builds Databases
                        Skype: danblank

                        Comment


                          #13
                          Re: Many to Many relationships

                          Ted,

                          Your note about "the problem still exists" suggests you are referring to two separate problems: one is relating books to people and another is displaying those relationships on a single screen. The latter problem was not disclosed before.

                          Nonetheless, if an intersection table were created (see earlier note), you could easily display the contents of that table, representing all checkouts by all people, in a single screen. Thus, both problems would be addressed by a single solution.

                          I get the sense that you prefer to take this in a different direction, though, so I'll let others move the ball forward...

                          Regards,
                          Dave

                          Comment


                            #14
                            Re: Many to Many relationships

                            Originally posted by Ted Giles View Post
                            Thanks everyone - problem still exists.

                            I still can't show all books for all borrowers on one screen.

                            Best I can do is show all borrowers for ONE book ID, or all books for ONE borrower ID.

                            Ho Hum!

                            Still I have given the problem to an Access self confessed genius (Ha! Ha!) who assures me it is possible - I can see smoke and the wheels coming off from here!
                            It sounds like what you want to see on screen is both lists showing all records in each table. What you need is a link field in each table that you populate with an "X". You can then link your set on that field and display 2 browses in your form. Now what do you want to do with these two lists?

                            Here is one I use to "move" machines to a new location.
                            Last edited by MoGrace; 10-17-2007, 11:02 PM.
                            Robin

                            Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

                            Comment


                              #15
                              Re: Many to Many relationships

                              A big thank you to everyone who has (and is still - Tim) contributing.

                              The difficulty I have is describing the problem in sufficient detail, but put simply, and not to use the books example.

                              I have a care provider who has to be reviewed for quality each year.

                              The quality question subjects can vary as they are built by the User

                              The question subjects cover Cleanliness, Staff, Day Activities and many other possibilities as defined by the User.

                              Each question subject has up to 6 questions linked to it.

                              Each question must be unique and might appear in more than one question subject.

                              I'd like to show All question subjects and All questions for a Provider within those subjects on one form.

                              Provider
                              Question Subject 1
                              Question 1
                              Question 2
                              Question 3
                              Question Subject 2
                              Question 4
                              Question 5
                              Question 1

                              Bit like a Group report.
                              See our Hybrid Option here;
                              https://hybridapps.example-software.com/


                              Apologies to anyone I haven't managed to upset yet.
                              You are held in a queue and I will get to you soon.

                              Comment

                              Working...
                              X