auto assign codes to field

[garyzak]

Hello;
I hope I can get a little help/direction.
I have one more step in this process to get my program working correctly. There is a field called AFFIL that is supposed to have affiliate codes automatically assigned as the files are uploaded in the temp directory. Examples of the codes are:
BMPR, HTTO etc. Is there a way(perhaps in a case statement, to have these codes automatically entered into the AFFIL field if they are part of the initial Excel file?
I am uncertain where and how to accomplish this in VFP.
I greatly appreciate any help.

*SET EXCLUSIVE OFF

* 1. Setup excel headers (Source doc)

* 2. Save excel as dbf (Table #1)

* 4. Export Exceed to excel

* 5. Convert Exceed excel to dbf (Table # 2)

* 6. Scan for dups

* 7. Export to excel (Table # 1)

* 8. Import into Exceed


CLOSE database
SET SAFETY OFF
SET DELETED on
clear
Set Default To c:\temp

cstr = Alltrim(Getfile('Excel:xls'))
*Alltrim..Removes all leading and trailing spaces or parsing characters from the specified character expression
Import From (cstr) Type Xls

*store ALIAS() TO cTableName

STORE JUSTFNAME(cstr) TO

STORE getwordnum(cFileName,1,".") TO cTableName
* Getwordnumreturns a specified word from a string
use

*IMPORT FROM c:\temp\+&cstr TYPE xls

SELECT 2
*this is the MERGE from Exceed
IMPORT FROM c:\temp\merge.xls TYPE XLS

SELECT 1
USE "c:\temp\"+(cTablename)+".dbf" exclusive
alter table (cTablename) rename column a to AIDNO
alter table (cTablename) rename column b to fNAME
alter table (cTablename) rename column c to lNAME
alter table (cTablename) rename column d to COMPANYDIV
alter table (cTablename) rename column e to ADDRES1
alter table (cTablename) rename column f to CITY
alter table (cTablename) rename column g to STATE
alter table (cTablename) rename column h to ZIP
alter table (cTablename) rename column i to PHONE
alter table (cTablename) rename column j to PHTYPE
alter table (cTablename) rename column k to PHNOTES
alter table (cTablename) rename column l to COUNTY
alter table (cTablename) rename column m to AFFIL

GOTO 1

DELETE

STORE 0 TO ncount
STORE 0 TO nduplicates

SCAN all

STORE ncount +1 TO ncount
STORE ALLTRIM(AIDNO) TO cAIDNO
STORE ALLTRIM(fname) TO cFname
STORE ALLTRIM(lname) TO cLname
STORE ALLTRIM(COMPANYDIV) TO cCOMPANYDIV
STORE ALLTRIM(addres1) TO cAddres1
STORE ALLTRIM(CITY) TO CCITY
STORE ALLTRIM(STATE) TO CSTATE
STORE ALLTRIM(ZIP) TO CZIP
STORE ALLTRIM(PHONE) TO CPHONE
STORE ALLTRIM(PHTYPE) TO CPHTYPE
STORE ALLTRIM(PHNOTES) TO CPHNOTES
STORE ALLTRIM(COUNTY) TO CCOUNTY
STORE ALLTRIM(AFFIL) TO CAFFIL

STORE "Source: "+cFname+" "+cLname+CHR(13)+cAddres1+", " +CCITY+", "+CSTATE+", "+CZIP TO cSource

SELECT 2

LOCATE FOR ALLTRIM(b) = cLname AND ALLTRIM(c) = cFname

IF FOUND()

store nduplicates+1 to nduplicates

STORE "Destination Record: "+ALLTRIM(c)+" "+ALLTRIM(b)+CHR(13)+ALLTRIM(i)+", " +ALLTRIM(k)+", "+ALLTRIM(l)+", "+ALLTRIM(m) TO cDestination

eMessageTitle = "Duplicate Checker"

eMessageText = "Duplicate found: "+csource+CHR(13)+CHR(13)+;
cDestination+CHR(13)+CHR(13)+ "Would you like to delete source record?"

nDialogType = 3 + 16 + 256

* 4 = Yes and No buttons

* 16 = Stop sign icon

* 256 = Second button is default

nAnswer = MESSAGEBOX(eMessageText, nDialogType, eMessageTitle)

DO CASE
CASE nAnswer = 2

cancel

CASE nAnswer = 6

SELECT 1

*this will delete record from data session
DELETE

CASE nAnswer = 7

SELECT 1

ENDCASE

ENDIF

*code if no dups

ENDSCAN

eMessageTitle = 'No Duplicates'
eMessageText = 'Completed Search. '+STR(ncount, 4)+' Records Searched'+Chr(13)+Chr(13)+STR(nduplicates, 4)+" Duplicates Found"

nDialogType = 0 + 16 + 256
* 4 = Yes and No buttons
* 16 = Stop sign icon
* 256 = Second button is default

nAnswer = MESSAGEBOX(eMessageText, nDialogType, eMessageTitle)

PACK
Select 1
COPY TO Exceed.CSV TYPE CSV

 

[garyzak]

Sorry;
just to clarify:
The codes BMPR, HTTO should corespond to certain companies:
BMPR = Buffalo Medical Primary Reception
HTTO = Hospital Transfer Trace Occupational
etc...
Please let me know if ther is a simple way to do this..
Thank you in advance for your time/help..

 

[JRB-Bldr]

"Is there a way(perhaps in a case statement, to have these codes automatically entered into the AFFIL field if they are part of the initial Excel file?"

It would be a lot easier to follow your code if instead of using SELECT 1 you would use SELECT Merge or whatever is the appropriate alias reference.

And you do not need to use
STORE ALLTRIM(COUNTY) TO CCOUNTY
instead use
cCounty = ALLTRIM(County)

And I am not clear as to which table is the Initial Excel file.

Regardless, whichever table is the Initial Excel file you can get the value from the Inital table and then use the REPLACE command into the other table.

SELECT Merge
cAffil = Merge.Affil

SELECT Other
REPLACE Other.Affil WITH cAffil

Or something like that.

Good Luck,
JRB-Bldr

 

[baltman]

Regarding duplicates:
SELECT Lname,Fname,COUNT(*) as nCnt group by Lname,Fname having nCnt into cursor curDupList

Regarding codes
alter table dbf() alter column AFFIL c(100)
scan
do case
case alltr(AFFIL)=="BMPR"
repl AFFIL with "Buffalo Medical Primary Reception"

case alltr(AFFIL)=="HTTO"
repl AFFIL with "Hospital Transfer Trace Occupational"
endcase
endscan

or

Store maps in a table and scan

use mymaps
scan
cCode = alltr(AFFIL)
cDest = Fullname

update MyTable set AFFIL = m.cDest where alltr(AFFIL)==m.cCode
endscan

 

[baltman]

excuse the omission on the having clause...

SELECT Lname,Fname,COUNT(*) as nCnt group by Lname,Fname having nCnt>1 into cursor curDupList

 

[garyzak]

Thank you jrbbldr and baltman...All set. I appreciate your time and expertise..

 

[garyzak]

Hello all;
this is the last thing, I promise....
Ran into a snag with the content managment program Exceed. It stores ID's in the Importid field from old records so at anytime there are id's like
1000-2000, with 35000 being the top end. Wehn I export to the .CSV, I need the numbers to start at the 35,000 and increment up so the Exceed will allow them.
Can you please show me an increment proceedure afetr the dups are checked so the id filed will be populated starting at 35,000?
I greatly appreciate any help.
Here is the newest version of the code. Thank you for your help:
*SET EXCLUSIVE OFF

* 1. Setup excel headers (Source doc)

* 2. Save excel as dbf (Table #1)

* 4. Export Exceed to excel

* 5. Convert Exceed excel to dbf (Table # 2)

* 6. Scan for dups

* 7. Export to excel (Table # 1)

* 8. Import into Exceed


CLOSE database

SET SAFETY OFF

SET DELETED on

clear

Set Default To c:\temp

cstr = Alltrim(Getfile('Excel:xls'))
*Alltrim..Removes all leading and trailing spaces or parsing characters from the specified character expression
Import From (cstr) Type Xls

*store ALIAS() TO cTableName

STORE JUSTFNAME(cstr) TO cFileName

STORE getwordnum(cFileName,1,".") TO cTableName
* Getwordnumreturns a specified word from a string
use

*IMPORT FROM c:\temp\+&cstr TYPE xls

SELECT 2
*this is the MERGE from Exceed
IMPORT FROM c:\temp\merge.xls TYPE XLS

SELECT 1

USE "c:\temp\"+(cTablename)+".dbf" exclusive

alter table (cTablename) rename column a to importid
alter table (cTablename) rename column b to fNAME
alter table (cTablename) rename column c to lNAME
alter table (cTablename) rename column d to COMPANYDIV
alter table (cTablename) rename column e to ADDRES1
alter table (cTablename) rename column f to CITY
alter table (cTablename) rename column g to STATE
alter table (cTablename) rename column h to ZIP
alter table (cTablename) rename column i to PHONE
alter table (cTablename) rename column j to PHTYPE
alter table (cTablename) rename column k to PHNOTES
alter table (cTablename) rename column l to COUNTY
alter table (cTablename) rename column m to AFFIL
alter table (cTablename) rename column n to atype
alter table (cTablename) rename column o to saluttype
GOTO 1

DELETE

STORE 0 TO ncount
STORE 0 TO nduplicates

SCAN all

STORE ncount +1 TO ncount
STORE ALLTRIM(importid) TO cimportid
STORE ALLTRIM(fname) TO cFname
STORE ALLTRIM(lname) TO cLname
STORE ALLTRIM(COMPANYDIV) TO cCOMPANYDIV
STORE ALLTRIM(addres1) TO cAddres1
STORE ALLTRIM(CITY) TO CCITY
STORE ALLTRIM(STATE) TO CSTATE
STORE ALLTRIM(ZIP) TO CZIP
STORE ALLTRIM(PHONE) TO CPHONE
STORE ALLTRIM(PHTYPE) TO CPHTYPE
STORE ALLTRIM(PHNOTES) TO CPHNOTES
STORE ALLTRIM(COUNTY) TO CCOUNTY
STORE ALLTRIM(AFFIL) TO CAFFIL
STORE ALLTRIM(atype) TO Catype
STORE ALLTRIM(saluttype) TO Csaluttype


STORE "Source: "+cFname+" "+cLname+CHR(13)+cAddres1+", " +CCITY+", "+CSTATE+", "+CZIP TO cSource

SELECT 2

LOCATE FOR ALLTRIM(b) = cLname AND ALLTRIM(c) = cFname

IF FOUND()

store nduplicates+1 to nduplicates

STORE "Destination Record: "+ALLTRIM(c)+" "+ALLTRIM(b)+CHR(13)+ALLTRIM(i)+", " +ALLTRIM(k)+", "+ALLTRIM(l)+", "+ALLTRIM(m) TO cDestination

eMessageTitle = "Duplicate Checker"

eMessageText = "Duplicate found: "+csource+CHR(13)+CHR(13)+;
cDestination+CHR(13)+CHR(13)+ "Would you like to delete source record?"

nDialogType = 3 + 16 + 256

* 4 = Yes and No buttons

* 16 = Stop sign icon

* 256 = Second button is default

nAnswer = MESSAGEBOX(eMessageText, nDialogType, eMessageTitle)

DO CASE
CASE nAnswer = 2

cancel

CASE nAnswer = 6

SELECT 1

*this will delete record from data session
DELETE

CASE nAnswer = 7

SELECT 1

ENDCASE

ENDIF

*code if no dups

ENDSCAN

eMessageTitle = 'No Duplicates'
eMessageText = 'Completed Search. '+STR(ncount, 4)+' Records Searched'+Chr(13)+Chr(13)+STR(nduplicates, 4)+" Duplicates Found"

nDialogType = 0 + 16 + 256
* 4 = Yes and No buttons
* 16 = Stop sign icon
* 256 = Second button is default

nAnswer = MESSAGEBOX(eMessageText, nDialogType, eMessageTitle)

PACK

Select 1
COPY TO Exceed.CSV TYPE CSV

 

[JRB-Bldr]

Again, there are a variety of approaches.
One of those might be:

* --- After your above SCAN/ENDSCAN and your DeDupe routine ---
mnID = (35000 - 1)  && Establish Default Starting Number
* --- Cycle through records incrementing ID and inserting into field ---
SELECT Merge
SCAN
  mnID = mnID + 1
  REPLACE Merge.Importid WITH mnID  && If the field is a Numeric
ENDSCAN

OR use
REPLACE Merge.Importid WITH ALLTRIM(STR(mnID)) && If the field is a Character

Good Luck,
JRB-Bldr

 

[garyzak]

Thanks JRB-Bldr;
stil getting "Variablbl importid not found" even though that field is in Merge...

*SET EXCLUSIVE OFF
* 1. Setup excel headers (Source doc)
* 2. Save excel as dbf (Table #1)
* 4. Export Exceed to excel
* 5. Convert Exceed excel to dbf (Table # 2)
* 6. Scan for dups
* 7. Export to excel (Table # 1)
* 8. Import into Exceed
CLOSE database
SET SAFETY OFF
SET DELETED on
clear
Set Default To c:\temp
cstr = Alltrim(Getfile('Excel:xls'))
*Alltrim..Removes all leading and trailing spaces or parsing characters from the specified character expression
Import From (cstr) Type Xls

*store ALIAS() TO cTableName

STORE JUSTFNAME(cstr) TO cFileName

STORE getwordnum(cFileName,1,".") TO cTableName
* Getwordnumreturns a specified word from a string
use

*IMPORT FROM c:\temp\+&cstr TYPE xls

SELECT 2
*this is the MERGE from Exceed
IMPORT FROM c:\temp\merge.xls TYPE XLS

SELECT 1

USE "c:\temp\"+(cTablename)+".dbf" exclusive

alter table (cTablename) rename column a to importid
alter table (cTablename) rename column b to fNAME
alter table (cTablename) rename column c to lNAME
alter table (cTablename) rename column d to COMPANYDIV
alter table (cTablename) rename column e to ADDRES1
alter table (cTablename) rename column f to CITY
alter table (cTablename) rename column g to STATE
alter table (cTablename) rename column h to ZIP
alter table (cTablename) rename column i to PHONE
alter table (cTablename) rename column j to PHTYPE
alter table (cTablename) rename column k to PHNOTES
alter table (cTablename) rename column l to COUNTY
alter table (cTablename) rename column m to AFFIL
alter table (cTablename) rename column n to atype
alter table (cTablename) rename column o to saluttype
GOTO 1

DELETE

STORE 0 TO ncount
STORE 0 TO nduplicates

SCAN all

STORE ncount +1 TO ncount
STORE ALLTRIM(importid) TO cimportid
STORE ALLTRIM(fname) TO cFname
STORE ALLTRIM(lname) TO cLname
STORE ALLTRIM(COMPANYDIV) TO cCOMPANYDIV
STORE ALLTRIM(addres1) TO cAddres1
STORE ALLTRIM(CITY) TO CCITY
STORE ALLTRIM(STATE) TO CSTATE
STORE ALLTRIM(ZIP) TO CZIP
STORE ALLTRIM(PHONE) TO CPHONE
STORE ALLTRIM(PHTYPE) TO CPHTYPE
STORE ALLTRIM(PHNOTES) TO CPHNOTES
STORE ALLTRIM(COUNTY) TO CCOUNTY
STORE ALLTRIM(AFFIL) TO CAFFIL
STORE ALLTRIM(atype) TO Catype
STORE ALLTRIM(saluttype) TO Csaluttype


STORE "Source: "+cFname+" "+cLname+CHR(13)+cAddres1+", " +CCITY+", "+CSTATE+", "+CZIP TO cSource

SELECT 2

LOCATE FOR ALLTRIM(b) = cLname AND ALLTRIM(c) = cFname

IF FOUND()

store nduplicates+1 to nduplicates



STORE "Destination Record: "+ALLTRIM(c)+" "+ALLTRIM(b)+CHR(13)+ALLTRIM(i)+", " +ALLTRIM(k)+", "+ALLTRIM(l)+", "+ALLTRIM(m) TO cDestination

eMessageTitle = "Duplicate Checker"

eMessageText = "Duplicate found: "+csource+CHR(13)+CHR(13)+;
cDestination+CHR(13)+CHR(13)+ "Would you like to delete source record?"

nDialogType = 3 + 16 + 256

* 4 = Yes and No buttons

* 16 = Stop sign icon

* 256 = Second button is default

nAnswer = MESSAGEBOX(eMessageText, nDialogType, eMessageTitle)

DO CASE
CASE nAnswer = 2

cancel

CASE nAnswer = 6

SELECT 1

*this will delete record from data session
DELETE

CASE nAnswer = 7

SELECT 1

ENDCASE

ENDIF
ENDSCAN

* --- After above SCAN/ENDSCAN and DeDupe routine ---
mnID = (35000 - 1) && Establish Default Starting Number
* --- Cycle through records incrementing ID and inserting into field ---
SELECT Merge
SCAN
mnID = mnID + 1
***REPLACE Merge.Importid WITH ALLTRIM(STR(mnID)) && If the field is a Char
REPLACE Merge.importid WITH mnID && If the field is a Numeric
ENDSCAN
eMessageTitle = 'No Duplicates'
eMessageText = 'Completed Search. '+STR(ncount, 4)+' Records Searched'+Chr(13)+Chr(13)+STR(nduplicates, 4)+" Duplicates Found"

nDialogType = 0 + 16 + 256
* 4 = Yes and No buttons
* 16 = Stop sign icon
* 256 = Second button is default

nAnswer = MESSAGEBOX(eMessageText, nDialogType, eMessageTitle)

PACK

Select 1
COPY TO Exceed.CSV TYPE CSV

 

[JRB-Bldr]

I'm guessing that you are not familiar with the VFP Debug tools - TRACE WINDOW, etc.

Keeping track of which table is SELECT 1 and which is SELECT 2 is confusing. You have renamed field 'A' to 'ImportID' within your SELECT 1 table (whichever table that is).
Without my totally re-writing your code into a more readable form (as suggested above and in your other posting on the same question thread1252-1322963), my best advice is to help you learn how to debug your own code.

Put the following Debug code in and examine what is truly going on.

* --- Cycle through records incrementing ID and inserting into field ---
SELECT Merge
SCAN
 mnID = mnID + 1
 [B]SET STEP ON && Launches TRACE Debug Window[/B]
 REPLACE Merge.importid WITH mnID  && If the field is a Numeric
ENDSCAN

That additional line will cause your code to SUSPEND its execution and open the TRACE WINDOW. Then you will have the opportunity to examine what is going on.

Once the code 'breaks', go to the VFP COMMAND Window and do a BROWSE to open Merge for viewing. You will be able to examine the table to see exactly what it contains field names, record values, etc. If you find something wrong, then CANCEL the program, re-work your code and try again.

If everything looks OK, you can use the STEP INTO button (Down Arrow between 2 Braces) on the TRACE MENU bar to single step through the execution of the remaining lines of code.

Good Luck,
JRB-Bldr

 

[garyzak]

All set, got the dang thing going! Thank you very much for your help on this. I will copy this post for further exploration as I learn. You are an inspiration and a great help.
Thanks again....

 

[garyzak]

Thank you, Mike.
The results, for each time the program runs, starts at 35,000. With the above code, I need to "store" the very highest number in the merge in a variable and then increment so that the number keeps going up. Can you please take a look? It is working as far as the numbers entered into the importid field, but they need to keep going up. I appreciate any help.

Thank you
* --- After above SCAN/ENDSCAN and DeDupe routine ---

mnID = (35000 - 1) && Establish Default Starting Number
* --- Cycle through records incrementing ID and inserting into field ---
SELECT 1

SCAN
mnID = mnID + 1
***REPLACE Merge.Importid WITH ALLTRIM(STR(mnID)) && If the field is a Char

REPLACE importid WITH STR(mnID, 6)

 

[JRB-Bldr]

Mike's comment is valid when using non-RELATED tables (not valid if RELATION is in place), but the comment does not apply to the small 2nd-step SCAN/ENDSCAN loop you are using to merely enter the ImportID values since in that loop you are not in Merge and you tried to write to alias "other"... Instead throughout that entire SCAN/ENDSCAN you are in a single table and modifying that same table's field values - not another table's.
And you tell us that you think that the upper SCAN/ENDSCAN loop where you are DeDuping the records is working OK.

If you do as I suggest and Debug the program yourself you will see what is going on
* Is SELECT 1 really picking the table you think it should?
* Is MERGE where the ImportID field really exists?
* Are the MERGE records populated correctly?
* Etc.
And with that information you will be able to resolve your problem rather than having us actually write your program for you. Our goal here is to assist others with their problems not just do all of the work for them.

Good Luck,
JRB-Bldr

 

[garyzak]

point taken. Sorry about that. I will dig in and take your suggestion.
Thank you.

 

[garyzak]

Issue resolved. It is working. Created another table to store the value for incremnting.

Thank you all for your help!

 

[JRB-Bldr]

Mike - I was referring to the applicability of your comment

There is a problem when doing replace across tables.
If you are in merge and you tried to write to alias "other"...

select merge
replace other.field with value

Your other comments are quite valid.

JRB-Bldr