Data Validation with Stored Procedures - SAP Business One: Tips & Tricks

[@MatthewBrownUK]

Welcome back MT, always an under used area in my option., probably due to the fact you can really mess things up if not correct, I don't remember you saying it, but always test these things off the production environment.

[@ashleymunsamy4003]

Totally agree...Boyum IT is amazing...B1UP is a life saver.

[@taytking]

This was very helpful. Question Crystal report for UDO document.

[@BattleshipCobraSAP]

Hi Tatenda, I think this is possible with a DocKey@ token but I'll add it as a topic. Thanks for the idea!

[@zeshanali1416]

Hello mike thanks alot for the video, can you make the part 2 of this on how to put controls in stored procedures & it should give error if not compliance e.g, GRPO can not be post without PO, or AP INVOICE should be based on Delivery, in short joining the 2 or more docuements

[@BattleshipCobraSAP]

Hello! This can be done, you just need to use EXISTS() for the SQL function (usually a count function for HANA) and just read the base reference columns on the rows of the GRPO. But I can add this to the list of topics, it's pretty specific although I can see it being valuable with some reference code. Thanks for watching!

[@akashaku5167]

Please Add videos of, How to create HANA Modeling using Cubes

[@michaelagyemanomari7704]

need a data entry device for our production floor that is compatible with sap B1

[@JanPeake]

Hi, trawling through your old content whilst hoping for some new content. Can you please confirm whether updating of properties is also not allowed via sp? Thanks.

[@BattleshipCobraSAP]

Hey Jan! I have not been making anything new unfortunately but I have a ton of ideas. Updating properties in items is not officially supported and I personally have not done it. BUT, I don't think it would be a problem since it's a dead-end field. (I do not officially recommend this though) A better way to do it is through a Dynamic Syntax Universal Function in B1UP. You can do a DI API call either on a schedule or via a button or validation which will properly use the SDK mechanism to change properties. I plan to do a video on C# basics with free examples this year. Just have been busy, thanks for watching! Sign up for my newsletter: newsletter.battleshipcobra.com/ also for official news, I'm going to be doing official announcements there moving forward.

[@petertipul]

Hi everyone, how to pass to stored procedure string parameters like currency from quotation form? Right now is not allowing me to pass for example USD currency if do not have quotes like 'USD'. This line do not working for me: @STORE2 = SQL(Select DocCur from OQUT Where DocNum=$[$8.0.NUMBER]); Transfer(SQL:EXEC [dbo].[JJ_GetBOMPriceForQuantity] $[$38.1.0], $[$38.11.NUMBER], @STORE2|@STORE5); but if I write straight currency its all ok: Transfer(SQL:EXEC [dbo].[JJ_GetBOMPriceForQuantity] $[$38.1.0], $[$38.11.NUMBER], 'USD'|@STORE5); Do you know how to pass this currency to stored procedures? Thx

[@BattleshipCobraSAP]

Hi Peter, have you tried the .CURRENCY selection in the dynamic syntax? IE, instead of .NUMBER? Also, if I'm working through something like this I always break it down line by line to make sure I'm getting the right info and then I sometimes use the "Debug" option at the bottom of the UF. This way I know at each step if I'm getting the right data. I will frequently use a very simple UF Macro with just the @STORE SQL selection and then a MessageBox(@STORE1); to output what I'm getting, then I'll add another row of code until I'm done. This way is a bit slower but avoids issues like you're having, sometimes on the rows you'll have to mess around before you can move on. Even with the currencies you can use direct SQL REPLACE() functions in the @STORE formulas if you need to remove currency. But try the .CURRENCY it might work for you. I also don't understand your Transfer macro there, is @STORE5 a field? You might want to read the documentation on the functions to make sure you're using the right commands for what you want to achieve. Good luck!

[@ssvalliani]

Down payment not access from Purchase Order how to cater in SP

[@danielmartinez7917]

The Transact Notification ona SAP B1 Hana is more Funy :D

[@BattleshipCobraSAP]

Everything in HANA is a little funny haha, thanks for watching!

[@amoscookie1336]

and this is the secret of writing a SP for the data validation without ppl interrupting u every 5 mins.

[@BattleshipCobraSAP]

Yes, use the PTN to update after (for UDFs) rather than blocking users if it's not a matter of needing user input. You could also use simple C# calls with the Dynamic Syntax Universal Function to fix up system fields. I'm planning on a video for that this year. Thanks for watching!

[@juanmanuelgordillogonzalez7186]

:D