wd_mdb_ModifyFieldData "Query to specify target record"

[Anonymous]

ACADE2018, Win10...

While attempting to use this API to modify an excel spreadsheet (output from BOM report saved as excel):

wd_mdb_ModifyFieldData: it keeps returning "nil" (failure to modify).

I suspect it is my "query" statement... but not sure.

Here's my statement:

(wd_mdb_ModifyFieldData bom_file_hdl cur_tabname (strcat "SELECT * FROM Sheet1$ WHERE USER2 = '" (cdr (nth 1 item))"'") (list
                                                                  (list "ITEM" (cdr (nth 0 item)));column1
                                                                  (list "USER2" (substr (cdr (nth 1 item)) 12 nil));column2
                                                                  (list "QTY" (cdr (nth 2 item)));column3
                                                                  (list "SUB" (cdr (nth 3 item)));column4
                                                                  (list "CATALOG" (cdr (nth 4 item)));column5
                                                                  (list "MFG" (cdr (nth 5 item)));column6
                                                                  (list "USER3" (cdr (nth 6 item)));column7
                                                                  (list "DESC" (cdr (nth 7 item)));column8
                                                                  );end list of  lists
      );end modify data

Those strcats look like this at run time:

(strcat "SELECT * FROM Sheet1$ WHERE USER2 = '" (cdr (nth 1 item))"'")=

"SELECT * FROM Sheet1$ WHERE USER2 = 'OBSOLETE - 150976'"

(list
                                                                  (list "ITEM" (cdr (nth 0 item)));column1
                                                                  (list "USER2" (substr (cdr (nth 1 item)) 12 nil));column2
                                                                  (list "QTY" (cdr (nth 2 item)));column3
                                                                  (list "SUB" (cdr (nth 3 item)));column4
                                                                  (list "CATALOG" (cdr (nth 4 item)));column5
                                                                  (list "MFG" (cdr (nth 5 item)));column6
                                                                  (list "USER3" (cdr (nth 6 item)));column7
                                                                  (list "DESC" (cdr (nth 7 item)));column8
                                                                  )=

(("ITEM" " 37") ("USER2" "150976") ("QTY" "1") ("SUB" "1") ("CATALOG" "S201U-K3") ("MFG" "ABB") ("USER3" "") ("DESC" "S200 MINIATURE MOLDED CASE CIRCUIT BREAKER, PRO M COMPACT"))

The help for this API says this about the "query" part: Query to specify target record.

But it gives no examples.

I have been poking around and can't seem to figure out what is wrong with the my query statement.

Anyone know how to formulate a valid query for this API?

[jseefdrumr]

I'm a real noob when it comes to this stuff, but it looks like there's an extra space in between the words 'of' and 'lists' at the end of the argument. It almost reads like it wants to insert a value there, like 'end list of 2 lists'.

Not sure if that can even be an issue here, but it stood out to me in all that spaghetti, lol.

[Anonymous]

LOL, that's a comment...

[jseefdrumr]

pfft hahaha, well I had to throw that out there, you never know. Like I said, I'm a noob when it comes to this part of things...I only learn what I HAVE to learn lol

[rhesusminus]

I don't know either, but I find it strange that you should specify "cur_tabname" and then specify that table name again in the query.

Could it be that the query part is everything from "WHERE ..." ?
That SELECT * FROM TABNAME is "given" ?

[Anonymous]

I agree, seems redundant, but I did try that... still nil.

(wd_mdb_ModifyFieldData bom_file_hdl cur_tabname (strcat "WHERE USER2 = '"
                                 (cdr (nth 1 item))"'"
                                 " AND CATALOG = '"
                                 (cdr (nth 4 item))"'"
                                 " AND MFG = '"
                                 (cdr (nth 5 item))"'"
                                 );end strcat
      (list
    (list "ITEM" (cdr (nth 0 item)));column1
    (list "USER2" (substr (cdr (nth 1 item)) 12 nil));column2
    (list "QTY" (cdr (nth 2 item)));column3
    (list "SUB" (cdr (nth 3 item)));column4
    (list "CATALOG" (cdr (nth 4 item)));column5
    (list "MFG" (cdr (nth 5 item)));column6
    (list "USER3" (cdr (nth 6 item)));column7
    (list "DESC" (cdr (nth 7 item)));column8
    );end list of  lists
      );end modify data----------------this is not working

