Fields from records 3

[Phil Thoms]

Is it possible to convert several account numbers contained in records from a dbf file into individual fieldnames in a temporary table or dbf.
I am currently using Visual FoxPro version 6.0
Thank you.

 

[EzLogic]

assuming your table's are characters
assuming no weired char are in the fiel
and there are less than 255 records

something like this.. .to give you an idea..

hope that's what you're looking for..

CLEAR
CREATE CURSOR test (account c(20))
INSERT INTO test (account) VALUES ('TS100')
INSERT INTO test (account) VALUES ('TS101')
INSERT INTO test (account) VALUES ('TS102')
INSERT INTO test (account) VALUES ('TS103')
INSERT INTO test (account) VALUES ('123TS100')
INSERT INTO test (account) VALUES ('TS 1001')

SELECT TEST

SELECT DISTINCT ACCOUNT FROM TEST ;
	ORDER BY account ;
	WHERE NOT EMPTY(ACCOUNT) ;
	INTO CURSOR MyCursor

SELECT myCursor 	
LOCATE 
IF EOF()
	MESSAGEBOX("No records found...")
	USE IN SELECT('myCursor')
	RETURN
ENDIF 

TRY 
lcCMD = 'create cursor NewTable (' 
SCAN
	lcfield = ALLTRIM(TRANSFORM(Account))
	lcField = STRTRAN(lcField,' ','_')
	lcField = IIF(ISDIGIT(SUBSTR(lcfield,1,1)),'_','') + lcField 
	lcCMD = lcCMD + ALLTRIM(lcField) + ' C(10), '

ENDSCAN 
lcCMD = SUBSTR(lcCMD,1,LEN(lcCMD)-2) + ' ) '
&lcCMD
IF USED('Newtable')
	SELECT Newtable
	BROWSE
ENDIF 

CATCH TO loexp
	MESSAGEBOX(loExp.Message + CHR(13) + ;
			   'Line: ' + TRANSFORM(loExp.LineNo))
ENDTRY

Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[EzLogic]

sorry, i meant:

* assuming the account numbers are characters or Ints in the table.
* assuming there are less than 255 account numbers (records)
* assuming no wierd characters in the account fields (no slashes, no commas, no points, no tildes, etc..)

Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[Phil Thoms]

Thanks for your reply, I don't understand your reply but maybe I didn't describe my problem accurately.
I have a dbf file which may have 1 or maybe 10 account numbers that I want to transfer to another dbf as fieldnames:-
eg; record 1 in first dbf is account no.1001 so I want the number 1001 created as a fieldname programaticaly in the second database and so on. I don't want to transfer the names manually if you get my point.
Thanks

 

[Olaf Doschke]

It's understood, that ou don't want to transfer the names manually, that's why Ali wrote code to automtically do it. The first few lines creating sample data could be replaced by your real data, adapted to your specific names.

But as you now were more specific: If your account names are just nunmbers, you will not be able to create field with that name, eg 1001 is not a valid field names. Field names must begin with a letter or underline. So what you could create is field _1001 eg for account 1001.

The cross tab wizard or pivot table wizard will do such conversions of data into names to create a column for each account or year or month etc. You could use these wizards and COPY TO some.dbf the final result cursor to store that result into a dbf file.

Bye, Olaf.

 

[Mike Lewis]

Philtoms,

Your general approach should be to programmatically build a CREATE TABLE statement, and then to execute that statement as a macro.

The following is off the top of my head. I can't guarantee it is 100 percent correct, but it should give you the general idea.

lcCmd = "CREATE TABLE SecondTable ( "
USE FirstTable IN 0
SELECT FirstTable
SCAN
  lcFieldName = "_" + TRANSFORM(FirstTable.AccountNo)
  lcCmd = lcCmd + lcFieldName + " C(12), "
ENDSCAN
* Remove final comma and add closing paren
lcCmd = LEFT(lcCmd, LEN(lcCmd)-2) + ")"

* Execute the CREATE TABLE
&lcCmd

This assumes that the relevant field in the first table is named AccountNo, and that you want the fields in the target table to be C(12). If that's not the case, it should be simple to adjust the above code accordingly.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[EzLogic]

Again, be careful that a free table will not let you creat a fieldname that is longer than 10char long.
so if your account number is longer than 10char long (alltrimed), you should create a database then create the table in it.

My code works for your if you have numeric account number as well, thus checking fot he isdigit() for the 1st character and handling acount numbers such as "100 001" (which as a field name is invalid and cannot contain spaces.

As Olaf pointed out, the first few line were just to create dummy data to show you how its done.

use YourTable in 0 && Or whatever your tablename is called.

&& This will remove duplicate account in case there are dups
select Distinct AccountNo from Yourtable order by AccountNo ;
  Into cursor _curAccountFields

Select _curAccountFields 

TRY 
lcCMD = 'create Table NewTable (' && assuming your new tablename is called NewTable
SCAN
	lcfield = ALLTRIM(TRANSFORM(AccountNo)) && Assuming the field for account is AccoutNo
	lcField = STRTRAN(lcField,' ','_') && Make the spaces to an underscore
	lcField = IIF(ISDIGIT(SUBSTR(lcfield,1,1)),'_','') + lcField  && if numeric, put a _ in the front
	lcCMD = lcCMD + ALLTRIM(lcField) + ' C(10), ' && build the statement for each field.

ENDSCAN 
lcCMD = SUBSTR(lcCMD,1,LEN(lcCMD)-2) + ' ) ' && remove the last 2 delimiters and add the last ) to close
&lcCMD  && execute the statement built
IF USED('Newtable') && check the cursor
	SELECT Newtable
	BROWSE
ENDIF 

