Solved

VFP, Append Grid data to a table +

Posted on 2010-09-10
9
1,172 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 41

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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 safe way to clean winsxs folder from your windows server 2008 R2 editions
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now