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

lookups and updating field?

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

  • lookups and updating field?

    Hello,
    I'm a bit confused here being newbie where I have a "Sites" table in which there is a field called "Site_Name" . In the table I do a lookup using field rules so the site names are in a drop-down list or popup in all my forms that reference the various site names. In another table I have a field called "Item_SiteName" to select the proper site name which is appropriate for that item.

    I assume it is because the field record is now stored in a different table, but if I go to the top table in sets which is the Site table and change the spelling in the "Site_Name" field (My Sites Form), it does not cascade to the next table that I just saved the record in the above description. If I click on the field in my (Item form) that references the saved record "Item_SiteName" (It is displaying old spelling), the correctly spelled name is there in the drop-down list and it updates once selected saved, but there must be a better way of doing this. I want any top level changes in top table to cascade down to all the children that reference it without going to every record and doing the drop-down to update each record.

    So, I have a table called "Sites" with each Site Name location and the unique ID number field called "Site_ID" and the other table item with a field for linking called "Item_SiteID" with a one to many relationship and referential integrity set to cascade changes. (Note:I have many table that use the site name drop-down list in their forms) Also each table has a field that matches "Site_Name" and "Item_SiteName". I do not link on these as I get an error message and forms do not open.

    Any help is appreciated as usual I am lost here.

    Thank you!

  • #2
    Re: lookups and updating field?

    Hi Paul,
    The best practice for the forum is to upload the tables with some sample data so we can see your situtation too. Otherwise a screenshot is helpful.
    Robin

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

    Comment


    • #3
      Re: lookups and updating field?

      Paul, if you go to 'edit field rules' for the sites table, then from the 'rules' menu 're-evaluate rules' thnis should refresh your exisiting data.

      I found it hard to follow your post, but I think this may be what you're looking for.

      Comment


      • #4
        Re: lookups and updating field?

        Hi Jon,
        I think he wants to pass multiple fields from the parent to the child in a set-based form.

        He could try linking the set on multiple fields, though I am not certain this would accomplish what he wants or not.
        Robin

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

        Comment


        • #5
          Re: lookups and updating field?

          Database normalization suggests you don't store something like site name in multiple tables for precisely this reason. You assign each site a unique id of some sort and have one field in one table for the corresponding site name. Any linkages/references to the site in other tables are on the id. If it is desired to display the site name in the context of any of the other tables a lookup to the site table can retrieve it at the time it needs to be displayed.
          There can be only one.

          Comment


          • #6
            Re: lookups and updating field?

            Stan,
            I was going to suggest that he use a calc field for this, but since he hasn't responded back yet, I thought that maybe that should wait...
            Robin

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

            Comment


            • #7
              Re: lookups and updating field?

              Thanks for all the responses to my confusing question/issue!

              I'll try to explain better tomorrow if this does not help. I probably need to draw a diagram.
              I am sure I am using wrong method to do this if I could only explain clearer.
              I am using unique id's in each table with referential integrity set to cascade changes.
              Site table has UID field name Site_ID and item table has UID Item_ID. I link on this between the two tables
              In the item table Site_ID is stored in the field Item_SiteID
              I use a table lookup in the field that I want to pick the site that the item is at. It is drop-down lookup using site table to fill the form that is using the item table. I use a type in field for drop-down lookup.
              I have more tables downstream from that and if I delete an item the child tables under item table are all cleared so no orphan records are left in child tables.
              If I delete the site, all records in other child tables referencing the site_ID are removed and no orphan records exist in child tables.
              If I make the change on the parent table, the type in field of the other tables stays with old name as it was saved to that field and I have to click drop-down to see corrected name, select, and save.

              Thank you everyone for trying to help out!

              Comment


              • #8
                Re: lookups and updating field?

                1. "Site table has UID field name Site_ID and item table has UID Item_ID. I link on this between the two tables In the item table Site_ID is stored in the field Item_SiteID" - Surely the link is between Site_ID & Item_SiteID

                2. Why would you want to change the Site_ID in the parent table?

                I'm not trying to be awkward, just trying to understand your thinkiong.

                Comment


                • #9
                  Re: lookups and updating field?

                  I never change the UID on any table once the record is entered as it is the reference to each record parent to child in my sets.

                  Maybe I should take a different approach as I was going to the "item form" straight away after entering in the sites and using the drop-down to select which site it was at.

                  I just now changed it where I go to sites and choose the site I want to deal with (The site has data details, etc. of that location). I have a link on the site form that I have used action scripting to get the site name and site id into variables called siteid and sitename. I open the item form and insert those variables into the sit ID field and site name field, so they are pre-populated at the new record start.

                  The catch is the site name is just a convenience like a domain name and easier to remember than an ID number. Site ID 1 might be Boston and site ID 101 Las Vegas.
                  The address and all other data for that site is in the site table. Now I buy an item for site id 101 Las Vegas, the Las Vegas field is just there on the form to make it easy. I really shouldn't store the site name in the item table as it is redundant data, but not knowing anything about scripting, I probably could just have the name pulled from the site table since the site id is known and display on the item page for convenience.

                  My problem is if I make a change in site name like Las Vegas to LasVegas or Las-Vegas which is the only reference I use for convenience does not change on my other forms as the previous site name from top parent has been stored into the other table which is redundant and not really needed since site id is stored. I don't know how to do it any other way at this moment due to my ignorance in programming on how to take that site id number and get the text of the site name to display on any forms that use it. It would then reflect the change in spelling since it is not stored on any table but one (the top parent table site).

                  Thanks again everyone for reading my bunch of confusion, but you are helping make me rethink things.

                  Comment


                  • #10
                    Re: lookups and updating field?

                    Like this.

                    Code:
                    'example from interactive window
                    current_site_id = "00002"
                    
                    ? lookup("sites","site_id = "+s_quote(current_site_id),"site_name")
                    = "Las Vegas                     "
                    sites table.png
                    There can be only one.

                    Comment


                    • #11
                      Re: lookups and updating field?

                      Hi Stan,
                      So this single line of code goes where to display the site name?

                      Thank you,
                      Paul

                      Comment


                      • #12
                        Re: lookups and updating field?

                        If you are on a form, for instance, based on another table where the site_id is a field in that table (say the site_id in this second table is "Item_SiteID") you can create a calculated field for the form and place it on the form. The expression for the calculated field would be

                        st_name = lookup("sites","site_id = "+s_quote(Item_SiteID),"site_name")
                        There can be only one.

                        Comment


                        • #13
                          Re: lookups and updating field?

                          I would make it a calculated field on each form that needs it , or possibly a global variable that could be referenced on all the forms.
                          the global variable might be more efficient as you would only be doing the look one when you change sites.

                          Tom

                          Comment


                          • #14
                            Re: lookups and updating field?

                            Thanks again Stan!

                            That does what I need it to do!

                            Thank you all for your responses.

                            Paul

                            Comment


                            • #15
                              Re: lookups and updating field?

                              "I would make it a calculated field on each form that needs it , or possibly a global variable that could be referenced on all the forms.
                              the global variable might be more efficient as you would only be doing the look one when you change sites.

                              Tom"

                              Hi Tom,
                              I just did that on a couple forms to test and that works based upon Stan's input and changing to the table it uses in the form, and I see the variable and global tab, but like before I have no clue as to what code needs to go in the variable/global panel to achieve what you say.

                              I've just started using this program last week and before that I used Access 20 years ago a little, so now I am getting older it gets harder to grasp everything. This is my retirement keep my mind going plan I hope along with PIC chip programming learning.

                              Thank you!

                              Comment

                              Working...
                              X