Start a project

Formatted search (FMS) builder for SAP Business One

Pick a document and what the field should show. Get a ready formatted search query with the right $[…] references, for SQL Server and HANA.

 
 
References used
     
    How to attach it
       
       
      Need an FMS that writes back, validates or blocks a document? We build transaction notifications and add-ons.Talk to us →
       
         
       
        
       
       
      How it works
      Three steps, no signup
       
       
      1
      Pick the document and the job
      Sales order, A/R invoice, purchase order… then what the field should show: an item value, a BP value, stock, last price.
      2
      Copy the query
      You get the SQL Server and HANA versions with the right $[…] references to the current form.
      3
      Attach it to the field
      Save it in Query Manager, click the field, press Alt+Shift+F2 and link the saved query.
       
       
       
       
       
       
      FAQ
      Questions about this tool
       
       
      What does $[$38.1.0] mean in SAP Business One?
      It is a formatted search reference to a value on the open form: item 38 is the document lines grid, column 1 is the Item No. column, and 0 means "use the value as text". Use NUMBER or DATE instead of 0 when the value is used in a calculation or a date comparison.
      What is the difference between $[$4.0.0] and $[ORDR.CardCode]?
      Both read the customer code from the open sales order. $[$4.0.0] uses the screen item ID (4), $[ORDR.CardCode] uses the database field name. The field name version is easier to read and also works for user-defined fields like $[ORDR.U_Region].
      How do I find the item and column ID of a field?
      In SAP Business One turn on View → System Information, then hover over any field. The status bar at the bottom shows the item ID, the column ID and the database field.
      Do formatted searches work on SAP B1 for HANA?
      Yes. The $[…] references stay the same; only the SQL around them must use HANA syntax, with column names in double quotes. The builder gives you both versions.
      Why does my FMS not refresh when I change the item?
      When linking the query, tick "Auto Refresh When Field Changes" and choose the field it depends on (for example Item No.), and tick "Display Saved Values" only if you want stored values instead of a fresh lookup.
       
       
       
       
       
       
      More free tools
      Built for SAP Business One teams
       
       SQL to HANA query converterPaste a SQL Server query and get the SAP HANA version, with every change explained.SAP B1 query libraryReady-made reports for sales, finance, stock and approvals, in SQL and HANA.SAP B1 table finderSearch any table: what it stores, its line tables and its key fields.ZATCA QR code decoderRead a Saudi e-invoice QR (TLV Base64) or generate a Phase 1 QR from invoice values. 
       
       
       
       
       
      Need this inside SAP Business One?
       
      We build add-ons, reports and integrations for SAP B1 on SQL Server and HANA. Tell us what you need and get a fixed price, usually within 24 hours.
       
       Start a project  Chat on WhatsApp