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

Working around limitations of auto-increment

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

  • Working around limitations of auto-increment

    I am still trying to make the transition from A4 to A5. This one has hung me up for hours and hours:

    I run a business with a long and unique inventory. In A4 field rules I could
    assign each item a unique number, but I also had a degree of flexibility which
    is lacking in the new A5 auto-increment field rule.

    I could skip a region of numbers and go back to it at a later time or I could
    insert a number between two existing numbers by assigning 10263A between 10263 and 10264. This flexibility is very necessary to my record keeping, and having talked with support it is simply not available in A5.

    The only work-around is to skip the auto-increment function and to assign a default value to my inventory number. I have tried and tried to come up with
    a workable expression to make this happen, but with no success.

    My inventory number is a 6-digit character. All I want to do is to find the
    expression that allows me to take the inventory number from the previous
    record and add "1" to it and assign that as the default value (one which I
    can override simply by typing over it when the need arised). I simply
    can't come up with the expression that makes it happen.

    Any help would be much appreciated.

    Thanks in advance.

    Paul M

  • #2
    I think your best bet is to store the field value most recently used in a separate file or table. During data entry your new record script would retrieve the field value from that external source. The same script would increment the field value and store the incremented field value in the external source. Then when you save your new record the field value actuallyused would be written back to the same source. This thread in the code archive illustrates approaches that are similar to this, though each will require further modification to deal with your requirements. -- tom

    http://msgboard.alphasoftware.com/al...auto+increment

    Comment


    • #3
      Thanks Tom, but...

      Thanks Tom for your solution, but I think I have to take a
      pass.

      I thought I was upgrading when I went from A4 to A5, but it
      seems as though the opposite is true. I wish there had been a
      prominent warning to the purchasers of A5 that some of the things which
      used to be simple in A4 and now devilishly complex. Instead I was
      told "a total novice who wants to build a simple database fast...[has
      come] to the right place."

      Since I know next to nothing about scripting in A5 and the solution
      seems to demand that I modify or write a complex script, I guess
      if time means anything to me the most "efficient" thing is to
      manually imput each and every inventory number. I probably could
      have entered a thousand numbers that way in the time I've spent
      learning that I don't know enough to do anything about it.

      Yes, I want my inventory number to mean more than one thing: a
      sequence but also a chronology and an admission that once in a while
      one leaves something out. Is that really asking too much?

      And to the developers of A5 I would ask if such a simple function requires
      such a convoluted solution. Couldn't they have programed in a second
      option for "auto-increment"? or at least offered a patch?

      Having looked over the message board I see that I am far from the only
      one who has raised this issue.

      Comment


      • #4
        Paul,

        I recognize the lament. Alpha Four and Alpha Five are two different products, designed for two different operating systems. Alpha Five is not an upgrade from Alpha Four, and shouldn't be sold that way in my opinion. Having said that, I hasten to add that I won't be going back to Alpha Four now that I've climbed the learning curve in the Windows environment. I can build a much better interface for the user, and can integrate my application with Windows in a much better way.

        While I don't think it's typical, it's not unusual that someone manages over time to build amazingly sophisticated apps with Alpha Four, and then wants to simply convert them over to Alpha Five. These folks are often disappointed to learn that they have sink significant effort and time in the project in order to achieve the same degree of sophistication and control in the Windows environment. You may not be old enough to remember the trauma Windows caused many successful DOS software suppliers, but it was awful.

        I sometimes think it's better for someone to come to Alpha Five from a competing Windows database tool, than from Alpha Four. Alpha Five shines, in my opinion, when compared with the other Windows databases in it's price range. The comparative learning curve is lower, and the package has longer legs. At least that's how I see it.

        In your case maybe you should consider changing your approach. Database gurus smarter than me will tell you it's a mistake to use a single field in a table for multiple purposes. In my own experience I've learned that permitting the user to enter their own id numbers for transaction records is often a big mistake. If I were designing your app I'd think about using a simple autoinc field to assign unique numbers to each record, and then I'd use a date field to position the record in the correct chronological sequence, when needed.

        -- tom

        Comment


        • #5
          Maybe this will work for you

          Add a field to your table called record_id,c,8

          Do an update operation that assigns serial values to this new field
          ----right click on the table and choose create operation... and then choose update records -- click the button create using genie -- then on the character tab choose assign serial values -- follow the prompts.

          After the update operation has run successfully, in the field rules for record_id change it to auto increment. This will give you a field that ids the record with an otherwise meaningless value.

          Right click on the table again and choose define index. define an index based on the record_id field. Name it Rcrdid_

          In the field rules for your inventory number go to the data entry tab -- default mode section. Choose simple default expression. For the expression use the following substituting the appropriate table and field name
          Code:
          increment_value(lookupc("F",tablemax("[COLOR="Red"]YourTableName[/COLOR]",".T.","Record_Id"),"[COLOR="Red"]YourInventoryNumField[/COLOR]","[COLOR="Red"]YourTableName[/COLOR]","Rcrdid_"))
          This uses three functions: increment_value(), lookupc(), and tablemax().

          Tablemax() finds the largest record_Id value which should belong to the last record entered. Lookupc() uses this value to search the index to find that record and return the inventory number of that record (ie the last inventory number you used.) Increment_value() increments by 1. If last used value is 123 you get 124 if 123a you will get 123b.

          Hope this helps. It shouldn't take too long to implement and uses resonably straight forward A5 features.
          Last edited by Tim Kiebert; 06-12-2006, 09:29 PM.
          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
            Eureka!

            Thanks so much, Tim. Since you did all the work and all
            I had was to slavishly follow your instructions, it took less
            than ten minutes. I inserted a few dummy records and
            since it was just a default, it works just the way I had
            grown used to in A4. You just saved me hours of boring
            data entry and trying to solve a problem that was clearly
            beyond my ability level.
            Paul Morse

            Comment


            • #7
              Tim, good work. You use the auto-increment field rule with a new field so that the default field rule for the inventory_id field can "find" the most recently entered inventory_id field value. Then you increment it and present it to the user as the candidate id for the next inventory transaction. Very clever.

              --tom

              Comment


              • #8
                Originally posted by pmorse
                Thanks so much, Tim. Since you did all the work and all
                I had was to slavishly follow your instructions, it took less
                than ten minutes. I inserted a few dummy records and
                since it was just a default, it works just the way I had
                grown used to in A4. You just saved me hours of boring
                data entry and trying to solve a problem that was clearly
                beyond my ability level.
                Paul Morse
                Paul, Your welcome. My experience with Alpha started with A5v5 and am now on v7. So I don't know how it compares to A4 and won't be able to fully appreciate your frustrations. I fall into the category that Tom mentioned of coming to A5 from a competing Windows based database program. I can say though that I have been very happy with what I can accomplish in A5 and I believe I have only scratched the surface. I encourage you to stick with it and come back here when your stuck. I have learned heaps from this forum.
                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


                • #9
                  Thanks Tom, just applying some of the things learned from yourself and others on this forum.
                  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

                  Working...
                  X