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

Table Open Table structure

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

  • #16
    Re: Table Open Table structure

    Originally posted by Mike Wilson View Post
    Code:
    Apologies for not giving you the complete story. Mike, I will see if i can cut and paste code to you...
    Sorry, not able to see how I can select where to paste my code to you - can you advise (one of the above icons? )
    Highlight and Ctrl-C, or Alt-A then Ctrl-C the code in the Code Editor and paste it into the message board window. With code highlighted use the # icon ablove the message board window.
    Mike,

    Thanks for the pointer. Works well

    Have a good one
    Dave Mac

    It's not so much what you don't know that gets you into trouble, but what you know for sure, that just ain't so. - Mark Twain.

    Comment


    • #17
      Re: Table Open Table structure

      Originally posted by Ted Giles View Post
      Dave,
      If it was my problem, I'd replicate what you have and change the replica.
      The trouble with the copy routine is that you will - if chosen - get duplicate Forms.
      You might be better off copying the whole database folder to a new one and working on that.
      How many Tables and associated Forms will you need to change?
      If you truncate, as has been commented upon, you will find it easier to follow the logic of the Forms and
      Scripts.
      Hi Ted,

      Replicate seems a good idea. I have quite a few changes to do. Get a load of attached debt.jpeg, and you can see what a problem I have here. Field structure, which follows Cal's recommendation should be more like attached dbt.jpeg, except that the table name if way too long and cumbersome.

      Problem being that I am still developing and there could well be changes as I go. Customers breathing down my neck steers me in a direction of completing what I have and then going back. Problem there is that the road back becomes longer every development day. One thing about this scenario is that I haven't been board since I don't know when.

      Have a good one
      Dave Mac

      It's not so much what you don't know that gets you into trouble, but what you know for sure, that just ain't so. - Mark Twain.

      Comment


      • #18
        Re: Table Open Table structure

        Hi Ted,

        I left the attachments behind.

        Here the are.
        Attached Files
        Dave Mac

        It's not so much what you don't know that gets you into trouble, but what you know for sure, that just ain't so. - Mark Twain.

        Comment


        • #19
          Re: Table Open Table structure

          Originally posted by Dave Mac Callum View Post
          Thanks for the input Guys. Cal, I need to go back to many of my tables and clean them up. Are you reasonably sure that Alpha5 directories are able to manage with all field and file name changes that are made without going up the wall ?

          With your Aims program that I use, it occurred to me that if your program can locate all occurrences of 'Trade_transactions' (which it does), can it not go a step further and alter all those located to 'Trde_xact' ?

          Looking forward
          I need to go back to many of my tables and clean them up.

          1. My general philosophy is, "Don't change it if it ain't broke." Assuming your application is pretty much finished and there are a lot of scripts and expressions to be changed, cutting long names to 10 chars or less just because they are long names may cause more problems than it will fix. It will probably also take more time than it's worth. If you are just beginning the app, then I'd consider changing the names. Obviously there's an "in between" but I'll leave it up to you decide where that is.

          2. I thought about adding the ability to automatically replace text. Then I thought about how many times I'd used similar features in other programs and ended up accidentally changing things that I didn't want to change - and I opted not to add that feature. (Besides, it would be a LOT of work to add that feature. The search routine is searching through my "documentation" table - updating that would be easy. However, to make changes in your application I'd have to figure out where that code was located then open the appropriate script/function or data dictionary and edit it and the method for editing would depend on where it is.)

          Are you reasonably sure that Alpha5 directories are able to manage with all field and file name changes that are made without going up the wall?
          Yes. But ...

          Field names:

          First, I agree with the other comments - make the changes in a copy of the app or at least have a backup ready.

          Changing field names in the table should work fine - even if you change every name before saving changes. What I would be careful of is changing names and field sequences at the same time. I've had problems with this in the distant past if I tried to do too much. It may work fine today and it may even have been my fault in the past (although I don't think so) but I am now much more cautious about how many changes I make before saving the changes and verifying them. I try to take it in steps. I might change all the names, save the changes, then change the location (sequence) of a few fields (5? 10?) and save the changes before relocating a few more. How many depends on how complex the moves. If I was moving 10 fields all to the top of the list, I'd probably do them all. If I was moving one field down 10, one field up 5, and one in between the other two, I might just move the first two and save before moving the last. In one post a long time ago I made the comment that if I'm starting to get even slightly confused about what changes I've made then it's time to save the changes in case A5 is "getting confused" also. At the very least, this will minimize the number of things that you have to fix - no matter what/who caused the error.

          Of course, changing the names in the scripts, functions, and expressions all at once is no problem unless you miss some or misspell some. This is one reason why I usually don't make field/index name changes on completed apps. The other reason is that it just isn't worth the time required. The only time I would be sure to make a field/index name change on a completed app is when the name is causing confusion during debugging/updating or, perhaps, if it's the same as a reserved word. (A5 has an official list of reserved words in their Help file but there are others I'd also be careful about using if for no other reason than to avoid confusion when debugging or updating. In general, my philosophy of using 2-word names pretty much avoids this problem so I can't think of any specific examples right now.)

          Comment


          • #20
            Re: Table Open Table structure

            Originally posted by CALocklin View Post
            I need to go back to many of my tables and clean them up.
            Field names:
            What I would be careful of is changing names and field sequences at the same time. Well pointed out. Do regular saves if you choose this route..
            )
            Didn't realise you were up to your backside in alligators Dave. As CAL says, it might be as well to slug it out with the format you have and sell 'em an upgrade later!
            What you have will probably work as it seems that the first chars are unique.
            Have a look in the database Design section for an embryonic Data Dictionary (Misspelled BTW). Might helpyou.
            http://msgboard.alphasoftware.com/al...t-DD-iteration.
            Last edited by Ted Giles; 09-01-2011, 03:22 PM.
            Ted Giles
            Example Consulting - UK
            .

            sigpichttp://ec12.example-software.com//
            See our site for Alpha Support, Conversion and Upgrade.

            Comment


            • #21
              Re: Table Open Table structure

              If it was my problem, I'd replicate what you have and change the replica.
              The trouble with the copy routine is that you will - if chosen - get duplicate Forms.
              I have had to delete a few duplicate forms, reports, etc in the past. I have also move fields in the database up or down in the distant past. I also had problems with that mostly in the form of messed up field rules. If adding fields, do so at the bottom. It may or may not matter, but I won't take the chance anymore.

              If adding fields in an update later, I do this with a temp adb that attaches a table or more. Once the adb is run one time, it is destroyed and the app(on start juast has added fields to a table or two and the associated files have been changed to accomodate them.

              Code:
              'this is actual code and it works, there are other functions to change the type of field and to make it longer, shorter, etc
              fields = <<%str%
              tankgals,n,2,0
              minleftpct,n,2,0
              maxleftpct,n,2,0
              Rearmin,n,8,3
              Rearmax,n,8,3
              Mindiag,n,8,3
              Maxdiag,n,8,3
              %str%
              table.add_fields("cars", fields)
              Dave Mason
              dave@aldadesktop.com
              Skype is dave.mason46

              Comment


              • #22
                Re: Table Open Table structure

                Originally posted by DaveM View Post
                ... I do this with a temp adb that attaches a table or more. Once the adb is run one time, it is destroyed ...
                That works fine - one time.

                But what happens if, as I've had happen a few times now, someone restores backup data from before my update? An error will be generated if any scripts, field rules, etc. are expecting the new fields.

                I use a function I call Add_update_fields() that will add the missing fields no matter how far back the data is restored. The function runs every time the application starts (i.e., it's called from the autoexec script) and checks for the last field that was added. If the last field added already does exist, the routine ends in just a few milliseconds. If that last field doesn't exist, ALL fields added since day 1 are checked and added in the same sequence they were originally added. (The field check is done one table at a time since the only sequence that is important is the sequence within each table.) If fields are only added and not updated, the 'addition' occurs very quickly. The speed of updating field values, of course, will depend on how many records there are and how complex the update is.

                One thing that most people seem to have a hard time grasping is that the data dictionary can have 25 fields "defined" while the actual table only has 20. This is what happens when an update is being installed or an old backup is restored - the existing data doesn't have the new fields yet but the data dictionaries do. The next time the application is started the add_update_fields() runs and adds the missing fields so that now the table and the data dictionaries match again. NOTE: This ONLY works if all new fields are always added at the bottom of the list - which, by the way, is the only way it can be done when using the a5_add_fields_to_table() command.

                The above paragraph may need some explanation. When I send an update to my customers, it NEVER includes a copy of their data. It only includes the data dictionary files and the .adb file. I rely completely on the Add_update_fields() to handle any field additions/changes necessary. Restoring an old backup does essentially the same thing as an update - an update is installing new data dictionaries with existing data whereas restoring an old backup puts old data with the "newer" (current) data dictionaries. In either case, "old" data exists with "newer" data dictionaries and the add_update_fields() function updates the old data structure so it will work correctly with the newer data dictionaries.

                I'm sure I've posted examples before but I'll attach an example that shows how to add fields, change existing structure, and update new fields. Note that new fields are ALWAYS added at the end of the table. (This is a shortened version of the actual function used in one of my applications. The actual function in that app is over 900 lines and growing - I've added a lot of features to this app.)

                I've been using this method for 10 years and the only situation I've found where I won't use it is for deleting fields - so I just don't delete fields even if they aren't needed anymore. If the field was a really large one, I'd probably change the structure so it was smaller but I don't think I've "deleted" more than 1 or 2 fields in all that time and they weren't very large so I just ignore them. The fields can be deleted with the a5_del_fields_fm_table() command but it can be rather dicey if someone restores old data without re-running the autoexec because the deleted field won't be deleted yet and it can make a real mess of things if the user starts trying to work with the data or, worse yet, "fix" it. In that case, the wrong data will appear in any field listed after the field that was supposed to be deleted. Similar issues can occur if someone installs an update without restarting the app - including restarting it on all workstations. (There is one situation were I used the a5_del_fields_fm_table() command but that was only because it was used so shortly after adding the field by mistake that I was confident that no other fields would be added first and cause the issues described above. See the example.)

                EDIT: After writing this and re-reading your post, I'm not sure what you mean by "I do this with a temp adb". I initially took it that you were including an updated table and importing the data from the old table then deleting the old table and renaming the new table. I know a number of people have done something like that but now I'm not so sure that's what you are doing.
                Attached Files
                Last edited by CALocklin; 09-06-2011, 03:10 AM.

                Comment


                • #23
                  Re: Table Open Table structure

                  This looks like a very handy piece of code.
                  Can I use it CAL?
                  Ted Giles
                  Example Consulting - UK
                  .

                  sigpichttp://ec12.example-software.com//
                  See our site for Alpha Support, Conversion and Upgrade.

                  Comment


                  • #24
                    Re: Table Open Table structure

                    Originally posted by Ted Giles View Post
                    This looks like a very handy piece of code.
                    Can I use it CAL?
                    Not a good idea - it probably wouldn't work very well as-is.

                    But, yes, feel free to use it as a guide/template to create your own. That's why I posted it.

                    Comment


                    • #25
                      Re: Table Open Table structure

                      Hi Dave and Cal,

                      Thanks for your pointers. I have this application which is parameter driven and is the basis of most of my apps. So, I need to straighten it out rather than have problems further down the road. So even if 'it's not broke' I need to fix it, although it hurts to spend the time on it.

                      Have a good one
                      Dave Mac

                      It's not so much what you don't know that gets you into trouble, but what you know for sure, that just ain't so. - Mark Twain.

                      Comment


                      • #26
                        Re: Table Open Table structure

                        Yes. Pain in the a... now, but I can assure you the pain down the road is almost like going to the hospital for a rectal operation.
                        Dave Mason
                        dave@aldadesktop.com
                        Skype is dave.mason46

                        Comment


                        • #27
                          Re: Table Open Table structure

                          This is an aging thread, but there is a trick that I'd like to share: Duplicating sets & forms, renaming them, and then dropping the originals. If you duplicate a set or table, you can then drop this duplicate from the database (when you first duplicate it, append the new set/table name with either version, or date).

                          • The neat thing is that once dropped, you have a complete backup including their associated forms, browses, reports, etc.... (which are now dropped from the database ie: not seen in the control panel, yet may be restored at a later point. (and then renamed back to the original set / file names)
                          • The coolest thing is that dropping the sets (and tables) also drop all the duplicate forms, browses, and reports etc. (So you don't end up with duplicates.) ~ And these dropped "companion" objects maintain their original names, even though the tables / sets were appended with versioning info.
                          • The one BAD thing about using this method is none of the backup routines in Alpha backup these dropped objects.* (So you can't expect to recover them if you use the built in backup / zip options.)


                          *The solution to this is to backup / zip the entire database (with sub-directories) using a third party zipping tool. (Or even the one built into windows) With this approach, you have everything, and will never worry about making irreversible changes. If you routinely backup sets & forms (duplicate/rename & drop) like this, you also protect yourself with backups of forms in the event they ever become corrupt beyond repair. (I've never experienced this, but years gone by, I've read about it here.)


                          Note: Scripts (before editing / changing) in the code tab still have to be backed up somehow, because dropping tables has no effect on items in the code tabs.

                          PS: Another thing that might help while working with live versus non-live data could be the use of aliases in system settings. (I haven't worked with them yet though.)
                          Last edited by SNusa; 02-01-2012, 01:25 AM.
                          Robert T. ~ "I enjoy manipulating data... just not my data."
                          It's all about the "framework." (I suppose an "a5-induced" hard drive crash is now in order?)
                          RELOADED: My current posting activity here merely represents a "Momentary Lapse Of Reason."

                          Comment


                          • #28
                            Re: Table Open Table structure

                            Originally posted by SNusa View Post
                            Note: Scripts (before editing / changing) in the code tab still have to be backed up somehow, because dropping tables has no effect on items in the code tabs.
                            Not exactly sure what you mean, but scripts, etc are stored in the databaseName.alm/alx/alb files
                            Peter
                            AlphaBase Solutions, LLC

                            Peter@AlphaBaseSolutions.com
                            https://www.alphabasesolutions.com


                            Comment


                            • #29
                              Re: Table Open Table structure

                              Peter;

                              The duplication "method" I mentioned above backs up all the associated layouts (forms, browses, letters, reports, and even labels I believe). ~ But it doesn't do anything with objects in the code tab. (So by duplicating and dropping, there is no backup of your scripts.)

                              Hence, if you restore (add the table/set back in, delete the previous version, and rename the re-added table/set back to it's original name) you have the previous version back and working with the exception of everything on the code tab. Your scripts and everything there is the the most recent stuff, as none of it is stored with dropped tables.)

                              TRY THIS WITH AN EXAMPLE DATABASE:

                              First look through the tabs on the control panel.
                              Next, duplicate a table or set. (when you duplicate it, rename it by adding a version. ie: customers becomes customers_v1, or customers_20120201)
                              Look at all the tabs. ~now evertything is duplicated except the stuff on the code tab.
                              Now, drop this newly duplicated table.
                              Here's the fun part: totally screw up your database by messing with the forms, messing with the layouts and browses associated with the table (or set) you previously deleted........ Now your database is totally broken........ and it's time for a quick restoration...... (A scenario where you have to go way back to a previous starting point)

                              Simply, delete the original table or set, and take notice of the name of this table/set. (depending upon what you chose to originally duplicate)
                              Now, add the backup (previously dropped table/set)
                              Last, rename this table (or set) back to customers. (In my example, "customers_v1" or "customers_20120201" gets named back to simply customers.)


                              VOILA - Everything is now restored & working. Except for everything in the code tab. (dropping and restoring tables /sets has no effect on your scripts, or any other objects within the code tab. ~ If you changed or messed up scripts, menus, toolbars, functions etc.... You still have the most current version of them.)

                              I guess, in essence you're saying the same thing that I 'm saying: In that the scripts, etc are stored separately. This trick is just an interesting way of "versioning" that keeps the old stuff fully intact and available, in the even you need to "hit the reset button" if things get messed up. (And using this method keeps everything (on all the other tabs) together by just focusing on dropping and adding tables/sets.

                              NOTE: If you're using live .dbf data, you obviously don't want to be re-adding tables because of their associated .dbf files are old. But as for the sets, this still works nicely! This works great for one of those scenarios where you have something that works very nicely, but you want to try something different using what already works. If it doesn't, you can quickly "bounce back" to what worked and try again. (Regardless, this makes for a great "hidden backup" that can be contained (out of the way) within the database directory/container.) ~ AWESOME TECHNIQUE FOR LEARNING....

                              I was using it for sets and not tables, and I was renaming sets to set_v01, set_v02 etc, by simply copying the original set and dropping the previous version that worked. (But I later realized I could do this with tables too, if the dropped tables & sets contained the "versioning" suffix instead. Plus, I have since learned that there are instances where a5 code uses a set name, and when it does, the renaming of sets in use would have caused problems.

                              So in a nutshell: To backup a complete set or table (with all it's layouts, browses and operations etc, except for associated code tab objects) all you have to do is:

                              1.) Duplicate it (and name this new duplicate using the original name + version info) and...
                              2.) Drop it. ~ Instant recover is in place, and you never have to look at it again, if & until you need it!

                              Just remember to backup your scripts too in case you need to rollback.
                              (And don't rely on the built in zip / backup routine to backup these dropped tables, because they don't, and unfortunately there is no checkbox setting for this.)
                              Robert T. ~ "I enjoy manipulating data... just not my data."
                              It's all about the "framework." (I suppose an "a5-induced" hard drive crash is now in order?)
                              RELOADED: My current posting activity here merely represents a "Momentary Lapse Of Reason."

                              Comment


                              • #30
                                Re: Table Open Table structure

                                Robert,

                                you may want to check your naming of tables, fields, forms, etc. I for one, desperately try to keep everything to 8 letters and maybe 10 on some stuff. There is(or at least was) a problem with names getting shortened by the system sometimes.
                                Dave Mason
                                dave@aldadesktop.com
                                Skype is dave.mason46

                                Comment

                                Working...
                                X