Structure for Variable within Lookup.

[James Russell]

Just wondering if anyone can help me with the SetWSCellFormula command. I was trying this:

Procedure Test;

VAR

FL :String;

SN,TN :Integer;

WSH :Handle;

Begin

SN:=1;

TN:=10;

WSH:=CreateWS(Concat('Test',Date(2,1)),10,5);

While SN<=TN Do

Begin

Case SN of

1: FL:='A';

2: FL:='B';

3: FL:='C';

4: FL:='D';

5: FL:='E';

6: FL:='F';

7: FL:='G';

8: FL:='H';

9: FL:='I';

10: FL:='J';

End;

SetWSCellFormula(WSH,SN,0,SN,0,'=Database((R IN [''TestRecord'']) & ([''TestRecord''.''FieldA'']=FL))');

SN:=SN+1;

End;

End;

Run (Test);

So the loop through seems to work fine although I only know this because I get 10 error message saying invalid factor. I've been struggling a lot with the SetWSCellFormula line, it works supremely well with the =database(R IN [''testrecord'']) by itself but I think I've broken it with a lookup with a variable.

Thanks in advance, especially if you read this far!

James

[Pat Stanford]

You can't use variables directly in the criteria. You need to use the Concat function to make a single string that is exactly the criteria you want and then use that string as the criteria.

MyCriteria:=Concat('=Database((R IN [''TestRecord'']) & ([''TestRecord''.''FieldA'']=FL))');

SetWSCellFormula(WSH,SN,0,SN,0,MyCriteria);

I have not looked at this extremely closely, but make sure all of your quotes and escaped quotes are correct.

[Miguel Barrera]

Expanding on Pat's observations, these are my 2 cents which are based on a working script.

MyCriteria:= Concat('=DATABASE((''TestRecord''.''FieldA''=', FL, '))');

SetWSCellFormula(WSH,SN,0,SN,0,MyCriteria);

You do not need 'R IN [''TestRecord'']' because the second argument implies FieldA is in TestRecord.

Make sure you type 2 single quotes '' and not just a double quote "

[Miguel Barrera]

I just realized that the default font does not help in showing the two single vs double quotes. So here it is again:

[font:Courier New]

Expanding on Pat's observations, these are my 2 cents which are based on a working script.

MyCriteria:= Concat('=DATABASE((''TestRecord''.''FieldA''=', FL, '))');

SetWSCellFormula(WSH,SN,0,SN,0,MyCriteria);

You do not need 'R IN [''TestRecord'']' because the second argument implies FieldA is in TestRecord.

Make sure you type 2 single quotes '' and not just a double quote " [/font]

[James Russell]

Thanks guys, exactly the solution I needed.

Cheers,

James

[Assembly]

James

What does this do?.

What is your application?.

[James Russell]

I'm currently making a worksheet generator. There is a style which has been adopted at my current workplace since vectorworks 10 or so and I'm attempting to replicate it into 2010.

Fingers crossed I can sort all the quirks out.

J