Jump to content

Worksheet Question using MAX function on a database range.


Recommended Posts

Hi all,

 

I have been trying to increase my worksheet skills and came across the following challenge. So this question is about learning more about worksheets. Not about finding alternatives outside of Vectorworks worksheets. 

 

I have a data base row that has a range of values that are sorted ascending. They are actually the accumulative values of the column next to it. 

 

They represent the DMX Addresses and DMX Footprint of lighting devices. The database row is filtered based on the value of the universe field of the lighting device record.. 

 

I would like to learn if it is possible to create a sort of last free DMX address value. This would be the highest value in the DMX Address field plus the corresponding DMX Footprint value. In this case 425 + 47 = 472. I tried to use the MAX function, but that returns 2159 the sum of all fields in the address column. See picture below. Is there a possible way of returning the desired values with a worksheet function?

 

BTW you would say that my value would be the sum of the DMX Footprint field +1. But if there is a open gap in the DMX addressing that would return a faulty value. 

79165044_ScreenShot2020-04-19at10_47_08.thumb.png.3f0589320efc5c5b953c489faee3db68.png

 

 

 

 

 

I have a second question too. I tried to make a list based on the lighting device channel numbers too. I wanted to create a database for each range of 100 channel numbers; 1 - 100, 101 - 200, 201 - 300 etcetera. I was not able to use the < > = characters to create this criteria on the channel field. I think this is caused by the fact that the channel field is a text type field and this can not be changed in the spotlight preferences. 

 

Is there an 'ISNUMBER' sort of function or other workaround to do this in the channel field?

 

BTW i did manage to get what i want using a combination of excel an a texteditor to create the following database formula. It works, but is a little crude to say the least :-):

 

=DATABASE((NOTINREFDLVP & NOTINDLVP & (PON='Lighting Device') & (R IN ['Lighting Device']) & (('Lighting Device'.'Channel'='101') | ('Lighting Device'.'Channel'='102') | ('Lighting Device'.'Channel'='103') | ('Lighting Device'.'Channel'='104') | ('Lighting Device'.'Channel'='105') | ('Lighting Device'.'Channel'='106') | ('Lighting Device'.'Channel'='107') | ('Lighting Device'.'Channel'='108') | ('Lighting Device'.'Channel'='109') | ('Lighting Device'.'Channel'='110') | ('Lighting Device'.'Channel'='111') | ('Lighting Device'.'Channel'='112') | ('Lighting Device'.'Channel'='113') | ('Lighting Device'.'Channel'='114') | ('Lighting Device'.'Channel'='115') | ('Lighting Device'.'Channel'='116') | ('Lighting Device'.'Channel'='117') | ('Lighting Device'.'Channel'='118') | ('Lighting Device'.'Channel'='119') | ('Lighting Device'.'Channel'='120') | ('Lighting Device'.'Channel'='121') | ('Lighting Device'.'Channel'='122') | ('Lighting Device'.'Channel'='123') | ('Lighting Device'.'Channel'='124') | ('Lighting Device'.'Channel'='125') | ('Lighting Device'.'Channel'='126') | ('Lighting Device'.'Channel'='127') | ('Lighting Device'.'Channel'='128') | ('Lighting Device'.'Channel'='129') | ('Lighting Device'.'Channel'='130') | ('Lighting Device'.'Channel'='131') | ('Lighting Device'.'Channel'='132') | ('Lighting Device'.'Channel'='133') | ('Lighting Device'.'Channel'='134') | ('Lighting Device'.'Channel'='135') | ('Lighting Device'.'Channel'='136') | ('Lighting Device'.'Channel'='137') | ('Lighting Device'.'Channel'='138') | ('Lighting Device'.'Channel'='139') | ('Lighting Device'.'Channel'='140') | ('Lighting Device'.'Channel'='141') | ('Lighting Device'.'Channel'='142') | ('Lighting Device'.'Channel'='143') | ('Lighting Device'.'Channel'='144') | ('Lighting Device'.'Channel'='145') | ('Lighting Device'.'Channel'='146') | ('Lighting Device'.'Channel'='147') | ('Lighting Device'.'Channel'='148') | ('Lighting Device'.'Channel'='149') | ('Lighting Device'.'Channel'='150') | ('Lighting Device'.'Channel'='151') | ('Lighting Device'.'Channel'='152') | ('Lighting Device'.'Channel'='153') | ('Lighting Device'.'Channel'='154') | ('Lighting Device'.'Channel'='155') | ('Lighting Device'.'Channel'='156') | ('Lighting Device'.'Channel'='157') | ('Lighting Device'.'Channel'='158') | ('Lighting Device'.'Channel'='159') | ('Lighting Device'.'Channel'='160') | ('Lighting Device'.'Channel'='161') | ('Lighting Device'.'Channel'='162') | ('Lighting Device'.'Channel'='163') | ('Lighting Device'.'Channel'='164') | ('Lighting Device'.'Channel'='165') | ('Lighting Device'.'Channel'='166') | ('Lighting Device'.'Channel'='167') | ('Lighting Device'.'Channel'='168') | ('Lighting Device'.'Channel'='169') | ('Lighting Device'.'Channel'='170') | ('Lighting Device'.'Channel'='171') | ('Lighting Device'.'Channel'='172') | ('Lighting Device'.'Channel'='173') | ('Lighting Device'.'Channel'='174') | ('Lighting Device'.'Channel'='175') | ('Lighting Device'.'Channel'='176') | ('Lighting Device'.'Channel'='177') | ('Lighting Device'.'Channel'='178') | ('Lighting Device'.'Channel'='179') | ('Lighting Device'.'Channel'='180') | ('Lighting Device'.'Channel'='181') | ('Lighting Device'.'Channel'='182') | ('Lighting Device'.'Channel'='183') | ('Lighting Device'.'Channel'='184') | ('Lighting Device'.'Channel'='185') | ('Lighting Device'.'Channel'='186') | ('Lighting Device'.'Channel'='187') | ('Lighting Device'.'Channel'='188') | ('Lighting Device'.'Channel'='189') | ('Lighting Device'.'Channel'='190') | ('Lighting Device'.'Channel'='191') | ('Lighting Device'.'Channel'='192') | ('Lighting Device'.'Channel'='193') | ('Lighting Device'.'Channel'='194') | ('Lighting Device'.'Channel'='195') | ('Lighting Device'.'Channel'='196') | ('Lighting Device'.'Channel'='197') | ('Lighting Device'.'Channel'='198') | ('Lighting Device'.'Channel'='199') | ('Lighting Device'.'Channel'='200'))))

 

