Worksheet cell record field calculations

[trevorgooch]

Hi, wizzards!  ( @michaelk, @Sam Jones)

 

I'm experimenting with single cell calculations in a worksheet, and getting tripped up.  I would like to have a single cell count, say, the total amount of stanchions meeting a set criteria.

 

I can get the total area of objects

ie: =AREA(((C='Queue Lines-Reserved') & (LOC=A1))) 

 

In a database, I can get there with a database that looks like this:

=DATABASE(INSYMBOL & (INVIEWPORT) & ((L<>'zzQUICK HIDE') &  (LOC=A1) & (PON='Rope and Stanchion')))

 

and a column header like this:

=VALUE('Rope and Stanchion'.'TTLStanchions')

 

 

It seems like the function "objectdata" is part of the answer, but, I'm a little stuck.

 

Any guidance would be super appreciated!

 

-Trevor Gooch

[Fuge]

Hi Trevor,

 

Why don't you just use  =COUNT  in a database row cell?

 

[Pat Stanford]

Or in a Spreadsheet Cell, most of the functions that work in a database cell also work, but they need to have a criteria included.

 

=Count in a database header row uses the database criteria to define what is being returned.

=Count(INSYMBOL & (INVIEWPORT) & ((L<>'zzQUICK HIDE') &  (LOC=A1) & (PON='Rope and Stanchion'))) SHOULD get you the number of Rope and Stanchion objects that meet the criteria.

 

HTH

[trevorgooch]

Thanks, @Pat Stanford- it’s been an interesting exercise mashing up database criteria and database header functions into a single cell.  It would SEEM that this is also pretty relevant to data manager expert mappings, and really leveraging data tags.
 

 

I have success with “count” and objects.  What I’d like to do is get a total of all the ‘Rope and Stanchion’.’TTLStanchions’ that meet a given criteria.   Sort of a sum of field given a specific criteria. 
 

 

[michaelk]

Unfortunately, 'Rope and Stanchion'.'TTLStanchions' returns TEXT!!!

 

You can use =VALUE('Rope and Stanchion'.'TTLStanchions') in a database.  

 

…but in a spreadsheet row cell - I'm trying to think if there is a way to use a worksheet script.

[michaelk]

@trevorgooch

I've never tried this before.  But it works!

 

Edit the script and you will see in the comments instructions on how to set the criteria for that cell.  You will need to duplicate the script and create new criteria for each different instance.

 

It might be possible to put part of the criteria in the script call, so one script can rule them all.  Maybe some other night. 🙂 

 

Great question.

 

btw, @Pat Stanford's idea would I have worked and I was going to suggest it, but it turns out that the stanchions are NOT symbols.  They are 3d geometry created by the plug-in object.  In a very unusual way.  All the bases are in one group.  All the uprights in another, etc.

 

Counting Stanchions.vwx

[trevorgooch]

@michaelk/ @Pat Stanford  / @Fuge

 

Thanks for the help!

 

I didn't think of a worksheet cell script!

 

It would be great to have criteria aware math functions or versions of functions - for example, Area and Count can be used in a cell as '=Area(**Critera**)'. or '=Count(**Critera**)'.

A bit like sumifs or lookup in excel.

 

I was sure that I had missed something obvious - but maybe what I had actually missed was another opportunity to home-brew the solution I needed.  Thanks for the assist!

 

[Pat Stanford]

Area and Count and most of the other functions are criteria aware.  In Spreadsheet cells you have to specify the criteria. In Database cells they use the criteria (and the objects) specified in the database subrow.

[trevorgooch]

@Pat Stanford Is there a way to apply a critera to SUM in a worksheet cell, and specify the data field?  I keep thinking I've missed something obvious!

 

Thanks!

[michaelk]

I don't think you need to worry about missing something obvious.  We're way past obvious.

[Pat Stanford]

Yes, way beyond obvious.

 

Something like:

 

=SUM(OBJECTDATA(PON='Door', 'Universal Value', 'Door', 'Width', FALSE))

=SUM(OBJECTDATA(PON='Door', 'Universal Value', 'Door', 'Width', FALSE))

 

May get you what you want, depending on what it is you want.

 

HTH

[michaelk]

That's another route I've never used in real life.  Just tried it.

 

=SUM(VALUE(OBJECTDATA(PON='Rope and Stanchion', 'Universal Value', '', 'TTLStanchions', FALSE))) doesn't work.

 

Neither does

 

=SUM((OBJECTDATA(PON='Rope and Stanchion', 'Universal Value', '', 'TTLStanchions', FALSE)))

 

