SAP Business One consultant specializing in Crystal Reports, SQL, B1UP and business intelligence in general. I work for Forgestik Inc. I love quotes, technology, sushi, vlogs, coffee, martial arts and video games.
For everything about me see my landing page: battleshipcobra.com
Hey, so I currently work with SAP B1 and when I've accidentaly ran 700 Direct Debits for the wrong date before my boss did something with SQL to change the dates of all the transactions without having to manually reverse and manually repost these 1 by 1... If you could show us how to change posting dates with SQL on SAP B1 that would be great!
Hello! By direct debits do you mean incoming payments with bank transfers as the payment means? How did you "run" them? Is it an integration or something? It's not immediately obvious to me exactly what you are asking so I can't give you a good answer. In general terms tho, updating system fields using SQL will cause damage to your database. If not apparent right away it could cause irreparable damage somewhere down the line. Contact me on LinkedIn for further questions. It's easier for me to reply. LinkedIn.battleshipcobra.com
Hello Michael, Tks for excellent video! I tried to execute on Crystal rep, but the my Crystal don't have the option 'store procedure'. This is the my Crystal version or SAP B1 version? (SAP 9.3 yet)
Thanks for watching! Try to rebuild your connection and make sure you're using a database user that has access to the objects. Might need some IT help. Good luck!
Hello! You add a function button to the document (right-click >> Add Function Buttons to This Window), add a new row, call it "Fit Column Width". Click in the Universal Function column and then push TAB. Make a new Universal Function and give it a name. Set it to "Macro" type and then all you need is: Activate(1300); Then add the UF, add the Function Buttons, close your form, re-open it and you have the button there. If you have no clue what I mean and / or you don't have B1UP then you might need more direction. You can check the Boyum training videos on Function Buttons and Macros for more help, reply to this message or contact me on LinkedIn: linkedin.battleshipcobra.com/
Hello Michael, Excellent video! I saw that with the import it was possible to create the menu, but is there any way through the SAP DI API to call a report?
Not sure exactly what you mean but Crystal Reports can be imported and run from the menu. Queries Manager in V10 has a new button called "Assign to Menu" to place queries in the menu as well. Let me know if this is what you mean. Thanks for watching!
This was excellent. Thank you. I've been searching for a CR course to enroll in. After watching 1 minute of your video, I went to Udemy and purchased the course. It was on sale! That's cool. Thank you.
What if you want to change your GL determination from warehouse to Item Group? Do you have to do a goods issue first then change the inventory item and then a goods receipt? What happens if you just change the type on the inventory tab?
If you need to change the inventory determination for any reason you have to issue stock out, make the changes and receipt the goods back in. Unless you don't care about your Inventory Audit Report matching your Balance Sheet. You could technically just use a JE to migrate the values. But you will not be able to show proper quantity x cost tied to your GL (which most people do).
Hi , how create a combo box in cascade in SQL Variables es the [%2] that depend from value selct inn [%1] ... where "Article" = [%1] doesn't work... thanks in advance
What I normally do is a macro UDF first with variables. Then I use an ExecuteSQL commend to write to a hidden UDT / UDF. You could also try accessing it with @STORE variables. IE do the variables in the first macro and then at the end of the first macro you use UF() to run your actual SQL Report UF. Then you draw form @STORE locations or the UDFs I mentioned. You can just direct UPDATE to the hidden UDFs. It's just a quick solid way to store information between screens. Especially if some of the elements don't access the '[%0]' format stuff.
Hi Mike! Thank you for your videos - you are doing a great job with them! Do you happen to do individual consultations? Or do you have a platform where people can ask you specific questions, unrelated to your videos, but related to the SAP?
Thanks for the compliment and for watching. HANA and SQL are 99% the same. IFNULL vs ISNULL is a fundamental function that is different but the structure and logic is all the same. I might make a video on the few differences but if you really get one then you'll get the other.
@@BattleshipCobraSAP a UDF in the contacts of business partner. I tried a lot of special characters but nothing work. If I put a space in the Excel file, it make an empty string but I would prefer a NULL value
Thank you, the code work very well with me, but what can i modify to add another colunm in excel for Qauntity comanded because the quantity in code imported it to quantity arbitred directly if anyone can help me in that thank you
You can add any field. You have to show the developer panel and dig into the macro. You need to match the column name in the data range with the field in SAP. But I've used this template for a ton of different documents.
I could yes, I'll add the video idea to my list. But if you contact me on LinkedIn I can give you a basic template if you're stuck rn. linkedin.battleshipcobra.com/
if we don't have EFT file, can we use payment wizard to generate voucher for outgoing payment for multiple invoices/vendor at one time? I mean the bank already proceeded the payment via paper documents, now accountant needs to post payment journal entries in SAP, but we cannot find the way to do it quicker instead of posting each transaction pmt for each vendor.
Hi Mike, Thanks for the video is there anyway you can do email reports or alerts on Purchase orders from individual staff to managers to get approvals through print and delivery. The alerts system the managers dont always login and see the alerts. We havent yet done but want to set up purchasing limit amounts and criteria but need quick turn around for approvals. Where do we get the script from to do this? we run sap b1
Hi Tracy, this is theoretically possible yes. You would need to write a query to detect pending authorized documents then use this system to distribute the emails. You need B1UP first and their Print & Delivery plugin. Scripts to do what I show in the video are pinned in a comment of this video. Thanks for watching!
Yeah absolutely. Anything exposed through the SDK can be done this way (which is most things). Check the SDK guide. Forum will also have some VB examples. Then you just integrate it into the spreadsheet from my example.
Just wondering if there is any solution for updating Sale Orders UDFs or Purchase Orders, we use this in our company but they are trying to get rid of DTW and have us using the import yet there is no function for updating AR invoice-sale Order or PO, is ther anything available.
Hello! Yes absolutely you can update UDFs on SO / PO documents (document and row), in your DTW template just use the database column 'U_YourField' to refer to it. The tricky part is understanding how to build your template for existing rows, you need the right row reference (LineNum and it's zero base). If the document / rows are in closed status you can still update them, for rows you need to go to Administration >> System Initialization >> Document Settings >> Per Document >> Purchase Order dropdown >> Check the box for "Allow Update of User-Defined Fields When Document Rows Are Not Editable" (implemented at some point in V10, repeat this for sales orders and invoices, etc.). This setting will allow you to update UDFs even if the row is locked and this needs to be checked for the DTW to update row level fields. The main message here is that if the document can't be updated via the UI like a system field (item code, pricing, etc.) on an invoice it can't be updated by any tool. So if you are looking for a tool to change invoices that are added then you're out of luck, you have to cancel the document and remake to change un-editable system fields. But UDFs are always update-able even on invoices if you check that box I'm referring to and have the right LineNum setup. The final tip I have here is you can simply use SQL UPDATE queries for most UDFs, as long as you created them and they're not linked then write to them directly so you could use SQL BCP, Excel VB (to trigger SQL code) or anything like that to update them as long as you have the right line references. Just make sure you don't update any system fields via direct via SQL or you will break the system. Contact me on LinkedIn if you have more questions.
Probably yes, but you would need to use a function of Crystal Reports. This video is about a built-in function of SAP Business One which is only at the document header line. I make many traditional barcodes and have ways to generate them but I don't use QR codes at all so I don't know of a solution offhand. You could just Google some function with CR that does it.
For updating you usually only need the primary key (BP code, item code for example) and then as long as you have the right column name you can update whatever you want. As few as two columns can work for updates. Thanks for watching!
Hello! I would bet it's possible although I have not done it. You would need to use the RDR10 (or whatever doc you're working with add "10") table for text rows. I tried it with the grid copy and it doesn't seem to work. I can get it to flip to a text row but it gets jammed up on the text entry. If you want to copy all of the rows I bet you could use Dynamic Syntax UI API commands to do a loop the SQL Report grid into a RecordSet, then open a new blank document and UI API the loop back into the new document. I don't know how practical this is and it's pretty experimental. Probably the best way is to simply use right-click "Duplicate" from an original document or you could use a "Free Text" type field in the row and then visualize it using Crystal Reports below the item instead of using the text rows. This way you can copy them easily with the grid data targets. It's an interesting issue, I don't have time to nail it right now since it's not a requirement. I have code to write the SQL Report grid into a temp table using C#, then I just need to do the other side with writing it out of the temp table into the matrix. If you want the code, hit me up on LinkedIn if you are OK with C#.
Hello Michael, thank you for this valuable information, I was watching this video and also another one about the stored procedures that you have published, I want to make a crystal format for invoices but in the stored procedure I cannot write dockey@ only @dockey but sap does not help me. recognizes the @dockey parameter for crystal generation, can you please tell me what I can do?
A good way to check is to edit one of the system Crystal Report layouts from SAP (Sales Order or AR Invoice, etc.). Then copy the exact parameters (make sure you copy the right case).
"enter your site user password and license server details" Site User ID: B1 Site User Password: License Server: Port: 3000 Note: Need your help to resolve this issue
Hi! I have no clue what you mean. You would want to reach out to your SAP Partner for help with infrastructure setup stuff. It's impossible for me to help via RU-vid comment.
"enter your site user password and license server details" Site User ID: B1 Site User Password: License Server: Port: 3000 Note: Need your help to resolve this issue
Whoever setup your SAP environment would have the password. B1SiteUser is the standard username, when you install it you setup a password. So you need that to get into your SLD or whatever you're trying to access. If you have no idea you need to get your SAP Partner to reinstall your server tools if you can't recover it.
Thanks! Ohhh, it took me a minute but I found the song. It's from the official RU-vid Audio Library and it's called "Fight or Flight" by Ethan Meixsell. Here is a link, boom: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-K8TZYSF1cgw.html
Hi! Can You tell me where "Mass Delivery" NOW (B1 hana 10.0 updated) at the Menu...? or how can I send query-results for Clients with time schedule?.. Thnx a lot! *(working with Yours SQL & Crystal totorials - thanks!)
Yes, if you have Print & Delivery just put the file somewhere in the network then use a custom Mass Delivery Schedule to pick it up as a custom attachment. You could even name the file something consistent (like including the date) and then when the P&D script runs it will look for that exact file name to attach. But it's 100% possible. Look for my video on email alerts. You can use that framework to make this email.
Hello Mike, a very Happy New Year-2024 to you. I benefitted from your tutorial couple of years back. There is a scenario I came across and couldn't find any thing online for it. After the payment wizard is complete and you generate the bank file, the addon caches the file path and generates the file with a fixed name. This name is the one mentioned in the BPP project under the target file properties--> Output File Name. Every time a user generates the bank file, the output file is with the same name. If there are multiple users generating the bank file in a shared folder, they will overwrite the file. Is there a way we can make the file name dynamic? Will appreciate your advise on this. Thank you
I don't think out of the box. I would imagine PowerShell could detect the file, move it and rename it. Or just get the user to do it as they process the payments. Not sure why SAP doesn't do a better job at naming the files.
Hello, I don't know what you mean you'll have to give me a timestamp to look at. I skimmed through the video and don't remember it being at all about pricing. Sorry I can't help!
Hi, great video!. Thing is this file has changed in recent SAP v10 FP2305, now the username is encrypted/encoded, do you know how to decrypt the username? ie. <UserName>Bpcru3eW6FAPFIY4nyy25um1CZQRMkHENMMvI9EcS3E=</UserName>
Hello Michael, great video. I have an SP that i create a temp table in, i then do a select into that temp table. I follow that up with two updates to the temp table to fill other columns. Final step in SP is Select column1, column2, column3, column4 from #temptable. When i pull the SP into a blank crystal report it shows the SP in the Database fields in field explorer but the + sign beside the SP does not pop it open, any ideas on what I might be doing wrong?