CATCH TO loexp
	MESSAGEBOX(loExp.Message + CHR(13) + ;
			   'Line: ' + TRANSFORM(loExp.LineNo))
ENDTRY

Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[Phil Thoms]

Thanks for all your help. I didn't realise that in the final database I will need to reference the original account no. so I can put values in the new fields from another table, but this probably sounds complicated.

 

[Mike Lewis]

Philtoms,

I hope the advice that Ali, Olaf and I gave you was helpful.

But it is not at all clear what your goal is. If you could explain the entire problem - in other words, what end product you are trying to achieve - we might be able to suggest a simpler way of going about it.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Phil Thoms]

Thanks Mike, I'll try to explain in a different way:-
I have a database with character field for the account numbers which run from 1001. I produce a second database showing the accounts with current workloads but the descriptions of the work are quite long so the client wants these descriptions to be down the left side of the table (which is easily done) and the account numbers which could number 2, 3 or more to be displayed running from left to right along the top with quantities displayed under each acct no. against the descriptions. So I want columns for the account numbers and I want to be able to reference them so I can place the relevant quantities under each one.
Hope this is clearer. Thanks for your time.

 

[Mike Lewis]

Yes, thank you. That's very clear.

In other words, you want a cross-tab (at least, that's what I've always called it; there might be a better name for it).

But, you know, you don't have to create a table with field names that match the account numbers. The account numbers at the top of the columns are only there for display purposes. You could simply name the fields Account_1, Account_2, etc. - or anything thing else you like. It's only at the point that you display or print the cross-tab that you need to pick up the actual account numbers.

You basically need to create a cursor or table with N+1 fields, where N is the actual number of accounts. The first field would contain the descriptions, the second would contain the data for account 1, and so on. If you are diplaying the result in a grid, it's the grid's column headers that will contain the actual account numbers. If you are printing the information in a report, the account numbers will go in fields in the page or group headings. (Of course, if the end product is a report, you will need to impose a maximum on the number of accounts, in order to fit the physical page.)

I hope this makes sense.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Phil Thoms]

Hello Mike,
Yes it does make sense. So I take it you would feed the account numbers into an array to ascertain the account numbers involved and spread them across the top of the page.
(From what I know I think there would never be any more than 5 or 6 so plenty of room).
Final report or table would be like:-

Description AccNo.1 AccNo.2 AccNo.3
1001 1029 1051
long txt 21 9 11
long text 2 1 2 7

and so on

 

[Mike Lewis]

Yes, if it's a printed report, it would probably be easiest to feed the account numbers into an array. You can do that like so:

USE AccountTable IN 0
SELECT AccountTable
COPY TO ARRAY laAccounts FIELD AccountNo

You could then reference each array element in the various report fields that form the column headings.

Note that you might have to make the array PRIVATE or PUBLIC, to ensure that it is visible within the report.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

You already advanced a few steps, good.

It doesn't matter, if your account data is char or numeric, field names can't begin with a digit. Captions be whatever you like, though, you don't need to let a grid display field names as captions, either you add a caption line to your data, which the grid displays, or you set the grid header.captions by secondary data.

Having a record #1 for captions would mean your whole table or cursor would need to be contructed with char fields only.

I like Mikes idea to simply name the account columns as account1,2, etc.

But as I already there is a crosstab wizard, and you can always expand on it's result.

To make use of it, here's a sample:

CREATE CURSOR curSourcedata (accountno c(4), iMonth I, nBalance Y)
INSERT INTO curSourcedata VALUES ("1001",1,100000)
INSERT INTO curSourcedata VALUES ("1001",2,105000)
INSERT INTO curSourcedata VALUES ("1001",3,110000)
INSERT INTO curSourcedata VALUES ("1002",1,123)
INSERT INTO curSourcedata VALUES ("1002",2,124)
INSERT INTO curSourcedata VALUES ("1002",3,125)

DO (_genxtab) WITH 'curResult',.t.,.f.,.t.,2,1,3
SELECT curResult
BROWSE

It'll display the accounts as columns, months as rows and the account balances as data. You'll see it copes just fine with the account numbers by adding a prefix c_ to them as field name. The caption of the grid you'd use to display this result doesn't have to have the field names as captions.

To get a description how genxtab works, simply read through the comment header via

MODIFY FILE (_genxtab) noedit

Bye, Olaf.

 

[Phil Thoms]

Thanks for all your advice, shouldn't take long to get some results.

 

[Phil Thoms]

For Mike Lewis:-
You mention referencing each array element in a report.
I assume you need to place a counter in the original scan statement or am I wrong?

 

[craigber]

Wait.. did anyone say you can't have a field that starts with a number? If the account no. is 1001, you can't have a field named 1001. You can have _1001 or No1001 or something like that.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[Phil Thoms]

Yes, this has been mentioned so I'll use an underscore before the actual number as suggested.

 

[Mike Lewis]

You mention referencing each array element in a report.
I assume you need to place a counter in the original scan statement ...?

No, I don't think so.

If you mean the SCAN in my first post in this thread (5 Aug 12 13:52), then you don't need a counter because the field names are derived from the actual account numbers. But, in any case, none of that code will apply if you are using array elements in the report. In that case, there is no SCAN. You just reference the account numbers as laAccounts(1), laAccounts(2), etc.

Similarly, you don't have to worry about preceding the field name with an underscore if you are going down the array route. In that case, you are not creating fields from the account numbers, so the issue of field names beginning with a number doesn't apply.

I hope I'm not confusing you with this. If I am, come back and I'll try to clarify it.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Phil Thoms]

For Mike Lewis,
Don't I need to know how many account numbers are involved to create the fields within a temporary table, hence the counter?
I may be getting confused a little but I still need it to be created programatically.