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

Field Lookup

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

    Field Lookup

    Hi all,

    My table uses a combined reference for each record in the form of [Year-Sales No.], i.e 2010-156. Each year the sales no reverts to 1 again.
    I moved this app to Alpha recently and haven't managed to implement this automatically. I can parse the 'Year' part from the date entered in the date field but cannot get the sales no. part to work. Auto increment takes in all the records and cannot revert to 1 every year. I thought the best way would be to have a simple salesno table with one record and have the reference field look this up, fetch the value in the field then increment the value by 1 for the next user/record.
    Is there a simple ready to use function that will do this, or maybe somebody ahs a better idea. Thanks.

    #2
    Re: Field Lookup

    I do use AutoIncrement for some tables but as with all databases it is possible to skip numbers unless you code around it.

    Sometimes I go get my own number.

    For you I'd suggest a table with two fields in it.

    Year
    SalesNo

    Data might look like...

    2008 128
    2009 156
    2010 132

    This way you'd always have the latest (or last) number used for the particular year you're in.

    To get the next number use...

    FUNCTION GetMaxSalesNo AS N (CurrentYear AS N )
    GetMaxSalesNo = TableMax("tblNextSalesNo", "Year=" + CurrentYear, "SalesNo")
    END FUNCTION

    TableMax reaches into the table, grabs the record specified by the first agrument, and returns the maximum number represented by the second argument.

    Then just add one and you've got your next number.

    If you actually use the number (and don't discard it for some reason), you'll then need to update the SalesNo field in this table.

    Further code would be... if you read the table and your Year isn't there, you know you're starting a new year, create the record and start at 1.

    Comment


      #3
      Re: Field Lookup

      Thanks David,

      i will try that. I suppose i was thinking of the way invoice numbers are dealt out in financial apps, a table lookup on a one field table, grab the value, then increment the field in the lookup table.
      The other thing is that the ref number in this scenario is generated by using a calculated field value. if recalculation is run on the table will everything be messed up? Once the ref number is assigned it should be permanent, unchangeble regardless whether the table or record is being updated, edited, etc.

      Comment


        #4
        Re: Field Lookup

        The way you're thinking is pretty much the same... but you want to deal with a Year as well. So... reach into the table based on the year, grab the value, use it, update the table (for that year) and you're done... same stuff. It can get more complex for unused numbers.

        Comment


          #5
          Re: Field Lookup

          Thanks Again,

          I tried this, creating the function, then calling it from the relevant field as a calculated field.

          First, when I call the function, it requires a variable or value in the parenthesis, so I put in 2010 and it works insofar as it then returns the max sales number from the target table. My problem with this is that when the sales table is recalculated all those reference nubers will update, i.e., if i have a record with ref 2010-150, and recalculate a 500 records later, that field will change to 2010-651, and all the tables in the interim likewise.
          Perhaps I shouldn't be using a calc field?

          Rgds

          Comment


            #6
            Re: Field Lookup

            Not sure I understand...

            I don't think I would use a calculated field.

            You have a table with two fields...

            Year NextNbr

            2009 123
            2010 321

            When you need a new number for a year, call the function, use the number and then update your table for 2010... so your table would now look like

            2009 123
            2010 322

            I don't think TableMax was the right function to use... I would change that to LookupN. Since there is only one record for each year you don't need the max number.

            Comment


              #7
              Re: Field Lookup

              Hi David,

              thanks for coming back on this. I tried the previous function you posted. I called it via an expression in a calculated field, like this getnextNo(2010). That part works but because it is a calculated field it will inevitably update each time the table is calculated.

              It would work with some coding to check if the field was already filled or if the record was being edited rather than created for the first time, that way the numbers would stay put. However i cannot code for this, nor can i get the lookup table's number to increment once i have taken the number, so leaving it ready for the next record - any chance of posting the code you have in mind?

              Rgds

              John

              Comment


                #8
                Re: Field Lookup

                John,
                attached is a small example putting together what has been recommended so far,I think. See if that helps.
                Mike W
                __________________________
                "I rebel in at least small things to express to the world that I have not completely surrendered"

                Comment


                  #9
                  Re: Field Lookup

                  Thank you Mike,

                  That looks like a neat solution. Given that it is an xbasic function, can it be used/ called from an A5w grid/ form in a web application, or used in an event procedure?

                  Rgds

                  John

                  Comment


                    #10
                    Re: Field Lookup

                    Really nice Mike... great seeing all the functions I don't know about. Code is simple and elegant.

                    Comment


                      #11
                      Re: Field Lookup

                      The code works great for a desktop form, but can anyone confirm whether user defined functions can be called by a Grid - I've tried but with no luck!

                      Comment


                        #12
                        Re: Field Lookup

                        Maybe ask in the web forum? :)
                        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