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

linked tables/set updating fields

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

    linked tables/set updating fields

    this is a branch of this thread
    http://msgboard.alphasoftware.com/al...light=coop9653

    But I have been playing with this and cannot get it to work.

    the idea is to have one main table, with information
    You have another table that is the categories for the information

    I make a set,
    I make a form based on the set.

    the way it works, is I enter the data for the main table, then enter the category. It makes a new record in the main and the category table, it makes a new record even if the data is the same


    The way I want it to work!


    I want to select the category from the drop down.
    It would NOT make a new category it would simply link to the field in the category table.

    If I enter a new category, then it would make a new record and use that
    This new category would not be available in the pull down.

    IF I edit an existing category, all the records in the main table should reflect the change!

    I figured out how to do this, with seperate tables and using lookups.

    BUT I cannot find a way to do this with sets!


    I am attaching my test database
    what silly basic thing did I do wrong?

    #2
    Re: linked tables/set updating fields

    I want to select the category from the drop down.
    It would NOT make a new category it would simply link to the field in the category table.
    You can't do this with your set. The CATEGORY table is the child table in a two table 1:1 set. Each parent table record will be linked to a new child table record. That's what 1:1 means. For each CATEGORY table record there is one and only one parent table that is related to it. Each parent table record is linked to a single child table record. You seem to be thinking that if those child table records happen to have the same field values that multiple parent table records should link to a single child table record. That's wrong. In a 1:1 set each parent table record has its own child table record. That's why it's called a one to one set.

    If I edit an existing category, all the records in the main table should reflect the change!
    You can't do this with your 1:1 set. Your question suggests you misunderstand what a one to one set is. As mentioned before, each record in the parent table is linked to its own child table record. In cases where the child table field values are the same they're still separate records. i.e. multiple parent records are NOT linked to the same child record. Theoretically you could do this with a 1:Many set but your CATEGORY table would have to be parent, and I don't think that will be helpful to you.


    I recommend you abandon the use of a set for this aspect of your project. Make the Categories table a lookup source. Put a Category field in your main table. Force the user to choose closest match and don't let them edit Category field values in the Main table.

    Use a separate form, based on the Categories table, to maintain the lookup list. Permit the user to enter new categories, but if they want to "change" an existing category find out if they want to change the category field values in all main table records that have already been entered. If so, run a saved update operation against the Main table to search for each record there that has the old category field value and update it.

    Comment


      #3
      Re: linked tables/set updating fields

      thanks, i understand this part so far.

      and I understand how to use a lookup from a seperate table

      But what happens when the data becomes larger.
      there should be a good way to link records from one table to another

      lets say, I have all my employees data in one table
      name, ssn, age, bday, favorite food, pet name,

      Now I want to link in other information about employees.

      So I make a table for vehicles assigned.

      I happen to have a fleet of 500 cars
      so I put in make model licence, oil change dates, lots of other info!

      Now multiple employees can be assigned the same car
      Bob, bill, jean, can all drive car 4 for example

      I would NOT want a new vehicle record for each person!


      to complicate matters, each employee may be assigned multiple cars.


      I may also make another table for benifits
      I would want to link emloyees to benifit plans same way.


      I just cannot get my mind around how to put this together?

      Comment


        #4
        Re: linked tables/set updating fields

        Q1 - Cars would be primary in a two table set. Link one to many. Employee table is child.

        Q2 - Benefit plan table would be primary in two table set. Link one to many. Employee table is child.

        Comment


          #5
          Re: linked tables/set updating fields

          ahh

          so I am just thinking backwards!

          Can I make 2 sets for the same tables?

          so in affect I have a many to many?


          that way I can have multiple links to employees from cars
          and multiple cars from employees?

          Comment


            #6
            Re: linked tables/set updating fields

            What Tom is talking about is illustrated here..
            Al Buchholz
            Bookwood Systems, LTD
            Weekly QReportBuilder Webinars Thursday 1 pm CST

            Occam's Razor - KISS
            Normalize till it hurts - De-normalize till it works.
            Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
            When we triage a problem it is much easier to read sample systems than to read a mind.
            "Make it as simple as possible, but not simpler."
            Albert Einstein

            http://www.iadn.com/images/media/iadn_member.png

            Comment


              #7
              Re: linked tables/set updating fields

              Tom,

              You can't do this with your 1:1 set. Your question suggests you misunderstand what a one to one set is. As mentioned before, each record in the parent table is linked to its own child table record. In cases where the child table field values are the same they're still separate records. i.e. multiple parent records are NOT linked to the same child record. Theoretically you could do this with a 1:Many set but your CATEGORY table would have to be parent, and I don't think that will be helpful to you.
              I have many 1:1 links to a file that does not have a child for every parent.

              Customer -> terms code
              Customer -> sales tax
              Terms code -> GL Chart

              All links are based on an auto increment ID field and Customer records are created through a form that uses a Record List Combo Box to give the valid choices of terms code and sales tax.

              I do not seem to have any problems so far with many customers pointing to the same terms code or sales tax record. I guess I could change to 1:many even though there would always be only one matching record.

              Still learning and trying to understand as I have many of these 1:1 links and code relying on those links.

              Jeff
              Jeff Ryder

              Comment


                #8
                Re: linked tables/set updating fields

                Jeff,
                now you confused me more<grin>

                do you have a sample database with this so I and others can look at it.

                I am still trying to get my mind around this

                Comment


                  #9
                  Re: linked tables/set updating fields

                  Jeff, there's a big difference between using a form based on one table and looking up field values from another and using a form based on set where the lookup table is itself bound to the primary table with a one to one link. If you have a set based form that does not have a child for every parent I, too, would like to see it. -- tom

                  Comment


                    #10
                    Re: linked tables/set updating fields

                    Cooper,

                    The attached what I believe is an example of what you might have in mind. Tom and Jeff, I believe, also described a reverse set that could be added to give a result that would be analogous to a M:M set. Hope this helps.
                    Mike W
                    __________________________
                    "I rebel in at least small things to express to the world that I have not completely surrendered"

                    Comment


                      #11
                      Re: linked tables/set updating fields

                      Cooper and Tom,

                      Here is a simple example of what I am doing for my customer master. There is one form for the customer master that is based on a set. For Terms and Tax databases just use the default browse. If you change the description of the tax or terms, when you scroll through the customers the new description will display.

                      Again, I am relatively new to Alpha but this seems to work for me.

                      Jeff
                      Jeff Ryder

                      Comment


                        #12
                        Re: linked tables/set updating fields

                        Originally posted by Mike Wilson View Post
                        Cooper,

                        The attached what I believe is an example of what you might have in mind. Tom and Jeff, I believe, also described a reverse set that could be added to give a result that would be analogous to a M:M set. Hope this helps.
                        This is pretty nice, I see you used a intermediary table to tie everything together
                        I would not of thought of that!

                        I am going to seriously study this, thanks a lot!

                        Comment


                          #13
                          Re: linked tables/set updating fields

                          Originally posted by jaryder View Post
                          Cooper and Tom,

                          Here is a simple example of what I am doing for my customer master. There is one form for the customer master that is based on a set. For Terms and Tax databases just use the default browse. If you change the description of the tax or terms, when you scroll through the customers the new description will display.

                          Again, I am relatively new to Alpha but this seems to work for me.

                          Jeff
                          This is COOL
                          You did what I did on my one database with NO lookups or anything
                          I am not sure HOW it works yet
                          but it works excellently!

                          are you able to change the lists? on the fly?

                          what I mean is, your entering a record, and suddenly you need a new term.
                          can you then quickly enter the new term, have it populate your term table
                          then select if, and continue?

                          I have that on my database with a lookup
                          you can add new records,
                          but it does not seem to work correctly

                          In the case of terms or categories, that do not change very often its really not a big deal to have a MANAGE TERMS form, and edit them there!
                          but for other things it may be nice to be able to add them on the spot

                          Comment


                            #14
                            Re: linked tables/set updating fields

                            Jeff, thanks for that example. However, your set based form contains no fields from the linked one to one child table. You're populating fields in the parent table record using a form of lookup that pulls field value candidates from the linked child tables. You can do the same thing without basing the form on a set. Your form cannot be used to enter new child table records which I thought was part of the original issue. Your form illustrates a good solution to the original problem, but it does not incorporate the two table design Collier was trying to use because you do no data entry at all into the linked child tables. His problem was occurring because he was entering new records into the child table instead of populating a field (via lookup) in his primary (parent) table.

                            Comment


                              #15
                              Re: linked tables/set updating fields

                              Tom,

                              Thanks for looking at it and now I understand what you were saying about 1:1. I am glad that what I am doing does work. You are correct in that I can not add child records in this case and I understand that. The main reason that they are setup as a set is that in my real data Terms and Tax Code have several more fields that are needed for point of sale, accounts receivable, several functions and several reports so by using a set the data from those tables is always there. I do also have lookups in field rules that use both static and table lists where the above criteria does not apply.

                              Cooper,

                              As Tom pointed out you can not directly add records to the child in my situation. Since most of the lists have many more fields then displayed I would add an add button next to the list the would open a standalone form to add term or tax records.

                              Jeff
                              Jeff Ryder

                              Comment

                              Working...
                              X