Jump to content

Worksheet database setting


MartinaL

Recommended Posts

Hello, 

I would like to create a worksheet database whose criteria are "select all walls with the Pset_WallCommon IFC value Reference IWS-1**

because here is the wall style Mark I want to use to divide my walls

 

I tried the string 

=DATABASE((('Pset_WallCommon'.'Reference')='IWS-1**'))

but it gives me no results, like it cannot read the IFC property 

 

When I use that field in a spreadsheet it requires the string 

=GETIFCPROPERTY(‘Pset_WallCommon’.’Reference’)

 so maybe I need to recall the IFC property some way?

 

I tried some variants but nothing worked, can someone help me?

 

Thank you very much

Link to comment
  • 1 year later...

Hi, I have the same problem, even if I'm trying to filter the worksheet with the criteria Pset_DoorCommon IsExternal true.

 

Is there anything new? I'v also seen @Pat Stanford's reply with a script to select a custom IFC property here 

 

but I don't know if there could be a way to use something similar for worksheets.

I just would like to know f there's a way to do it or if I have to find a workaround. 

 

Thank you, 

Martina

 

 

Link to comment

@MartinaL

 

There is no default criteria for accessing a pSet Property value as part of a Criteria.

 

But there is a handy dandy function called DatabaseByScript that will allow you to put whatever you can script into a database.

 

Copy the script below and paste it into a new Vectorscript names PsetValueAsCriteria.

 

Create a database row and put in some criteria as a place holder. Right click on the database header row (i.e. 3, not 3.1, 3.2, etc) and choose Edit Database Formula.

 

Delete everything that is there and paste in the following line

=DATABASEBYSCRIPT('PsetValueAsCriteria', 'ifcDoor', 'Pset_DoorCommon', 'IsExternal', '1')

 

The first section is the name of the script.

The second is the IFC Entity for the Pset. This will limit the number of objects in the drawing that have to be checked.

The third is the name of the Pset you are working with.

The fourth is the name of the Property you want to use.

The fifth is the value of the Property you want to have included in the database. In this case IsExternal returns a value of 1 when the door is marked as External.

 

This should work for any Entity/Pset/Property where you only want a single value in the database so a check for equivalence of the whole Property value to the "check value" is sufficient. If you need more complicated comparisons, you will have to modify the script itself.

 

HTH.

 

Ask again if you need more information.

 

Procedure PsetValueAsCriteria;

