count unique property entries?

[MuirEng]

Hello,

I could use a hint on this one. I would like my schedule to report the elevation of devices, but I do not want a unique row in the schedule if the elevation is not consistent across all similar devices. In that case, I would like the schedule to read "VARIES"

The solution would seem to lie with a formula column in the schedule. My idea is to have the formula loop through the assigned elevation values and as soon as it finds an elevation value different from the first element it will drop out and report "VARIES". If it gets to the last entry then it would go ahead and report the elevation of the first element (now known to be the elevation of all elements).

This assumes that there is no function that can report the number of distinct entries in a property of course.

That is my specific question, but I could use some guidance on this in general. Are there any tools that can be used to build and test code for this sort of application, which include some level of debugging tools? Working on the formula in a schedule is painful, becuase it seems if there is a syntax error MEP simple spits out the offending code in the results field with no information about why it is complaining. Or reference materials?

(I would like to look up if the FOR EACH statement is supported, just to cite an example).

thanks

[Keith.Brown]

You can find some information at this website.  http://www.w3schools.com/vbSCRIPT/  

You can also see if the syntax is supported by using the INSERT VBSCRIPT CODE in the lower right hand corner of the formula dialog.  If the syntax that you want is in there then it is supported.  If it is not then it is probably not supported.  The exception to this will be when you are using COM in a formula.

Note.  It would be better to do this sort of thing outside of a schedule formula and instead in an API language and then just fill in the value in a property set and use that value in your schedule.

 I am not saying that it cannot be done using the formula editor I am just saying that it will probably be difficult because you will have to loop through each device in the database and compare all schedulable properties.  This post might be of help.  http://forums.autodesk.com/t5/AutoCAD-MEP/Extending-Neighboring-Conduit-in-Custom-Schedules/td-p/470...

[Keith.Brown]

---

MuirEng wrote:

This assumes that there is no function that can report the number of distinct entries in a property of course. 

---

You can always use [QUANTITY].  It is available inside the formula editor.

[MuirEng]

Hi Keith, just responding to your last post. I did experiment with quantity but it seemed this simply reported the quantity of devices associated with the schedule row. I need to base logic on the number of unique elevations across all devices of a given type, and not the number of devices of that type.
I'll check out your prior post. Once again, thanks for the advice!

[VitalyF]

Set app = GetObject (,"AutoCAD.Application")
On Error Resume Next
RESULT = "--"

Set baseApp 	= app.GetInterfaceObject("AecX.AecArchBaseApplication.7.5")
Set ActiveDoc 	= baseApp.ActiveDocument
Set ObjectCollection 	= ActiveDoc.ModelSpace

z = "[DviceObject:Elevation]"
eq = z

For Each Object In ObjectCollection
	If Object.ObjectName = "AecbDbDevice" Then
		If Object.Elevation <> z Then 
			eq = "Varies"
		End If
	End If
Next
RESULT = eq

 If at least one is not equal to it Varies? 

 It is right?

[VitalyF]

Another example is sorted by style

Set app = GetObject (,"AutoCAD.Application")
On Error Resume Next
RESULT = "--"

Set baseApp 		= app.GetInterfaceObject("AecX.AecArchBaseApplication.7.5")
Set ActiveDoc 		= baseApp.ActiveDocument
Set ObjectCollection 	= ActiveDoc.ModelSpace

z = [DviceObject:Elevation]
s = "[DviceObject:Style]"

For Each Object In ObjectCollection
	If Object.ObjectName = "AecbDbDevice" Then
		If Object.StyleName = s Then 
			If Object.Elevation <> z Then 
				z = "Varies"
			End If	
		End If
	End If
Next
RESULT = z

 

example dwg file 

Vitaly

[MuirEng]

awesome, looks great,

I'll give that a go but it seems right on target

thanks!

[Keith.Brown]

Nice code VitalyF but if i understand his question correctly wouldnt you have to add an if statement for each column that is in the schedule?  (I am not sure how many columns are in the schedule that MuirEng is using) What makes a row unique in a schedule is that all column values (whether the column is visible or not) are the same.  So for each column that you have in the schedule you would have to add an if statement to check and make sure that it is equal to the device row.  Just make sure that the last thing you check is the elevation.

One thing that I would add is that once you find a duplicate elevation then exit out of the loop.  If you have 2000 devices and if you find a different elevation on the 10th time through the loop then there is no need to loop through the rest of the devices.  The time difference might be negliable but you never know.  2000 devices with each of them looping 2000 times can add up. In this case it would loop 4,000,000 times!

I am not sure if my syntax is correct but something like the code below.

Set app = GetObject (,"AutoCAD.Application")
On Error Resume Next
RESULT = "--"

Set baseApp 		= app.GetInterfaceObject("AecX.AecArchBaseApplication.7.5")
Set ActiveDoc 		= baseApp.ActiveDocument
Set ObjectCollection 	= ActiveDoc.ModelSpace

z = [DviceObject:Elevation]
s = "[DviceObject:Style]"

For Each Object In ObjectCollection
	If Object.ObjectName = "AecbDbDevice" Then
		If Object.StyleName = s Then 
			If Object.Elevation <> z Then 
				z = "Varies"
                                Exit For
			End If	
		End If
	End If
Next
RESULT = z

[VitalyF]

Ok! )))

PS 

If 1-2 thousand devices it is ok!

[MuirEng]

Hi Guys,

thanks for the code. I've loaded the VBA applications for AutoCAD and am trying to figure out how to follow Keiths suggestion that it should live outside the schedule and load a property set value. I have a bunch of gaps in my understanding but I hope to figure it out. Any advice or handholding is certainly welcome.

update: I created a property set value associated with the STYLE corresponding to a particular device (in this case a light fixtures), called "SameElevation". I can manually set this to true/false. The light fixture schedule looks at this and if is set to true then it displays the device elevation, if false it displays "VARIES". The missing element now is having the code above run automatically and set this property value. This is not a bad setup, because usually only a few fixtures vary in height so it is not a lot of manual work. Still, it would be great if it can be made automatic, and I think if I can learn how to do that it will open up some other ideas.

thanks again

[VitalyF]

Hi,

This formula is not for VBA. This ready-made formula for Schedule Table.

See the attached dwg file above.

How to use it:

1. Open your file with devices or create a new.

2. Paste this Schedule Table into it

3. Right-click on the Schedule Table "Add All property Set"

4. Right-click on the Schedule Table > Selection > Add > select device

Or copy the formula and paste into your Schedule Table

If you need more help, I will answer later

Vitaly