Create empty cursor dynamically using field values from a single column table 6

[carolx]

Hello MVP's Carolx seeking you assistance.

I have been trying to create a cursor dynamically using field values from a single column table without success.
Please see example coding below.

*> Field values in table empfld for this example are Casual, Holiday, Temporary, Permanent

LOCAL aFldnam(1)
nType = RECCOUNT('empfld')

DIMENSION aFldnam(nType+1)
aFldnam(1) = 'Month'

SELECT empfld
GO TOP

FOR cnt = 2 TO nType+1
aFldnam(cnt) = TRIM(desc)
SKIP
ENDFOR

CREATE CURSOR emp FROM ARRAY aFldnam && syntax error here
DISPLAY MEMORY LIKE aFldnam

ALTERNATIVE:

SET TEXTMERGE ON
nType = RECCOUNT('empfld')

cCommand = 'CREATE CURSOR'
cAlias = 'emp'
cString = '<<cCommand>> <<cAlias>>('

SELECT empfld
GO TOP

FOR cnt = 1 TO nType
cField = TRIM(desc)
cString = cString + '<<cField>> c(25))',
SKIP
ENDFOR

cString = SUBSTR(cString,1,LEN(cString)-1)
cExecute = TEXTMERGE(cString)
&cExecute && syntax error here - the columns all have the last field value as heading
*> It works if there is only one value in the table.

 

[Scott24x7]

You're macro-substituting the value with the &cExecute call. Have you tried evaluating it instead?

EVALUATE(cExecute)

Not sure how your text string is coming out. Use the debugger to check the value of cExecute (or messagebox: MESSAGEBOX(cExecute) to see what the content is. Probably there is something not getting built properly if the &cExecute is creating a syntax error.


Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[Mike Lewis]

CREATE CURSOR emp FROM ARRAY aFldnam && syntax error here

Well, for a start, you can't create a cursor from a single-column array. The array has to be in a certain format:

[tt]FROM ARRAY ArrayName[/tt]
Specifies the name of an existing array whose contents are the name, type, precision, and scale for each field in the temporary table .... For the proper format of the contents of the array, see AFIELDS( ) Function.

Next, it's difficult to see why your [tt]&cExecute[/tt] is failing without knowing exactly what's in your cExecute variable. I suggest you capture that variable and examine the contents closely. By "capture" I mean either display it on the screen (using the ? command), examine it in the debugger, or store it in the clipboard ([tt]_CLIPTEXT[/tt]) and then paste it into a text file.

If you can't then see what's wrong with it, paste it into this thread and we can try to work it out.

Finally - and most importantly - it seems to me that you are going about this whole problem in a way that's much too complicated. Given you have some data in a table Empfid, and you want to get some of that data into a cursor, the easiest way would be with a simple [tt]SELECT ... INTO CURSOR[/tt]. If you could explain what's in the original table, and what you want to end up with in the cursor, we should be able to show you how to do it.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Tom Borgmann]

Hi CarolX
i did something like that some time ago for a form that needed to be expanded dynamically by our customers just for being able to add additional texts.
What I did was to use a two column grid that's first culumn contains the fieldname and the second columns opts to input individual texts.
All is based on a rather simple table (like yours) that reads like this:

[pre]create table ps_seequalifiers
(
sq_qualifier integer primary key default 0,
sq_description char(50) default " "
);[/pre]

We offer this table with two predefined records and it can be modified by the customers for their own needs. However, it only makes sense for character fields, otherwise the table would need more info like fieldtype, fieldlength, ranges a.s.o.

The tricky part is, to combine this descriptive data with the informations the users have already entered and/or want to enter and has to be saved!

Therefore there isn't a simple select on the qualifier table. The select contains virtual fields, too. Those are filled als soon as existing data is read from the DB and has to be visualized in the grid.

Version 1 (used for new orders without already existing data)

[pre]select
sq_description,
cast(" " as char(250)) as abst_text,
sq_qualifier as abst_qualifier,
CAST(0 as int) as abst_mandant,
CAST(0 as int) as abst_orderno
from ps_seequalifiers
where sq_beschreibung <> " "
order by sq_qualifier
[/pre]

Version 2 (used for already existing data in the target table)

[pre]select
sq_description,
cast(nvl(abst_text,"") as char(250)) as abst_text,
sq_qualifier as abst_qualifier,
cast(nvl(abst_mandant,0) as int) as abst_mandant,
cast(nvl(abst_orderno,0) as int) as abst_orderno
from ps_seequalifiers
left join ps_abseetexte
on abst_qualifier = sq_qualifier
and abst_mandant = <<m.vMandant>>
and abst_orderno = <<m.vOrderno>>
where sq_description <> " "
order by sq_qualifier[/pre]

As you can see, the select mixes up the qualifiertable with relevant columns from the target table (abst_text, abst_qualifier, abst_mandant, abst_orderno). That way, no loop is needed.

HTH

-Tom

https://www.blogger.com/profile/08903165976787522012

 

[Olaf Doschke]

Mike explained aus FROM ARRAY doesn't work. The textmerge on setting dies not mean all <<expressions>> are evaluated. Use the Textmerge function or TEXT...ENDTEXT with ADDITIVE clause

Olaf Doschke Software Engineering

https://www.doschke.name

 

[mplaza]

create cursor empFld ( desc c(10))
insert into empFld ( desc ) values ( 'Casual')
insert into empFld ( desc ) values ( 'Holiday')
insert into empFld ( desc ) values ( 'Permanent')
insert into empFld ( desc ) values ( 'Temporary')

* do:
select desc,'c' as fldtype,25 as length,'0' as ndec from empFld into array astr
create cursor emp from array astr

modify structure

Marco Plaza
@vfp2nofox

 

[carolx]

Thanks for the help.

The solution forwarded by Marco Plaza is the preferred one. I knew all along, in the back of my mind, that you had to supply at least the description and type and size of field in the array. And as Mike had suggested, the array has to have a certain format as outputted by AFIELDS() for a table.

I can use the solution hinted at by Olaf also.

 

[Olaf Doschke]

OK, thanks for the feedback.
I consider what mplaza did as a hack, you could better do

CREATE CURSOR crsStub (id int autoinc)
AFIELDS(laStub)

Then you have the full array structure CREATE TABLE or CREATE CURSOR FROM ARRAY needs. because indeed you can use afield info as a blueprint. You can expand the array by adding further rows with DIMENSION and make use of all field information to put in there. AFIELD describes the array elements and their meaning.

Bye, Olaf.


Olaf Doschke Software Engineering

https://www.doschke.name