At run time values are:

strcat statement:

"WHERE USER2 = 'OBSOLETE - 84324' AND CATALOG = 'S201U-K40' AND MFG = 'ABB'"

list of lists:

(("ITEM" " 38") ("USER2" "84324") ("QTY" "1") ("SUB" "1") ("CATALOG" "S201U-K40") ("MFG" "ABB") ("USER3" "6.4") ("DESC" "UL LISTED 40 AMP CIRCUIT BREAKER, SINGLE POLE"))

The data returned from get_Recs for this line item:

(("ITEM" . " 38") ("USER2" . "OBSOLETE - 84324") ("QTY" . "1") ("SUB" . "1") ("CATALOG" . "S201U-K40") ("MFG" . "ABB") ("USER3" . "6.4") ("DESC" . "UL LISTED 40 AMP CIRCUIT BREAKER, SINGLE POLE"))

[rhesusminus]

Let's check if @Anonymous might help us out here...

[PatMurnen_Adsk]

Hi, I'd be glad to take a look.  Playing a little catch up here - if I understand what you are trying to do is modify a piece of data in the spreadsheet, right? Would it be possible to upload the spreadsheet here? This would help me to figure out where it is failing.

Regards,

Pat Murnen

[Anonymous]

Pat,

Yes, just want to modify the data in the spreadsheet to delete the string "OBSOLETE - " from cells in the USER2 column.

The one attached would be typical for I am trying to do.

I suspect wd_mdb_DeleteRecs would require a similar type query as this API?

I couldn't get it to work either.

[PatMurnen_Adsk]

Yes, I think deleting a record would be the same. Do you have a .lsp file where all this is put together, i.e. opening the file, pulling out records, and then the call to try and modify them? Can you upload that as well? 

Regards,

Pat

[PatMurnen_Adsk]

I did experiment with this for a while and had no luck getting this to work on an Excel spreadsheet. It worked fine on an MDB Access file as long as the file had an Autonumber field. Then I was able to use that field for the query. I will log this issue to be looked at by the development team.

In the meantime, I had 2 ideas-

1) It seems like this Excel spreadsheet was output from a report. What if we took out the obsolete text with a user post in the report before it was saved to the Excel spreadsheet? This would be pretty easy I think.

2) Instead of modifying the spreadsheet I think a program could be written that would read the spreadsheet line by line and write a new .xls file taking out the obsolete text as it reads and writes.

Let me know if you want to try either of these options. I can probably write a first pass of the .lsp code and let you test it at your end. 

Regards,

Pat Murnen

[jtoverka]

To clarify, has anyone gotten the APIs to do anything with excel spreadsheets? Have you tried any of the other functions?

[PatMurnen_Adsk]

I am able to use the APIs to open, read fields, read data, and close when working with xls files. I can create and write records to a new excel spreadsheet. But so far I am unable to modify an existing one. That is what I will log to have the team look at. I am unable to look at the code so I am not 100% sure if I have tried everything. My suspicion is that because Excel doesn't have a key or autonumber field it is failing to find the correct record to modify.

Regards,

Pat Murnen

[jtoverka]

Tested OP's code using AutoCAD Electrical 2018.1.2. With some minor modification I have successfully ran the API. He was right, the query was incorrect. I will note one thing. The wd_mdb_ModifyFieldData API likes to return true even if it did not actually update the spreadsheet. The only way around this is to read the file's timestamp. If you obtain the timestamp using (vl-file-systime filename) prior to executing the code, and compare it to after, this will allow you to know if the modification was successful.