Tried it with the record name in place of ''.  No difference.

 

 

[Pat Stanford]

I think you need the Record name as well as the field name.

 

=SUM(VALUE(OBJECTDATA(PON='Rope and Stanchion', 'Universal Value', 'Rope and Stanchion', 'TTLStanchions', FALSE)))

 

Untested, but may work.

[michaelk]

Tested.

 

Doesn't work.

[michaelk]

@Pat Stanford do you remember kicking around the "extra" argument on WSScript calls.  You found a cool way to use that argument as text.  Could the criteria be shoved in the extra argument suitcase?

[Pat Stanford]

You can pass as many arguments to a WSScript as you want. They are accessed via the WSScript_GetPrmInt, GetPrmReal, GetPrmStr.

 

The hard part is generating a version of a criteria that can be passed as a string. If you need single quotes inside the criteria it will blow up the single quotes necessary to delist the passed string.  It might be better (if you know enough about what you are doing ahead of time) to pass just the parts of the criteria and then assemble the actual criteria string inside the WSScript.

 

=RunScript('MyScript','MyRecord','MyField','MyLayer')

vMyRecord:=WSScript_GetPrmStr(0);

vMyField:=WSScript_GetPrmStr(1);

vMyLayer:=WSScript_GetPrmStr(2);

 

vMyCrit:=Concat('((L=',vMyLayer,') & (',vMyRecord,'.',vMyField,'=MichaelK))';

 

(and I am not positive but I think that the passed parameters are zero indexed.  Text it yourself to make sure you are getting the right value into the right variable.

 

[Pat Stanford]

I have and enhancement request in to add an additional GetPrmCrit function added that would let you avoid some of the escaping quote nonsense in trying to do this.

[trevorgooch]

@Pat Stanford - you're speaking my language!

 

It seems that parameters are zero indexed.  Round one - working on a script to sum field values based on a criteria, where the records and fields and criteria are passed. Sort of a generic "sum field values by criteria"

 

Also, I had no idea that we can pass a string as a criteria.  This is very cool.

 

Off to the races!  Thanks, Pat!  

Edited August 14, 2023 by trevorgooch
Remembered another thing..

[trevorgooch]

 

Fun stuff!  You can simply paste the criteria into a parameter, and add an additional single quote to get around sting literal issues.

 

Working this way, leaves me with a flexible method for commands that I wish were criteria aware, but are not!

 

 

Here's a quick example:

 

 

 

 

PROCEDURE WSSum;
{    This is a group effort, born out of Michael Klaers education draft!  
    mucked around with by Trevor Gooch.}

VAR

    s    : DYNARRAY[] of CHAR;
    RecName    :    string;
    FieldName    :    string;
    Criteria    :    string;

    r,r1    :    REAL;

PROCEDURE SumReals(h1:HANDLE);
    BEGIN
        s := GetRField(h1,RecName,FieldName);
        r := Str2Num(s);
        r1 := r1 + r;
    END;

{ This now takes three parameters: the record name, the field name, and the critera.  
    To get around single quotes in the parameter string, simply use two consequetive single quotes.}

BEGIN
    RecName:=WSScript_GetPrmStr(0);
    FieldName:=WSScript_GetPrmStr(1);
    Criteria:=WSScript_GetPrmStr(2);
    
    
    ForEachObject(SumReals,Criteria);
    WSScript_SetResInt(r1);
END;    

RUN(WSSum);
 

Definitely solved my problem!

 

Thanks!

Counting Stanchions.vwx

[Pat Stanford]

While the three of us (me, Trevor, Michael) might be able to do the extra quotes to escape a criteria, a normal user (who is not a relatively advanced scripter) will be total lost and confused by how to do that and how to trouble shoot it when it does not work.

 

So be careful with posting/sharing scripts that pass criteria as strings. They are fragile and can rapidly eat up your time trying to support them.

[trevorgooch]

@Pat Stanford Thank you for the advice, that is surely born out of experience.

 

I've been considering the best way to address it, looking forward.  I suspect simply not posting the scripts directly is the most prudent move.

 

One of the big picture challenges I'm looking at currently, is how to make the drawings and solutions as resilient as possible.  The opposite of "fragile" or easily broken. 😉.   

I will be on the look out for more danger zones!

 

 

[trevorgooch]

So, I've taken this a little farther: collecting the channels of lighting devices that share a dimmer (useful for labeling breakouts or breakers).

 

 

 

The script looks for the match field and field to concat and return.

 

Lots of options here - and no criteria.  This does seem friendlier.