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

Help with xbasic operation to update table

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

    Help with xbasic operation to update table

    I have a table that has the following fields and about 12,000 records

    Manf (C)
    Model (C)
    Begin_ser_num (C)
    End_ser_num (C)
    Year (C)

    Example data, Record 1:
    John Deere
    8400
    12001
    22501
    1999

    Record 2:
    John Deere
    8400
    22502
    25900
    2000

    Record 3:
    John Deere
    8400
    25901
    26000
    2001

    I would like to add a new field:
    Year_range (C)
    1999-2001

    Is there a way to run an xbasic update operation that would fill in the new field "Year_range" for each record?
    "Year_range" will vary for each "Model" examples... 1972-1985 ...... 2014-2015 ........ 1995 ...... 1980-1989 ...etc

    I can enter each one manually, but would be very time consuming!

    Thanks
    Tony

    #2
    Re: Help with xbasic operation to update table

    If your data is clean, build another table with each model listed once and it's Year_range as the 2 fields.

    Then build a set that links the tables by model.

    Build an update operation on the set that updates the new field Year_Range from one table to the other.

    (Or don't update the table and use the set instead, or a mapped table linking by Model)

    Lots of options.
    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


      #3
      Re: Help with xbasic operation to update table

      The question is how do you determine the year range, and are you using dbf or sql.
      Gregg
      https://paiza.io is a great site to test and share sql code

      Comment


        #4
        Re: Help with xbasic operation to update table

        Yes, year range is what I would like to add and I am using dbf
        Thanks
        Tony

        Comment


          #5
          Re: Help with xbasic operation to update table

          It can also be done with a post operation.

          a_equip.zip
          a_eq_range.zip
          There can be only one.

          Comment


            #6
            Re: Help with xbasic operation to update table

            add the field to your table, then create and run an update on the table.
            On the left side, you put the filed name to update : Year_range
            In the middle, the value, or expression to get the value you want in the Year_range field.
            and for the right column, the value or expression to choose the records to be updated..
            Gregg
            https://paiza.io is a great site to test and share sql code

            Comment


              #7
              Re: Help with xbasic operation to update table

              Stan

              I downloaded your files and viewed the tables and the post operation.

              Per your example, the a_eq_range table had the following data:

              John Deere 8400 1999-2001
              Kubota 3000 1998-2007

              Unless I am mistaken, from the data in the a_equip table, it should have had:

              John Deere 8400 1999-2001
              Kubota 3000 1975

              I emptied the a_eq_range table and then ran the post operation ..... zero records were posted

              I'm sure I'm missing something simple

              Thanks
              Tony








              emptied the equip table, ran post and zero records were posted.

              Comment


                #8
                Re: Help with xbasic operation to update table

                Gregg
                This is what I was hoping for.
                But, I don't know how to write the xbasic to cycle thru 12,000 records, then for each model, only retrieve the first and last year.
                Then add "-" between the first year and the last year.
                Thanks
                Tony

                Comment


                  #9
                  Re: Help with xbasic operation to update table

                  The post was designed to post to the a_equip table, which had the fields you originally specified as your main table with the range field added ready to be filled. The a_eq_range table had the manf and model fields with the appropriate ranges to select from. The a_eq_range table was designed to be the source of the values to put into a_equip, emptying it left no values to be posted from.

                  The post operation linked on manf and model, and filled the range field in the a_equip table where the year in the a_equip table fell within the beginning and end "words" of the range in the a_eq_range. In other words, if the manf and model match between the two tables and the year in a_equip is 2000, fill in the range field in a_equip from a_eq_range on the condition there is a manf/model match where 2000 is in the range. In the range would be satisfied if the a_eq_range were, 2000-2000, 2000-2001, 1999-2000, 1000-3000, etc.
                  There can be only one.

                  Comment


                    #10
                    Re: Help with xbasic operation to update table

                    ok...here we go.
                    Using the a_equip.dbf Stan sent you, right click the table, choose Create Operation.
                    Complete the matrix as you see fit (Feel free to use the data in the picture below.
                    After you have that done, click the lightning bolt.
                    Your results should resemble the 2nd image. (I did widen the yr_range field to accommodate my example).
                    If you have any questions, feel free to call me (608)345-2697. I should be able to talk you through it.

                    Capture.PNG
                    Capture2.PNG
                    Gregg
                    https://paiza.io is a great site to test and share sql code

                    Comment


                      #11
                      Re: Help with xbasic operation to update table

                      I don't believe the suggested update will work as he specified

                      "Year_range" will vary for each "Model"
                      and likely meant for each manufacturer as well.

                      The sample I posted has a defined post operation which examines manf and model and finds the year range encompassing the specified year.

                      before.png
                      Image 2.png
                      after.png

                      The Kubota in a_equip is still missing after the post operation completes. This is by design and illustrates that when no suitable range is found for a manf/model/year combination nothing is placed in the range field.
                      There can be only one.

                      Comment


                        #12
                        Re: Help with xbasic operation to update table

                        Originally posted by twwtc View Post
                        Gregg
                        This is what I was hoping for.
                        But, I don't know how to write the xbasic to cycle thru 12,000 records, then for each model, only retrieve the first and last year.
                        Then add "-" between the first year and the last year.
                        Thanks
                        Tony
                        Build a summary operation that groups on model then shows the first year and last year.

                        From that you can build the range of years 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


                          #13
                          Re: Help with xbasic operation to update table

                          I provided a solution based on the data I had.
                          With a small tweak to the condition, it performs the required task without the need for an additional table.
                          We are working with 4 records, Tony is working with >= 12000 records.
                          Attached Files
                          Gregg
                          https://paiza.io is a great site to test and share sql code

                          Comment


                            #14
                            Re: Help with xbasic operation to update table

                            Thanks for all the replies!

                            I'm not sure I have conveyed very well the result for the year_range field I am looking for.
                            Each model will be different. Some models were only made one year. Others, anywhere from one year to maybe 15 years.
                            So the data that I want for each model is the year range any given model was actually made.
                            For example, for a model that was only made one year .... year_range = "2005" ..... a model that was made 10 years .... year_range = "1999-2009"
                            (In the examples above, I just pulled the year ranges out of the air, all 12,000 models in my table could have completely different ranges)

                            I may be going about it all wrong with wanting a stored field. If it would be easier to have a calculated field on a report, that would be fine as well.

                            Thanks again for the help
                            Tony

                            Comment


                              #15
                              Re: Help with xbasic operation to update table

                              Originally posted by twwtc View Post
                              I may be going about it all wrong with wanting a stored field. If it would be easier to have a calculated field on a report, that would be fine as well.

                              Tony
                              Then build the data with the summary operation and link to it in a set for the report.

                              That would be much faster and result in a simple calculated field on the report.

                              https://www.alphasoftware.com/alphaf...l=1#post737978
                              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