Writing to fields in child table of a set

[Davo]

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

[Tom Cone Jr]

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.

[MoGrace]

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!

[DaveM]

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.



.

[MoGrace]

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.

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!

[DaveM]

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.


.

[MoGrace]

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.

Rambling perhaps, but now on the same page! ;)

[Davo]

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

[Tom Cone Jr]

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

[Tim Kiebert]

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. :)

[Davo]

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.

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