DB connected blocks - how to colour automatically via script

[Jeanette_A]

Hi,

I've got a floorplan containing hundreds of desks / blocks. Each of these blocks has an attribute; a unique number for each desk. The desks (numbers) are connected to a database via DB connect where I extract data (employees names) so I can see their location on the floorplan. And some of these desk are marked as "Reserved" (extracted from the database).   

Does some know how I can create a script and colour all the reserved desks on the floorplan?

Thanks! 

Jeanette 

[DannyNL]

Here you go.

Without knowing the block name and attribute tag, see code below. It will color all attributes with the value "RESERVED" to the specified color after selecting the blocks.

(defun c:Test ()
   (if
      (and
         (setq T_Selection (ssget '((0 . "INSERT")(66 . 1))))
         (setq T_NewColor (acad_colordlg 1 nil))
      )
      (progn         
         (vla-StartUndoMark (vla-get-ActiveDocument (vlax-get-acad-object)))
         (foreach T_Entity (vl-remove-if '(lambda (T_Item) (listp (cadr T_Item))) (ssnamex T_Selection))
            (setq T_Attributes (vlax-safearray->list (vlax-variant-value (vla-GetAttributes (vlax-ename->vla-object (cadr T_Entity))))))
            (foreach T_Item T_Attributes
               (if
                  (= (vla-get-TextString T_Item) "RESERVED")
                  (vla-put-Color T_Item T_NewColor)
               )
            )
         )
         (vla-StartUndoMark (vla-get-ActiveDocument (vlax-get-acad-object)))
      )
   )
   (princ)
)

[Jeanette_A]

Thanks a lot for the code above!! 

I'm not too familiar with acad code (yet (only been using the program for a year or so)) and I'm a little confused to what lines to change to make my desks change colour. I have quite a few different style desks on one floor (name of desks are Vitra, Triax etc..) so I will have to add more lines for these. All reserved should be coloured white: would you add "white" to the code or 255,255,255?). The attribute tag is "LOCATION".

Lines I've changed so far doesn't work but I'm a novice in the field... Would you be able to direct me to what part of the code I would need to change in order to make it work? I appreciate all help I can get 🙂 Thanks!! 

[DannyNL]

OK, so you want to color the desk itself and not (only) the text if I understands correctly.

This is also possible but requires you to have your blocks for the desks setup correctly. Normally most people would draw the entities in the block in the color BYLAYER, but the most easy way in this case would be if all entities in the block are drawn in the color BYBLOCK, if all blocks need to remain on the same layer.

So if the block has been setup in the right way with the color as described above, it would only take one additional line of code to color code the desk as well. If you are having multiple blocks with different names, that would not be a problem as long as the attribute tag is LOCATION.

[Jeanette_A]

That's correct; I want to colour the desk only. Not the LOCATION tag or name (it's purely for visibility reasons). 

The Block is drawn "By Block". 

The attribute tag is always LOCATION. 

Here's a screenshot of what it currently looks like: 

Snapshot from block editor so you can see the properties of the block. 

[DannyNL]

Ok, here is the new code.

You can only select blocks and it will iterate through all blocks in search of the attribute tag LOCATION with the value RESERVED. If found, the corresponding block will be colored white (I've removed the manual selection of a new color) and all entities in the block in the color BYBLOCK will be shown white.

(defun c:Test (/ T_Selection T_Entity T_Object T_Attributes)
   (if
      (setq T_Selection (ssget '((0 . "INSERT")(66 . 1))))
      (progn         
         (vla-StartUndoMark (vla-get-ActiveDocument (vlax-get-acad-object)))
         (foreach T_Entity (vl-remove-if '(lambda (T_Item) (listp (cadr T_Item))) (ssnamex T_Selection))
            (setq T_Attributes (vlax-safearray->list (vlax-variant-value (vla-GetAttributes (setq T_Object (vlax-ename->vla-object (cadr T_Entity)))))))
            (foreach T_Item T_Attributes
               (if
                  (and
                     (= (vla-get-TagString T_Item)            "LOCATION")
                     (= (strcase (vla-get-TextString T_Item)) "RESERVED")
                  )
                  (vla-put-Color T_Object 7)
               )
            )
         )
         (vla-StartUndoMark (vla-get-ActiveDocument (vlax-get-acad-object)))
      )
   )
   (princ)
)

[Jeanette_A]

I did a copy / paste job of the above but doesn't seem to work. Trying to get my head around the code but I think I've got to go back to basics and understand that first. 

I've written some simple code before by copying and pasting the commands from the command line and added them to the scr file but this time around I can't get it to work. I'll keep you posted about my progress though. 

[DannyNL]

See red remarks in the code below for some explanation.

And as explained before make sure the entities in your block definition that you want to be colored to white are set to the color BYBLOCK.

If the blocks are separate DWG-files and you have modified these, do not forget to redefine the blocks in your drawing else these will not be updated with the changes you've made. If you have modified the blocks directly in your drawing then everything should be fine (for that drawing only).

If you cannot make it work, please post a couple of your blocks and I'll modify them for you and give a working example drawing.

(defun c:Test (/ T_Selection T_Entity T_Object T_Attributes)
Define function name Test and set local variables

   (if
      (setq T_Selection (ssget '((0 . "INSERT")(66 . 1))))
      Let user select blocks with attributes and if selected proceed

      (progn         
         (vla-StartUndoMark (vla-get-ActiveDocument (vlax-get-acad-object)))
         Start internal Undo mark to group following modifications into one Undo action

         (foreach T_Entity (vl-remove-if '(lambda (T_Item) (listp (cadr T_Item))) (ssnamex T_Selection))
         For each entity item (= block) in the selection set (after removing non-entities from the selection set)

            (setq T_Attributes (vlax-safearray->list (vlax-variant-value (vla-GetAttributes (setq T_Object (vlax-ename->vla-object (cadr T_Entity)))))))
            Retrieve all attributes from the current block

            (foreach T_Item T_Attributes
            For each attribute execute the following

               (if
                  (and
                     (= (vla-get-TagString T_Item)            "LOCATION")
                     (= (strcase (vla-get-TextString T_Item)) "RESERVED")
                  )
                  Check if Attribute tag is LOCATION and attribute value is RESERVED

                  (vla-put-Color T_Object 7)
                  If above is true then set the color of the block (!) to color 7 (white) 

               )
            )
         )
         (vla-StartUndoMark (vla-get-ActiveDocument (vlax-get-acad-object)))
         End internal Undo mark to group all modifications after the start Undo mark

      )
   )
   (princ)
)

[Jeanette_A]

@DannyNL 

I've dived into your code and realised that the code is trying to retrieve the "reserved" information from the attribute field on the block itself. 

Extracting information from the block attribute is not possible, as the attribute is not linked to my database (even though the text attribute is part of the desk, it's only the desk block and its geometry that is attached to the database).  

I'll try and explain a little further: 

I'm using ODBC Driver 11 for SQL Server 2008.
I need to extract the informaion from a database table called "subsetcadlink". Please see picture below (sorry, but I had to grey out a few fields)
- "mslink" refers to a uniquie desk identifier
- "lastname" will either be dispayed with an actual last name or as "reserved" <-- I believe this is where I would like the code to point to
- "location" refers to which block the field should be linked to (I'm using attached link labels when linking the desk blocks to the database)

I hope this helps. Do you have any references or external websites that I can use to teach myself LISP? 

[DannyNL]

OK, I see.

But in your example image the word RESERVED is at the location of the desk (although not visible in your Attribute Edit dialogue). How is this text placed with the desk? Just as a separate text or is the block created on the fly with a fixed text object?

Because as long as the word RESERVED is connected to the desk block somehow, you would be able to use LISP to make the modifications to the desks after they are placed in your drawing.

LISP does not have any commands to directly connect to an external database and in that case code has to be written using MS ADO to connect and read the database. And unfortunately I do not have any experience (yet) in writing such code to connect to databases. But maybe someone else does and can help to write such code.

More information about developing for AutoCAD (incl. LISP) can be found here. There is also a topic on connecting to databases:

http://usa.autodesk.com/adsk/servlet/index?siteID=123112&id=1911627

For learning LISP you can start to use the Autodesk developer documentation and also a lot of forums like these can help you.

[Jeanette_A]

The attribute dialogue is only a reference for me so I know which desk to choose when placing the linked label from the database (sorry, I think I was misguiding you in my first post). I could choose to NOT include an attribute on the desk, but in my scenario it has to be included for data extraction purposes. 

The "Reserved" text (or employee's names) is automatically created / instantly populated on the desk itself when attaching the desks to the database via the "Link!" function (image posted in my previous post). It is not a separate text I've placed down and it's not part of how the block itself is created. 

"Reserved" is definitely connected to the desk itself. Below is a screenshot of the properties the desk. 

Thanks a lot for the link below. I will have a look and see if I'm able to find out more about this. 

[stevor]

Your image, IMG.jpg, shows text like data on each desk,

of the desk ID and a name,

which probably came from fields of the SQL.

If the are not part of the Insert,

what program or service generated them in the dwg?

A similar function could make a list or text file that

could be used to provide the info for  Danny's programs.

[DannyNL]

Thanks for the additional info. It seems the link will place the desk as a BLOCK and a separate MTEXT indicating the name or 'reserved'. They might be connected to each other with (invisible) additional data, but this is not visible in the properties table.

But I still think it will be possible to recolor the desks after they are inserted in the drawing. Although the text 'reserved' is a separate object and no part of the block itself, the location of the text is within the boundaries of the desk. With LISP it is possible to read the boundaries of the desk block, find any MTEXT inside those boundaries and act on it depending on the contents of the MTEXT.

Otherwise someone has to come up with some advise for you how to connect directly to the database, but that will make definitely a much more complex LISP code.

[Jeanette_A]

The process is this: 

I add the desk block to my floorplan and once added:

1) insert block Edit Attributes prompts me to give the block a desk number (as mentioned, this is for dataextraction purposes)

2) in this scenario I edit the text from 1000 to GB350. This is then reflected on the desk.

3) I open the dataview in DBConnect and find the relevant desk and link it

4) the desk is now linked and marked as reserved according to the database ("reserved" (which is lastname) is the only information I extract from the database)

Perhaps you understand the process better with the image and description above, and how the procedure is done + how the linkage works. 

The desk and the text is definitely linked but I can't physically see the connection. I think this might be the key to write the LISP code...? 

[DannyNL]

Thanks. That looks pretty straightforward.

Would it be possible to post a drawing with just one single desk and the reserved indication, so I can check and see how they are connected and hopefully come up with a solution for you?

[Jeanette_A]

Hi Danny,

I sent you a pm with the block and reserved information attached. Obviously not with the database information but hopefully enough for you to work with. I appreciate your help. 

Thanks a lot!! 

Jeanette  

[DannyNL]

Hi Jeanette,

Please check your PM.

If you are OK with the possible solution I'll post it here as well.

[DannyNL · Accepted solution]

@Jeanette_Ahas send an example drawing with the specified block.

In the example the desk and the text containing the name/reserved are two separate objects, a BLOCK and a MTEXT. The two objects are somehow connected as when you move/copy the desk, the text is moved/copied with it. But both objects can also be manipulated separately, so i.e. erasing one doesn't erase the other. Furthermore the desk block does contain one attribute with the tag 'location'

To solve the recoloring issues, the code makes the user select blocks with attributes only. It will then step through each block and look for an attribute with the tag 'location'. If it find such an attribute, the lower left and upper right of the block bounding box is used to make a Window selection on texts containing the word 'Reserved'. Only if the selection succeeds, the block will be recolored to white.

(defun c:Test (/ T_Selection T_Entity T_Object T_Attributes T_ProcessCheck)
   (if
      (setq T_Selection (ssget '((0 . "INSERT")(66 . 1))))
      (progn         
         (vla-StartUndoMark (vla-get-ActiveDocument (vlax-get-acad-object)))
         (foreach T_Entity (vl-remove-if '(lambda (T_Item) (listp (cadr T_Item))) (ssnamex T_Selection))
            (setq T_ProcessCheck nil)
            (setq T_Attributes (vlax-safearray->list (vlax-variant-value (vla-GetAttributes (setq T_Object (vlax-ename->vla-object (cadr T_Entity)))))))
            (foreach T_Item T_Attributes
               (if
                  (= (strcase (vla-get-TagString T_Item)) "LOCATION")
                  (setq T_ProcessCheck T)
               )
            )
            (if
               T_ProcessCheck
               (progn
                  (vla-GetBoundingBox T_Object 'T_LowerLeft 'T_UpperRight)
                  (if
                     (ssget "_W" (vlax-safearray->list T_LowerLeft) (vlax-safearray->list T_UpperRight) '((0 . "*TEXT")(1 . "*Reserved*")))
                     (vla-put-Color T_Object 7)
                  )                      
               )
            )
         )
         (vla-EndUndoMark (vla-get-ActiveDocument (vlax-get-acad-object)))
      )
   )
   (princ)
)

[Jeanette_A]

Hi Danny,

Thanks a lot for the code provided! I have been able to test it and it works absolutely fine - exactly what I was after!! 

Now as I've got the base code working, I can easily add more functions to it and tweak as necessary. 

Thanks again for all your help!! 

[DannyNL]

You're welcome & glad I could help