• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1451
  • Last Modified:

VFP, Append Grid data to a table +

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
jws2bay
Asked:
jws2bay
  • 2
  • 2
  • 2
  • +3
2 Solutions
 
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
_SCREEN.Caption holds the text of the application.

I did not understand your first question.
0
 
pcelbaCommented:
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
 
jws2bayAuthor Commented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
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
 
tusharkanvindeCommented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
jrbbldrCommented:
"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
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
jws2bayAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now