How to write data from memo field from one file to a memo field in a different file?

[mfrag44]

I am trying to create a new file from fields extracted from a larger file that contains two memo fields. I wrote a sql program that queries and extracts the fields I need for my new file. The routine works fine except the two memo fields are empty. Does anyone have a solution?

SELECT Service.sproblem, Service.swrkst, Service.smec_num, Service.misc2,;
Service.svcorder, Service.custcode, Employee.last, Employee.Emplcode;
FROM sds!service, sds!Employee;
WHERE Employee.Emplcode = Service.smec_num ;
ORDER BY Service.custcode, Service.svcorder, Service.swrkst ;
into dbf TMPSERV.dbf readwrite

SELECT TMPSERV
SCAN
M.SPROBLEM = TMPSERV.SPROBLEM
M.swrkst = TMPSERV.swrkst
M.smec_num = TMPSERV.smec_num
M.MISC2 = TMPSERV.MISC2
M.svcorder = TMPSERV.svcorder
M.CUSTCODE = TMPSERV.CUSTCODE
M.EMP_LAST = TMPSERV.LAST
SELECT MBLSERV
GO BOTTOM
APPEND BLANK
GATHER MEMVAR
=TABLEUPDATE(.T.)
SELECT TMPSERV
ENDSCAN

 

[Tore Bleken]

You must add the keyword Memo, Gather Memvar Memo. See Help for details.

BTW, you use Gather, I suggest that you also use Scatter when you read the data from the source table. Again, check Help.

 

[Olaf Doschke]

Gather is skipping memos, unless you specify [tt]GATHER MEMVAR MEMO[/tt]. Just look up the reference:

https://technet.microsoft.com/en-us/library/security/aa977807.aspx

MEMO

Specifies that the contents of memo fields are replaced with the contents or array elements or variables. [highlight #FCE94F]If you omit MEMO, memo fields are skipped when GATHER replaces the contents of fields with the contents of an array or variable[/highlight]. General and picture fields are always ignored in GATHER, even if you include the MEMO keyword.

Bye, Olaf.

 

[Olaf Doschke]

If you aim for shorter code, You can [tt]INSERT INTO MBLSERV SELECT * FROM TMPSERV[/tt] to copy over the data. No need to iterate data with [tt]SCAN..ENDSCAN[/tt] nor [tt]SCATTER[/tt] nor [tt]GATHER[/tt].

In detail you might want to pick fields in different order or only some of the TMPSERV fields instead of [tt]SELECT *[/tt], but you can not only query data into a new dbf file, you can just insert it into the target table in one step.

Bye, Olaf.

 

[mfrag44]

I'd like to thank each and everyone of you that responded to my request for help. Your sugggestions worked perfectly!

 

[Mike Lewis]

I'd like to thank each and everyone of you that responded to my request for help

I'm sure they both appreciate your thanks. Keep in mind that you can click the "Great post" link at the bottom of each message to show that the post was helpful. This is also useful for other visitors who might have a similar problem and are looking for questions that have been answered.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

I would have used a construct such as this:

SELECT TMPSERV
SCAN
	SCATTER TO TMPFIELDS MEMO
	SELECT MBLSERV
	APPEND BLANK
	GATHER FROM TMPFIELDS MEMO
	SELECT TMPSERV
ENDSCAN

or, more likely for me - as I prefer to code without SCAN..ENDSCAN (for no good reason, just habitual)

SELECT TMPSERV
GO TOP
DO WHILE .NOT. EOF()
	SCATTER TO TMPFIELDS MEMO
	SELECT MBLSERV
	APPEND BLANK
	GATHER FROM TMPFIELDS MEMO
	SELECT TMPSERV
	SKIP
ENDDO

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Tore Bleken]

Actually, with VFP9 you can do it all in only one line of code, something like

Insert Into Destination (field list) Select field list from source where somecondition.

 

[GriffMG]

Tbleken, I think Olaf said exactly that above, I was just offering mine as a comparison.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Tore Bleken]

I don't want to offend anyone, but I only want to "empty myself" about one thing that always surprises me. And that's why it's so important to show different ways of doing things. If there's a one liner, isn't that enough? If there's a better way, I can accept that it's shown, of course. But just to show that there are other solutions, why? I think that the more solutions we give, the more we confuse people.

And last, please note that my thoughts are not necessarily so obvious in this thread. In another forum I frequent, a huge number of people are seemingly spending a lot of time to find alternative solutions. Most of the answers there are more confusing than helpful.

 

[SamBones]

tbleken, no offense. As I see it, there are two huge reasons for giving multiple answers to questions.

Some people come to this site just looking for a quick solution. For them a single answer might be good. BUT, most of them don't have time, or are unable to explain their FULL situation, so that one answer may or may not actually be the best answer. When you get multiple answers that do things differently, they usually are based on different sets of circumstances or technology. Hopefully the person posting the question will take the time to understand the strengths and weaknesses of the different solutions and use the one that fits their problem best.

Some people come to this site to learn. For them, multiple answers show them that there really isn't a single answer to most questions. If that person wanting to learn takes the time to understand each answer, they will become a very strong technical person that will be able to solve problems regardless of the situation they are working in.

I guess another reason would be because multiple people answer the questions here, and different people approach problems differently. It's all based on their knowledge and experience. Some may have better solutions than others, but that may not become obvious until several solutions have been given and the original poster has provided more information to clarify. These questions aren't like "what is 2 + 2", these are questions that really do have multiple solutions.

