Enhancing Circuit Builder with User-defined Functions - AutoCAD Electrical

• 

  Coinciding with the acquisition of VIA Development, Nate joined Autodesk in March of 2003 after a decade stint as an entrepreneur following a two-decade stint as a controls engineer and software applications developer at Owens-Corning. Nate is now the lead product architect for AutoCAD Electrical. He loves this stuff.

  About Nate

Latest Post

• Add to Bookmarks Send to Peer

  Enhancing Circuit Builder with User-defined Functions - AutoCAD Electrical
  December 2, 2008, 04:53 PM Nate Holt

  Thanks to Autodesk's Pat Murnen for this idea...

  Let’s say that you are working together with an instrumentation engineer on a fairly big project. You’re doing the electrical controls and he is doing what he does. He supplies you with an Excel spreadsheet listing the major equipment that you have to power like motors and pumps and stuff. His spreadsheet might look like this:

  

   

  For the motor circuits, you want to run the Circuit Builder command to pop in the 3-phase schematic power circuits. But the motor tag, description, and equipment spec horsepower values then need to be manually pulled from the spreadsheet and annotated on to the schematic’s motor symbol.
  Painful.
  Wouldn’t it be cool if, right inside of Circuit Builder, we could pop up this Excel data and just “pick” on the row of data and have it automatically get pushed on to the circuit’s motor symbol?
  Let’s do it. Here’s one strategy…
  1.    Create a little AutoLisp utility that reads the P&ID data spreadsheet, displays the data in a pick list dialog. User picks a row of data. The little AutoLisp utility then pushes the picked row’s data out to the last electrical symbol that was inserted into the drawing.
  2.    Modify the Circuit Builder spreadsheet file, “ace_circuit_builder.xls”, to include a “call” to our little AutoLisp utility at a strategic point in the spreadsheet just AFTER the motor symbol is inserted. The AutoCAD "handle" of the motor symbol should be accessible from a global Lisp variable "#hdl_list" that Circuit Builder maintains for a short time after each component inserts. The call to our utility will tie in to this global to grab the motor symbol's handle and then push the picked P&ID data out to it.

  Step 1 – The little AutoLisp utility

  This will be in two parts: A) a medium-sized AutoLisp program saved in a text file that we will name “pid_pick.lsp”, and B) a small text file that defines the basics of the pick list “dialog” that will display the spreadsheet data. We’ll will name this file “pid_pick.dcl”.

   

  Here is the program part, file pid_pick.lsp followed by the dialog definition file pid_pick.dcl …
  ; ** 01-Dec-2008 - sample program based on example created by Autodesk's Pat Murnen
  ; ----------- P I D _ P I C K . L S P ------------
  ;
  ; This function called from within Circuit Builder when a motor symbol has just been inserted.
  ; It opens an Excel file containing data extracted from a drawing such as a P&ID. This data is
  ; displayed in a small dialog. User picks an entry in this dialog and hit's OK. Selected entry's
  ; data is then pushed out to specific TAG1 and RATINGx attributes on the just-inserted motor
  ; symbol.
   
  (defun c:pid_pick ( xlsfnam / cancel dcl_id dclnam dsplst ff fields fldnamstr
      fnam lst recs rtrn xlshdl en tabnam xlsfnam ben newlst data x)
     
   ; NOTE: do NOT declare #hdl_list as a local variable here. It is a global Lisp variable that
   ; is set by Circuit Builder's "insert component" API call.                        
   (setq rtrn nil)
   (setq recs nil)
   (setq ben nil)
   (setq fnam nil)
   
   ; First, test to see if Circuit Builder has just inserted a component. The global "#hdl_list"
   ; should be non-nil and should contain a valid handle as the first element of the list.
   (if (OR (not #hdl_list) (not (car #hdl_list)) ; #hdl_lst does not exist
            (not (setq ben (handent (car #hdl_list))))) ; big assumption that first block is our target
      (progn ; problem, no global exists or doen't carry valid handle
        (princ "\nProblem: could not find handle of recently inserted component\n")
      )
   ; ELSE
      (progn ; Have what appears to be the recently inserted component. Okay to continue.
        ; Step #1 - open up the spreadsheet containing the P&ID data. Read in the data and
        ;           display in a pick list.
        ; read in the spreadsheet with the extracted P&ID data
        (if (not xlsfnam)(setq xlsfnam (getfiled "P & ID Extracted Data Spreadsheet" "pid_extract.xls" "XLS" 0)))
   ) )
   (if xlsfnam
      (progn ; A spreadsheet filename identified. Try to open it and read it in.  
        (if (/= (strcase (substr xlsfnam (- (strlen xlsfnam) 2))) "XLS")
          (setq xlsfnam (strcat xlsfnam ".xls"))
        ) 
        (if (not (setq fnam (findfile xlsfnam))) ; look for the XLS file in normal Acad path list
          ; Not found. Keep looking.
          (setq fnam (findfile (strcat GBL_wd_usr xlsfnam))) ; look for it in AcadE "User" folder
        )
        (if (not fnam)
          (progn ; could not find the target spreadsheet file
            (princ "\nCannot find ")
            (princ xlsfnam)
          )
        ; ELSE 
          (progn
            ; open it and read it in
            (if (AND (setq xlshdl (wd_mdb_gethandle 2)) ; get next file handle
                     (setq ff (wd_mdb_Open xlshdl fnam "Excel 5.0;HDR=YES")) ; open as Excel file
                     (setq tabnam (wd_mdb_GetTabNames xlshdl)) ; list of sheet names
                     (setq tabnam (car tabnam))) ; use first or only sheet defined in xls file
              (progn
                ; Excel file exists and was able to be opened. Read in the list of
                ; column field names pulled from the target sheet.
                (setq fields (wd_mdb_GetFieldSpecs xlshdl tabnam))
                ; Assuming columns in the sheet are in the expected order ( ! )
                (if fields
                  (progn ; only pulling out 2nd (Tag) and 4th (Description) fields and 6th (Rating1)
                    (setq fields (car fields))
                    (setq fldnamstr (strcat (car (nth 1 fields)) "," (car (nth 3 fields)) "," (car (nth 5 fields))))
                  )
                ; else
                  (setq fldnamstr "Tag,Description,Equipment Spec")   
                )
                ; Read in all the data from the first sheet of the spreadsheet
                (setq recs (wd_mdb_GetRecs xlshdl tabnam fldnamstr "" ""))
                (wd_mdb_Close xlshdl) ; close the spreadsheet file
                (setq xlshdl nil)
                ; Now process the returned spreadsheet data dump
                (setq dsplst '())
                (if recs
                  (progn ; some spreadsheet data returned
                    ; clean up the returned data. It consists of dotted pairs with first part
                    ; being a copy of the column name. Strip off the column names leaving just
                    ; a list of sublists of row data.
                    (setq data recs)
                    (setq recs nil)
                    (foreach lst data
                      (setq newlst '())
                      (foreach x lst
                        (setq x (cdr x))
                        (if (= x " ") (setq x ""))
                        (setq newlst (cons x newlst))
                      )
                      (setq recs (cons (reverse newlst) recs))
                    )
                    (setq recs (reverse recs)) ; put back into original order
                    ; Format the cleaned spreadsheet data into a display list of data
                    (foreach lst recs
                      ; display format is different order than actual data. It needs to be a
                      ; list of concatenated text strings with TAB characters appropriately
                      ; positioned.
                      (setq dsplst (cons (strcat (car lst) "\t" (nth 2 lst) "\t" (cadr lst)) dsplst))
                    )
                    (setq dsplst (reverse dsplst)) ; restore original order
                ) )          
                ; Look for dcl file of same name, open if found.
                (setq cancel nil)
               
                (if (AND dsplst (setq dclnam (c:ace_find_file "pid_pick.dcl" 16)))
                  (progn ; 16 bit set =display error dialog if file not found
                    (setq dcl_id (load_dialog dclnam))               
                    (if (new_dialog "main_select" dcl_id)
                      (progn ; successfully found and opened the DCL file and dialog definition
                        (set_tile "main_title" "P & ID Equipment List")
                        ; Push the spreadsheet's formatted data list out to the display
                        (start_list "dsplst")
                        (mapcar 'add_list dsplst)
                        (end_list)
                        ; Set up to allow user to select a row of data from the display list
                        (action_tile "dsplst" "(setq rtrn (nth (atoi $value) recs))")
                        (action_tile "cancel" "(setq cancel 1)") ; "Cancel" button
                        (start_dialog)
                        (unload_dialog dcl_id) ; user exits dialog with OK or cancel
                ) ) ) )
                (if (AND (not cancel) rtrn)
                  (progn ; user didn't cancel out of dialog, okay to continue
                    (if (AND (car rtrn) (/= (car rtrn) ""))
                      (progn ; returned non-blank tag-ID value
                        (c:ace_mod_tag_attrval ben (car rtrn) nil) ; push tag-ID out to TAG1/TAG2
                        ; Mark the tag as "fixed"
                        (c:ace_tag_fixed en 1 nil)
                    ) )
                    ; Push data out to DESC1 and RATING1 attributes
                    (if (AND (cadr rtrn) (/= (cadr rtrn) "")) (c:wd_modattrval ben "DESC1" (cadr rtrn) nil))
                    (if (AND (nth 2 rtrn) (/= (nth 2 rtrn) "")) (c:wd_modattrval ben "RATING1" (nth 2 rtrn) nil))
     
                ) )
   ) ) ) ) ) )             
   (princ)
  ) 
   
  ... and here is the small dialog definition file, pid_pick.dcl :
   
  main_select : dialog
   {
   key="main_title";
   :text{label="Select motor or equipment number from this list";}
   :list_box{key="dsplst";width=75;height=17;tabs="20 30";allow_accept=true;}
   ok_cancel;
   }
   

  Step 2 – Add reference into the ace_circuit_builder.xls spreadsheet file.

   
  Once the above two files are saved to some folder that is in the AutoCAD support path list, you’re ready to add a reference to this utility, (c:pid_pick nil), to the spreadsheet.
  The key is to insert this reference at the point just after the motor symbol inserts.
   

  Here is the sheet for the “horizontal” three-phase circuit definition. The entry that triggers the motor to insert is shown here in the "H" column of the spreadsheet:

  

  Then the “I” column has two API calls: (c:ace_cb_anno…) to do some annotation and (c:ace_cb_save…) to save the resulting motor’s tag-ID to memory for potential later use (for doing wire numbers, for example).

  

   

  The call we want to insert will be the main program’s name from the lisp file above. It would look like this: (c:pid_pick nil) where the “nil” part means that we are not passing a pre-defined pid extract file name to the function.
   

  So, let’s edit the Excel spreadsheet for the “3PH_H” sheet and change this cell to look like this:

    

  Save the edited spreadsheet.

   

  Now let’s test.

  1.    Make sure that pid_pick.dcl is saved into a folder that is included in the ACAD support path.

  2.    Appload the pid_pick.lsp file

  3.    Launch Circuit Builder and insert a horizontal 3-phase motor circuit.

  4.    Hopefully, as the circuit builds, it will get to the point where our new lisp API call is embedded. You should get prompted to browse to the P&ID data extraction spreadsheet.

  5.    The utility reads the spreadsheet and pops up a pick-list dialog:

   

  

  Pick an entry and hit OK.

   

  The utility will push the above row’s values out to the motor symbol as circuit builder creates the three-phase motor circuit ( ! ).

  Here are the sample files if you'd like to try out Pat's idea. You'll need to manually edit your ace_circuit_builder.xls file to include the call to (c:pid_pick nil).

  files/22601_22700/22621/file_22621.xls - rename pid_extract.xls

  files/22601_22700/22631/file_22631.dcl - rename pid_pick.dcl and put into an ACAD support folder

  files/22601_22700/22641/file_22641.lsp - rename pid_pick.lsp and put into an ACAD support folder

   

   

   

  2 Comments | Add Comment Controlling the Machine >

Comments

• 

  December 4, 2008 12:12 AM Vladimir Panic

  Can those values be added to global "#data" (or some new global) at the start of circuit builder and than used in the same way like "#data" values?

• 

  February 18, 2009 10:40 AM Nate Holt

  Good idea. Seems like it might be possible to modify the pid_pick.lsp to push this out to some new #pid_data global and then reference this instead of #data in some of the Circuit Builder's API calls. Do you want to experiment?