Jump to content
Developer Wiki and Function Reference Links ×

How to get the current WSSubrow?


Recommended Posts

When I have a worksheet script running in a particular column, I want the script to know whether it's the first subrow or not.

That's because I want a calculation to run only once, even though there are many objects in the list. But since I already calculated the necessary value in the first subrow in the rest of the subrows I just want to get the value from the first subrow.

 

I'm imagining something along these lines

if (WSSubrowCurrent==1):
   calculate()
else:
   GetWSSubrowCellValue(1)

Of course GetWSSubrowCellValue would require the current row and column, which brings me back to: how do I get the current row, column and subrow?

I'm not seeing any VS command in the dev wiki to accomplish this. Does anybody have good ideas?

Link to comment

My vague understanding is that a change was made a few Vw versions ago in how database rows were built. I think now the rows don't actually exist until after the full calculation for all rows is completed, so there is no way to know when the first row is calculating.

 

An alternative approach you could try, is do define the worksheet row formula yourself (example below) then use the following for the DB row formula:

 

=DATABASEBYSCRIPT('LoadDBRow')

 

I have no idea if this would work, but might be worth playing with. Note that the first handle found is not influenced by the sort order of the worksheet, but the creation order of the items found by the criteria you enter, in the document.

 

 

{ the script - add something like this to a script in a script palette called LoadDBRow. Sorry I don't know Python so this is VS }

Procedure LoadDBRow;

VAR

    gb1 : BOOLEAN;

Procedure DoIt(h1 HANDLE);

BEGIN

    IF NOT gb1 THEN DoMyCalculationAndLoadCell; { load a spare cell with the first row calculation (done here), then use that cell in your column formula }

    gb1 := True;

    WSScript_AddHandle(h1);

END;

BEGIN

    gb1 := False;

    ForEachObject(DoIt, <insert your criteria here>);

    ClearTheCell; { empty out the spare cell }

END;

Run(LoadDBRow);

  • Like 1
Link to comment

I agree with Julian, it is not possible to get the subrow. I filed the following Enhancement Request in August of 2020.

 

Quote

Currently in a worksheet script there is no way to access the data in other columns for the specific subrow the script is accessing.

In my current use case, a customer wants to create a formula in a worksheet based on object parameters and do a calculation. They then want that value stored in a Record.Field.

I do not have a way to access the data in other cells in the subrow, so the calculation has to be done (or redone) in the worksheet script. If I could just access say "Column C" then the user could change the formula and the worksheet script would only have to store that value into the field.

There are many other cases where the ability to access the other columns of data in the worksheet would be useful in a script instead of having to go an recollect that data directly from the subrow referenced object.

 

  • Like 1
Link to comment
  • 2 years later...

hi there,

 

is it possible to fill a spreadsheet with the subrows of another spreadsheet?

 

i need some data out of the subrows, where vlookup (in my datastamp) don´t get, so it wont be possible

to read out the data directly, but if i can copy the subrows into ordinary rows of another spreadsheet, it will work.

 

I need it for a datastamp which works correctly if i manually copy the data block

into another spreadsheet, but i have to do it by hand now and its not very comfortable.

 

maybe i did not understood this topic, then pls ignore my question.

 

br KC

Link to comment

Hi,

 

If I understood your question correctly you can use GetWSSubrowCellStrN or GetWSSubrowCellValue to retrieve the value of a subline.

Then you can use SetWSCellFormulaN to write the value to a cell: https://developer.vectorworks.net/index.php/VS:SetWSCellFormulaN

This part is quite important from what I've tried:

'It is best to turn off that flag before using SetWSCellFormulaN repeatedly or in a loop and restore the flag after all operations are done'

 

Hope I understood your question correctly, 

 

Have a great day,

 

Thomas

 

  • Like 1
Link to comment

Hi @KingChaos,

 

I don't think I have enough knowledge and time in VectorScript to make a program that works in all cases and on all worksheets, but I think the important points in relation to the problems I may have encountered could be a start scenario:

 

1- Gets the AutoRecalc state for the specified worksheet : GetWSAutoRecalcState()

2- Sets the AutoRecalc flag for the specified worksheet to False : SetWSAutoRecalcState()

3- Returns whether the specified range is within the valid range of the referenced worksheet VS:IsValidWSRange

4- Find the number of sub-lines you want to copy to be sure you can write them Returns a count of displayed subrows for a specified database row : VS:GetWSSubrowCount

5- Inserts rows into a referenced worksheet if there are not enough according to the result above : VS:InsertWSRows

 

X- Sets the AutoRecalc flag for the specified worksheet to : True : SetWSAutoRecalcState()

 

Are your DataBase lines always on the same lines?

 

Maybe experts can enlighten you more but it's not easy because database subrow cells and their contents are dynamically created.

 

 

Maybe post an example of what you want?

 

Have a great day

Edited by Thomas W
  • Like 1
Link to comment

hi,

 

i got a green grocery storage shelf here we are selling in big qt und other versions of it.

image.thumb.png.501cc7d6a8352eb9113b9d201be119f3.png

each part which is made in our metalfab gots a database entry and i got a spreadsheet listing them up and count the qt.

 

image.thumb.png.21b8d34028edfefd875fcf534de89643.png

so i modified the datastamp in such a way, that i can read out (vlookup) the value of a specific column (the quantity in this case) in which the stamped item is in the level of contruction.

 

now one thing in addition, i have to make a duplicate level for my metal parts, because i have to destroy the symbol cvharacter of some pieces to let the stamp look into (datastamps CANT look into black symbols), this is not cool, but now its working and it gots not only disadvantages.

 

There i got all my metalparts for layouting them. In the layout i can stamp now on the stuff and it reads out the qt and the Length with MAX(X,Y,Z), BUT the datastamp cant look into the subrows of my spreadsheet "Schlosserei V100" 

 

WaFa t900 3 etagig stueckliste aus Tabelle.vwximage.png.ad0eaf08e609a04f1489451a62dba849.png

 

So i made a spreadsheet "Schlosserei V100 Data" into which i copy the datablock of the subrows and all is working well.

 

image.thumb.png.664f1634017be7f805c68b644357b4b1.png

 

If i modify my shelfs all is following, but not the Schlosserei V100 Data, because it is not linked to the Schlosserei V100 because i manually copied the subrows into the Schlosserei V100 Data.

 

maybe it sounds something like "through the  eye, through the knee then to the ears" but i have no time for counting manually the placed parts and i need an 100 % accuracy in this quantity things so it has to be done through VWX somehow.

 

If i can Let VWX make this manually copy of the subrows of Schlosserei V100 into the Rows of Schlosserei V100 Data (as u see in the example file VWX 2020) automatically, it would help AYE LOT!

 

br KC

Link to comment

Ok, I think I understand how you work.

 

But you would like the "Schlosserei V100 Data" table to update as soon as the "Schlosserei V100" table changes?

 

Automatically, I don't know, but I think it can be done with a script to be run manually as soon as you update the "Schlosserei V100" table.

 

I look at what is feasible and what I could do!

Link to comment

Hi,

 

I made a script that will fetch the values of the sub rows of the database which is on row 4 of the "Schlosserei V100" table and will copy them from row 2 of the "Schlosserei V100 Data" table.

To make it work:

1- do not change the names of the tables

2- leave the database on line 4

3-check that there are enough rows/columns in the table where you are going to copy the rows

4- delete the lines at each update before running the script

 

Tell me if it works as you want, to optimize it you should check the number of lines automatically then empty the content before copying.

 

Below is the script to copy paste into a new script:

 

PROCEDURE CopySubrowToAnotherWorksheet;

VAR
	H1, H2 :HANDLE;
	NSR1, DatabaseRow1, Subrow1, numRows, numColumns :INTEGER;
	cellStrgA, cellStrgB, cellStrgC, cellStrgD, cellStrgE, cellStrgF, cellStrgG, cellStrgH, cellStrgI, cellStrgJ, cellStrgK, cellStrgL, cellStrgM, cellStrgN :DYNARRAY OF CHAR; 
    State :BOOLEAN; 
    ObjectName1, ObjectName2 :STRING;
    
BEGIN
	ObjectName1 := ('Schlosserei V100');		{ Worksheet Name1 }
	H1 := GetObject(ObjectName1);
	
	ObjectName2 := ('Schlosserei V100 Data');		{ Worksheet Name2 }
	H2 := GetObject(ObjectName2);	
	
	RecalculateWS(H1);
		
	State := GetWSAutoRecalcState(H1);	{ Gets the AutoRecalc state for the specified worksheet }
	SetWSAutoRecalcState(H1, FALSE);	{ Sets the AutoRecalc flag for the specified worksheet } 				
										
	DatabaseRow1 := 4;	{ Database row to be queried }
	GetWSSubrowCount(H1, DatabaseRow1, NSR1);	{ Returns a count of displayed subrows for a specified database row }
	
	FOR Subrow1 := 1 TO NSR1 DO 
		BEGIN
			GetWSSubrowCellStrN(H1, DatabaseRow1, 1, Subrow1, cellStrgA);	{ Returns the displayed string in a database subrow cell. }
			SetWSCellFormulaN(H2, (Subrow1+1), 1, (Subrow1+1), 1, cellStrgA);	{ the Subrow1=1 of 'Schlosserei V100' database is copied to line 2 of 'Schlosserei V100 Data', so Subrow1+1 }
			{ SetWSCellFormulaN(worksheet, topRow, leftColumn, bottomRow, rightColumn, formula) }
			
			GetWSSubrowCellStrN(H1, DatabaseRow1, 2, Subrow1, cellStrgB);	
			SetWSCellFormulaN(H2, (Subrow1+1), 2, (Subrow1+1), 2, cellStrgB);
			
			GetWSSubrowCellStrN(H1, DatabaseRow1, 3, Subrow1, cellStrgC);	
			SetWSCellFormulaN(H2, (Subrow1+1), 3, (Subrow1+1), 3, cellStrgC);
			
			GetWSSubrowCellStrN(H1, DatabaseRow1, 4, Subrow1, cellStrgD);	
			SetWSCellFormulaN(H2, (Subrow1+1), 4, (Subrow1+1), 4, cellStrgD);
			
			GetWSSubrowCellStrN(H1, DatabaseRow1, 5, Subrow1, cellStrgE);	
			SetWSCellFormulaN(H2, (Subrow1+1), 5, (Subrow1+1), 5, cellStrgE);
			
			GetWSSubrowCellStrN(H1, DatabaseRow1, 6, Subrow1, cellStrgF);	
			SetWSCellFormulaN(H2, (Subrow1+1), 6, (Subrow1+1), 6, cellStrgF);
			
			GetWSSubrowCellStrN(H1, DatabaseRow1, 7, Subrow1, cellStrgG);	
			SetWSCellFormulaN(H2, (Subrow1+1), 7, (Subrow1+1), 7, cellStrgG);
			
			GetWSSubrowCellStrN(H1, DatabaseRow1, 8, Subrow1, cellStrgH);	
			SetWSCellFormulaN(H2, (Subrow1+1), 8, (Subrow1+1), 8, cellStrgH);
			
			GetWSSubrowCellStrN(H1, DatabaseRow1, 9, Subrow1, cellStrgI);	
			SetWSCellFormulaN(H2, (Subrow1+1), 9, (Subrow1+1), 9, cellStrgI);
			
			GetWSSubrowCellStrN(H1, DatabaseRow1, 10, Subrow1, cellStrgJ);	
			SetWSCellFormulaN(H2, (Subrow1+1), 10, (Subrow1+1), 10, cellStrgJ);
			
			GetWSSubrowCellStrN(H1, DatabaseRow1, 11, Subrow1, cellStrgK);	
			SetWSCellFormulaN(H2, (Subrow1+1), 11, (Subrow1+1), 11, cellStrgK);
			
			GetWSSubrowCellStrN(H1, DatabaseRow1, 12, Subrow1, cellStrgL);	
			SetWSCellFormulaN(H2, (Subrow1+1), 12, (Subrow1+1), 12, cellStrgL);
			
			GetWSSubrowCellStrN(H1, DatabaseRow1, 13, Subrow1, cellStrgM);	
			SetWSCellFormulaN(H2, (Subrow1+1), 13, (Subrow1+1), 13, cellStrgM);
			
			GetWSSubrowCellStrN(H1, DatabaseRow1, 14, Subrow1, cellStrgN);	
			SetWSCellFormulaN(H2, (Subrow1+1), 14, (Subrow1+1), 14, cellStrgN);
			
			SetWSAutoRecalcState(H1, TRUE);	{ Sets the AutoRecalc flag for the specified worksheet }
		END; 
END;
Run(CopySubrowToAnotherWorksheet);

 

Good test and let me know if it works well for you,

 

Thomas

Link to comment

Cool!

 

Yes you can change the names of the worksheets in the script.

The script will fetch sub rows from the database which is on line 4 and defined in the script as DatabaseRow1 := 4, in case you can change that too.

 

When you say automate these scripts would it be that the 'Schlosserei V100 Data' table is recalculated as soon as 'Schlosserei V100' is modified?

If that's it I think it's doable but I wouldn't have time to look for the necessary functions and maybe not all the skills, you really have to know your operation well so that there is no problem. command errors.

 

If you are interested, you can already read the explanations of these functions used in this script and it may make you want to go further!

 

https://developer.vectorworks.net/index.php/VS:GetWSSubrowCellStrN

https://developer.vectorworks.net/index.php/VS:SetWSCellFormulaN

 

Happy already that it works like that and good use!

 

  • Like 1
Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...