I just think it's kind of silly that you expect only one solution to be given and assume it will be the best one. This is a "Forum", which means it's a place where ideas are discussed. That includes offering multiple ideas that can be discussed. I'm not sure of the forums that you visit, but the ones I do , when someone offers a solution where one was already given, there's usually an explanation of why the new solution might be a better choice, under what circumstances. I personally find that very educational. Anyone who is not learning in technology is going backwards. I personally welcome seeing different approaches to a problem.

 

[Tore Bleken]

Sam,

no, I don't want only one solution. I agree with everything you write. My point us that sometimes people come up with another solution for no other reason than to show that it's possible to do it in another way. Also please notice that I don't see this so much on this forum. On another forum I have seen silly discussions where the "correct" should be an easy one, but still people come up with a lot of more or less stupid answers. I will give you one example which happened a few years ago, I rephrase it according to my memory.

Q: I have two text strings, ta='abc' and tb='abcd'. Why does ?ta=tb give .F. while ?tb=ta gives .T.? I expect .F. in both cases.

One of the first answers was a relatively long explanation and a few references, like Set Exact and ==. In my opinion a really good and fulfilling answer, and very helpful. Still people came up a lot of "workarounds" and "alternative solutions", and a loooong discussion followed. I especially remember the "foolproof" solution of "If ta=tb and tb=ta and .not ta#tb and .not tb#ta"! My only reply in that thread was "Sigh.....!"

 

[TamarGranor]

FWIW, using DO WHILE when SCAN would work is, in my view, a bad idea for the following reasons:

1) It automatically starts at the top, so you don't need GO TOP beforehand.
2) You don't have to remember to reselect the work area at the end; it's automatic.
3) You don't have to include SKIP; it's automatic.
4) It's faster. (For an empty unordered loop, SCAN takes about 70% of the time of DO WHILE.)

Not as bad an advantage as FOR has over DO WHILE, where you gain about an order of magnitude, but still.

Tamar

 

[GriffMG]

It's a bit off topic, but I personally like the DO WHILE.. ENDDO approach, but then when writing new code I close my IF... ENDIF and DO CASE ENDCASE structures as I create them.

Each to their own.


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

???

 

[TamarGranor]

But why do you prefer DO WHILE? SCAN not only has the advantages I laid out, but also tells whoever's reading the code instantly that it's a loop through a table.

Tamar

 

[GriffMG]

Well, it's habitual.
No one else reads my code, so DO WHILE .NOT. EOF() is pretty clear to me.



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

Griff, even accepting that habit and "muscle memory" your argument fails to make sense to me:

when writing new code I close my IF... ENDIF and DO CASE ENDCASE structures as I create them.

When you write SCAN you can also immediately add the ENDSCAN line and then put the cursor into the middle and start coding the loop body code.

There is a thing you gain with WHILE NOT EOF() because the two automatisms Tamar mentions don't run, the workarea is not automatically selected is seldom an advantage. It can make your loop fail, if you just check EOF() without specifying the alias, you might never end the loop, when finally a workarea is selected you don't loop and SKIP in. SKIP 1 not being done automatic has a questionable advantage: It means you can decide to iterate one record twice or you already have skipped anywhere else beforehand, maybe even not with a SKIP but with a LOCATE or GOTO. Partly you can also do the same in SCAN..ENDSCAN, you just need to locate the record one before the next to process, and if that menas a SKIP -1 in comparison to WHILE loop code.

Anyway, such fiddling with the record pointer is not best practice. To illustrate that let's look at a C/C++/C#/Java/Javascript/... for loop:

for (i = 1; i<=10; [b]i++[/b])

The first part is an initialization (i=1), the second part (i<=10) is evaluated and when getting false ends the loop the third part (i++) happens after the loop body is executed and before the second part is evaluated again. i++ means (as you should know even not being a C/C++/Java/Javascript/... developer) i is increment by 1. This compares to SKIP 1 in your WHILE loop.

The for construct also allows SKIP 2, just change i++ to i+=2 or i=i+2.

Now the argument against such flexibility and even worse possibilities to increment two counters (i++,j++) when the two increments are separated by comma still mean the third part of the for loop "parameters":

It is considered best practice to leave it at the simple i++ always and not make use of any flexible other ways, some compilers will even warn you, if you do something else.

The SCAN loop is the concise loop for scanning the rows of a table. You use ot for that purpose. You profit of rushmore optimizations of FOR and REST WHILE and any such scopes and you throw all these advantages away by not using it. That's all there is to know in the end.

Bye, Olaf.

 

[GriffMG]

Hi Olaf

It is just habitual, I type the whole thing DO WHILE, SELECT, SKIP and ENDDO every time I start a loop, before I type the contents, I don't even think about it.



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

And you can add a new habit of writing SCAN..ENDSCAN You overlook it's not only about the advantage to not miss something, you miss the possible rushmore optimization of scan loops.

Type SCANEND[Space] and you end up this way:

SCAN |

ENDSCAN

Cursor position could be more ideal for the usual case to be in the next line, but you can continue writing a FOR condition or other scope operators this way.
There are several of those macros, simply start up intellisense manager, there also is a DOWHILE macro, but it doesn't add the SKIP and SELECT. With SCANEND it's not needed. Also you can simply change the data memoe field and set the ~ signe where the editor text cursor should end up.

Bye, Olaf.