{March 31, 2022}
{October 3, 2020}
{©2020, 2022 Patrick Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

{No Warranty Expressed of Implied. Use at your own risk.}

{A sample script to show how to use the DatabaseByScript}
{functionality in a worksheet to create your own objects when}
{the standard criteria are not enough.}

{Based on work from 2020. This version takes parameters of}
{IFC_Entity, Pset, Property, and check value to determine}
{objects included in the Database.}
{Only objects that have the IFC_Entity assigned are checked}
{to minimize the processing time and not have to check all}
{objects in the drawing. Objects that have the Entity are}
{then queried for the value of the specified Pset and Property}
{The returned Property value is compared to the passed "Check Value"}
{Objects where the returned value matches the check value}
{are added to the database.}

{This script is run from the Database Formula Bar using a syntax of:}
{=DatabaseByScript('PsetValueAsCriteria', 'ifcEntity Name', 'Pset_Name',}
{    'Property_Name', 'Check_Value')}


{Procedure Execute does the actual processing. It is called }
{for each object that has the ifc_Entity attached.}

{The main body of the code only gets the parameters that are}
{passed to the script and uses the passed Record & Field}
{as Criteria in the ForEachObject procedure. ForEachObject}
{gets a Handle for each object that matches the criteria}
{and passes the handle to that object to the Execute procedure.}
{The criteria in the ForEachObject line can be changed as 
{necessary to return only the correct objects.}

{There is no error checking in this script.}

{Do not operate heavy machinery or drive an automobile}
{while using this script. If use causes excessive itching or }
{unexplainable hair loss, discontinue use immediately and see}
{a programmer immediately. There be Dragons Here.}


Var	N1: Integer;
	TheEntity, ThePset, TheProperty, PropValue, CheckValue, CritString: String;
	B1:	Boolean;

Procedure Execute(Hd1:Handle);

	Begin
		B1:=IFC_GetPsetProp(Hd1, ThePset, TheProperty, PropValue, N1);
		If PropValue=CheckValue then WSScript_AddHandle(Hd1);
	End;

	
Begin
	TheEntity:=WSScript_GetPrmStr(0);	{passed ifcEntity}
	ThePset:=WSScript_GetPrmStr(1);		{passed Pset Name}
	TheProperty:=WSScript_GetPrmStr(2);	{passed Property Name}
	CheckValue:=WSScript_GetPrmStr(3);  {passed "Check Value"}
	CritString:=Concat('((IFC_ENTITY=', TheEntity, '))');  {build criteria as a single string}
	ForEachObject(Execute, CritString);  {run Execute on each object matching Criteria}
End;

Run(PsetValueAsCriteria);

 

Link to comment

@Pat Stanford

 

Thank you very much Pat,the instructions are perfect and you solved my problem!

 

Just one more question: after changing the database criteria to a DatabaseByScript, the Spreadsheet Sort (Ascending, Descending, None) doesn't work anymore; I tried using both a GETIFCPROPERTY spreadsheet formula and a Record Door formula, but I doesn't work anyway.

 

Is there maybe anything that could be added to the script to Sort the results by IfcDoor.Name ascending?

If not, thank you very much anyway.

 

Martina

Link to comment

DatabaseByScript does exactly as you say and creates subrows in the order they are created. It is possible to save all the objects into an array, sort them, then make the database.

 

Run this using a databasebyscript formula of:

 

=DATABASEBYSCRIPT('SortedPsetValueAsCriteria', 'ifcDoor', 'Pset_DoorCommon', 'IsExternal', '1', 'Name')

 

The script will sort on the value stored in Entity Property Name based on the values passed above.

 

I very lightly testing this one on just a couple of objects. Please test before using on a real file.

 

Procedure SortedPsetValueAsCriteria;

{April 4, 2022}
{March 31, 2022}
{October 3, 2020}
{©2020, 2022 Patrick Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

{No Warranty Expressed of Implied. Use at your own risk.}

{A sample script to show how to use the DatabaseByScript}
{functionality in a worksheet to create your own objects when}
{the standard criteria are not enough.}

{Based on work from 2020. This version takes parameters of}
{IFC_Entity, Pset, Property, check value, and SortProperty }
{to determine objects included in the Database.}

{Only objects that have the IFC_Entity assigned are checked}
{to minimize the processing time and not have to check all}
{objects in the drawing. Objects that have the Entity are}
{then queried for the value of the specified Pset and Property}
{The returned Property value is compared to the passed "Check Value"}
{Objects where the returned value matches the check value}
{are added to the database.}
{In this script the SortProperty is an Entity Property, NOT}
{a Pset property.}

{This script is run from the Database Formula Bar using a syntax of:}
{=DatabaseByScript('SortedPsetValueAsCriteria', 'ifcEntity Name', 'Pset_Name',}
{    'Property_Name', 'Check_Value', 'SortProperty')}

{The matched records are stored in an array and sorted based on the SortProperty}
{value prior to being returned as the database objects}


{Procedure Execute does the actual processing. It is called }
{for each object that has the ifc_Entity attached.}

{The main body of the code only gets the parameters that are}
{passed to the script and uses the passed Record & Field}
{as Criteria in the ForEachObject procedure. ForEachObject}
{gets a Handle for each object that matches the criteria}
{and passes the handle to that object to the Execute procedure.}
{The criteria in the ForEachObject line can be changed as 
{necessary to return only the correct objects.}

{There is no error checking in this script.}

{Do not operate heavy machinery or drive an automobile}
{while using this script. If use causes excessive itching or }
{unexplainable hair loss, discontinue use immediately and see}
{a programmer immediately. There be Dragons Here.}
Type
	SortIFC = STRUCTURE
		Prop	:String;
		Hand	:Handle;
	End;

Var	N1,N2,N3,N4: Integer;
	TheEntity, ThePset, TheProperty, PropValue, CheckValue, CritString :String;
	SortProperty, S1: String;
	B1:	Boolean;
	A1: DynArray of SortIFC;

Procedure Execute(Hd1:Handle);

	Begin
		B1:=IFC_GetPsetProp(Hd1, ThePset, TheProperty, PropValue, N1);
		If PropValue=CheckValue then
			BEGIN
				{Store the value of the field to sort on and handle in array}
				{Since the Name is and Entity Propery instead of a Pset property}
				{we need to use a different function. IFC_GetEntityProp2 appears}
				{to return Mapped data as well as by Instance. Where IFC_GetEntityProp}
				{appears to only return data from the Instance.}
				B1:=IFC_GetEntityProp2(Hd1, SortProperty, S1, N1, N1);
				A1[N4].Hand:=Hd1;
				A1[N4].Prop:=S1;
				N4:=N4+1;
			End;
	End;

Begin
	N4:=1;
	TheEntity:=WSScript_GetPrmStr(0);	{passed ifcEntity}
	ThePset:=WSScript_GetPrmStr(1);		{passed Pset Name}
	TheProperty:=WSScript_GetPrmStr(2);	{passed Property Name}
	CheckValue:=WSScript_GetPrmStr(3);  {passed "Check Value"}
	SortProperty:=WSScript_GetPrmStr(4);{passed name of property to sort by}
	CritString:=Concat('((IFC_ENTITY=', TheEntity, '))');  {build criteria as a single string}
	N2:=Count(CritString);				{How may objects meet criteria}
	Allocate A1[1..N2];					{Set size of array to number of objects}
	ForEachObject(Execute, CritString); {run Execute on each object matching Criteria}
	SortArray(A1, N2, 1);				{Sort the array by the Sort Property}
	For N3:= 1 to N2 DO
		WSScript_AddHandle(A1[N3].Hand);{set the database to the sorted objects}
		
End;

Run(SortedPsetValueAsCriteria);

 

Link to comment

Hello @Pat Stanford

 

I'm sorry for bothering you again with scripting, but I have an other issue.

With this second script the door names are ordered ascending, but the summarize items spreadsheet option for the 'name' does not work properly. 

In the DOOR TEST 1 I used the SortedPsetValueAsCriteria script and database header, as a result the PE-01 are not correctly summarized and the PE-02 are correct.

In the DOOR TEST 2 I used the PsetValueAsCriteria script and database header, as a result the PE-01 are correctly summarized and the PE-01 are correct.

 

Maybe there's a summarizing option to be added to the script?

That way I suppose it won't be possibile do de-summarize the results using the spreadsheet options, right?

 

Thank you again for your time.

 

Martina

 

 

Schermata 2022-04-05 alle 12.01.47.png

Schermata 2022-04-05 alle 12.02.09.png

Link to comment

@MartinaLIt would be challenging to do a version of DatabaseByScript that would SUMmarize. You would have to create proxy objects with the summed information and store those rather than the real objects in the database.

 

I have submitted enhancement requests for a Sortable/SUMmable version of DatabaseByScript.

 

Unfortunately, I have run out of scripting time this week, so I will not be able to do anything on a SUMmed version of the script for you.

 

 

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