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

A table for lookup list or derive list on the fly

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

    A table for lookup list or derive list on the fly

    Good morning,

    I have a contact manager application I am completing for a non-for-profit organization. It has at this point 39 tables, 16 of which will contain records with contact individuals information linked through a personal unique identifier (pqid) field. The client wants a method to delete (not archive or inactivate), contacts from the database. My intent is to open one-by-one the tables that contain pqid records filtered to the contact's pqid, and then <tbl>delete_range() the records. So as a starting point, I need a list of tables that contain pqid records. With the consideration that the delete event will be infrequent, my question is this:

    Is it best practice to maintain another table that identifies the tables that contain pqid records as a lookup, OR generate this on the fly using A5.table_enum(), cycle through that list with table.external_field_name_get(),removing the tables without a pqid field to achieve the list of tables, OR some other way I haven't considered.

    Thanks.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

    #2
    Re: A table for lookup list or derive list on the fly

    Originally posted by Mike Wilson View Post
    Is it best practice to maintain another table that identifies the tables that contain pqid records as a lookup, OR generate this on the fly using A5.table_enum(), cycle through that list with table.external_field_name_get(),removing the tables without a pqid field to achieve the list of tables, OR some other way I haven't considered.
    .
    Hhhmmm...

    It seems to me that unless you will be adding/removing tables regularly or randomly, hard code the list. But, if the former, do it on the fly.
    Peter
    AlphaBase Solutions, LLC

    [email protected]
    https://www.alphabasesolutions.com


    Comment


      #3
      Re: A table for lookup list or derive list on the fly

      Hi Mike,

      What Peter said.
      Tim Kiebert
      Eagle Creek Citrus
      A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

      Comment


        #4
        Re: A table for lookup list or derive list on the fly

        OK,
        On the fly it will be. As dynamic as possible is my thinking. Thanks for the thoughts.
        Mike W
        __________________________
        "I rebel in at least small things to express to the world that I have not completely surrendered"

        Comment


          #5
          Re: A table for lookup list or derive list on the fly

          Mike,

          May i ask why you would have pqid in 16 seperate tables? I know this not a part of your request, but why not single table? A field possibly to identify the purpose instead of another table???? Multiple tables makes for a lot of redundancy.

          It would/could make life easier?

          Maybe I just read the question wrong?

          dave
          Dave Mason
          [email protected]
          Skype is dave.mason46

          Comment


            #6
            Re: A table for lookup list or derive list on the fly

            Dave,
            you asked so I will tell you. It is a good exercise to recap the construct.

            This is an expanded contact manager application for a non-for-profit organization centered on crisis intervention and counseling for church and evangelical pastors. The organization is called PastorCare (PC). So the tables and their purpose that hold pqid (personal unique id) and various info are:

            contacts � Main demographics table for each contact.

            referrals � This hold the pqid�s of contacts referrals to and from so the line of connection can be documented. Who referre whom to whom�. etc

            relations - This holds 2 pqid�s per record that documents family, friend and business relationionship connections. Who knows whom, who is related to whom, etc

            events - PC holds events such as seminars, fund raising banquets, family gatherings, etc. They sign people up, communicate with these groups, track attendance, and engage in follow up to attendees. This table holds those data.

            comm_action_temp - When communicating with a group of people, such as a group scheduled to participate in an upcoming seminar, some want communication by mail, others email, others telephone calls. This is a temporary table that holds pqid's of each subgroup as it emails each, or prints mailing labels for each, or prints a list with telephone numbers for communication actions.

            staff - PC is an organization of paid and volunteer personell. PC also provides counseling services. This table holds this data of whom these personell are, their status, position, activity levels, etc.

            group_assign: PC contacts become part of groups, such as pastors, counselors, professionals (attorneys, doctors, CPA's), practical help (plumbers, mechanics, photograpers) etc. This is a central, major element of this database.

            donations - This table holds the donation provided to PC

            churches - A large table holding the names,addresses,pastors, etc of regional churches.

            church � This is a table that holds the church a person belongs to what church as a component of the database that is being replaced. This will disappear after the complete build and transition into the this new A5 db.

            s_error - This is a table that hold pqid and specific info about errors in the demographic of contacts when discovered and not yet resolved.

            client_staff - This is part of a complex set that holds the person's registration into the client (counseling) services, the attachments of the primary and secondary (consulting) providers, and the counseling session events and details (date, time, duration, notes) etc. This section of the database has to have strick confidentiality safegaurds.

            diary � This is the appointment scheduler for the main staff members.

            interactions � Similar to an audit table, every interaction (meeting, telephone call, participation in an event, etc is logged in this table. The diary is the scheduling of interactions and is staff centered. The Interactions table is contact centered, and hold the pqid�s of all the people that have interacted with PC; the who, what, when where and how of the interactions. It is far beyond the Diary and provides for generating a list of all persons and their engagements with PC.

            comm_way � This is a many child table to the Contacts table that holds the preferences of the contacts as to how they wish to be contacted for the various activities PC engages in. Some people want emails for general information/ announcements, but regular mail for special events like the annual banquet, and maybe receive nothing for the weekly newsletter. This is a source for dividing up the communication events that feeds the comm_action_temp table.

            Alerts � This holds contact centered, non-sheduled, non-PC central mission based, futuristic information such as upcoming weddings, birthdays, anniversaries, special events in peoples lives, etc , which pastor people like to have registered to be available for acknowledgements.

            Enough said.
            Mike W
            __________________________
            "I rebel in at least small things to express to the world that I have not completely surrendered"

            Comment


              #7
              Re: A table for lookup list or derive list on the fly

              hi Mike

              you might look in the help files under
              referential integrity


              just a thought
              regards

              martin
              www.jollygreenthumb.com

              Comment


                #8
                Re: A table for lookup list or derive list on the fly

                Martin,
                Thank you. That applies to parent-child tables within a set. These are all mostly unlinked tables.
                Mike W
                __________________________
                "I rebel in at least small things to express to the world that I have not completely surrendered"

                Comment


                  #9
                  Re: A table for lookup list or derive list on the fly

                  Originally posted by Mike Wilson View Post
                  Martin,
                  Thank you. That applies to parent-child tables within a set. These are all mostly unlinked tables.
                  I've never tried referential integrity to delete records with that many tables linked. But you could create a set with the parent as contact and make the links either one to one or one to many as needed and use the set to mark the records. The form you do this on only needs to show limited info to identify the contact. Then buttons could mark or unmark specific records or globally. Then you could reiterate thru the tables to delete the marked records. If all the tables involved have a pqid field, you can link them on that field.
                  Robin

                  Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

                  Comment


                    #10
                    Re: A table for lookup list or derive list on the fly

                    Hi Mike

                    i dont want to be disagreeable but i believe
                    that your particular problem is exactly what
                    relational database manegement is all about
                    and most of the leading dbms on the market
                    include some form of referential integrity and alpha uses
                    a "set" as the basis of maintaining this

                    think about it
                    you have a parent table "CONTACTS" that is linked by the
                    pqid field to many child tables

                    if you create a set based on this relationship you
                    are basically setting up a list of child records to be referenced by
                    the link to the parent field pqid- and one of the functions
                    that is ALREADY programmed into the set is the ability to do
                    cascading DELETES - which is what you are trying to achieve


                    so i believe that you can maintain your list and save yourself the
                    trouble of coding by using the "SET" to do what you want

                    if i am way off base here - i hope someone will set me straight


                    jmho
                    regards

                    martin
                    www.jollygreenthumb.com

                    Comment


                      #11
                      Re: A table for lookup list or derive list on the fly

                      16 of which will contain records with contact individuals information linked through a personal unique identifier (pqid) field
                      Tgis is something I would work a month to avoid. Would rather have 1 table with a field to differentiate the person into a category. If they belong to more than one category, then a second category or a category field with letters in it that can be something like "A" for preacher and "B" for council member. a simple search of the field would yield all the preachers even if they are or not council members.

                      It is called redundancy reduction and worth it's weight in gold. The whole database becomes less complicated.

                      Yes Mike I think you need to use set(s) and reduce redundancy. A header table would be the alternative.

                      Then, Maybe i am wrong or misreading what i read??

                      DaveM
                      Dave Mason
                      [email protected]
                      Skype is dave.mason46

                      Comment


                        #12
                        Re: A table for lookup list or derive list on the fly

                        Originally posted by DaveM View Post
                        Then, Maybe i am wrong or misreading what i read??
                        Dave, more like I wasn't very exact in what I was relating. One table holds the persons demographics and detailed information. The other 15 are like child tables and only hold the PQID, and quantifiers, qualifyers and notes as it pertains to that person's record within reasons and dealings for the particular table.

                        But, I didn't think about what Robin and Martin are relating... make one gargantuan set with all the tables linked to the parent Contacts, and engage referential integrity, and use that to fully delete a contact entry from the database. Yup, I get it now. I have just been so indoctrinated by Jim Chapman's article that teaches NOT to produce enormous sets.

                        Thanks
                        Mike W
                        __________________________
                        "I rebel in at least small things to express to the world that I have not completely surrendered"

                        Comment


                          #13
                          Re: A table for lookup list or derive list on the fly

                          Mike,
                          If you need this set only occasionally and you won't be adding any layouts or operations to it then, since you like programming so much :), you could make the set 'on the fly' using the the <TBL>.RELATION_ADD() function. This way you won't have the set in the control panel and you won't have the corresponding group of files that go along with the set. Not that it takes up much disk space but just reduces clutter. And I know you would have fun doing it.:D
                          Tim Kiebert
                          Eagle Creek Citrus
                          A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

                          Comment


                            #14
                            Re: A table for lookup list or derive list on the fly

                            Thanks Tim! Don't know that function, but will soon.

                            Speaking of liking to write scripts, did you see the monster Stan wrote in this thread?

                            http://msgboard.alphasoftware.com/al...ad.php?t=69881
                            Mike W
                            __________________________
                            "I rebel in at least small things to express to the world that I have not completely surrendered"

                            Comment

                            Working...
                            X