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

Multiple Primary Keys soapbox rant

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

    Multiple Primary Keys soapbox rant

    I realize I am the new guy and do not have a thimble full of knowledge compared to most of you, but this is the response I received after doing a bug report and attached video for a problem with Dialogs and multiple primary keys:

    Andrew

    We apologize for the slow response, but there simply isn't enough information for us to test this. Whatever value you are using to link tables, the values must be unique. Using multiple primary keys is very problematic in any database and normally not recommended as it is often very difficult to insure they are unique. We are confident there is no problem in the builder.

    However, the only way we could evaluate your problem is with a test dialog and sample data that shows the problem.

    Really.....very problematic? PostgreSQL seems to handle it quite nicely and has no problems making sure the row is unique via two primary keys. You know PostgreSQL, that free program you download off of the internet. Since PostgreSQL is the backend and handling the details of constraints, etc., why does Alpha even worry about it? My biggest question of all, why does Alpha work fine in some areas, such as row expander, with multiple primary keys, while other areas such as Dialogs with repeating sections not? Therefore, there is a problem in your builder, or you need to copy the code out of row expander into dialog repeating sections. If you can't have multiple primary keys, why are there 5 rows in "Linking Fields" portion of Data Binding? I just can't fathom why it is "very difficult to insure they are unqiue" This seems like database 101 to me.

    So does everybody out there have an autoid column in each table whose only purpose in life is to be a unique identifier?....when the other two columns in the database which have to be there for lookups anyway, say the same thing when combined? This is a problem for me because my database is multitenancy and requires at least two primary keys for every table and therefore will have this unique row link column in every table. What does an Alpha developer do if he/she has a transaction table and didn't allow enough room for expansion of autoid's? Seems like you are building in a Y2K type of problem that someday, next year, next decade, somebody will have to deal with when you run out of room for autoid's?

    Somebody please tell me have this all wrong and I just didn't do _____. I have to go do my breathing exercises and then modify my entire PostgreSQL data model.

    #2
    Re: Multiple Primary Keys soapbox rant

    Post your problem, with a sample (that helps a lot) and we'll have a look.

    Comment


      #3
      Re: Multiple Primary Keys soapbox rant

      Andrew -

      Does your solutions from an earlier post not solve the problem?
      http://msgboard.alphasoftware.com/al...897#post600897

      I just spent the past day or two working with Dialogs. One of my repeating panels is based on a table that is simply 2 ids, and so together, they are identified as PKs. I'm using a MSQL back end, and not experiencing any issues.

      What problem are you running into with a repeating panel that is based on multiple columns creating the PK?

      I noticed while linking tables in Dialog binding that there were explanatory notes about using multiple pipings ||| when linking multiple primary keys and that there is importance in terms of the order in which the columns are selected.

      Since I am now familiar with dialogs, and have a table that uses 2 pks, I'm trying to replicate the problem....
      MSQL since 2010
      A5V11 since Feb 2012

      Comment


        #4
        Re: Multiple Primary Keys soapbox rant

        This is the video I sent into Alpha with the bug report, which explains it far better than I can via keyboard. It is has nothing to do with any xbasic programming or the like, it just relates to the genies setting up table relationships in dialogs, which do not work. I then showed the actual data in the Postgres back to verify that it wasn't a data error.
        http://www.screencast.com/t/YiZaLYm3DC

        Since the video, I then constructed a row expander parent grid and child grids using the exact same Postgres tables and data and relationships and it worked perfectly. You set up row expander relationships the exact same way you setup dialog bindings. I then resigned myself to using row expander grids that would popup a single level dialog for editing and entering(I hate the way grids enter/edit data and use them only to display info) on each row of the grid as necessary.

        My final frustration was when I tried to do a select innerjoin statement on the row expander grid that failed and was again related to the multiple primary keys. Therefore, row expanders work with multiple primary keys as long as you do not do any innerjoins in your select statement.

        Probably what I should do is make a video of the row expander setup and show how it works with the same backend that the dialog fails on.

        This is an off shoot from a post I put in regarding when/what I would hear back regarding the bug report. It evolved into a discussion regarding reporting systems and opinions. Much of the discussion revolves around more complex bug reporting, which I feel mine is neither complex or high level.

        http://msgboard.alphasoftware.com/al...-Response-Time

        Comment


          #5
          Re: Multiple Primary Keys soapbox rant

          Originally posted by MarionT View Post
          I noticed while linking tables in Dialog binding that there were explanatory notes about using multiple pipings ||| when linking multiple primary keys and that there is importance in terms of the order in which the columns are selected.

          Since I am now familiar with dialogs, and have a table that uses 2 pks, I'm trying to replicate the problem....
          I know that I ran into the piping issue when I was doing some security dialogs and specifically in the OnDialogInitialize event.

          The dialog I am talking about is right in the main databinding section of the dialog. Not buried down in any server-side event.

          I did make another video that show the row expander working and the dialog not working when they are both bound to the same Postgres back end tables. I hate to expect people to watch it, but it much better than my fumble typeing explanation.

          http://www.screencast.com/t/PhD3GWWTJ0

          The other link further up in this thread is the video I sent to Alpha with the bug report.

          I don't know, maybe it is a Postgres problem. I kind of assumed it wasn't since rowexpander worked, dialog didn't.

          Thanks for the help.

          Comment


            #6
            Re: Multiple Primary Keys soapbox rant

            Andrew -
            I"m trying to replicate the problem with my tables - but I had to stop and go on errands (i have internet in the car but won't be able to concentrate)

            I notice with the A5 windows in the data-binding section a difference between what you are doing and what I'm doing but havne't had a chance to explore.

            You are linking tables based on multiple fields AND the child table has multiple primary keys.

            I am linking tables based on one field (a single primary key) and my child table is a table that only has 2 fields, both of which make up the pk

            that may be why I'm not running into the problem.

            ie, I keep hearing you say that the problem is that your table has multiple primary keys. I'm not sure if that is the problem, so much as the fact that you are LINKING based on multiple keys.

            I think I"ll be able to set up a scenario to try to duplicate the problem (I'm using MSQL) - that won't solve anything other than to help isolate whether or not it's your database.....

            PS - I'm 99% sure that the explanatory note I mentioned about piping and the ORDER in whcih the columns are selected is within the databinding section - not down in the action scriping. I can't look right now, but the windows on your screencast did not have explanatory text and my windows do
            MSQL since 2010
            A5V11 since Feb 2012

            Comment


              #7
              Re: Multiple Primary Keys soapbox rant

              2012-03-03_multiple_primary_keys.png

              here's my screen capture
              MSQL since 2010
              A5V11 since Feb 2012

              Comment


                #8
                Re: Multiple Primary Keys soapbox rant

                Originally posted by MarionT View Post
                [ATTACH=CONFIG]31164[/ATTACH]

                here's my screen capture
                Correct....yours is working because you have only a 1 column primary key. Which is what I understand Alpha says to do. But, if you hit the builder button next to your primary key, you will notice up to 5 rows are available for putting in primary keys. So if that is the case, why did they put in 5 rows?

                So what my child tables are saying is that there are multiple rows of company #3, and multiple rows of employee #5, but only one row that has company #3 and employee #5. So, maybe I don't understand what a primary key is doing. I know that Postgress has constraints setup that will not allow more than 1 company #3/employee#5, because I have tested that and gotten an error. So I feel very comfortable on the uniqueness issue.

                Watch where you are going, there is snow on the ground in Illinois, I am sure there is in Rochester too! Don't crash over my dumb problems.

                Comment


                  #9
                  Re: Multiple Primary Keys soapbox rant

                  Andrew - the purpose of that screen cap was NOT to show you the pk

                  it was to show you the explanatory text at the bottom of the screen

                  I will make a more pertinent screen capture and show you.

                  We're actually visiting my daughter in NJ and the weather is dry; we're out shoe-shopping (without her)
                  MSQL since 2010
                  A5V11 since Feb 2012

                  Comment


                    #10
                    Re: Multiple Primary Keys soapbox rant

                    2012-03-03_child_table_with_2pk.png

                    ok this screen capture shows that my child table does have 2 pk

                    and that the LINK is only set with 1 field.
                    I know you are saying (and I agree) that A5 permits multiple links becuase the dialog opens and shows 5 rows.

                    My point is that for communication sake - I'm trying to establish a distinction between:

                    a child table having multiple fields making up its PK

                    vs

                    LINKING on multiple fields
                    Last edited by MarionT; 03-03-2012, 02:29 PM. Reason: adding attachment
                    MSQL since 2010
                    A5V11 since Feb 2012

                    Comment


                      #11
                      Re: Multiple Primary Keys soapbox rant

                      Why not sent A5 the app with data and details on how THEY can use it to reproduce the problem!!

                      Bob
                      Bob Alston
                      -------------------------------------------------------------------
                      http://webpages.charter.net/bobalston/bob1.htm
                      Alpha five v11 Tips

                      Comment


                        #12
                        Re: Multiple Primary Keys soapbox rant

                        Originally posted by bobalston View Post
                        Why not sent A5 the app with data and details on how THEY can use it to reproduce the problem!!

                        Bob
                        Be happy to do. Not sure if I am smart enough to do it though. Not sure how to email the Postgres side of things and/or still have the corresponding AlphaDAO links work, etc. Think it is fairly easy to send the Alpha side of things though. Pretty much took, "We are confident there is no problem in the builder. " as they were through with looking at it though.

                        Comment


                          #13
                          Re: Multiple Primary Keys soapbox rant

                          Not sure how to email the Postgres side of things and/or still have the corresponding AlphaDAO links work, etc.
                          You can do a SQL dump, zip it and send it along with the necessary web components. They'll build their own connection string.
                          Jim Coltz
                          Alpha Custom Database Solutions, LLC
                          A5CustomSolutions.com
                          [email protected]

                          Comment


                            #14
                            Re: Multiple Primary Keys soapbox rant

                            Great news to anyone following this thread....Selwyn has fixed the multicolumn primary key problem in dialog repeating sections as of 3/10/2012 update. I am still having a problem with multicolumn primary keys in a row expander sub grid when referencing two tables within 1 grid. I am not sure if this is related, but that was not working yet as of 3/10/2012. I did file a bug report regarding the grid problem.

                            Andy

                            Comment

                            Working...
                            X