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

Testing for Uniqueness

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

  • Testing for Uniqueness

    I have a form, and would prefer to present my own error message if a user enters a duplicate record.

    First the user enters a client code, and then the tax year for the record. I would like the CanDepart event for the taxyear field to test the combination of Code and Taxyear (alltrim(code)+taxyear) to see if this already exists. If so, I want to display a custom message rather than the sometimes cryptic message Alpha gives when doing this with field rules.

    I've played around with Key_unique() and Exist() but am missing something, I guess... Any help would be appreciated.

  • #2
    Re: Testing for Uniqueness

    Attachment is a knockoff of the cansaverecord event for the table as shown in the help under Validation Rules, Uniqueness test for multiple fields. Does not rely on the form. The validation occurs when a record is saved in the data table.
    There can be only one.

    Comment


    • #3
      Re: Testing for Uniqueness

      Thanks much Stan

      Comment


      • #4
        Re: Testing for Uniqueness

        Originally posted by Stan Mathews View Post
        Attachment is a knockoff of the cansaverecord event for the table as shown in the help under Validation Rules, Uniqueness test for multiple fields. Does not rely on the form. The validation occurs when a record is saved in the data table.
        Stan - the Uniqueness test has worked well in order to prevent duplciate entries without using field rules (per se), however the issue I'm having now is that the method you showed me is now blocking attempts to DELETE records... could you explain what I need to do to get around this? Thanks again.

        Comment


        • #5
          Re: Testing for Uniqueness

          I made an addition to my original cansaverecord event code shown in red.

          Code:
          tbl = table.current()
          if key_exist("comb", alltrim(tbl.code) + " " + tbl.taxyear) [COLOR="Red"].and. tbl.mode_get()= 2[/COLOR] then
              cancel()
              ui_msg_box("Error", "The combined values of Code and Taxyear must be unique")
          end if
          This should prevent entry of duplicates but allow deletions. One caveat is that it will allow editing of an existing entry to become a duplicate - deletion is a change like editing.
          There can be only one.

          Comment


          • #6
            Re: Testing for Uniqueness

            Thanks Stan...

            Is there a different aproach you would suggest than the one I'm taking? I understand a simple field-rule requiring uniqueness would be the easy route (I rarely take that option, sadly... I always try to make things difficult for myself), however I do like to control what messages pop up, and like to make them as instructive as possible... which is the reason I'm doing this...

            Comment


            • #7
              Re: Testing for Uniqueness

              This may also be useful:

              Syntax:
              A_Deleting_Record

              Description:
              To Alpha Five, changing the value in a field, and deleting a record, are both changes to the current record, and both actions trigger the Change and Save record events. This makes it impossible to distinguish between an effort by the user to change a record, or delete the current record. The reason for this is that a record is "deleted" by changing the value in a special "hidden" field in the record.


              A_DELETING_RECORD is set to .T. during a record delete, and to .F. when the record is being edited.


              With this variable, you can write scripts that do something when the user makes a change to a field value, but not when they delete the record.

              Examples:
              For example, the following OnChange event script shows how you would use the variable:
              IF .not. A_Deleting_Record THEN
              ....perform actions normally associated with a record change
              END IF

              FWIW: This is another of those situations where I could not find this variable in the A5 Help file (because I didn't know exactly how to spell it but I knew it existed) but found it almost instantly in my own, free A5v5 XBasic Help file just by doing a search on "delete". I'm really surprised that more xbasic users haven't downloaded it. About 1/2 the time that I need to find a function, I can't find it in Alpha's Help file but can find it in mine because the keywords are better.

              Unfortunately, the project files for my A5v5 XBasic Help file became corrupted and I can no longer update it. Also, it is the older style .hlp file which now requires a separate download from Microsoft if you want to run it. So, I just started a new project last week. I'm gradually building a new A5_Function_Notes.chm file. The primary purposes of this new file will be to provide (a) better keywords for those functions that are hard to find and (b) explanatory notes like the one below for anything that isn't obvious from the A5 descriptions. (The new file is not intended to include syntax, descriptions, or examples BUT I will probably cut-and-paste from the A5 Help file in most cases just to avoid the need to open a second help file once the function is located in mine.)

              Code:
              EMAIL_PROFILE_SAVE()
               
              The profile can only be saved if there is already an existing profile by the 
              same name. Use something like the following to create one if it doesn't exist:
              
              this_ver = [COLOR=#008000]ltrim(str( version() ))[/COLOR]
              this_prof_chr = <profile number as a character string>
              [COLOR=#008000]registry.sys_create([/COLOR] "HKey_Current_User\Software\Alpha Software\Alpha Five " + this_ver + ".0\init\email\profiles\profile" + this_prof_chr + "\name", "<profile_name>" )
              If anyone has something they want me to add to this ongoing project, send me an e-mail with the description and I'll put you on the list to receive updates. Of course, I reserve the right of final decision as to what will be included in the file. (As I said, this project was just started last week so there are only a couple items so far - and A_Deleting_Record was just added. I expect it to be much larger in a few months.)
              Last edited by CALocklin; 04-18-2010, 05:13 PM.

              Comment


              • #8
                Re: Testing for Uniqueness

                Originally posted by CALocklin View Post
                This is another of those situations where I could not find this variable in the A5 Help file (because I didn't know exactly how to spell it but I knew it existed) but found it almost instantly in my own, free A5v5 XBasic Help file just by doing a search on "delete".
                In the webhelp under System, Variables.

                Just slipped your mind, I'm certain.

                End result being, if I remember the usage of Cal's suggestion....

                Code:
                tbl = table.current()
                if key_exist("comb", alltrim(tbl.code) + " " + tbl.taxyear) .and. tbl.mode_get()= 2 .and. (.not. a_deleting_record) then
                    cancel()
                    ui_msg_box("Error", "The combined values of Code and Taxyear must be unique")
                end if
                Would allow deletions but not changes to existing records which resulted in duplicates.
                Last edited by Stan Mathews; 04-18-2010, 07:21 PM.
                There can be only one.

                Comment


                • #9
                  Re: Testing for Uniqueness

                  First, thanks for finishing up that xbasic.
                  Originally posted by Stan Mathews View Post
                  In the webhelp under System, Variables.

                  Just slipped your mind, I'm certain.....
                  I suppose you could say it slipped my mind because I have found it there before. However, it was probably just like this time - I couldn't remember where to find it so I used my own help file, searched for "delete", and found it right away. Then I searched A5 Help using the full name just out of curiosity and found out it was under "System Variables".

                  So, the question is, "How did you find it?" Did you do a search on A_Deleting_Record? If so, you cheated.

                  Try looking in the Index list under "Delete" (good luck!) or "Variable" (good luck!). Then try running a Search on "Delete" (it's there but with over 500 hits most people won't think to look at System Variables unless they happen to remember it from before - and it's all the way down at item 350) or "Variable" (over 400 hits and the same problem as searching on Delete but it is item 31 this time). Of course, "System Variables" is in the Index if you look under "System" but I would never think to search under "System" when looking up a variable that has to do with "deleting" something.

                  It just seems to me that someone should add the keyword "delete" to this topic so it would be easier to find. If Ed was still around, I'm pretty sure he'd do it. He did add a few others I suggested. Of course, that won't help much in Alpha's current Help file because A_Deleting_Record isn't a separate topic anymore (it was back in the PDF files of v5) and users still might not think to check under System Variables. (FWIW, I just added "variable" as a keyword in my file but I won't bother adding "system" because I can't imagine anyone actually looking for it under that.)

                  These are the kinds of things that drive me to building my own help file for reference. In the long run it saves me time. (Well anyway, it does when I don't waste it posting on the forum.)

                  Comment


                  • #10
                    Re: Testing for Uniqueness

                    Cal, Stan - Thanks both for your efforts in answering my question. Just got back from a Tax season hiatus and will delve into this in the next day or so, and will report on my progress. Your assistance is appreciated.

                    UPDATE:

                    Works like a charm, just what I was looking for. Cal - I'd be interested in your own version of the help file (any help I can get is great...). Also, where can I obtain that old A5V5 help file you had mentioned? I wouldn't mind having that, too...
                    Last edited by smrogers; 04-20-2010, 12:24 PM.

                    Comment


                    • #11
                      Re: Testing for Uniqueness

                      Glad it worked.

                      Originally posted by smrogers View Post
                      Also, where can I obtain that old A5V5 help file you had mentioned? I wouldn't mind having that, too...
                      See the link in my original post above. And note that even though it says A5v5 it has many v6 commands and few from later versions.

                      The newer version isn't really worth having yet. It's in such an early stage that I'm still working on the "format". As I add new topics I find I have to re-organize it to keep it from getting too confusing. Once I have a more stable format I'll start posting it on my "developer addins" page. (The A5v5 Xbasic Help link is also on that addins page near the bottom. And I just realized that the old A5v4 file is also still there.:o I suppose I should remove that next time I edit the page now that I'm also giving the new version away for free and everything the the A5v4 version is also in the newer version.)

                      Comment


                      • #12
                        Re: Testing for Uniqueness

                        Thanks Cal - never been to your site, but it looks very helpful. As a casual developer, I don't get a chance to do this all the time - things like what you've offered are a great aid and one of the reasons I'm using Alpha instead of something else...

                        Comment


                        • #13
                          Re: Testing for Uniqueness

                          One other caveat is if you are changing an existing record, like say the address field, and you have code like the above, the cansaverecord event will fire - thus telling you that that would be a duplicate, when actually it wouldn't be.

                          The way I get around this is like this: at the caneditrecord event put:
                          Code:
                          dim global thisrecnum as n
                          t=table.current()
                          thisrecnum=t.recno()
                          then at the cansaverecord event put
                          Code:
                          dim global thisrecnum as n
                          tbl=table.current()
                          if a_deleting_record
                               end
                          end if 
                          m=tbl.mode_get()
                          if m=2 'entering a new record
                               if key_exist("comb", alltrim(tbl.code) + " " + tbl.taxyear)
                                   cancel()
                                   ui_msg_box("Error", "The combined values of Code and Taxyear must be unique")
                               end if
                          else 'they are editing
                               if key_exist("comb", alltrim(tbl.code) + " " + tbl.taxyear) .and. tbl.recno() <> var-> thisrecnum
                                   cancel()
                                   ui_msg_box("Error", "The combined values of Code and Taxyear must be unique")
                               end if
                          end if
                          Although I have not tested extensively, I will add a note that in converting an app from V8 to V10, using "exist()", I have run into several occassions where "exist()" will cause the table to show as open, and thus I can't get a lock on it. And I have in those scenarios had to use a work-a-round for "exist()." I don't know if key_exist() will cause any problems.

                          PS: I still also use Cal's V5-4 help file.
                          Cole Custom Programming - Terrell, Texas
                          972 524 8714
                          martin_w_cole@msn.com

                          ____________________
                          "A young man who is not liberal has no heart, but an old man who is not conservative has no mind." GB Shaw

                          Comment


                          • #14
                            Re: Testing for Uniqueness

                            Thanks for your input Martin... I'll check this out and try to decipher... as I've mentioned, I'm a part-timer here, and having 6+ months between projects often leads to cobwebs in the attic when it comes to recalling various programming protocols

                            Comment


                            • #15
                              Re: Testing for Uniqueness

                              Cal,
                              First, thanks for reiterating the functionality of the A_Deleting_Record and its use to finely adjust coding to get exactly what is wanted as a result.

                              Regarding the help file--I also tried using Delete as a search term....and knew there was a system/alpha/whatever variable of some name but without knowing it that is what I would search for......but I found it on the second try of the search results for "delete" using the index search box.

                              This page http://support.alphasoftware.com/alp...ord_Events.htm which was gotten via choosing the "Test whether a record is being deleted" under the choices given for the Delete search.

                              I agree that the help NEEDS help, but the reason I have not downloaded your generous offering of the help file you created is that I have generally been able to find easily what I want via the current Alpha one......(Go figure right!! :D )
                              Mike
                              __________________________________________
                              It is only when we forget all our learning that we begin to know.
                              It's not what you look at that matters, it's what you see.
                              Henry David Thoreau
                              __________________________________________



                              Comment

                              Working...
                              X