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

Script not appending all records

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

    Script not appending all records

    I have a script writen to go through the "master" table and check to see if a record already exists (solely my email address for this one). If the record from the transaction table is in the master, only a "ID" is added to the table for the matching record. If the record is not found in the table, it is appended with the corresponding fields.

    Ever record (assuming that it has an email address to evaluate) should be entered in some way to the table, whether by new entry or simply the ID number to existing record. Not all of the records from the initial transactions tables are being "appended".

    This is kind of the same coding used in Thread "Append Operation Locking up Alpha" (http://msgboard.alphasoftware.com/al...ad.php?t=77151)

    I have attached the db and the script is called "App_em".

    Thanks!!

    #2
    Re: Script not appending all records

    Jenn:
    Evidently, this is Masterpiece Theater Feature Attraction Part II !

    I missed Part I and so as this does not go into Part V and VI, can you please remove all unnecessary tables from the Encyclopedia Britannica you attached and just leave the pertinent tables? I am at loss as to where to look not to mention none of the tables are added.

    Comment


      #3
      Re: Script not appending all records

      Okay,
      While I am rezapping and pulling exactly what you need and don't need. (sorry about that all) Here is basically some of the script:


      'Append operations to join all the email addresses from TPG,
      'Maximizer and Quickbooks together in a single email table,
      'each email identified by the id from each source

      'Append all records from TPG as these are active emails used for web users
      DIM Append as P

      a_tbl = table.open("email")
      append.t_db = "t_tblusers"
      ON ERROR GOTO ERROR12012009132356795


      append.m_key = ""
      append.t_key = ""
      append.m_filter = ""
      append.t_filter = "isnotblank(\"Emailaddress\")"
      append.type = "All"
      append.m_count = 4
      append.m_field1 = "EMAIL"
      append.m_exp1 = "@T_Tblusers->Emailaddress"
      append.m_field2 = "USERID"
      append.m_exp2 = "@T_Tblusers->Id"
      append.m_field3 = "EMAIL_TYPE"
      append.m_exp3 = "\"User Email\""
      append.m_field4 = "Active"
      append.m_exp4 = ".t."
      append.t_count = 0

      a_tbl.append()
      a_tbl.close()

      'Loop through all email records
      'For each record determine if a corresponding rec already
      ' exists in the MAX email tables. If so, add Max Id numbers
      ' If not, then append it.
      'debug(1)

      'Create an index for email
      dim tbl as P
      tbl = table.open("email")
      Table.index_create_begin("App_em", "left(Email,25)", "", "")
      index_pointer = tbl.index_create_end()

      'Create an index for m_a_e_mail_address
      dim tbl as P
      tbl = table.open("m_a_e_mail_address")
      Table.index_create_begin("app_em", "left(a_e_mail_address,25)", "", "")
      index_pointer = tbl.index_create_end()

      'Make indexes primary on both tables
      t_tbl = table.open("m_a_e_mail_address")
      t_tbl.index_primary_put("app_em")

      m_tbl = table.open("email")
      m_tbl.index_primary_put("app_em")

      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_email = left(t_tbl.A_e_mail_address,25)
      vc_srch_key = vc_email
      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.Email_type = "Email1"
      m_tbl.Email = t_tbl.A_e_mail_address
      m_tbl.Active = .t.
      m_tbl.Maxcid = t_tbl.Client_id
      m_tbl.Maxcont = t_tbl.Contact_number
      m_tbl.enter_end(.t.)
      else ' find succeded so append only Maxid
      m_tbl.change_begin()
      m_tbl.Maxcid = t_tbl.Client_id
      m_tbl.Maxcont = t_tbl.Contact_number
      m_tbl.change_end(.t.)
      end if
      vn_counter = vn_counter + 1

      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)


      Jenn

      Comment


        #4
        Re: Script not appending all records

        Okay,
        Here it is.

        Comment


          #5
          Re: Script not appending all records

          Jenn, I haven't had a chance to explore your script but see trouble looming just from looking at the code. Suggest you break the original script down into separate scripts. One for each append source table. Test them separately. Let us know how each does.

          It's a common mistake to assume that each line of the script will be executed ONLY AFTER the preceding line in the script has finished its work. Statements that kick off external processes (reads and writes to a disk file; printing a report, etc) will not cause the processing of the script to stop. Windows automatically multi-tasks these external processes, and your script continues to run to conclusion. This means that your loop through the "m_a_e_mail_address" table is probably trying to run even before the first append operation is finished.

          Have you used debug(1) to see where things break down? I'm not sure you can create an index without an exclusive lock on a table. Can't get an exclusive lock if another process is reading and writing to or from the table.

          You're swimming in the deep end of the pool.

          Break the script down into pieces. Get each piece working. Use debug().

          -- tom

          Comment


            #6
            Re: Script not appending all records

            Hey Tom!
            (By the way, Tom is the one who helped me so much the first time around)
            I actually wrote them all separately, and they process fine. Just all the records are not being included. I will try and break it all apart and debug tonight and see if I get us any more information to go off of.
            Thanks!

            Comment


              #7
              Re: Script not appending all records

              Jenn,
              You're doing something which may be allowed by Xbasic, but it shouldn't be. You are creating table pointers and not closing them and you are reassigning the same pointer from one table to another and from one index to another without closing it in between. I don't know if that is causing the problem, but it surprises me that your script works at all. I added a few lines to your code:

              Code:
              'Append operations to join all the email addresses from TPG,
              'Maximizer and Quickbooks together in a single email table,
              'each email identified by the id from each source
              
              'Append all records from TPG as these are active emails used for web users
              DIM Append as P
              
              a_tbl = table.open("email")
              append.t_db = "t_tblusers"
              ON ERROR GOTO ERROR12012009132356795
              
              
              append.m_key = ""
              append.t_key = ""
              append.m_filter = ""
              append.t_filter = "isnotblank(\"Emailaddress\")"
              append.type = "All"
              append.m_count = 4
              append.m_field1 = "EMAIL"
              append.m_exp1 = "@T_Tblusers->Emailaddress"
              append.m_field2 = "USERID"
              append.m_exp2 = "@T_Tblusers->Id"
              append.m_field3 = "EMAIL_TYPE"
              append.m_exp3 = "\"User Email\""
              append.m_field4 = "Active"
              append.m_exp4 = ".t."
              append.t_count = 0
              
              a_tbl.append()
              a_tbl.close()
              
              'Loop through all email records
              'For each record determine if a corresponding rec already
              ' exists in the MAX email tables. If so, add Max Id numbers
              ' If not, then append it.
              'debug(1)
              
              'Create an index for email
              dim tbl as P
              tbl = table.open("email")
              Table.index_create_begin("App_em", "left(Email,25)", "", "")
              index_pointer = tbl.index_create_end()
              [COLOR="Red"]tbl.close()[/COLOR]
              
              'Create an index for m_a_e_mail_address
              [COLOR="Red"]delete tbl[/COLOR]
              [COLOR="Red"]delete index_pointer[/COLOR]
              dim tbl as P  
              tbl = table.open("m_a_e_mail_address")
              Table.index_create_begin("app_em", "left(a_e_mail_address,25)", "", "")
              index_pointer= tbl.index_create_end()
              [COLOR="Red"]tbl.close()[/COLOR]
              
              'Make indexes primary on both tables
              t_tbl = table.open("m_a_e_mail_address")
              t_tbl.index_primary_put("app_em")
              
              m_tbl = table.open("email")
              m_tbl.index_primary_put("app_em")
              
              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_email = left(t_tbl.A_e_mail_address,25)
              vc_srch_key = vc_email
              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.Email_type = "Email1"
              m_tbl.Email = t_tbl.A_e_mail_address
              m_tbl.Active = .t.
              m_tbl.Maxcid = t_tbl.Client_id
              m_tbl.Maxcont = t_tbl.Contact_number
              m_tbl.enter_end(.t.)
              else ' find succeded so append only Maxid
              m_tbl.change_begin()
              m_tbl.Maxcid = t_tbl.Client_id
              m_tbl.Maxcont = t_tbl.Contact_number
              m_tbl.change_end(.t.)
              end if
              vn_counter = vn_counter + 1
              
              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)
              Again, I'm not sure if this has anything to do with your issue, but it could be that you're confusing Alpha Five enough by using, say, the same index_pointer variable for both tables that it is unable to loop and search properly.

              Comment


                #8
                Re: Script not appending all records

                Too late getting back to this. Your question has probably been answered already . Only one comment: "email" is a reserved word. You should not have any tables or fields named "email".

                Comment


                  #9
                  Re: Script not appending all records

                  Hey Peter,

                  Originally posted by Peter.Wayne View Post
                  'Create an index for email
                  dim tbl as P
                  tbl = table.open("email")
                  Table.index_create_begin("App_em", "left(Email,25)", "", "")
                  index_pointer = tbl.index_create_end()
                  tbl.close()

                  'Create an index for m_a_e_mail_address
                  delete tbl
                  delete index_pointer
                  dim tbl as P
                  tbl = table.open("m_a_e_mail_address")
                  Table.index_create_begin("app_em", "left(a_e_mail_address,25)", "", "")
                  index_pointer= tbl.index_create_end()
                  tbl.close()

                  .
                  Why do you do the delete table? Why not just the pointer?

                  And thank you for the note about "email"...I will fix the name of that table.
                  J

                  Comment


                    #10
                    Re: Script not appending all records

                    Why do you do the delete table? Why not just the pointer?
                    Tbl is the pointer.
                    There can be only one.

                    Comment


                      #11
                      Re: Script not appending all records

                      You dim'd a variable and named it "tbl".
                      If you want to re-use the same name, this variable still exists in the application's stratosphere with the same name, scope, value etc. When you use delete, you are basically killing, removing this variable from it's miserable existence. It does not exist any more. So, now you could re-use the same name for a new variable.
                      For someone like me who is literatureally-challenged and can't think of too many names, I use delete liberally. In fact I have the habit of doing this before dimming any variable:
                      delete x
                      dim x as c
                      This way if this variable does exist, it wouldn't create a problem.
                      If you want to pick up a fight with Ira, then you could subscribe to my way of doing things (which happened to be the same as alpha's) but Ira happened to believe that delete leaks. I don't think so. So take your pick. But if you are not literaturealy-challenged, then just use new names for new variables.
                      You could use:
                      dim tbl as p
                      later use
                      dim tbl_1 as p
                      and so on
                      That way, everyone will be happy.
                      Last edited by G Gabriel; 01-13-2009, 12:59 PM.

                      Comment


                        #12
                        Re: Script not appending all records

                        Beware of deleting the pointer to a table that's still involved in active processing. As mentioned previously, it's a mistake to assume that the script will halt while each statement is processed to conclusion. FWIW in my own work I never re-use a pointer name in the same script. -- tom

                        Comment


                          #13
                          Re: Script not appending all records

                          Well since I am still in the "under 6 months of databases" catagory and my knowledge is limited...I think I will stick to coming up with names for everything. Should I use different names for all scripts with-in a database, or just within a script, or conditionally if they will ever be running at the same time?

                          What about the names of indexes. Should they always be different, even if they are for separate tables. For example, I used the same index name for the existing "m_a_e_mail_address" and the new "email" table. I am sure this would help ensure that the script does not get glitched on another table that might still be open?

                          Only one comment: "email" is a reserved word. You should not have any tables or fields named "email".
                          What other words are "reserved". Would phone, address, company, client...any of those other ones? What about using "Email" as a Value?

                          Thanks guys! You have no idea sometimes what these little "hints" open up. :)

                          Quote:
                          Why do you do the delete table? Why not just the pointer?

                          Tbl is the pointer.
                          Thanks Stan! Haha. After someone says it, then I look back and understand clearly. Of course. Think of me as one of those people who can read enough of a language to not get on the wrong bus and ask where the bathroom is...but I'm still learning to understand the conversational XBasic. :)
                          Last edited by WOTH; 01-13-2009, 02:16 PM.

                          Comment


                            #14
                            Re: Script not appending all records

                            Originally posted by WOTH View Post
                            What other words are "reserved". Would phone, address, company, client...any of those other ones? What about using "Email" as a Value?
                            Strangely enough there is an entry in the documentation titled Reserved Words.
                            There can be only one.

                            Comment


                              #15
                              Re: Script not appending all records

                              Thanks Stan!
                              So I was going through and renaming all my variables and was noticing that Alpha uses the variable name "Append" for the XBasic of an Operation created through the genie.
                              Example:
                              Code:
                              DIM Append as P
                              
                              a_tbl = table.open("email_address")
                              append.t_db = "t_tblusers"
                              
                              append.m_key = ""
                              append.t_key = ""
                              append.m_filter = ""
                              append.t_filter = "isnotblank(\"Emailaddress\")"
                              append.type = "All"
                              append.m_count = 4
                              append.m_field1 = "EMAIL"
                              append.m_exp1 = "@T_Tblusers->Emailaddress"
                              append.m_field2 = "USERID"
                              append.m_exp2 = "@T_Tblusers->Id"
                              append.m_field3 = "EMAIL_TYPE"
                              append.m_exp3 = "\"User Email\""
                              append.m_field4 = "Active"
                              append.m_exp4 = ".t."
                              append.t_count = 0
                              
                              a_tbl.append()
                              a_tbl.close()
                              Should I just leave it as that or add a "Append_1"? Also is the variable name m_tbl. and/or t_tbl. offlimits as they are always used for joins, appends, posts, search and you don't DIM those. Are there any other (character)_tbl. that need to be avoided? I started to use names like that, i.e. "em_tbl." or "m1_tbl." and wanted to make sure those wouldn't glitch. What do you think?

                              Comment

                              Working...
                              X