Solved

VFP, Append Grid data to a table +

Posted on 2010-09-10
9
1,241 Views
Last Modified: 2012-05-10
I'm working VFP 9.0 SP2  - without getting into the details, I am updating a table from a cursor using the following:

IF !USED ('special_order_items')
  USE special_order_items IN 0
ENDIF
SELECT special_order_items
APPEND FROM DBF('ordered_list') for !EMPTY(ordered_list.part_number)
SELECT 0

I want to add an additional field (order_number) to each item which is being appended to the table.  I didn't have the order_number at the time the cursor was created.  Is there a simple way to add this into the append statement?

2nd Question:  How do you change the Title at the very top of the app. above the menu bar next to the icon?  I've changed the icon, but haven't found the source of the text.

Thanks in advance
 
0
Comment
Question by:jws2bay
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 27

Assisted Solution

by:CaptainCyril
CaptainCyril earned 100 total points
ID: 33647169
_SCREEN.Caption holds the text of the application.

I did not understand your first question.
0
 
LVL 42

Expert Comment

by:pcelba
ID: 33647220
2nd question:  

_screen.Caption = "New Title"

1st question:

You have to go through all records in a loop and update the order number by appropriate value:

USE YourTable
SCAN ALL
  REPLACE order_number WITH  <Your New value>
ENDSCAN

If the <Your New value> is in some different table then you have to find appropriate record in that table and use the value in REPLACE command.

Another possibility is to use UPDATE - SQL command but you did not provide enough info to be more specific.

0
 

Author Comment

by:jws2bay
ID: 33647364
I don't know all of the append options. I was wondering if it could be as easy as something like this?

SELECT special_order_items
APPEND FROM DBF('ordered_list') , order_no   for !EMPTY(ordered_list.part_number)
SELECT 0

0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 27

Expert Comment

by:CaptainCyril
ID: 33647389
It is like this

APPEND FROM DBF('...') FIELDS order_no FOR <condition>

This way you are importing one field.

If you are updating one field based on a relationship to another file then UPDATE is what you should be looking into.

If you wish to create a field:

ALTER TABLE <table> ADD COLUMN order N(10)
0
 
LVL 14

Expert Comment

by:tusharkanvinde
ID: 33647437
Since you are adding records, I think you need INSERT - SQL

INSERT INTO special_order_items ( Your Field List Here) SELECT ordered_list.*, YourOrderNumber where !EMPTY(ordered_list.part_number)
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 33648460
There is no way to add a field within the append command. If you can add the field to the cursor when it is created in the first place you could stay with the append.

I would also go in the direction of tushar, don't append the cursor, which lacks an order_no field, INSERT INTO FROM SELECT and add that order_no field on the fly during the SELECT FROM the cursor.

You can easily generate the field list of the cursor: set a breakpoint before append, then in the commandwindow do:

Set Fields global
Set Fields To all Like alias.*
_cliptext = Set("Fields",1)

Change alias to the cursor name

after executing the code you have the list of field names in the cliptext and can insert the list where tushar wrote "( Your Field List Here)". and add the order_no field there as last, like the position of "YourOrderNumber" within the SELECT part of the INSERT. Perhaps make that CAST(YourOrderNumberHere AS I) or "AS C(5)" or whatever the
field type is.

Bye, Olaf.
0
 
LVL 12

Accepted Solution

by:
jrbbldr earned 400 total points
ID: 33649346
"I didn't have the order_number at the time the cursor was created."

First off, when you first created your cursor you could have added the field with an empty value at that time, used the cursor as needed and then it would be available when you do your Append.

SELECT <whatever field(s)>,;    SPACE(10) AS Order_no;    FROM <your tables>    WHERE <whatever selection criteria>    INTO CURSOR ordered_list READWRITE

Or you can create yet another cursor just for your APPEND and add the extra field to it BEFORE the APPEND.

SELECT *,;     Order_no;    FROM Ordered_list    WHERE !EMPTY(ordered_list.part_number)    INTO CURSOR AppendDataSELECT special_order_itemsAPPEND FROM DBF('AppendData')

And, as was suggested above, you can use the ALTER TABLE command to add a field to a table.
 
 However I am not sure where you are acquiring the Order_no data value from.  
I assume that by the time of the APPEND, you will have one or more values that can be used to populate the associated field.

Something to note...
    order_number     is 12 characters long and, as such, is not a valid field name for a FREE VFP data table (that's why I shortened it above in my examples).
If you try to add it, as is, into your SELECT SQL statement, it will be truncated.

Assuming that you need the full field name as stated, then you will have to put it into a VFP Database which can support longer field names.

Good Luck

0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 33649984
As you finally want to append the cursor to the table, you could also initially create it by

IF !USED ('special_order_items')
  USE special_order_items IN 0
ENDIF
SELECT *  FROM special_order_items WHERE .F. INTO CURSOR curGrid READWRITE

Then you have by definition all fields as in the table.

Or use AFIELDS and CREATE CURSOR FROM ARRAY.

Bye, Olaf.
0
 

Author Comment

by:jws2bay
ID: 33651025
Thanks for all the helpful info.

jrbbldr:

This is along the lines of what I was trying to do.

"First off, when you first created your cursor you could have added the field with an empty value at that time, used the cursor as needed and then it would be available when you do your Append.

SELECT <whatever field(s)>,;
   SPACE(10) AS Order_no;
   FROM <your tables>
   WHERE <whatever selection criteria>
   INTO CURSOR ordered_list READWRITE"

I'll work with this idea.

Thanks for the help

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question