Antonio Landsberger Posted May 5, 2021 Share Posted May 5, 2021 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? Quote Link to comment
Julian Carr Posted May 5, 2021 Share Posted May 5, 2021 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); 1 Quote Link to comment
Pat Stanford Posted May 5, 2021 Share Posted May 5, 2021 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. 1 Quote Link to comment
Antonio Landsberger Posted May 6, 2021 Author Share Posted May 6, 2021 @Pat Stanford Perfect, thank you. This wish pretty much would have solved what I was trying to accomplish. 1 Quote Link to comment
KingChaos Posted August 21, 2023 Share Posted August 21, 2023 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 Quote Link to comment
Thomas W Posted August 21, 2023 Share Posted August 21, 2023 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 1 Quote Link to comment
KingChaos Posted August 21, 2023 Share Posted August 21, 2023 if u now translate it into german it would be fine ^^ Quote Link to comment
KingChaos Posted August 21, 2023 Share Posted August 21, 2023 joking, lets try it. the problem is, i cant implement it, because i stopped programming in gw basic in 1994 Quote Link to comment
KingChaos Posted August 21, 2023 Share Posted August 21, 2023 can u help me pls making it possible without having this "coding" work to do by myself? i have some other guys haveing trouble with the spreadsheetproblem of subrows. So it is not only for me it would solve the so called "roomproblem" for other VWX users. Quote Link to comment
Thomas W Posted August 21, 2023 Share Posted August 21, 2023 (edited) 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 August 21, 2023 by Thomas W 1 Quote Link to comment
KingChaos Posted August 21, 2023 Share Posted August 21, 2023 hi, i got a green grocery storage shelf here we are selling in big qt und other versions of it. each part which is made in our metalfab gots a database entry and i got a spreadsheet listing them up and count the qt. 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.vwx So i made a spreadsheet "Schlosserei V100 Data" into which i copy the datablock of the subrows and all is working well. 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 Quote Link to comment
KingChaos Posted August 21, 2023 Share Posted August 21, 2023 WaFa t900 3 etagig stueckliste aus Tabelle v2.zip Quote Link to comment
Thomas W Posted August 21, 2023 Share Posted August 21, 2023 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! Quote Link to comment
Thomas W Posted August 21, 2023 Share Posted August 21, 2023 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 Quote Link to comment
KingChaos Posted August 22, 2023 Share Posted August 22, 2023 checked it, works perfect 1 Quote Link to comment
KingChaos Posted August 22, 2023 Share Posted August 22, 2023 (edited) then i can modify to my second table, only with changing the spreadsheets names? cool, thx Edited August 22, 2023 by KingChaos Quote Link to comment
KingChaos Posted August 22, 2023 Share Posted August 22, 2023 yea, even that will work, thanx a lot. is it generally possible to automate this scripts? Quote Link to comment
Thomas W Posted August 22, 2023 Share Posted August 22, 2023 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! 1 Quote Link to comment
KingChaos Posted August 23, 2023 Share Posted August 23, 2023 to start it wiith my datastamp i only have to call its name? So i dont need to start it manually ^^ 1 Quote Link to comment
Recommended Posts
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.