[Solved/Solution] Form Mode automatically changed from OK Mode to Update Mode

Form Mode automatically changed from OK Mode to Update Mode while click on Customized Tab in system form; 

Created new tabs along with the existing tabs in SAP Default Screen. This works fine in Add Mode. 

But in Ok Mode, while click on the newly created Tab, Form Mode got changed to Update Mode.

That can happen because, one of the controllers of your tab gets changed, there's value when it get focus. (May be a text box or any controller)

Advertisement

If that behaviour is compulsory, then consider to change that Item's (controller's) 'AffectsFormMode' property as below.

YourController.Item.AffectsFormMode = false;

-

Add QR Code in Crystal Report - Method 2 - SAP B1 Patch

There is an Option other than SAP Patch - Method 1(Click Here)

Or the Below SAP 10 PL02 Patch method

QR codes became popular also outside the automotive industry, and used in applications to include product tracking, item identification, time tracking, document management, and general marketing.

You just have to scan it. But, before you scan it, you have to generate it… for that, SAP Business One introduces new functionality allowing you to assign QR Codes to marketing documents. Enter all relevant values in the General Settings > QR Codes tab:

In the marketing documents, a new field is added in the Accounting tab, where you can either enter text or use formatted search for generating the required value:

How to get Net Balance of A/R Down Payment Invoice?

Down payment  available net value can be fetch from  DPI15 A/R DP - Drawn Dpm Applied Table; Check the column [DpApplNet] - Dpm Appl Net LC;

Once linked to invoice level, Document Line table 9(eg. INV9) have the details of Down payment linked Invoice. Filed

DrawnSum have the down payment value used in the Invoice. So if user minus the value from the 

DocTotal of Down paymnet and DrawSum, will get the net amount in the Down Payment.

SELECT 
  T0.DocNum,
  T0.DocDueDate,

Create all Stored Procedure While Installing Addon

We use so many stored procedure while developing Addon; And most of the time we create this manually in SQL while installing the Addon.

Below is a set of Command whcih can be used in a BAT file, so that the code will read the SQL query from the .sql file and execute the same in Server.

Steps to Do..
1. Create a BAT file and set the command as below with proper password and file patch

     "How to create a BAT file, Open a notepad copy the command and save the notepad with 
      extension .bat "

if not exist "C:\SAPLog" mkdir C:\SAPLog
sqlcmd -S localhost -U sa -P sql@123  -i "E:\sp_Drop.sql" -o "C:\SAPLog\Log.txt"

2. Execute the BAT file in the add-on after company connection.

Log File path in the command will give you the status of the SQL file execution. Or user can create a menu for the file to execute. And can be trigger when ever required.

Menu ID list of SAP B1

The following table lists the business objects that are exposed through the DI API.
Object Name Object ID Description

ID		Name
257		&About SAP Business One
272		&Help
275		&Context Help    F1
276		H&elp Settings
512		&File
514		&Close
518		&Page Setup
519		P&rint Preview
520		Pr&int    Ctrl+P
523		&Launch Application
524		Loc&k Screen
526		Exi&t Ctrl+Q
768		&Edit
769		&Undo Ctrl+Z
770		&Redo Ctrl+Shift+Z
771		&Cut Ctrl+X
772		C&opy Ctrl+C
773		&Paste Ctrl+V
774		&Delete Del
775		&Select All
1024		&Window
1025		&Cascade
1026		C&lose All
1029		M&essages/Alert Overview
1030		&Main Menu Ctrl+0
1280		&Data
1281		&Find Ctrl+F
1282		&Add Ctrl+A
1283		&Remove
1284		&Cancel
1285		R&estore
1286		Cl&ose
1287		&Duplicate Ctrl+D

Read Excel using SQL

Excel reading is possible only if system is installed with AccessDatabaseEngine(32 or 64bit), and below configuration is done in SQL;

Step 1 : Re-Configure the SQL settings for reading Excel.

 sp_configure 'show advanced options', 1;
 GO
 RECONFIGURE;
 GO
 sp_configure 'Ad Hoc Distributed Queries', 1;
 GO
 RECONFIGURE;
 GO

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO


Step 2 : Reading Excel

