I want to copy data from a number of different mv columns to a single new mv column.

1. How can I do that so that the data transferred from each data column is appended to the new data column and not overwritten?

How about:

Read @RECORD From [/color]FILE_IN, [/color]@ID Else 
    [/color]status=Set_FSError()  
    [/color]Return  
End 
[/color] 
[/color]NEW=[/color]@RECORD1][/color]: [/color]@VM[/color]: [/color]@RECORD2][/color]: [/color]@VM[/color]: [/color]@RECORD3] 
[/color] 
[/color]** Transfer col 1 & col2 & col 3 to col 4 
[/color]@Record4] [/color]= NEW 
[/color]@RECORD1] [/color]= [/color]'' 
[/color]@RECORD2] [/color]= [/color]'' 
[/color]@RECORD3] [/color]= [/color]'' 
[/color] 
[/color]Write @Record to [/color]FILE_IN, [/color]@ID else 
    [/color]Msg([/color]"Error Write"[/color]) 
[/color]End[/color][/color][/size]         
               

Could you possibly explain this a bit further? I'm not clear where this sits in the whole procedure, or otherwise what the process is. An explanation of each line would be great, particularly what each variable is. Cheers Joh

Hi Joh,

Perhaps you need to be more explicit to what your requirements are. What Kauko has given you is a code snippet that could be used in a Write Procedure (ie on-going amendment) or for a one off loop through a table.

Are your requirements to copy/move the data ?? Within a table or to a new table ?? Columns 1 + 2 + 3=4 or similar ??

Simon

UK based if that helps …

Okay, I'll be more explicit (and please consider me a klutz in terms of programming).

I have a table which is designed to document details related to performing arts items, such as set models, costumes and reviews. It also details people related to that item. This is done by using a number of mv colums. Each separate mv column is devoted to a specific role of a person, i.e. composer, author, actor, designer, etc. Each list box is based on an ID (the key of a related table), and two symbolic fields for first name and last name. Using separate mv columns for each role seemed a good idea at the time.

Now the owners of the database want all those separate columns combined in a single mv column and have a fourth column in the listbox listing the role.

I know how to transfer data from one, single value column to another in the same table, but this is much more complex as not only there are multiple values in each record, but each next tranfer needs to append to what was transferred before. AND with each transfer a value needs to entered (such as 'composer' or 'playwright') into the new fourth column in the listbox.

I hope this is clear. It would be easier if I could show a sketch and I would do that if I knew how.

Any help is greatly appreciated.

Joh

Hi Joh,

So I am led from what you are saying to believe something like this

CompData @vm PWData @vm CompData @vm CompData @vm PWData …

'Composer' @vm 'PlayWright' @vm 'Composer' @vm 'Composer' @vm 'Pla…

And that for instance

Composer data comes from position 1

Playwright data from position 2

Conductors in position 3

Dancers in position 4 etc etc

Does this meet what you are trying to do ??

Regards,

Simon

Joh,

If I understand you want to take columns A,B,C which are dedicated to specific roles and merge all of this into a new setup of mv columns that are role generic. I would do this like this one for each type of role:

oldData=@Record

columnCnt=count(oldData,@vm) + (oldData ne '')

roles=str("C":@vm,columnCnt) ;* composer?

roles-1,1='

newData =@Record

newRoles=@Record

if len(newData) then newData := @vm ; newRoles := @vm

newData := oldData

newRoles:= roles

@record=newData

@record =newRoles

HTH,

John

Hi Simon (and John),

Lest I misunderstand the code, I'll set out what I want as follows:

This is what I have ('ID' is the key from another Table - First and Last Name are relationally linked):

Column: Composers: (displayed as listbox)

ID | First Name | Last Name

345 | John | Smith

567 | David | Jones

21 | Felicity | Goscombe

etc.

Column: Actors:

ID | First Name | Last Name

543 | Paul | Hocking

765 | Hilary | O'Neill

11 | John | Hill

etc.

I have more than a dozen of these columns. After transfer, they should result in the single mv column:

Column: Associated artists:

ID | First Name | Last Name | Function

345 | John | Smith | composer

567 | David | Jones | composer

21 | Felicity | Goscombe | composer

543 | Paul | Hocking | actor

765 | Hilary | O'Neill | actor

11 | John | Hill | actor

etc.

So I need code to transfer multivalues from one column to a new one, do this a dozen or so times, with each time the data appended to what was transferred before + entering the value in the additional Function column. This new value is not relationally linked to the ID, but added.

If you can provide me with the code to do this, I can modify the variables in that code for each next iteration (ie. change the column number from which data is taken and change 'composer' to 'designer' to 'musician' for each function etc.) I don't need anything fancy, because this is a one time application to reorganise the database and won't have ongoing value or be implemented by the client.

I hope that is clear and that you can help.

Cheers

Joh

Hi Joh,

I think I am getting there with this. Can you just clarify the following …

The data in this table, I'll call ARTISTS, only contains the Ids to link to the forenames/surnames in another table, I'll call PERSONAL for ease of understanding.

I am also guessing at this point that the PERSONAL table does NOT hold the information as to the persons 'function'. I may well be wrong on this.

If this is the case then what I would do is :-

a) Do the copy for the data in your ARTISTS table to copy the data from columns a to y into z, and while doing this put an entry in the PERSONAL table entry (using the current index values) for their function.

b) Remove all current relational indexes

c) Create the 3 new relational indexes as required.

If you can confirm this then the code should be relatively easy to sort.

Simon

Hi Simon,

Yes, you are almost correct. The PERSONAL table does list functions, but this cannot be linked relationally, because the same person may have a variety of functions in different contexts (the same person may be a composer in on record, but an actor in another). So the second part of your a) can't be done. I guess that makes it a lot more difficult?

Cheers

Joh

Hi Joh,

Not so much more difficult, but I was thinking as much as future data at the time.

Perhaps if we continue this off the forum, you can get me at [email protected]

Regards,

Simon

View this thread on the Works forum...