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

Append Operation Locking up Alpha

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

    Append Operation Locking up Alpha

    Hey all you Alphaholics!
    I have to confess that I have been stocking you for a month or so, but haven't participated in the joy of posting yet. I am brand new to both databases and Alpha, so bear with me?

    I am working with several databases trying to integrate all of the records so that we in the office can work from and make links from one single database. I am needing to join records from these three tables to make sure that all the id's from the separate db's are included on a single client entry.

    If you look in the code tab, I have all the joins running smoothly with indexes being created on the resulting joined tables, with the thought this would speed up the appending operations. I can append the table that has all the "matching" or "joined" fine, but when I try to append records that were not included on the join functions, it processes about the first 100 records or so and then freezes up. Like I said earlier, I have set indexes on all of the tables and the "master table" I am appending everything into, with the hopes that this is what was bogging everything down.

    To run the joins, run script "zaps, joins, Indexes", then from there I am trying to run the operation "app join mtq to AlphaID" and then "app t_q not= AlphaID" is the one where it trips up.

    I wish I could pinpoint it to an error or some code...If anyone has any time to look at this for me with fresh eyes, I would be so thankful!!

    P.S. Can I just say, that I appreciate so much the support and community that Alpha 5 has! Thank you to you all!

    Jenn

    #2
    Re: Append Operation Locking up Alpha

    Hi Jenn,

    Welcome to the world of Alpha.

    I have had quick play with your app. First let me ask why you have so many tables in different folders? This does not lead to smooth running.

    I'm off to the land of Nod now but will take another look in the morning.

    Do you have Skype? If not can you get it.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

    Comment


      #3
      Re: Append Operation Locking up Alpha

      Hi Jenn,
      I am using v8 so maybe v9 tightened up a bit in how it sees things because I did not have a problem running what you stated locked up or did not work. I did notice in the "offending" append operation called "app t_q not= AlphaID" you are using isblank() for the numeric field Userid. This may be causing a problem as you cannot test a numeric value for null/blank. You can, however, test the text property if on a form or use str(Userid)="" for your case.

      Even if not the problem it is something to be aware of.
      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


        #4
        Re: Append Operation Locking up Alpha

        Jenn,

        I echo Keith's concern about splitting off your tables into mutiple directories. This is not recommended for the typical Alpha Five database because of "pathing" issues that will occur when you move the database to another machine. A more typical design would include all your tables in a single directory.

        I'm suspicious of the name you've chosen for the append op that's failing. The equals sign may be giving Alpha heartburn. A good rule to follow when naming layouts, scripts, tables, fields, operations,... in fact when naming anything in Alpha is this: Begin the name with an alpha character, include only alpha characters, the underscore character, and numeric digits. No blank spaces. No punction marks. No symbols (like equals, greater than, less than, exclamation mark, etc.). I wonder if renaming your append will solve your problem?

        ps.
        Your DBF files are called "tables" in Alpha Five. The collection of tables used in your application is called a "database". This is different from the terminology used in Alpha Four.

        Comment


          #5
          Re: Append Operation Locking up Alpha

          Hey Guys!
          Sorry, I was out on other missions for the weekend. I Had initially set all of the tables to be stored in separate folders, because after the all is set up through Alpha, those files wouldn't be used anymore. Only the tables in the main folder. I did however go back and redo all of the files to be in the same folder, and am working to make sure all of the operations are now repathed. Thanks for that first tip. These are definately things I do not think through.

          I will also take care of the "isblank()" expression. I had honestly just added that on Friday as another thing to try and filter the records. But that's also useful.

          Keith, I do have skype. I will try and watch when you are on, to see if we can chat? My username is just jennlindgren.

          Thanks guys!
          Jenn

          Comment


            #6
            Re: Append Operation Locking up Alpha

            Okay,
            All the tables and necessary files are saved together, all the names are renamed without spaces or extra characters, and I am making use of the str(UserID)="" for all of the numeric fields for filters. (Sidenote: the isblank() or the operation names were not the glitching factors. The operation names were only in comments in the code, so they should not have effected of confused the processes of A5)

            I am still running into A5 freezing after reading 100-500 records out of 18,000 for the second append opperation. Any other ideas on what might be inhibiting A5 to process through all of the records?

            I did attatch the db with changes.

            Thanks!

            Comment


              #7
              Re: Append Operation Locking up Alpha

              Ok, now that everything has been renamed my trail map is out of date. Please give us the step by step instructions we should follow to mimic the steps you are taking that lead to the problem. Thanks. -- tom

              Comment


                #8
                Re: Append Operation Locking up Alpha

                Using Jenn's first guide I can get a freezeup when using the offending append operation app t_q notin_AlphaID....but when I convert to xbasic to debug it runs fine and completes.

                First thought is a timing issue (something Tom has brought to users attention quite often!).

                I will test a bit more to see if other diagnostic occurances can be found.
                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


                  #9
                  Re: Append Operation Locking up Alpha

                  Inconsistant results--I ended up getting a freezeup most times but not always...I then changed the isblank("UserID") to str(UserID)="" in the append operation and it works everytime. Worth a try I think.
                  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


                    #10
                    Re: Append Operation Locking up Alpha

                    Okay guys.
                    I have been on the phone on and off with Keith all day. Keith...Thank you!!

                    The problem is...I am still locking up. I will zip what I have again, minus the join tbls we don't need so it will be a bit smaller.

                    Here is the XBasis for the operation for app_tq_notin_AlphaId, if you wouldn't mind just comparing it with yours:

                    DIM Append as P

                    a_tbl = table.open("alpha_id")
                    append.t_db = "join_t_q"
                    ON ERROR GOTO ERROR23122008154516879


                    append.m_key = "alltrim(LAST)+alltrim(substr(FIRST_W,1,5))+str(USERID,11,0)"
                    append.t_key = "alltrim(LASTNAME)+alltrim(substr(FIRSTNAME,1,5))+str(USERID,11,0)"
                    append.m_filter = "str(Userid)=\"\" .OR. isblank(\"Listid\")"
                    append.t_filter = ""
                    append.type = "Unique only"
                    append.m_count = 4
                    append.m_field1 = "Last"
                    append.m_exp1 = "@Join_T_Q->Lastname"
                    append.m_field2 = "First_W"
                    append.m_exp2 = "@Join_T_Q->Firstname"
                    append.m_field3 = "Userid"
                    append.m_exp3 = "if(Userid=0,@Join_T_Q->Userid,Userid)"
                    append.m_field4 = "Listid"
                    append.m_exp4 = "if(isblank(\"Listid\"),@Join_T_Q->Listid,Listid)"
                    append.t_count = 0

                    a_tbl.append()

                    I have also tried without the filters on append.m_exp3 & 4. It is at least going through the progress meter once and then gets stuck about 2/3 the way throught the second meter. I got it one time to go to 1700 and then it stopped,now, it is not even processing records. Like Mike said, inconsistent. The other thing that Keith and I changed was in the linking key expression: we changed from - to +'s. (I orginally was using -'s because in the beginning, Alpha was building the expression that way through the Genie.

                    If you open the zip again, everything other script or operation is run except the app_tq_notin_Alpha.

                    Thank you guys so much for your time today.
                    Jenn
                    Last edited by WOTH; 12-23-2008, 07:12 PM. Reason: Further instruction

                    Comment


                      #11
                      Re: Append Operation Locking up Alpha

                      Jenn, I took a close look at your database this evening. Using Vers 9 the append op fails. I suspect a conflict that Alpha can't resolve is located somewhere in the midst of the following, all of which are present in your system:

                      a) validation field rules in the Alpha_id table, which transaction records violate
                      c) field rule field events tied to the Last field inthe Alpha_id table
                      d) field rule record events tied to the First_W field inthe Alpha_id table
                      e) mismatched linking field definitions in the append design
                      f) master table filter in the append design
                      g) conditional filters for the transaction table in the append design
                      h) and, finally, your append is not a simple append, it's an append but only if an existing record with the same key field values isn't already present in the master table.

                      Frankly, it was more than I could figure out.

                      Easier to write a simple script to do the work of the append.

                      Check out "Toms_Script" in the code page of the control panel in the attached example. It steps through the transaction table, checks to see if a matching record already is found in the master table. If so, the transaction record is skipped. If not, the transation record is appended.

                      The script relies on two new indexes I've defined for the master and transaction tables. I defined the indexes so that their key lengths would be identical when comparing a transaction record to the existing records in the master table. This is critical. Your earlier link field specifications resulted in mismatched key lengths because you trimmed and alltrimmed field values in your expressions. I recommend that you define link field expressions that have a fixed width. Which my new index definitions illustrate.

                      In order to get my script to work I had to strip out your validation, field event, and record event field rules. The substance of those rules can be programmed into the script I've written if they're needed at all.

                      -- tom

                      Comment


                        #12
                        Re: Append Operation Locking up Alpha

                        I see now that my file upload failed.

                        Here it is again. Let me know if you have questions.

                        Please extract the contents of my zip file to an empty folder. It should not be unzipped to your development folder or mixed with live data.

                        -- tom

                        Comment


                          #13
                          Re: Append Operation Locking up Alpha

                          I am just realizing that I never posted a big, huge THANK YOU to all you guys. Tom, not only is your script working swimmingly, but it taught me more about different limitations for numeric vs. character and now I am using it for many other operations I am needing to perform! It is excellent and thank you for making it easy for me to use! Thank you, thank you, thank you. Merry Christmas and Happy New Year!

                          Comment


                            #14
                            Re: Append Operation Locking up Alpha

                            You're welcome, Jenn. Glad you got it going and it's proved useful. -- tom

                            Comment


                              #15
                              Re: Append Operation Locking up Alpha

                              Hey Tom,
                              1. I'm working on the same kind of append operation, but for addresses. Could you look at this code, I am trying to append the records that are not in tbl.address already, and it they are, I only want to append the UserID to the tbl.address. I am thinking my error is in the "if result = 0" for the vc_srch_key:

                              2. Second question. Is there any other things we can do to the code, indexes we can create, filters, that would help the scripts to run faster? Just thought I would throw that out there, especially when I am trying to run them a few times to error check the scripts, etc.

                              Thanks Tom!!

                              'Date Created: 06-Jan-2009 01:52:00 PM
                              'Last Updated: 06-Jan-2009 01:52:00 PM
                              'Created By : Administrator
                              'Updated By : Administrator
                              'Loop through all address records
                              'For each record determine if a corresponding rec already
                              ' exists in the address table. If so, add UserID or ListID
                              ' If not, then append it.
                              'debug(1)
                              t_tbl = table.open("t_tblmemberinfo")
                              t_tbl.index_primary_put("app_add_t")

                              m_tbl = table.open("address")
                              m_tbl.index_primary_put("App_Add_T")

                              t_tbl.fetch_first()
                              dim vn_counter as N = 0 'counts records actually appended
                              vn_recs_to_process = t_tbl.records_get() 'count of recs to be processed
                              vn_tenth = round(vn_recs_to_process / 10, 0)

                              dim p3 as waitdialog 'for progress bar
                              p3.create(1,"percent")

                              vn_iteration = 1 'count of records actually processed

                              while .not. t_tbl.fetch_eof()

                              vc_address1 = left(t_tbl.Address1,80)
                              vc_city = t_tbl.City
                              vc_zip = t_tbl.Zip
                              vc_srch_key = vc_address1 + vc_city + vc_zip

                              result = m_tbl.fetch_find(vc_srch_key)
                              if result < 0 then ' find failed, so append the rec
                              m_tbl.enter_begin() ' enter new rec to master table
                              m_tbl.Address1 = vc_address1
                              m_tbl.Address2 = t_tbl.Address2
                              m_tbl.City = t_tbl.City
                              m_tbl.State = t_tbl.State
                              m_tbl.Postal_code = t_tbl.Zip
                              m_tbl.Country = t_tbl.Country
                              m_tbl.Userid = t_tbl.Userid
                              m_tbl.Country_of_service = t_tbl.Countryofservice
                              m_tbl.enter_end(.t.)
                              vn_counter = vn_counter + 1
                              end if
                              if result = 0 then ' find succeded so append only UserID
                              m_tbl.enter_begin() ' enter new rec to master table
                              m_tbl.Userid = t_tbl.Userid
                              m_tbl.enter_end(.t.)
                              vn_counter = vn_counter + 1
                              end if

                              t_tbl.fetch_next() 'get next transaction record
                              vn_iteration = vn_iteration + 1 'increment count of recs actually processed
                              if mod(vn_iteration, vn_tenth) = 0 then
                              p3.set_percent(vn_iteration, vn_recs_to_process) 'set progress bar when 10% is done
                              end if
                              end while

                              t_tbl.close()
                              m_tbl.close()

                              'statusbar.set_text("")
                              p3.set_percent(100,100)

                              msgbox("Results",Ltrim(str(vn_counter)) + " unique records were appended")

                              Comment

                              Working...
                              X