[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1130
  • Last Modified:

Visual Fox Pro7 - Updating multiple fields for multiple account records

Can someone tell me the best way/the best command argument to use to update multiple records for the following fields in one shot to a dbf (free) table using Visual FoxPro 7?  The information update for each field will be the same for all records.  Basically, I have dozens of account for one client...their contact and billing info has changed, so I want to be able to apply the updates to all records in one shot.  Currently, the front end of my Customer/Trade database only allows for updating records 1 by 1.

 Essentially, I need help constructing the entire command argument to execute this.  I am familiar with some basic foxpro stuff but not this combinantion.

Table to update (free table)
Investor.dbf

Fields to be updated (all string fields except where indicated)
i_username
i_letter1
i_fname
i_lname
contact
i_address1
i_address2
i_phone
i_fax
i_wire [Memo]
ach [Memo]

Update all fields withwhere investor.taxid = XX-XXXXXXX
0
IO_Dork
Asked:
IO_Dork
  • 6
  • 5
  • 3
  • +2
1 Solution
 
CaptainCyrilCommented:
The investor profile should have been in another table. There is replication of data here.

One way
SELECT investor
REPLACE i_username WITH cUserName, ..., ach WITH mAch WHERE taxid = XX-XXXXXXX

UPDATE investor SET;
    i_username = cUserName,;
    ...;
    ach = mAch;
    WHERE taxid = XX-XXXXX
0
 
IO_DorkAuthor Commented:
let me clearify, I just want to update what is in each of those fields for all records with taxid of xx-xxxxxxx....i.e.:

Currently
Fname: John
Lname: Smith

Change to
Fname: Jane
Lname: Doe

Currently
Address1: 123 Main Street

Change to
Address1: 654 First Avenue


etc
0
 
jrbbldrCommented:
let me clarify, I just want to update what is in each of those fields for all records with taxid of xx-xxxxxxx

That is precisely what CaptainCyril showed you above.

If you don't see how that would do what you need, then something is missing in your description of what you are looking to do.

Good Luck
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jharkinsCommented:
Lets say for this example the info you want to start with is in one of the records for this client.

We just select the record, dump the fields into memory variables, update the variables, and update all the other records from the memory variables.  You can also use an array, or an object instead of individual memory variables to accomplish the same thing. I'm going to show this with variables

1) Select investor.dbf
2) Select the record that has the starting information

3) The following command will create a memory variable containing the contents of the corresponding field for each field in the FIELD list:

"SCATTER MEMVAR MEMO FIELDS i_username, i_letter1, i_fname, i_lname, contact, i_address1, i_address2, i_phone, i_fax, i_wire, ach"

IF you want a variable for ALL variables just leave off the FIELDS list:
SCATTER MEMVAR MEMO

IF you want a BLANK variable for ALL variables add the BLANK keyword
SCATTER MEMVAR MEMO BLANK

After this command you will have one "m." variable for each field
"m.i_username" will contain the contents of i_username, etc

4) From there you can update the data to what it needs to be.

5) After the data in the memory variables is up to date you can update all records several ways:

You can do it with one single command:
REPLACE FIELDS field1 WITH m.field1, field2 WITH m.field2, field3 WITH m.field3 FOR investor.taxid=XX-XXXXXX

This will update all records with matching taxid fields.


Or you can do it with a scan loop:
SCAN for investor.taxid=XX-XXXXXX
      GATHER MEMO MEMVAR FIELDS fieldlist
ENDSCAN

If you use the scan loop method you can skip the field list altogether by SCATTERing all fields, update the ones you want, and then GATHERing without needing the specify a fieldlist.

Hope this helps,

Jack
0
 
Olaf DoschkeSoftware DeveloperCommented:
Here's a simple sample of a multirecord sql-update.

First some sample data (simplyfied):
create cursor curData (taxid C(3), otherdata C(10))
insert into curData Values ("abc","address")
insert into curData Values ("abc","address")

* now one update:
update curData set otherdata = "new address" where taxid="abc"

This updates both records with taxid "abc". It's quite easy to understand the concept, if you think of an update as first querying all data matching the where, as a Select-SQL would do, and then apply the update SET FIELD=VALUE on all records.

Bye, Olaf.
0
 
jrbbldrCommented:
let me clearify, I just want to update what is in each of those fields for all records with taxid of xx-xxxxxxx

I guess the only question I would have is Update those records with WHAT?

If you want to update all of the specified records with the same values then what CaptainCyril showed you above is what you would use.

If you wanted to selectively update those specified records with something other than the same values, then you need to let us know what that something might be.

The approach could vary a good bit depending on your answer

One approach might be something like:
SELECT NewValues
SET ORDER TO CustID

SELECT Investors
SET RELATION TO CustID INTO NewValues
REPLACE ALL Investors.Field1 WITH NewValues.Field1,;
    Investors.Field2 WITH NewValues.Field2,;
     <and so on>;
   FOR Investors.taxid = XX-XXXXX

Good Luck
0
 
Olaf DoschkeSoftware DeveloperCommented:
You said "The information update for each field will be the same for all records", so I wonder why you need the solution with the relation.

