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

Writing to fields in child table of a set

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

  • Writing to fields in child table of a set

    Hi

    I am trying the Demo of A5 so I thought I might try converting an application I wrote long long ago in Clipper.

    The part I am stuck on right now is:
    I have imported two tables from the old app, a customer table "customer.dbf" and an orders table "standord.dbf".
    The original design related the two tables one to many by a manually entered customer code. I want to replace this key field with an auto increment field named ID.

    To implement the new relationship I have created a field called ID in each table and have populated the Customer->ID field with the incrementing IDs.
    My problem is copying this ID from the "customer" record to the related "standord" child records using Xbasic.

    I have a created a Set which I have called "fix cust_standord_set" based on the old relationship with customer related to standord by customer field in a one to many relationship. The parent and child records are all related perfectly on the old key field.

    I have previously been able to modify and update fields in a single table but I can't make the leap to sets.

    I am not sure how to write to fields in different tables in a set. I have not been able to stumble on an answer in the help or the forum. I am confused about issues such as where to use set pointers ands where to use table pointers.

    I was trying solutions based on the following skeleton.
    My understanding is that I should be able to loop through the set and copy Customer->ID to Standord->ID for each record sequentially, one record at a time from beginning of file to end.


    dim sptr as P
    dim ctbl as P
    dim stbl as P

    sptr=set.open_session("fix_cust_standord_set")
    sptr.fetch_first()

    WHILE .not. sptr.fetch_eof()

    'need instruction here
    'copy customer->id to standord->id

    sptr.fetch_next()
    END WHILE

    ' Close Table
    sptr.close()



    Thanks

  • #2
    Re: Writing to fields in child table of a set

    First thought here is that you could build a new set. Make StandOrd the parent and link 1:1 to the Customer table, based on the old id's. Then define an UPDATE Operation to assign the new autoinc field value in the linked Customer table to it's parent. You will need to disable the autoinc field rule in the StandOrd table first.

    Comment


    • #3
      Re: Writing to fields in child table of a set

      Forgive me if this is stating the obvious, but an auto_inc rule for Custid is only needed in the Customer table - not in Standord. The Orderno seems to be the likely candidate for an auto inc rule for that table. If you are wanting to change an old custid field that already exists in Standord, add the new field to that table first before you remove the old one. Same with the Customer table. Update the Customer field first before you set the new field rule to make the series. Then you can do a post matching the old custid fields to update the new field in Standord. Then go back to Customer and set the field rule to auto inc for any new records.

      In your new set link on Custid and when a new order is added, the Custid field in Standord will be filled in automatically.

      Forgive my manners Davo - Welcome to the board!
      Robin

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

      Comment


      • #4
        Re: Writing to fields in child table of a set

        Davo,

        I did clipper also.

        You should already have a linking field in both tables from your Clipper. use the same fields to link customers as master and then orders as one to many using the same linking fields. Once you have it all linked up. Go to you customer table and make the linking field an autoincrement. Alpha should go from the last value(highest) forward.



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

        Comment


        • #5
          Re: Writing to fields in child table of a set

          Originally posted by DaveM View Post
          Davo,

          I did clipper also.

          You should already have a linking field in both tables from your Clipper. use the same fields to link customers as master and then orders as one to many using the same linking fields. Once you have it all linked up. Go to you customer table and make the linking field an autoincrement. Alpha should go from the last value(highest) forward.
          Hi Dave,
          But what if his previously manually entered field will not transfer to an auto inc field in its present format? I am guessing that is why he wants a new field. If it does then your idea is the best!
          Robin

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

          Comment


          • #6
            Re: Writing to fields in child table of a set

            Robin,

            Clipper used basically the same fields we do in Alpha. There should be nothing to stop it and you can autoincrement numeric as well as character easily. A clipper programmer would learn early on to use one or the other, so I am sure it would.

            The only scenario i can think of is where a field he is using MUST be entered manually and he wants to make another field(maybe invisible to user) for linking. If so, you can make one new id field in the master table, copy the values in the old field to it(an operation). then do an operation to fill in all the children's matching id's, which you shouldn't have to because those are already in a field. could rename the child field to id for consistency.

            How about that for a bunch of rambling.


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

            Comment


            • #7
              Re: Writing to fields in child table of a set

              Originally posted by DaveM View Post
              Robin,

              Clipper used basically the same fields we do in Alpha. There should be nothing to stop it and you can autoincrement numeric as well as character easily. A clipper programmer would learn early on to use one or the other, so I am sure it would.

              The only scenario i can think of is where a field he is using MUST be entered manually and he wants to make another field(maybe invisible to user) for linking. If so, you can make one new id field in the master table, copy the values in the old field to it(an operation). then do an operation to fill in all the children's matching id's, which you shouldn't have to because those are already in a field. could rename the child field to id for consistency.

              How about that for a bunch of rambling.
              Rambling perhaps, but now on the same page! ;)
              Robin

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

              Comment


              • #8
                Re: Writing to fields in child table of a set

                Wow,
                So many replies, thanks guys. Sorry I still have not got it!

                Tom,
                intuitively I feel your solution will work and I thought I might be able to build the required update and learn from the xBasic generated. However I do not know how to build the Update. The Genie does not seem to have relevant choices for me.
                I would love to know if my sample skeleton could have been used as an approach to a solution because I could learn from the feedback.
                In Clipper I would have just looped through the Customer records and updated the field in each child record as I skip to the next customer record using something like REPLACE standord->id with customer->id

                My problem is trying to bring my old rusty ways of working to Object Oriented Approach.


                Robin
                Yes as you have pointed out, I have the auto incrementing rule only in the customer table and much to my joy A5 filled in the new ID field perfectly and automatically when I imported the table from the current application.
                This system does not use an order number. You did say "Then you can do a post matching the old custid fields to update the new field in Standord."
                I think this is what I am trying to do and I am trying to find out how to do it.
                Many thanks for the welcome and the comments.

                My problem as stated elsewhere is to put the ID into the corresponding child records so I can then remove the old key field altogether. I will then implement a hidden key field. The operator will then not have to concern themselves with the ID field at all.

                DaveM
                I gathered from some of your other posts you had previously used Clipper. Man those were the days. (No runtimes either! You could always dream of picking up 500 users)
                I remember when I was over the moon when I tried Blinker!. I let the whole OOP and GUI thing go by and I am now way out of my depth.

                Anyway better not ramble.

                So, sorry if I have not explained my issue clearly enough but I do have the old key field and the operator used to manually create a mnemonic of the customers name and use that as the key field. We had reasons to use that method at the time but it is no longer required. So I am going to use A5 auto increment as the Key starting at "AAAA". No problems in the parent I just want to update the child records manually now and then switch over to the new key field and remove the old key field from the structure as it will be redundant. This is a one off operation as I modify the old data to fit the new system.

                SO I have two questions
                (i) Could my skeleton have been simply changed or added to achieve my goal? If not then what was fundamentally wrong with that approach?
                (ii) How would I define the update operation suggested by Tom?

                Sorry but I am asking to be spoon fed at this point

                Comment


                • #9
                  Re: Writing to fields in child table of a set

                  Davo said:
                  In Clipper I would have just looped through the Customer records and updated the field in each child record as I skip to the next customer record using something like REPLACE standord->id with customer->id
                  Ok. Here's an example for you to play with and learn from. Unzip the attachment to an empty folder. Begin by opening the default form for the set and understanding how the records are linked. Then play with the button on the form. Have fun. I think you'll find the syntax very easy to pick up.

                  Later on ...

                  I added another approach, based on the use of a simple inverted set.

                  -- tom
                  Last edited by Tom Cone Jr; 05-22-2008, 07:03 AM.

                  Comment


                  • #10
                    Re: Writing to fields in child table of a set

                    Tom, compliments on the well commented example. That certainly is one of my weak points.

                    Using your example I tried to create an update operation against the inverted set. I chose Inv_no as the field to update and Inv_Hdrs->Inv_No as the expression. However this would not update any records. No error messages. Just the message that no records would be updated. After some tinkering,...(bang, crash, boom), I found that after changing either of the field names so that they were not the same (ie Inv_no) the operation updated the items table as expected. Can you (or anyone) confirm this?

                    Of course if one just uses your excellent code example there is no problem at all. :)
                    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


                    • #11
                      Re: Writing to fields in child table of a set

                      Thank you so much Tom,

                      I very much appreciate the time you have taken out to help me.
                      Also thank you to the others who contributed.

                      Your sample turned the light on for me. I get it.
                      I would encourage anyone interested in this topic to look at your sample file and understand it.

                      In retrospect a fairly simple problem but the learning curve is quite steep and it is easy to lose the plot.

                      I didn't want to use a form with this so I have taken the essentials from your sample and changed to suit my purposes.

                      I put my code here for any one in future looking at the topic.

                      So to recap - my problem was that I wanted to get rid of the existing linking key field in the customer and order tables. The existing system used a key field that was made up by the operator based on a mnemonic of the customer name.
                      It was a cumbersome and unnecessary system. I wish to move to a hidden incremental key field. "AAAA" gives me 456976 combinations which is quite adequate for my needs.

                      I created empty tables in A5 based on the structure of the tables I wished to import plus I added a new field named "ID" to both tables - this will become the new key field.
                      I created a field rule for the ID field in the customer table to be auto increment with a default value of "AAAA"

                      When I import the old data into the new customer table A5 populates the key field automatically this work very well.
                      When I import the items I did not have a way to copy the contents of the customer ID field int the related order records.

                      At this stage the two tables are still related perfectly well by the old key field.
                      So I create a set that I named "fix_cust_orders_set" based on the old relationship. They are related one to many by the old existing key field that I wish to dispense with.

                      The code below uses the old relationship to loop through the records and update the child records with the correct new ID code.
                      I can then delete the set definition and the old key fields and use the new incrementing hidden key.

                      This code will be run only once - when I am changing the old system over to the new system at commissioning.
                      It is part of importing and modifying the old to fit the new. By automating the changeover the procedure can be rehearsed and the outcome more reliable.

                      Code:
                      s = set.open("fix_cust_orders_set") 'Open the set to enforce the required relationship
                      ' Parent and child have 1 to many relationship linked on a field other than the one being written to
                      t_parent = s.customer 'get a pointer to the parent table - customer
                      t_child = s.orders 'get a pointer to the child table - order
                      while .not. t_parent.fetch_eof()   'establish parent loop, fetch_eof()	                            
                      	t_child.fetch_first() 'reset items pointer position for each new hdr record 	                      
                      	while .not. t_child.fetch_eof()    'establish child loop
                      		t_child.change_begin()		   'place table in change mode	
                      		t_child.id = t_parent.id  'assign the value of parent->id to Child->id
                      		t_child.change_end(.t.)        'save (commit) the changed record		
                      		t_child.fetch_next()  'fetch next linked items record
                      	end while 'loop back til no more to fetch	
                      	t_parent.fetch_next()  'fetch next hdr record
                      end while  'loop back til no more parent records to process
                      s.close()  'all done - close set
                      Last edited by Davo; 05-22-2008, 09:54 AM. Reason: format Code

                      Comment

                      Working...
                      X