(defun initialTest ( / bom_file_hdl fnam item cur_tabname query)
	(if (setq bom_file_hdl (wd_mdb_GetHandle 2))
	  (progn
		(princ "\nAble to obtain handle\n")
		
		(setq 
			fnam "J:\\TEST_BOM_OBSOLETE.xls"
			item 
				'(
					("ITEM" . " 38")
					("USER2" . "OBSOLETE - 84324")
					("QTY" . "1")
					("SUB" . "1")
					("CATALOG" . "S201U-K40")
					("MFG" . "ABB")
					("USER3" . "6.4")
					("DESC" . "UL LISTED 40 AMP CIRCUIT BREAKER, SINGLE POLE")
				)
			cur_tabname "Sheet1$"
			query 
				(strcat 
					"USER2 = '" (cdr (nth 1 item))"'"
				)
		)
		(if (wd_mdb_Open bom_file_hdl fnam "Excel 5.0;HDR=YES")
		  (progn
			(princ "\nAble to open file\n")
			
			(if (= T
					(wd_mdb_ModifyFieldData
						bom_file_hdl 
						cur_tabname
						query
						(list
							(list "ITEM" 	(cdr (nth 0 item)))					;column1
							(list "USER2" 	(substr (cdr (nth 1 item)) 12 nil))	;column2
							(list "QTY" 	(cdr (nth 2 item)))					;column3
							(list "SUB" 	(cdr (nth 3 item)))					;column4
							(list "CATALOG" (cdr (nth 4 item)))					;column5
							(list "MFG" 	(cdr (nth 5 item)))					;column6
							(list "USER3" 	(cdr (nth 6 item)))					;column7
							(list "DESC" 	(cdr (nth 7 item)))					;column8
						);end list of  lists
					);end modify data
				)
			  (progn
				(princ "\nSpreadsheet Modified\n")
			  )
			  (progn
				(princ "\nSpreadsheet Not Modified\n")
			  )
			)
			
			(wd_mdb_Close bom_file_hdl)
		  )
		  (progn
			(princ "\nUnable to open file\n")
		  )
		)
	  )
	  (progn
		(princ "\nUnable to obtain handle\n")
	  )
	)
	(princ)
)

[Anonymous]

Pat, thanks for looking into this.

I have been having issues with the user post stuff, I had one written a while back but when we upgraded to 2016 (or was it 2018...) it broke. I just haven't had time to get back to it to figure out what is going on with it.

Idea 2 sounds doable.

It now appears this is a "feature" of this particular API so I can move  on to other ways of getting things done.

The LISP code I currently have can open, get any data I want, then close... just couldn't figure out this modify/delete bit. I don't think I need any help there just yet, thank you for the offer. I may call you on that again.

I am currently drawing wires now so I don't have much time for development. I'll try to sneak in a test of Idea 2 before too long and post results/failure.

Oh, could you post the query you used on the database that worked?

I am not sure where  to start... "select" "from" "where"... where?.... being the tabname is already defined... there was some questions there earlier...

[PatMurnen_Adsk]

Regarding older user posts - there was a change made in an earlier release (I believe it was 2016) that allowed user posts to be run automatically from a report format (.SET) file. This required a format change to the user post .lsp files. To fix an older user post you can copy and paste the code for the specific option into the .lsp file supplied for the release you are using. That way you get the newer format and your older option. 

Before trying other options, did you see the reply from jtoverka that said they got it to work with a spreadsheet? The query was pretty simple and involved only one field. I haven't tried it yet at my end.

Regards,

Pat

[PatMurnen_Adsk · Accepted solution]

I did find some time to try the suggested code and it worked for me as well. So the query part was simply -

(strcat "USER2 = '" (cdr (nth 1 item))"'")

Regards,

Pat

[Anonymous]

Oh, so no "where" clause.... that must be automatically implied by the API.

....if the API help would have just said that...

well at least I'll be able to move forward without all the overhead of recreating the spreadsheet.

Thanks All!

[rhesusminus]

---

@Anonymous wrote:

Oh, so no "where" clause.... that must be automatically implied by the API.

---

😄 If I have read this thread correctly:

https://forums.autodesk.com/t5/autocad-electrical-forum/wd-mdb-modifyfielddata-quot-query-to-specify...

[PatMurnen_Adsk]

That's correct, no where is needed. I will log an issue about the API Help. It would be good to get an example of the query in the topic.

Regards,

Pat Murnen