By no doubt it's the best for VFP7, if you need to update one table with data from another one joined by a matching condition. VFP9 has UPDATE-SQL  to do that.

If you were asking to update one table with new data in another you could have had that solution earlier, not only by me, I'm sure.

Just saying. Everythings fine.

Bye, Olaf.
0
 
IO_DorkAuthor Commented:
I guess I still don't understandActually, the new data does not reside anywhere...I was just wanted to write an sql statement that says:


Update field investor.city with "Alibene" where taxID = 11-1111111
Update field investor.state with "TX" where investor.taxid = 11-1111111
Update field investor.zip with "76901" where investor.taxid = 11-1111111
....and so on for each field that needed to be updated.
0
 
jrbbldrCommented:
You could use a SQL UPDATE statement on your VFP table such as:
 UPDATE Investor;
   SET City = "Alibene",;
        State = "TX",;
        Zip = "76901",;
         <and so on...>;
   WHERE taxid = "11-1111111"


NOTE - since you have a dash in the Taxid example you gave above, it must be a character string which needs to be within quotes.

Or if you had all of the new values in memory variables, you could modify the SQL UPDATE statement to something like:
 UPDATE Investor;
   SET City = m.City,;
        State = m.State,;
        Zip = m.Zipcode,;
         <and so on...>;
   WHERE taxid = m.TaxID


That syntax AS-SHOWN would not work if the data table Investor was in a different data 'backend' such as SQL Server, but it could again be modified to work in a similar manner.

Good Luck
0
 
IO_DorkAuthor Commented:
Thank you jrbbldr, that is exactly what I was looking for!
0
 
IO_DorkAuthor Commented:
jrbbldr - can i use the same update command if the field that needs to be modified is a MEMO field? or is it a different command?
0
 
jrbbldrCommented:
If one of the fields to be Updated is a Memo field, nothing changes.

The same command format will work.

So that you can better understand what does and what does not work, you should create a TEST data table and run some tests yourself.  
You can do this is a small TEST PRG or directly from the Command Window depending on how complex your test needs to be.

* --- Create TEST Data Table ---
CREATE TABLE c:\Temp\Test.dbf FREE;
    (Field1 C(10), Field2 I, Field3 M, Field4 D, IDField C(10))
SELECT Test
* --- Create A Record ---
APPEND BLANK
* --- Populate That Record ---
REPLACE Field1 WITH 'Data1',;
   Field2 WITH 1,;
   Field3 WITH "This is Memo Field Data",;
   Field4 WITH DATE()-7,;
   IDField WITH "9-99"
* --- Create A 2nd Record ---
APPEND BLANK
* --- Populate That Record ---
REPLACE Field1 WITH 'Data2',;
   Field2 WITH 2,;
   Field3 WITH "This is Memo Field Data2",;
   Field4 WITH DATE()-4,;
   IDField WITH "99-99"
* --- Run UPDATE Test ---
UPDATE Test;
   SET Field1 = "Test",;
     Field2 WITH 99,;
     Field3 WITH "This is a Change To The Memo Data",;
     Field4 WITH DATE();
    FOR IDField = "99-99"
* --- Examine Results ---
BROWSE


Then when you see how things work, you can implement it into your 'live' code as needed.

Good Luck
0
 
IO_DorkAuthor Commented:
Thanks, again!!  Very helpful.
0
 
IO_DorkAuthor Commented:
one last question:

What if my memo field needs to have the data updated but formatted into with several lines (carriage return after each line)?  How do I enter the data in between the quotes?

UPDATE Investor SET i_wire = "Bank Name Address City ST Zip ABA#XXXX-XXXX-X
A/C #XXXXXXXXXX Final Credit: Institution Name" WHERE taxid = "11-1111111"
 

for example, data needs to look like this after its copied to the MEMO field:

Bank Name
Address
City, ST 79601
ABA#XXXX-XXXX-X
A/C #XXXXXXXXXX
Final Credit: Institution Name
0
 
Olaf DoschkeSoftware DeveloperCommented:
You can prepare multiline texts in a variable via

TEXT to Variable
Bank Name
Address
City, ST 79601
ABA#XXXX-XXXX-X
A/C #XXXXXXXXXX
Final Credit: Institution Name
ENDTEXT

And then do
UPDATE Investor SET i_wire = m.Variable WHERE taxid = "11-1111111"

jrbbldr already mentioned use of variables several times, this is one of the benefits.

Bye, Olaf.
0
 
jrbbldrCommented:
While Olaf's method above is a very good way (and perhaps the preferred method), you can also use an alternate method to populate your 'new memo value' string by utilizing  the CHR(13) to insert the CarriageReturn between the lines.

cNewMemoData = "Bank Name";
   + CHR(13);
   + "Address";
   + CHR(13);
   + "City, ST 79601";
   + CHR(13);
   + "ABA#XXXX-XXXX-X";
   + CHR(13);
   + "A/C #XXXXXXXXXX";
   + CHR(13);
   + "Final Credit: Institution Name"

Then do
UPDATE Investor SET i_wire = cNewMemoData WHERE taxid = "11-1111111"

Again try this (and/or Olaf's approach) in your TEST PRG to see how it works.  
Then use it in your 'live' code

Good Luck
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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