Excel must be inside any of the Drive folder with full permission of system having SQL Installed.

Execute the below query; Were excel is in D:\ExcelUpload folder

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\ExcelUpload\YourExccelName.xlsx', 'select * from [Sheet1$]')

Reading the SYSTEM Variable in User Data Source string

There are many ways to read the system variable in SAP SDK. But Simplest way is to read directly from data source. based on the Variable ID

 strTotal = PForm.DataSources.UserDataSources.Item("SYS_85").ValueEx;

Remote System Login through VB Code

You can do a remote login using VB or C# code. Only few things is required; Like IP, User Name and Password

Remote Login and Copy File

'''''''''''''''Login To Remote system Share Folder '''''''''''''''''''''''

   Open_Remote_Connection(ServerIP, ServerUser, ServerPass)

                    '''''''''''''''Copy File Share Folder '''''''''''''''''''''''

''' To Copy a file please create a shared folder in the destination system with full access.   

System.IO.File.Copy(strFullFile, ServerPath + strDocentry + "_" + strCardCode.Trim() + ".xls")

Private Sub Open_Remote_Connection(ByVal strComputer As String, ByVal strUserName As String, ByVal strPassword As String)

        Try

            Dim ProcessStartInfo As New System.Diagnostics.ProcessStartInfo

            ProcessStartInfo.FileName = "net"

            ProcessStartInfo.Arguments = "use \\" & strComputer & "\c$ /USER:" & strUserName & " " & strPassword

            ProcessStartInfo.WindowStyle = ProcessWindowStyle.Hidden

            System.Diagnostics.Process.Start(ProcessStartInfo)

            System.Threading.Thread.Sleep(2000)

        Catch ex As Exception

        End Try

    End Sub

Print Layout for the User Defined Form - SAP 8.82 or Above only

The below explained method can be used only in SAP B1 8.82 and above. There is a limitation in B1 8.81 version: Only UDO objects whose id is numeric are supported. If the UDO code is alphanumeric such as CR_8110, it will not work. In B1 8.81 we won’t able to create the UDO with a numeric value.

Please follow the steps in the attached document. If you cannot set the ID of report after loading, you have to dynamically query the ID from the table to access the report. Details are in given the document.

Advertisement

Attachment : Detail Document with Image

Step 1 : Create a crystal report for the Printlayout of the user defined form.The parameter of the crystal
report must be DocKey@ with type number. Ref the Image below
Step 2 : Upload this report to SAP B1.

Step 3 : Have to add the Event trigger in the InitialSettings.cs.

Advertisement

Add the below codes in the
InitialSettings()
<Code>
Global.SapApplication.LayoutKeyEvent += new
SAPbouiCOM._IApplicationEvents_LayoutKeyEventEventHandler(SapApplication_LayoutKeyEvent);
<Code>
Add the event function to trigger the event function of “VPurchaseRequest.cs”
<code>
private void SapApplication_LayoutKeyEvent(ref SAPbouiCOM.LayoutKeyInfo eventInfo, out bool
BubbleEvent)
{
Global.bubblevalue = true;
if (eventInfo.ReportCode == "RCRI" & eventInfo.ReportTemplate == "RCRI0012")
{
VPurchaseRequest.Instance.SapApplication_LayoutKeyEvent(ref eventInfo, out
BubbleEvent);
}
BubbleEvent = Global.bubblevalue;
} 

<code>
RCRI – Is the type code of Crsyatl report

Chart of Accounts Report

Dear Experts,
i need to create either a Query or a crystal report to Display chart of accounts same as it display on char of account screen ( Draws, Levels and accounts hierarchy wise).
regards

 Answer 

You have to update the query based on number of level you have in COA


select TBL1.[GROUP],(TBL1.[AcctCodeLevel-1] + ' - ' + TBL1.[Acct Name Level-1]) [Level-1],
(TBL1.[AcctCodeLevel-2] + ' - ' + TBL1.[Acct Name Level-2]) [Level-2],(TBL1.[AcctCodeLevel-3] + ' - ' + TBL1.[Acct Name Level-3]) [Level-3] ,
(TBL1.[AcctCodeLevel-4]  + ' - ' + TBL1.[Acct Name Level-4]) [Level-4],
(TBL1.[AcctCodeLevel-5] + ' - ' + TBL1.[Acct Name Level-5]) [Level-5]

from (
     SELECT t0.groupmask, t0.grpline, t0.levels, T0.[AcctCode]  [AcctCodeLevel-1],
T0.[AcctName] [Acct Name Level-1],'-' [AcctCodeLevel-2] ,'-' [Acct Name Level-2],'-' [AcctCodeLevel-3],'-' [Acct Name Level-3]
,
'-' [AcctCodeLevel-4],'-' [Acct Name Level-4],'-' [AcctCodeLevel-5],'-' [Acct Name Level-5],

(case T0.[GroupMask] when 1 then 'ASSET' WHEN 2 THEN 'LIABILITIES' WHEN 3 THEN 'EQUITY'
WHEN 4 THEN 'REVENUE' WHEN 5 THEN 'EXPENSES' END) [GROUP]
  FROM OACT T0 WHERE t0.Levels = '1'
  union al

Logged on User in SAP B1

In addon using C#.net SDK coding method you can use the below code to access the current user logged in SAP.  

Global.SapCompany.UserSignature.ToString() 

In formatted search use the below format.

SELECT $[USER] 
  
In transaction notification use 

SET @USERID = (SELECT ORDR.UserSign FROM ORDR WHERE ORDR.DocEntry = @list_of_cols_val_tab_del )
  
If you are trying to find who is updating the document in transaction notification use the  'usersign2' filed in the table.

Advertisement

SAP Business One Menu ID List

Objects ID List
 
The following table lists the business objects that are exposed through the DI API.
Object Name Object ID Description

ACT 1 Chart of Accounts
CRD 2 Business Partner Cards
ITM 4 Items
PLN 6 Price list names
SPP 7 Special prices
CPR 11 Contact employees
USR 12 Users
INV 13 Invoices
RIN 14 Credit notes
DLN 15 Delivery notes
RDN 16 Revert delivery notes
RDR 17 Orders
PCH 18 Purchases
RPC 19 Revert purchases
PDN 20 Purchase delivery notes
RPD 21 Revert purchase delivery notes
POR 22 Purchase orders
QUT 23 Quotations
RCT 24 Receipts incoming payments
DPS 25 Bill of Exchange Deposits
BTD 28 Journal vouchers
JDT 30 Journal entries
ITW 31 Item warehouse
CLG 33 Contact activities
CRN 37 Currency codes
CTG 40 Payment terms types
BNK 42 Bank pages
VPM 46 Payments to vendors
ITB 52 Item groups
CHO 57 Checks for payment
IGN 59 Inventory general entry
IGE 60 Inventory general exit
WHS 64 Warehouses codes and names
ITT 66 Product trees

Data Type ID in SAP B1

Data Types used in SAP B1 

dt_LONG_NUMBER = 0

dt_SHORT_NUMBER = ,

dt_QUANTITY = 2

dt_PRICE = 3

dt_RATE = 4

dt_MEASURE = 5

dt_SUM = 6

dt_PERCENT = 7

dt_LONG_TEXT = 8

dt_SHORT_TEXT = 9

dt_DATE = 10

SAP B1 Control Type Property

The application supports the following item types:

Member	Description	Value
it_BUTTON	Button item. Not relevant in Column.Type.	4
it_STATIC	Static text. Not relevant in Column.Type.	8
it_EDIT	Text Box.	16
it_FOLDER	Tab. Not relevant in Column.Type.	99
it_RECTANGLE	Frame rectangle. Not relevant in Column.Type.	100
it_COMBO_BOX	Combo Box (List Box, Popup, etc.)	113
it_LINKED_BUTTON	Link arrow to other items.	116
it_PICTURE	Image.	117
it_EXTEDIT	Text Box with multiple lines. Not relevant in Column.Type.	118
it_CHECK_BOX	Check Box. Not relevant in Column.Type.	121
it_OPTION_BUTTON	Option Button. Not relevant in Column.Type.	122
it_MATRIX	Matrix (table). Not relevant in Column.Type.	127
it_PANE_COMBO_BOX	Pane Combo Box. Not relevant in Column.Type.	104
it_ACTIVE_X		102