SAP B1 becoming more and more popular among small and even mid-size businesses, especially in light manufacturing, assembly, services, warehouse management, transportation, logistics. However all mentioned business niches often require ongoing data integration.
Plus, being realistic, you should not expect high-end IT department within such organizations – to support luxury SB1 integration add-ons, such as Ibolt. In this small publication we are giving you highlights on how to build pretty smart and robust integration utility with SAP Business One Data Transfer Workbench, and ideally this page should help you with your SAP Business One consultant, VAR, technology partner:
1. Workbench can use Excel templates, however it is not Excel (CSV files) is not the only data source platform. You should use Excel as the samples in building your sophisticated ODBC query
2. ODBC queries and DTW. You should understand several principles here. First of all, for the queries, where you expect ongoing integration you should try to place the source into SQL DB platform, easiest would be Microsoft SQL Server. Second, DTW automatically recognizes the source if you provide exactly the same field names, as you see in Excel templates. Third, if you are importing something which has one-to-many relation (such as Inventory Items and Item Prices) – these are linked via RecordKey field – the easiest way to create one is to add identity insert column to your source table
3. SQL Views and their flexibility. SQL view will allow you to provide required field names for automatic linking as we already mentioned in previous paragraph. Plus View allows you to pull data back from your SB1 company database. Imagine example when you are creating new items as the replacement to the existing items in SB1 and the price should be set as it is at the existing retiring item. In this case you pull the price from ITM1 table
Andrew Karasev, Alba Spectrum LLC, help@albaspectrum.com, http://www.albaspectrum.com, 1-866-528-0577 , subdivision of M2-D2, SAP Business One VAR and Reseller in Illinois, Georgia, South Carolina, California, Texas. Please visit our info portal Pegas Planet: http://www.pegasplanet.com. Local Service in Chicago, Atlanta, San Diego, Los Angeles, Orange County, Houston. We also have SAP Business One Software Development Factory and SB1 Technical Support Call Center, serving USA country side businesses and internationally: Canada, Brazil. Illinois practice: Naperville, Aurora, Plainfield, Marseilles, Ottawa, DeKalb, Springfield, Oakbrook, Glenn Ellyn, Wheaton, Bolingbrook, Romeoville, Lisle, Melrose Park, Brookfield. San Diego
