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

Compound Index

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

    Compound Index

    I identify a record within a table using a compound
    key made up of the member ID (3 chars) and YEAR (4 chars).

    However, I can actually add duplicate records to the
    table - which have the same primary key.

    Why is that?

    I've re-indexed, yet I still see the problem.

    #2
    RE: Compound Index

    Kevin,

    You do not say why you expect duplicate records to be prevented. Perhaps you're expecting the "Unique Only" setting in an indextag specification to enforce uniqueness. It's not designed to do that. Unlike other database engines, for Alpha Five this specification simply means that the index list will be filtered to show one entry for each unique indextag value. It's a way of taking a table that has duplicate field values, and filtering it to show only one entry per each unique indextag value. It's not a way of validating data entry to prevent duplicates. This has been discussed in other threads here and in the A5V5 forum. If my guess is right I suggest you look to the validation field rules for your table instead.

    -- tom

    Comment


      #3
      RE: Compound Index

      I see.

      So, will I then need to add code to the
      'CanSaveRecord' record event and check
      for the existance of a key with the same
      value as the one I'm about to save?

      If so, can you provide me a bit of code to
      do that?

      Let's call the table 'Salary' and the compound
      key will consist of the 'member_id' and 'current_year'
      fields; where the 'member_id' is 3 characters, and
      the 'current_year' is four characters.

      Comment


        #4
        RE: Compound Index

        Kevin,

        Yes, you can do it by hand if you wish.

        The Validation field rules make this much easier, however. You can specify that a particular field must be "unique", or that the computed value of a particular expression must be "unique". This latter option would seem to fit you to a "T".

        If you still want to see what the hand written code would look like, how about supplying a dummy table for us to use?

        -- tom

        Comment


          #5
          RE: Compound Index

          Thankyou very much - that sounds like it will solve
          the problem, without the need to code. :)

          I would like to see the 'CanSaveRecord' event
          code as well though. The compound key would
          have a name of 'Salary_ID' and would be a combination
          of the 'member_id' and 'year' values.

          Comment


            #6
            RE: Compound Index

            Could not do this using KEY_UNIQUE() in the CanSaveRecord event of field rules. Perhaps this is limited to the context of the validation field rule. Check the docs.

            This same sort of thing can be done inside the data entry form, but it would be bound to the form, not the table. Still, if you wanted to build a custom validation routine and your app used a single data entry form for each table...

            -- tom

            Comment


              #7
              RE: Compound Index

              Actually what you suggested (within the field
              validation rules) did not work, and I was still
              able to add multiple records with the same
              compound key value.

              When adding the validation rule, is it done for
              the member_id, the year field or both?

              I added the validation rule for the member_id column.
              I want to guarantee uniqueness on the expression:
              member_id + year

              The member_id is a foreign key in another table,
              but I don't see why that should be an issue.

              Comment


                #8
                RE: Compound Index

                Kevin,

                Create a calculated field in the one table that creates the unique field, then use the validation rules on that field.

                Good luck
                Cheryl
                Cheryl
                #1 Designs By Pagecrazy
                http://pagecrazy.com/

                Comment


                  #9
                  RE: Compound Index

                  The calculated field would be member_id + year.

                  I can do that, but validation on calculated fields
                  is not allowed.

                  Can you explain you solution again?

                  Comment


                    #10
                    RE: Compound Index

                    sorry Kevin, brain fart, forgot we could not validate a calculated field via field rules ... would have to use xbasic for the validation
                    Cheryl
                    #1 Designs By Pagecrazy
                    http://pagecrazy.com/

                    Comment


                      #11
                      RE: Compound Index

                      Kevin

                      A shot in the dark but is the problem due to the field lengths involved ie: are all entries in the field member_id of the same length?

                      example if one member_id was "001" then the expression would read "0012005" whereas if another member id was "1" then the expression would read "1 2005"

                      As I said just a shot in the dark?!

                      Regards

                      Glen
                      Glen Schild



                      My Blog

                      Comment


                        #12
                        RE: Compound Index

                        No, the key length are always identical - 7 characters.

                        I have a form which is based on a result set.

                        The master table (with the member_id field) is linked
                        in a one-to-many relationship with two detail tables
                        by the foreign key value of member_id.

                        The primary key within both detail tables is the
                        member_id + year.

                        The year is automatically calculated by a global
                        variable, so the user does not input that value.

                        Isn't it true for a master-detail form that when
                        you add a record to the master table, it will
                        add a detail record as well when you go ahead
                        and edit the detail data?

                        I think the problem I've having is getting adds,
                        edits and deletes working on the detail level within
                        a form which also contains a master record.

                        Will sub-forms within the main (master) form work better for the detail record information?

                        Comment


                          #13
                          RE: Compound Index

                          Kevin

                          When a thread gets this long and no code/tables have been posted it shows that something is missing in the communication.

                          Here is an image of what Alphasports does for what I think you are trying to do.

                          Please post your tables/code if this isn't what you want.
                          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


                            #14
                            RE: Compound Index

                            Al's example is spot on.

                            However, a subtle point should be mentioned here, too.

                            The validation expression uses two table fields from the "current" record. The field rule is defined for the SECOND of these two fields. NOT the first. This means that the validation is not enforced (or attempted) when only the first field value is available. Stated differently, the field rule is defined for the LAST table field used in the expression.

                            capisce?

                            -- tom

                            Comment


                              #15
                              RE: Compound Index

                              Tom

                              Good point, as always, about the second field.

                              The rule can be entered for the first field also and then the condition has to be met when entering or changing either field.
                              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