@Pat Stanford Would you know this?
 

 

 

Edited by Sebastiaan
Link to comment

Second question first.  If a text field has only numbers that can be converted into a number (ie. 123, not 123A or 123 Kg) then you can use the =Value function to get the numeric version of that number so you can use it to do math. 

 

First Question. There is no (good) way to get a specific sub-cell out of a database. Since databases are dynamic the next time you add or delete an object and recalculate, then the cell would probably change and not have any way of knowing how to access the previous data.

 

Some functions will take a criteria when used outside of a database row, but Min, Max, Average are not included in those.

 

The following  worksheet script will (I think) return the maximum value of a record field combination. Copy the entire script below into a script in your file called MaxForField. Use a formula in a spreadsheet cell of your worksheet of =RUNSCRIPT(MaxForField, 'Record', 'Field')  where Record and Field are replaced with the appropriate Record and Field names. Capitalization MUST match the Record Definition.

 

Procedure MaxForField;
{April 19,2020}
{©2020 Patrick Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

{This is a worksheet script to return the maximum value in}
{a Record.Field combination. Use at your own risk. No warranty}
{expressed or implied.}
{Save the script in a script palette with a script name of MaxForField}
{in the file and run using a formula of}
{=RUNSCRIPT(MaxForField, 'Record', 'Field') }
{Note that the quotes around the Record Name and Field Name are mandatory}
{and the capitalization of the formula must match the Record Definition}

Var	Rec,Rec1,Fld,CS,S1		:String;
	R1,M1				:Real;
	B1					:Boolean;
	

Procedure Walk(H1:Handle);
	Begin
		S1:=GetRField(H1, Rec, Fld);
		R1:=Str2Num(S1);
		If R1>M1 then M1:=R1;
	End;

Begin
	M1:=-9;
	Rec:=WSScript_GetPrmStr(0);
	Rec1:=Concat(CHR(39), Rec, Chr(39));
	Fld:=WSScript_GetPrmStr(1);
	CS:=Concat('(R in [',Rec1, '])');

	ForEachObject(Walk,CS);

	WSScript_SetResReal(M1);
End;

Run(MaxForField);

HTH.

  • Love 1
Link to comment
6 hours ago, Pat Stanford said:

Second question first.  If a text field has only numbers that can be converted into a number (ie. 123, not 123A or 123 Kg) then you can use the =Value function to get the numeric version of that number so you can use it to do math. 

 

First Question. There is no (good) way to get a specific sub-cell out of a database. Since databases are dynamic the next time you add or delete an object and recalculate, then the cell would probably change and not have any way of knowing how to access the previous data.

 

Some functions will take a criteria when used outside of a database row, but Min, Max, Average are not included in those.

 

The following  worksheet script will (I think) return the maximum value of a record field combination. Copy the entire script below into a script in your file called MaxForField. Use a formula in a spreadsheet cell of your worksheet of =RUNSCRIPT(MaxForField, 'Record', 'Field')  where Record and Field are replaced with the appropriate Record and Field names. Capitalization MUST match the Record Definition.

 


Procedure MaxForField;
{April 19,2020}
{©2020 Patrick Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

{This is a worksheet script to return the maximum value in}
{a Record.Field combination. Use at your own risk. No warranty}
{expressed or implied.}
{Save the script in a script palette with a script name of MaxForField}
{in the file and run using a formula of}
{=RUNSCRIPT(MaxForField, 'Record', 'Field') }
{Note that the quotes around the Record Name and Field Name are mandatory}
{and the capitalization of the formula must match the Record Definition}

Var	Rec,Rec1,Fld,CS,S1		:String;
	R1,M1				:Real;
	B1					:Boolean;
	

Procedure Walk(H1:Handle);
	Begin
		S1:=GetRField(H1, Rec, Fld);
		R1:=Str2Num(S1);
		If R1>M1 then M1:=R1;
	End;

Begin
	M1:=-9;
	Rec:=WSScript_GetPrmStr(0);
	Rec1:=Concat(CHR(39), Rec, Chr(39));
	Fld:=WSScript_GetPrmStr(1);
	CS:=Concat('(R in [',Rec1, '])');

	ForEachObject(Walk,CS);

	WSScript_SetResReal(M1);
End;

Run(MaxForField);

HTH.

 

Thank you @Pat Stanford for your effort. Will try the value function in the database formula.

 

I tried to use the custom function. It runs without errors. But it returns a valua of -9 in stead of 450 which is the highest value in this database row. 

 

Also when I think about is. I think function returns the highest value of a field of all the lighting devices in the document. Where I would only want the highest value of the database row that I created with the the criteria on the universe field. Not of all the lighting devices in total. So only the highest value with the extra criteria, would that be possible?

 

Do you think in the future they will make more worksheet functions available?this could be so useful.

 

Also does the value function also work in a conjunction with a database formula? Tried the value function both in the database formula as in the row formula, but it does not chance the outcome. I am trying to use a nummeric criteria for a record field that is text. Could that criteria ever work then? the database formula looks at the record field?

 

 

Edited by Sebastiaan
extra question
Link to comment

You probably have the field or record name incorrect. Double check the spelling and CaPiTalliZation very carefully.

 

-9 is the default value, I meant to use -9999999999 but forgot to change it after testing.

 

The script could be extended to allow you to specify the other criteria from the database to limit the records, but I would have to think about the best way to do it.

 

  • Like 1
Link to comment
1 hour ago, Pat Stanford said:

You probably have the field or record name incorrect. Double check the spelling and CaPiTalliZation very carefully.

 

-9 is the default value, I meant to use -9999999999 but forgot to change it after testing.

 

The script could be extended to allow you to specify the other criteria from the database to limit the records, but I would have to think about the best way to do it.

 

 

I'm pretty sure I got the capitals right. i copied them from an existing formula generated by the create report wizard =RUNSCRIPT(MaxForField; 'Lighting Device'.'DMX Address')

 

Would be amazing to be able to use a variable to this function. I'll PM you the file i'm practicing in. Thank you!

Link to comment

Comma between the Record and Field (they are separate parameters of the function), not the period you used. Your format is correct for using the Record.Field into a cell, but the script needs the separate Record and Field name.

 

What do you mean to use a variable in the function? I think I know but I would like to know exactly what you are thinking.

  • Like 1
Link to comment
4 minutes ago, Pat Stanford said:

Comma between the Record and Field (they are separate parameters of the function), not the period you used. Your format is correct for using the Record.Field into a cell, but the script needs the separate Record and Field name.

 

What do you mean to use a variable in the function? I think I know but I would like to know exactly what you are thinking.

 

If I use a comma between the record and field then the formula reverts it back to period automatically. Could that be a mac vs windows thing? But still see the -9 value. 

 

What I would like to know is the highest DMX Address in a used Universe. For instance the highest value of lighting devices that have '1' in the Universe field could be 450. the highest value of lighting devices that have '2' in the Universe field could be 475. I would like to be able to return 450 by adding the criteria "1" to the universe field. 

 

 

Link to comment
34 minutes ago, Pat Stanford said:

Actually, I just noticed that you are using semi-colons instead of commas between fields. I will re-phrase my above comment. Use the field separator that is correct for your localization (commas in the US, semi-colons in many other places) to separate each of the three parts of the function call.

 

Got it! Semi colon in between record and field too I now get 498 as return value which is the highest value of all the lighting devices. 

Link to comment

OK, Here is a version that takes a third parameter for the criteria that you want to use to limit the search to. Be very careful as the criteria has to be "ESCAPED" to account for the multiple places that single quotes are required to make this work.  Make sure you read the comments in the header carefully before trying to use this. Not responsible for lost hair due to trying to debug escaped quote symbols.

 

To help with the debugging part, if you hold down the OPTION (ALT) key when recalculating a worksheet, a dialog box will display showing the total criteria the script it seeing. NOTE: Ampersands (&) do not display in dialog boxes, so assume there is alway an ampersand between back to back parentheses i.e. ) (  should actually be read as ) & (

 

The script would be entered into a worksheet as:

 

=RUNSCRIPT(MaxCriteriaField, 'Lighting Device', 'DMX Address', '''Lighting Device''.''Universe''=''1''')

 

where everything in multiple SINGLE QUOTES. You CAN NOT use double quotes or it won't work.

 

Good luck.

 

Procedure MaxCriteriaField;
{April 20, 2020}
{April 19,2020}
{©2020 Patrick Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

{This version has been modified to accept a third parameter which is a}
{properly "escaped" version of the criteria to limit the search to.}

{A proper function to run this may look something like:}
{}
{=RUNSCRIPT(MaxCriteriaField, 'Lighting Device', 'DMX Address', '''Lighting Device''.''Universe''=''1''')}
{}
{Where all of the quotes in the third parameter are single quotes.}
{The very first and very last quotes are required to make the entire}
{parameter a single string. Since the Record, Field and search data}
{need to have single quotes around them also you MUST put in }
{TWO SINGLE QUOTES at each place a quote mark is required in the criteria.}
{So this means 3 quotes at the beginning and end and TWO SINGLE QUOTES}
{everywhere else.}

{If you hold down the Option Key while recalculating the worksheet a}
{dialog box will display the combined criteria that are sent to the }
{Maximum function. Note that Ampersands that are actually part of the}
{criteria don't display in the dialog box.}

{This is a worksheet script to return the maximum value in}
{a Record.Field combination. Use at your own risk. No warranty}
{expressed or implied.}
{Save the script in a script palette with a script name of MaxForField}
{in the file and run using a formula of}
{=RUNSCRIPT(MaxForField, 'Record', 'Field') }
{Note that the quotes around the Record Name and Field Name are mandatory}
{and the capitalization of the formula must match the Record Definition}

Var	Rec,Rec1,Fld,CS,S1,Crit1		:String;
	R1,M1							:Real;
	B1,B2							:Boolean;
	L1								:LongInt;

Procedure Walk(H1:Handle);
	Begin
		S1:=GetRField(H1, Rec, Fld);
		R1:=Str2Num(S1);
		If R1>M1 then M1:=R1;
	End;

Begin
	M1:=-9;
	Rec:=WSScript_GetPrmStr(0);
	Rec1:=Concat(CHR(39), Rec, Chr(39));
	Crit1:=WSScript_GetPrmStr(2);
	If Crit1='' then Crit1:='All';
	Fld:=WSScript_GetPrmStr(1);
	CS:=Concat('((R in [',Rec1, ']) ', CHR(38), '(',Crit1,'))');
	B2:=AutoKey(L1);
	If Option=True then AlertInform('The criteria string the function sees (Ampersands are not show) is: ', CS, False);
	ForEachObject(Walk,CS);

	WSScript_SetResReal(M1);
End;

Run(MaxCriteriaField);

 

  • Love 1
Link to comment
6 hours ago, Pat Stanford said:

OK, Here is a version that takes a third parameter for the criteria that you want to use to limit the search to. Be very careful as the criteria has to be "ESCAPED" to account for the multiple places that single quotes are required to make this work.  Make sure you read the comments in the header carefully before trying to use this. Not responsible for lost hair due to trying to debug escaped quote symbols.

 

To help with the debugging part, if you hold down the OPTION (ALT) key when recalculating a worksheet, a dialog box will display showing the total criteria the script it seeing. NOTE: Ampersands (&) do not display in dialog boxes, so assume there is alway an ampersand between back to back parentheses i.e. ) (  should actually be read as ) & (

 

The script would be entered into a worksheet as:

 

=RUNSCRIPT(MaxCriteriaField, 'Lighting Device', 'DMX Address', '''Lighting Device''.''Universe''=''1''')

 

where everything in multiple SINGLE QUOTES. You CAN NOT use double quotes or it won't work.

 

Good luck.

 


Procedure MaxCriteriaField;
{April 20, 2020}
{April 19,2020}
{©2020 Patrick Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

{This version has been modified to accept a third parameter which is a}
{properly "escaped" version of the criteria to limit the search to.}

{A proper function to run this may look something like:}
{}
{=RUNSCRIPT(MaxCriteriaField, 'Lighting Device', 'DMX Address', '''Lighting Device''.''Universe''=''1''')}
{}
{Where all of the quotes in the third parameter are single quotes.}
{The very first and very last quotes are required to make the entire}
{parameter a single string. Since the Record, Field and search data}
{need to have single quotes around them also you MUST put in }
{TWO SINGLE QUOTES at each place a quote mark is required in the criteria.}
{So this means 3 quotes at the beginning and end and TWO SINGLE QUOTES}
{everywhere else.}

{If you hold down the Option Key while recalculating the worksheet a}
{dialog box will display the combined criteria that are sent to the }
{Maximum function. Note that Ampersands that are actually part of the}
{criteria don't display in the dialog box.}

{This is a worksheet script to return the maximum value in}
{a Record.Field combination. Use at your own risk. No warranty}
{expressed or implied.}
{Save the script in a script palette with a script name of MaxForField}
{in the file and run using a formula of}
{=RUNSCRIPT(MaxForField, 'Record', 'Field') }
{Note that the quotes around the Record Name and Field Name are mandatory}
{and the capitalization of the formula must match the Record Definition}

Var	Rec,Rec1,Fld,CS,S1,Crit1		:String;
	R1,M1							:Real;
	B1,B2							:Boolean;
	L1								:LongInt;

Procedure Walk(H1:Handle);
	Begin
		S1:=GetRField(H1, Rec, Fld);
		R1:=Str2Num(S1);
		If R1>M1 then M1:=R1;
	End;

Begin
	M1:=-9;
	Rec:=WSScript_GetPrmStr(0);
	Rec1:=Concat(CHR(39), Rec, Chr(39));
	Crit1:=WSScript_GetPrmStr(2);
	If Crit1='' then Crit1:='All';
	Fld:=WSScript_GetPrmStr(1);
	CS:=Concat('((R in [',Rec1, ']) ', CHR(38), '(',Crit1,'))');
	B2:=AutoKey(L1);
	If Option=True then AlertInform('The criteria string the function sees (Ampersands are not show) is: ', CS, False);
	ForEachObject(Walk,CS);

	WSScript_SetResReal(M1);
End;

Run(MaxCriteriaField);

 

 

Thank you so much! I now have what I want.

 

With this highest value with criteria I am now able to make a helper database row.

Where I use the cell with the  returned value by your custom function in the database formula as field value criteria for the DMX Address field. This gives me a single row with just the highest DMX adress in the desired Universe. I can then just add up the DMX Footprint column with the DMX address column giving me the last free DMX adress of the Universe. 

 

I know have a fully dynamical DMX patch sheet that I used to make in Excel where I needed to do lots of manual thing and now it comes straight out of VW. And I learned a lot from worksheets. 

 

Thank you very very much. 

Link to comment
44 minutes ago, Pat Stanford said:

You are very welcome. I should have commented the code more, but it is pretty clean.

 

I don't envy you getting the escaped criteria correct. My eyes go blurry with double and triple quotes 😉


mine too so a copy and paste of your example was a good starting point 🙂 

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...