Home

sODBC plug-in Manual - Somi-t

1. sPhonel sPhone2 sFax sEmail sWebSite sTaxCode nLangPref dtSince sMemo 550 Vendor Update Ild sName sCntcName sStreetl sStreet2 sCity sProvState sCountry sPostalZip sPhonel sPhone2 sFax sEmail sWebSite sMemo s50 Utils CheckSglSyntax Sql s50 Utils Date fmDate s50 Utils DateFirstOfLastMonth fmDate s50 Utils ESQ Text 24 s50 Utils FormatDecimal Number Somi t inc sODBC e S50 Utils getNextld Tableld e S50 Utils getNextl nvoiceNumber e S50 Utils getNextReceiptNumber e S50 Utils getl nvoiceBalance slnvoiceNumber ICustomrld e S50 Utils getPurchaseBalance slnvoiceNumber IVendorld e S50 Utils getTaxCodeld sTaxCode e S50 Utils getTaxInfo sTaxCode ITaxAccountNumber e S50 Utils setNextld Tableld e S50 Utils setNextl nvoiceNumber Number e S50 Utils setNextReceiptNumber Number s50 sASUserld e Description Function to change the name of the user you want to use s50 sASOrgld e Description Function to change the name of the organization that you want to use s50 Customer Exist sName e Description Returns the unique number of the customer in Sage 50 if there is otherwise returns e sName Parameter Customer company name in Sage50 s50 Customer New sName sCntcName sStreet1 sStreet2 sCity sProvState sCountry sPostalZip sPhone1 sPhone2 sFax dCrLimit sEmail sWebSite nLangP
2. 5 If an account name and password are asked to open the file enter them in the dialog box and click OK 6 In the Import dialog box custom functions select functions to import then click OK 7 The Import summary dialog box appears 8 Click Open Log File button to view the log file or click OK to close the Import summary dialog box 9 Close the dialog box Manage Custom Functions Sample files The FileMaker database containing custom functions are provided only in sets of custom functions sold separately from sODBC plug in for FileMaker For example with all the custom features for Acomba and Sage 50 Somi t inc sODBC EXTERNAL FUNCTIONS List of external functions for sODBC plug in SODBC Open ConnectionString SODBC Close sODBC Query Query sODBC NonQuery Query SODBC QueryA Query Get FileName ScriptName SODBC NonQueryA Query Get FileName ScriptName SODBC FieldSeparator SODBC RecordSeparator SODBC GetSchema SchemaName AttributeFilter SODBC Register Name Key SODBC Version Format sODBC Open ConnectionString Description Opens the connection to the ODBC data source ConnectionString Parameter Connection parameter to the ODBC data source o Example SODBC Open DSN MonAcomba UI D Supervisor PWD DEMO SODBC Open DSN MonFp7 Ul D test PWD test SODBC Open DSN Northwind SODBC Open D
3. Exist sName Description Returns the unique number of the supplier in Sage 50 if there is otherwise returns empty string sName Parameter Supplier company name in Sage 50 s50 Vendor New sName sCntcName sStreet1 sStreet2 sCity sProvState sCountry sPostalZip sPhone1 sPhone2 sFax sEmail sWebSite nLangPref dtSince sMemo Description Adding a supplier Returns the unique number of the supplier in Sage 50 sName Parameter Supplier company name sCntcName Parameter Name of main contact at supplier company sStreet1 Parameter Company address line 1 sStreet2 Parameter Company address line 2 sCity Parameter City name sProvState Parameter Name of province sCountry Parameter Name of country sPostalZip Parameter Postal code sPhonel Parameter Phone number 1 sPhone2 Parameter Phone number 2 sFax Parameter Fax number sEmail Parameter Email address sWebSite Parameter Website address nLangPref Parameter Preferred language 0 English 1 Spanish 2 French dtSince Parameter Date supplier was added to Sage 50 sMemo Parameter Notes s50 Vendor Update 1Id sName sCntcName sStreet1 sStreet2 sCity sProvState sCountry sPostalZip sPhone1 sPhone2 sFax sEmail sWebSite sMemo Description Update general supplier information lid Parameter Unique number of the supplier in Sage 50 sName Parameter Suppl
4. Re ee Ge Re ee Ge Re ee Re ee Ge Re ee 32 s50 Utils getlnvoiceBalance slnvoiceNumber ICustomrild le 32 s50 Utils getPurchaseBalance slInvoiceNumber IVendorld le 33 50 Utils get TaxCodeld STaxGOde cases EE EES tr GESE e tr o GE Re BEGEER ERGER EE RE eb Ee deer 33 s50 Utils getTaxInfo sTaxCode ITaxAccountNumber sse 33 50 Utils setNextld Tableld Juan 33 Somi t inc SODBC s50 Utils setNextInvoiceNumber Number 33 s50 Utils setNextReceiptNumber Number 33 REFERENT 34 Technical MOLES azo cs exces sas RE RE OR RE OE EE EN 34 THROU DIES OO EE AE AO EE AE OE EE EE Ce 35 About File Maker ING eege geed Er 35 Somi t inc SODBC 7 PREAMBLE This manual assumes you have basic knowledge about the FileMaker Pro database development platform the use of plug in with external functions and custom functions This manual as well as files that are included are provided under license of agreement and may not be copied photocopied reproduced translated or converted to electronic format in whole or in part without written permission in advance from Somi t Inc The FileMaker term used in the descriptions in this manual generally refers to software versions FileMaker Pro and FileMaker Pro Advanced Somi t Inc is a proud FileMaker Business Alliance FBA member since 2006 Does Somi t Inc assumes no liability for errors or inaccuracies in this manual Do not hesitate to contact us at plugin
5. TransactionType TaNum e Aco Utils DecimalSeparator Number e Aco Utils ESQ Text e Aco Utils SOCountryCodeEn Country e Aco Utils 1SOCountryCodeFr Country e Aco Utils Phone Text Aco AccountStatement ASAmountDue CuNumber InInvoiceNumber e Description Returns the balance of an invoice e CuNumber Parameter Customer number e InInvoiceNumber Parameter Customer invoice number Aco Charter ChCategory ChNumber e Description Returns the category of the account number e ChNumber Parameter Account number Aco Charter ChDescription ChNumber e Description Returns the description of the account number e ChNumber Parameter Account number Aco ControlCustomer Interest e Description Returns the default interest rate Aco Customer Exist CuNumber e Description Returns the customer internal number of Acomba RecCardPos if the customer exist 20 e CuNumber Parameter Customer number Somi t inc SODBC 17 Aco Customer NewUpdate CuNumber CuSortKey CuName CuCareOf CuAddress CuCity CuPostalCode CulSOCountryCode CuPhoneNumber1 CuPhoneExtension1 CuPhoneNumber2 CuEMail CuWebAddress CuReceivable CuTaxGroupNumber CuTermNumber CuInterest CuMarginLimit CuComment e Description Add or update information from a customer e CuNumber Parameter Customer number e CuSortKey Parameter Customer sort key e CuName Parameter Customer nam
6. s50 invoice Rollback s50 Invoice Freight sInvoiceNumber dFreightAmount sFreightTaxCode dFreightTaxA IFreightAccountTaxA dFreightTaxB IFreightAccountTaxB Description Use to include the costs of transport in a typical customer invoice sl nvoiceNumber Parameter Customer invoice number dFreightAmount Parameter Costs of transportation sFreightTaxCode Parameter Tax code to use dFreightTaxA Parameter A tax amount IFreightAccountTaxA Parameter A tax account number to use dFreightTaxB Parameter B tax amount I FreightAccountTaxB Parameter A tax account number to use s50 Invoice Commit Description Recorded permanently Sage 50 database the records generated by the functions s50 Invoice New and s50 InvoiceDetail New s50 Invoice Rollback 28 e Description Cancel transaction to the Sage 50 database 50 generated by functions s50 Invoice New and s50 InvoiceDetail New ES EN Somi t inc SODBC s50 InvoicePayment New sInvoiceNumber ICustomrId dtDate dAmount lType IBankAccount sReference SComment e Description Adds a payment to a transaction customer invoice e sinvoiceNumber Parameter Invoice number used by Sage 50 e ICustomrld Parameter Unique number of the customer in Sage 50 e dtDate Parameter Payment date e dAmount Parameter Payment amount e Type Parameter Payment type 0 Cash 1 Cheque gt 1 Credit card e IBankAccount Parame
7. 34 awards in the world during those years Millions of users from individuals to large companies rely on FileMaker to manage analyze and share information FileMaker Inc is a subsidiary of Apple Inc 35 Somi t inc SODBC
8. E ID 101 sODBC_NonQuery INSERT INTO Contacts FirstName LastName VALUES Linda L Amour e Result Returns the number of records modified by the query sODBC QueryA Query Get FileName ScriptName WINDOWS ONLY e Description Same as SODBC Query but the query runs in its own execution thread freeing the user interface 11 e Parameter Not used stay empty with EX Somi t inc sODBC e Query Parameter SQL query to execute e Get FileName Parameter File name where the script defined by ScriptName e ScriptName Parameter FileMaker script name to run after executing the SQL query e Result None e Note To access the query result inside the script ScriptName use the Get ScriptParameter function from FileMaker Pro sODBC NonQueryA Query Get FileName ScriptName WINDOWS ONLY e Description Same as SODBC NonQuery but the query runs in its own execution thread freeing the user interface e Parameter Not used stay empty with e Query Parameter SQL query to execute e Get FileName Parameter File name where the script is defined by ScriptName e ScriptName Parameter FileMaker script name to run after executing the SQL query e Result None e Note To access the query result inside the script ScriptName use the Get ScriptParameter function of FileMaker sODBC FieldSeparator e Description Returns the field delimiter used and or ass
9. EMail CuWebAddress CuReceivable CuTaxGroupNumber CuTermNumber Culnterest CuMarginLimit CuComment Aco CustomerPaymentLine RecCardPos CPI nvoiceARCP Aco InvoiceAP Exist InSupplierNumber InInvoiceNumber Aco InvoiceAR Exist InCustomerSupplierNumber InInvoiceNumber Aco Supplier Exist SuNumber Aco Supplier NewUpdate SuNumber SuSortKey SuName SuCareOf SuAddress SuCity SuPostalCode SulSOCountryCode SuPhoneNumber1 SuPhoneExtension1 SuPhoneNumber2 SuEMail SuWebAddress SuPurchaseAccountNumber SuTaxGroupNumber SuPaymentTermNumber SuComments Aco SupplierTrans Balance SuNumber InInvoiceNumber Aco TransAP Begin Aco TransAP Cancel Aco TransAP End Aco TransAPDetail Update PACharterNumber PAAmount PaDescription PAlsTax TaNum Aco TransAPHeader New InInvoiceNumber InSupplierNumber InType InDate InAmount InSubTotal InReference InDescription TANumPosts Aco TransAR Begin Aco TransAR Cancel Aco TransAR End Aco TransARDetail Update PACharterNumber PAAmount PaDescription PAlsTax TaNum Aco TransARHeader New Inl nvoiceNumber InCustomerSupplierNumber InTransactionType InDate InAllPaid InTaxGroupNumber InReference InDescription TANumPosts Aco Utils CityProvince City Province 16 Aco Utils DebitCreditAR Amount TransactionType TaNum Somi t inc sODBC e Aco Utils DebitCreditAP Amount
10. I Windows ASCII Mac ISO 8859 1 ISO 8859 2 ISO 8859 3 ISO 8859 4 ISO 8859 5 ISO 8859 6 ISO 8859 7 ISO 8859 8 ISO 8859 9 ISO 8859 15 Shift IS Mac ShiftJIS Win Korean_Mac Korean_Win Korean Johab ChineseTrad Mac ChineseTrad Win ChineseSimp Mac ChineseSimp Win Cyrillic Mac Cyrillic Win CentralEurope Mac EasternEurope Win Turkish Mac Turkish Win Baltic Win Arabic Mac Arabic Win Greek Mac Greek Win Hebrew Mac Hebrew Win sODBC error codes and meaning e 11 101 Wrong license Open e 1 e 1 2 e 1 3 e 1 4 e 1 5 e 1 6 e 11 7 e 1 8 e 11 10 Connection open specific error Open Query command specific error Query Query read specific error Query NonQuery command specific error NonQuery NonQuery read specific error NonQuery Connection closing specific error Close GetSchema specific error GetSchema Not connected Open Query NonQuery GetSchema Empty parameter Open Query NonQuery GetSchema Somi t inc SODBC 15 CUSTOM FUNCTIONS ACOMBA List of custom functions for the Acomba software Aco AccountStatement ASAmountDue CuNumber InInvoiceNumber Aco Charter ChCategory ChNumber Aco Charter ChDescription ChNumber Aco ControlCustomer Interest Aco Customer Exist CuNumber Aco Customer NewUpdate CuNumber CuSortKey CuName CuCareOf CuAddress CuCity CuPostalCode CulSOCountryCode CuPhoneNumber1 CuPhoneExtension1 CuPhoneNumber2 Cu
11. User Manual sODBC Plug in Somit ZA Small Office Management Integrated Tools USER MANUAL OF sODBC PLUG IN FOR FILEMAKER PRO By Henri and Mathieu Frappier 2014 Somi t inc All right reserved Somi t inc SODBC TABLE OF CONTENT PREAMIBLE EE AE ROI E t PR 8 INTRODUCTION ER 8 SUMMA cci oett RT N EE NM HR Eo aga 8 Plug in installation 8 Custom functions installation era rine Pere tret ttn rr Foe bea SE pobre Eo ER De pea aiai De ek happen 9 Sample files ceteri err Rm ere e E reete SHIRE DIR EEUU IS 9 EXTERNAL Leg e KE 10 List of external functions for SODBC polug Jm 10 SODBC Open ConnectionString nerit DES RSG EE REKEN DRESS REED EES GEES 10 SODBE lose T 11 sODBC Query el E 11 sODBC NonQuery Query le 11 sODBC QueryA Query Get FileName ScriptName J rrrnnnrnnnnnvnnnnvrnnavvrnnnvvrrnvvnrnnre 11 sODBC NonQueryA Query Get FileName ScriptName le 12 sODBC FieldSepatator inire rr Eee E rer N NE Ed 12 sODBC RecordSenarator nennen enne nnns ente ede ee ee ee intrans asses ense tanga nns 12 sODBC GetSchema SchemaName AttributeFilter rrrrrrrrnnrrrrrrnnrrrrrnnarrrrnnnnrnrnnnnnnr 13 sODBC Register Name Key ciet rera kampe nme te ERE eua puta 13 sODBC Versiont 1 Format ise cette terre HE reverenter rbv eames e rete Ee tute ee eg 14 MEX EMCO EE 14 SODBC error codes and meani
12. action InDate Parameter Transaction date InAmount Parameter Total amount of the transaction InSubTotal Parameter Sub total amount of the transaction InReference Parameter Invoice reference InDescription Parameter Invoice description Somi t inc sODBC 21 e TANumPosts Parameter Number of details for the bill generally 1 detail for an account receivable 1 detail more per item plus 1 detail per tax see example of Aco TransARHeader New Aco TransAPDetail Update PACharterNumber PAAmount PaDescription PAlsTax TaNum e Description Create a detailed invoice for a supplier transaction invoice e PACharterNumber Parameter Account number e PAAmount Parameter Amount e PaDescription Parameter Description general description of the account see Aco Charter ChDescription e PAlsTax Parameter 1 if this detail is a tax 0 if not e TaNum Parameter Sequential number of invoice detail Aco Utils CityProvince City Province e Description Returns a string consisting of the city and province e City Parameter City e Province Parameter Province two letters ex QC Aco Utils DebitCreditAR Amount TransactionType TaNum e Description Formats the amount depending on the customer invoice e Amount Parameter Amount e TransactionType Parameter Type of transaction e TaNum Parameter Sequential number of invoice detail Aco Utils DebitCreditAP Amoun
13. allation steps 1 Install iODBC Framework in Mac OS X available at this address http www iodbc org downloads iODBC iodbc sdk 3 52 7 macosx 10 5 dmg 2 The sODBC fmplugin plug in must be located under Application FileMaker Pro version Extensions folder before opening FileMaker Pro 3 The ODBC driver for the application must be installed on the computer according to the manufacturer s recommendations 4 Although the ODBC connection manager ODBC Manager is available in the Application Utilities we recommend that you install OpenLink ODBC Administrator available here at this address http www iodbc org dataspace iodbc wiki iODBC Downloads 5 Use User DSN or preferably the System DSN to add ADD the source to the list 6 The ODBC source is now available using external functions provided with the SODBC plug in for FileMaker Pro Custom functions installation Only for users of FileMaker Pro Advanced You can import or copy and paste our custom functions in a different file from our FileMaker included with the set corresponding to the target software It is not possible for users of FileMaker Pro to import the custom functions Steps to run to import the custom functions 1 Open the destination file you want to import the custom functions 2 On the File menu select Manage Custom Functions 3 In the dialog Manage custom functions click Import 4 Open the file that contains the custom functions to import
14. attributes are separated by the field separator See sODBC FieldSeparator sODBC Register Name Key Description Register the plug in His condition switches from Demonstrator to Registered Name Parameter Organization name you provided to Somi t when you purchased the Key Parameter Registration key provided by Somi t 13 Somi t inc SODBC sODBC Version Format e Description Returns the version number and or the name of the plug in e Parameter short long Text Encoding The Mac version of the plug in SODBC offers the possibility to change the text encoding The default encoding used by the plug in sODBC Mac version is iso 8859 1 To change the encoding of the input text use the keyword inputEncoding followed by the desired encoding To change the encoding of the output text use the keyword outputEncoding followed by the desired encoding Examples with SODBC Query e SODBC Query inputEncoding UTF8 SELECT FROM Province WHERE Nom Qu bec e SODBC Query outputEncoding UTF8 SELECT FROM Province WHERE Nom Qu bec e SODBC Query inputEncoding UTF8 outputEncoding UTF8 SELECT FROM Province WHERE Nom Qu bec Example with sODBC_NonQuery e SODBC NonQuery inputEncoding UTF8 UPDATE Province SET nom Qu bec WHERE nom Quebec 14 Somi t inc sODBC List of available encoding Native UTF8 ASCII DOS ASCI
15. e e CuCareOf Parameter Contact name A S e CuAddress Parameter Customer address e CuCity Parameter Customer city see Aco Utils CityProvince e CuPostalCode Parameter Customer postal code e CulSOCountryCode Parameter Customer country two letter code see Aco Utils ISOCountryCodeEn e CuPhoneNumber1 Parameter Phone number of 1 customer e CuPhoneExtension1 Parameter Extension of phone number 1 e CuPhoneNumber2 Parameter Phone number of 2 customer e CuEMail Parameter Customer email address e CuWebAddress Parameter Customer website e CuReceivable Parameter Number of the account receivable for that customer e CuTaxGroupNumber Parameter Acomba Group Number taxes for that customer e CuTermNumber Parameter Term of payment for that customer e CuMarginLimit Parameter Line of credit for that customer e CuComment Parameter Comment about that customer Aco CustomerPaymentLine RecCardPos CPInvoiceARCP e Description Returns the number of internal payment of Acomba RecCardPos e CPInvoiceARCP Parameter Internal invoice number of Acomba RecCardPos Aco InvoiceAR Exist InCustomerSupplierNumber InInvoiceNumber e Description Returns the number of customer internal invoice of Acomba 20 e InCustomerSupplierNumber Parameter Customer number e InInvoiceNumber Parameter Customer invoice number 18 Somi t inc SODBC Aco InvoiceAP Exist InSupplierNu
16. e Rollback uote tree iet o et rio o Ee Dee e ee iens 28 s50 InvoicePayment New slnvoiceNumber ICustomrld dtDate dAmount IType IBankAccount sReference sComment sesser rsin 29 s50 Purchase New sName IVendorld dtDate sReference sDescription sTaxCode dSubTotal IChargeAccount dTotalTaxA lAccountTaxA dTotalTaxB AccountTaxB sNonTaxableDescription dNonTaxableAmount INonTaxableAccount dTotal sSComment s50 Vendor exist sName neret igna ent Re ER e ee Ee EE EER a HER EE e UNSER Y Ye Y geed 30 s50 Vendor New sName sCntcName sStreet1 sStreet2 sCity sProvState sCountry sPostalZip sPhone1 sPhone2 sFax sEmail sWebSite nLangPref dtSince sMemo 30 s50 Vendor Update lid sName sCntcName sStreet1 sStreet2 sCity sProvState sCountry sPostalZip sPhone1 sPhone2 sFax sEmail sWebSite sMemo 30 S50 Utils CheckSglSynmtax Sal ua 31 50 Utils Date fmDate Vir ee tero erede ta eet bete ee se Ee oek ee oe E rhe be qe 31 s50 Utils DateFirstOfLastMonth fmDate le EN SS0 VIS ESO TE eege ED N ee OE ee ree eer ee 31 550 Utils FormatDecimal Number Juss isstandlussmukruenetkivnutteneeshun 31 s50 Utils getNextld Tableld rtr ttt rta ttn ttt trt arena n 32 s50 Utils getNextInvoiceNumber iese se ee se ee GR Re ee GR AR Re ee Ge Re ee Re triente nennen nna 32 S50 Utils getNextReceiptNumber iis se ee se ee GR Re ee GR AR
17. ee Re AR Ee nennen nennt snnt nnn 23 Aco Utils Phone Test 23 CUSTOM FUNCTIONS SAGE 50 Simple Comptable 24 List of the custom functions for the Sage 50 Software ie ee ee ee ee RA ee Ee ee 24 EE E 25 AA 25 s50 Customer Exist ehamel enne enne nnn rennen enne 25 s50 Customer New sName sCntcName sStreet1 sStreet2 sCity sProvState sCountry sPostalZip sPhone1 sPhone2 sFax dCrLimit sEmail sWebSite nLangPref dtSince s50 Customer Update lid sName sCntcName sStreet1 sStreet2 sCity sProvState sCountry sPostalZip sPhone1 sPhone2 sFax sEmail sWebSite sMemo 26 s50 Invoice New sName ICustomrld dtDate sReference sTaxCode dSubTotal dTotal dTotalTaxA lAccountTaxA dTotalTaxB lAccountTaxB sComment le 27 s50 InvoiceDetail New slInvoiceNumber sPartCode IQuantity dPrice sTaxCode dAmountTaxA lAccountTaxA dAmountTaxB J ccountfasxb treerne 27 s50 InvoiceDetailAccount New sInvoiceNumber Account sDescription IQuantity dPrice sTaxCode dAmountTaxA lAccountTaxA dAmountTaxB lAccountTaxB 28 Somi t inc SODBC s50 Invoice Freight sinvoiceNumber dFreightAmount sFreightTaxCode dFreightTaxA IFreightAccountTaxA dFreightTaxB IFreisbt Accountfaxb renten ee Re 28 s50 Involce Comimlt es ciii ra etae OE ER ECT ve e Toe N EE 28 s5 rvoic
18. gsomi t com Web site http www somi t com or http www FollowTheGuide ca Last update made to this manual November 24th 2014 INTRODUCTION Summary The sODBC plug in allows FileMaker Pro solutions to communicate with any ODBC data source installed on a Windows or Mac OS X system After installation of the SODBC plug in it will be easy to use all the eleven external functions and the basic SQL functions Select Update Delete and Insert The sODBC plug in allows you to control the actions of opening and closing of the ODBC connection unlike Execute SQL script that opens and closes the connection automatically without consulting you It uses the syntax of the SQL language used by the ODBC driver of the target database Example MySQL syntax for MySQL software Plug in installation on Windows Summary of installation steps 1 The sODBC fmx plug in must be located under C Program Files FileMaker FileMaker Pro version Extensions folder before opening FileMaker Pro 2 The ODBC driver for the source software must be installed on the local computer according to the manufacturer s recommendations 3 Verify if the data source is available through the Control Panel ODBC Data Source Drivers tab 4 Use User DSN or System DSN to add ODBC source to the list 5 The ODBC source is now available for SODBC using external functions from FileMaker Somi t inc sODBC Plug in installation on Mac OS X Summary of inst
19. ier company name sCntcName Parameter Name of main contact at supplier company sStreet1 Parameter Company address line 1 30 sStreet2 Parameter Company address line 2 E EN Somi t inc sODBC e sCity Parameter City name e sProvState Parameter Name of province e sCountry Parameter Name of country e sPostalZip Parameter Postal code e sPhonel Parameter Phone number 1 e sPhone2 Parameter Phone number 2 e sFax Parameter Fax number e sEmail Parameter Email address e sWebSite Parameter Website address e sMemo Parameter Notes 550 Utils CheckSqlSyntax Sql e Description Check the SQL query for missing values e Sql Parameter SQL query s50 Utils Date fmDate e Description Convert a FileMaker date into format required by Sage 50 e fmDate Parameter Date s50 Utils DateFirstOfLastMonth fmDate e Description Returns the start date of the previous month fmDate Ex 28 09 2013 returns 01 08 2013 e fmDate Parameter Date s50 Utils ESQ Text e Description Convert single quote in double quote for the SQL query e Text Parameter Text s50 Utils FormatDecimal Number e Description Change the number format with a dot As decimal separator e Number Parameter Number to format bed Somi t inc sODBC s50 Utils getNextId Tableld e Description Returns the next unique number to use for the table Table
20. igns the field separator to use e Parameter String s to be used as a separator o Example SODBC FieldSeparator SODBC FieldSeparator Char 31 SODBC FieldSeparator lt MySeparatorl gt e Result Returns the field separator used e Note The default field separator is the ASCII code 31 equivalent to the function Char 31 of FileMaker sODBC RecordSeparator e Description Returns the record delimiter used and or assigns the record separator to use e Parameter String s to be used as a separator o Example 12 SODBC RecordSeparator Somi t inc sODBC SODBC RecordSeparator Char 30 SODBC RecordSeparator lt MySeparator2 gt Result Returns the record separator used Note The default field separator is the ASCII code 30 equivalent to the function Char 30 of FileMaker sODBC GetSchema SchemaName AttributeFilter WINDOWS ONLY Provides access to Schema information from the data source SchemaName Parameter Name of Schema Possible values Restrictions Tables Columns Views ViewColumns Users DataTypes ReservedWords Procedures ProcedureParameters Indexes IndexColumns ForeignKeys DataSourcel nformation MetaDataCollections AttributeFilter Parameter Filter results by attribute name Example SODBC GetSchema Tables TABLE NAME Result Returns schema information as a value list If the filter attribute is not used the
21. it sEmail sWebSite nLangPref dtSince sMemo s50 Customer Update Ild sName sCntcName sStreet1 sStreet2 sCity sProvState SCountry sPostalZip sPhone1 sPhone2 sFax sEmail sWebSite sMemo 550 Invoice New sName ICustomrld dtDate sReference sTaxCode dSubTotal dTotal dTotalTaxA lAccountTaxA dTotalTaxB lAccountTaxB sComment s50 InvoiceDetail New sl nvoiceNumber sPartCode IQuantity dPrice sTaxCode dAmountTaxA lAccountTaxA dAmountTaxB lAccountTaxB s50 InvoiceDetailAccount New slnvoiceNumber Il Account sDescription IQuantity dPrice sTaxCode dAmountTaxA lAccountTaxA dAmountTaxB lAccountTaxB 550 Invoice Freight sInvoiceNumber dFreightAmount sFreightTaxCode dFreightTaxA IFreightAccountTaxA dFreightTaxB IFreightAccountTaxB s50 Invoice Commit s50 Invoice Rollback s50 InvoicePayment New sInvoiceNumber ICustomrld dtDate dAmount IType IBankAccount sReference scomment Aco InvoiceAR Exist InCustomerSupplierNumber InInvoiceNumber s50 Purchase New sName IVendorld dtDate sReference sDescription sTaxCode dSubTotal IChargeAccount dTotalTaxA lAccountTaxA dTotalTaxB lAccountTaxB sNonTaxableDescription dNonTaxableAmount INonTaxableAccount dTotal sComment s50 Vendor Exist sName 550 Vendor New sName sCntcName sStreetl sStreet2 sCity sProvState sCountry sPostalZip
22. ld e Tableld Parameter Unique number from Sage 50 table e Table ID 20 tAccount 24 tBRSum 25 tBRTr 26 tBRSum E 30 tProject 40 tVendor 43 tVenTr 44 tVenTrDt 50 tCustomr 53 tCusTr 54 tCusTrDt 60 tPurOrdr 62 tSalOrdr 70 tEmpUS 72 tPyDetU 80 tEmp 82 tPyDet 90 tinvent 97 tPrcList 100 tJ ourEnt 103 tJ EntLY 106 tJ EntCsh 110 tUser 113 tCurrncy 115 tCCard 116 tShipper 119 tTask 121 tStmtDwn 122 tSDLine 123 tUserWnd 126 tTaxDtl 150 tl TRec 160 tRCGen 163 tRCPur 166 tRCSal 169 tRCPrl 171 tRCPrIU 173 tRCTrf 176 tRCAdj 179 tRCPay 191 tTaxAuth 192 tTaxCode 200 tActHRel 201 tActHDat 210 tRCBOM 212 tRCTSIp 220 tTSRec 222 tPmtHdr 223 tRcpHdr 224 tPriHdr 225 tActDpt 255 tActRang 256 tJ obCat 261 tCnActRI 268 tDeposit 269 tDSCheq 270 tDSCash 272 tDSDenom 273 tDSMult 304 tRptOpts 307 tRptGrp 350 tI nvLoc 352 tSDSHdr 353 tSDSChqs 354 tSDSCash 369 tRQRet 381 tGrpExp 386 tCustShp 388 tlnvLot 390 tInvCate 391 tUsrRole 400 tinvSNum 404 tSBom 407 tJ EntPH 408 tJ ELYPH 410 tCCardTr 414 tSDNode 415 tSDUUID 438 tGovForm 440 tSDDgst 445 tSDTax s50 Utils getNextInvoiceNumber e Description Returns the next customer invoice number to use s50 Utils getNextReceiptNumber e Description Returns the next receipt number to use s50 Utils getInvoiceBalance sInvoiceNumber ICustomrld e Description Returns the balance of a customer invoice e sInvoiceNumber Parameter Invoice number used by Sage 50 e ICustomrld Parameter Unique number of the c
23. mber InInvoiceNumber e Description Returns the number of supplier internal invoice of Acomba 20 e InSupplierNumber Parameter Supplier number e InInvoiceNumber Parameter Supplier invoice number Aco Supplier Exist SuNumber e Description Returns the supplier internal number of Acomba RecCardPos if the customer exist 20 e SuNumber Parameter Supplier number Aco Supplier NewUpdate SuNumber SuSortKey SuName SuCareOf SuAddress SuCity SuPostalCode SulSOCountryCode SuPhoneNumber1 SuPhoneExtension1 SuPhoneNumber2 SuEMail SuWebAddress SuPurchaseAccountNumber SuTaxGroupNumber SuPaymentTermNumber SuComments e Description Add or update information from a supplier e SuNumber Parameter Supplier number e SuSortKey Parameter Supplier sort key e SuName Parameter Supplier name e SuCareOf Parameter Contact name A S e SuAddress Parameter Supplier address e SuCity Parameter Supplier city see Aco Utils CityProvince e SuPostalCode Parameter Supplier postal code e SulSOCountryCode Parameter Supplier country two letter code see Aco Utils I SOCountryCodeEn e SuPhoneNumber1 Parameter Phone number of 1 supplier e SuPhoneExtension1 Parameter Extension of phone number 1 e SuPhoneNumber2 Parameter Phone number of 2 supplier e SuEMail Parameter Supplier email address e SuWebAddress Parameter Supplier website e SuPurchaseAcc
24. meter Description of the invoice e TANumPosts Parameter Number of details for the bill generally 1 detail for an account receivable 1 detail more per item plus 1 detail per tax Ex TaNumPosts 5 for an invoice of 1 00 taxes 1 Account receivable 1 14 2 Apple 1 x 0 50 3 Banana 1 x 0 50 20 4 GST 0 05 596 5 QST 0 09 8 5 Somi t inc SODBC Aco TransARDetail Update PACharterNumber PAAmount PaDescription PAIsTax TaNum Description Create a detailed invoice for a transaction invoice PACharterNumber Parameter Account number PAAmount Parameter Amount PaDescription Parameter Description general description of the account see Aco Charter ChDescription PAlsTax Parameter 1 if this detail is a tax O if not TaNum Parameter Sequential number of invoice detail Aco TransAP Begin Description Begins a supplier transaction type Aco TransAP Cancel Description Roll back a transaction previously opened with Aco TransAP Begin Aco TransAP End Aco TransAPHeader New InInvoiceNumber InSupplierNumber InType InDate Description Closes a previously opened transaction with Aco TransAP Begin InAmount InSubTotal InReference InDescription TANumPosts Description Create the header of a supplier transaction invoice InInvoiceNumber Parameter Supplier invoice number InSupplierNumber Parameter Supplier number InType Parameter Type of trans
25. ng isse lande rene oret e E eb eee Nee Ee Dee ees 15 CUSTOM FUNCETIONS ACOM BA E 16 List of custom functions for the Acomba software 16 Somi t inc SODBC Aco AccountStatement ASAmountDue CuNumber InInvoiceNumber 17 Aco Charter ChCategory ChNumber iese sees ee RA GR Re RA ee ve nnne nnne 17 Aco Charter ChDescription ChNumber sees se ee se AR Re RA ee AR Re GR Re ee ee nnns 17 Aco ControlC stomier Intetest oro tero rei Poesie eb reg as EE bx LE Pa segun Sere DEERE EE 17 Aco Customer Exist CuNumber ccccccecssscessseceeccecseeeesaecscseeceseeesaueeseseeceeeeeaeesesaeseeneeeegs 17 Aco Customer NewUpdate CuNumber CuSortKey CuName CuCareOf CuAddress CuCity CuPostalCode CulSOCountryCode CuPhoneNumber1 CuPhoneExtension1 CuPhoneNumber2 CuEMail CuWebAddress CuReceivable CuTaxGroupNumber CuTermNumber Culnterest CuMarginLimit CuComment sees 18 Aco CustomerPaymentLine RecCardPos CPInvoiceARCP sss 18 Aco InvoiceAR Exist InCustomerSupplierNumber InInvoiceNumber 18 Aco InvoiceAP Exist InSupplierNumber InInvoiceNumber le 19 Aco Supplier Exist SuNumber nennen nennen nenne nennt nsns ee nan 19 Aco Supplier NewUpdate SuNumber SuSortKey SuName SuCareOf SuAddress SuCity SuPostalCode SulSOCountryCode SuPhoneNumber1 SuPhoneExtension1 SuPhoneNumber2 S
26. ountNumber Parameter Number of the account receivable for that supplier e SuTaxGroupNumber Parameter Acomba taxes group number for that supplier e SuPaymentTermNumber Parameter Term of payment for that supplier e SuComments Parameter Comment about that supplier 19 Somi t inc SODBC Aco SupplierTrans Balance SuNumber InInvoiceNumber e Description Returns the balance to pay for a supplier transaction e SuNumber Parameter Supplier number e InInvoiceNumber Parameter Supplier transaction number Aco TransAR Begin e Description Begins a customer transaction type Aco TransAR Cancel e Description Roll back a transaction previously opened with Aco TransAR Begin Aco TransAR End e Description Closes a previously opened transaction with Aco TransAR Begin Aco TransARHeader New InInvoiceNumber InCustomerSupplierNumber InTransactionType InDate InAllPaid InTaxGroupNumber InReference InDescription TANumPosts e Description Create the header of a customer transaction invoice e InInvoiceNumber Parameter Customer invoice number e InCustomerSupplierNumber Parameter Customer number e InTransactionType Parameter Type of transaction e InDate Parameter Transaction date e InAllPaid Parameter 1 If an invoice is paid in full or O otherwise e InTaxGroupNumber Parameter Acomba taxes group number e InReference Parameter Invoice reference e InDescription Para
27. r s50 invoice Rollback Refer to the demo file delivered with the custom functions kit for more details on the settings for Sage 50 software A problem for the receipt number is related to the behavior of Sage 50 Software Example If you open the billing module the next invoice number is loaded in memory and is updated when closing Sage 50 So if you do the normal work in addition to export invoices into Sage 50 the number that had been stored is no longer correct There are system in place to try to manage this behavior with the invoice number We recommend that you do not use the same Sage 50 session to work and to export The best would be to close Sage 50 and reopen before export and not to go elsewhere using different section of the software So we open Sage 50 we do not touch anything and than you do the exportation If you had a duplicate payment you could simply delete it and 34 recreate by hand Somi t inc SODBC Troubleshooting e s this is the first user manual for SODBC plug in for FileMaker Pro we invite users to communicate with us to help us build this section of the manual About FileMaker Inc e FileMaker develops database program awards since 1990 Its products include the legendary FileMaker Pro product line for Windows Mac and FileMaker Go for iPhone iPod and iPad FileMaker has won over 52 awards more than his eight competitors combined from 2003 to 2009 in the United States and a total of 1
28. ref dtSince sMemo e Description Adding a client Returns the unique number of the customer in Sage 50 e sName Parameter Customer company name e sCntcName Parameter Name of main contact at customer company e sStreet1 Parameter Company address line 1 e sStreet2 Parameter Company address line 2 e sCity Parameter City name e sProvState Parameter Name of province 25 e sCountry Parameter Name of country ES Somi t inc SODBC sPostalZip Parameter Postal code sPhonel Parameter Phone number 1 sPhone2 Parameter Phone number 2 sFax Parameter Fax number dCrLimit Parameter Credit Limit sEmail Parameter Email address sWebSite Parameter Website address nLangPref Parameter Preferred language 0 English 1 Spanish 2 French dtSince Parameter Date customer was added to Sage 50 sMemo Parameter Notes s50 Customer Update Id sName sCntcName sStreet1 sStreet2 sCity sProvState sCountry sPostalZip sPhone1 sPhone2 sFax sEmail sWebSite sMemo Description Updating general customer information lid Parameter Unique number of the customer in Sage 50 sName Parameter Customer company name sCntcName Parameter Name of main contact at customer company sStreet1 Parameter Company address line 1 sStreet2 Parameter Company address line 2 sCity Parameter City name sProvState Parameter Name of p
29. river Microsoft Access Driver mdb 3 DBQ c bin Northwind mdb Result Returns 1 if the connection was successfully established otherwise returns the error code preceded by 10 Somi t inc SODBC sODBC Close e Description Closes the connection to the ODBC data source e Parameter Not used stay empty with e Result Returns 1 if the connection was successfully closed otherwise returns the error code preceded by sODBC Query Query e Description Execute a SELECT SQL query e Parameter Not used under Windows stay empty with For the Mac version you can change the text encoding of input and output See Text Encoding section of this document e Query Parameter SQL query to execute o Example SODBC Query SELECT FROM Contacts WHERE Status Active e Result Returns the query result If more than one field is selected the field separator will be used Also if more than one record is selected the record separator will be used See SODBC FieldSeparator and sODBC_RecordSeparator sODBC NonQuery Query e Description Execute a INSERT UPDATE or DELETE SQL query e Parameter Not used under Windows stay empty with For the Mac version you can change the text encoding of input and output See Text Encoding section of this document e Query Parameter SQL query to execute o Example SODBC NonQuery UPDATE Contacts SET Status Inactive WHER
30. rovince sCountry Parameter Name of country sPostalZip Parameter Postal code sPhonel Parameter Phone number 1 sPhone2 Parameter Phone number 2 sFax Parameter Fax number dCrLimit Parameter Credit Limit sEmail Parameter Email address sWebSite Parameter Website address sMemo Parameter Notes 26 Somi t inc sODBC s50 Invoice New sName ICustomrld dtDate sReference sTaxCode dSubTotal dTotal dTotalTaxA lAccountTaxA dTotalTaxB lAccountTaxB sComment Description Creates the header of a transaction customer invoice and returns the invoice number used by Sage 50 sName Parameter Customer company name ICustomrl d Parameter Unique number of the customer in Sage 50 dtDate Parameter Transaction date sReference Parameter External reference sTaxCode Parameter Tax code to use dSubTotal Parameter Transaction sub total dTotal Parameter Transaction total dTotalTaxA Parameter A tax amount IAccountTaxA Parameter Tax A account number dTotalTaxB Parameter B tax amount IAccountTaxB Parameter Tax B account number sComment Parameter Notes Note the Commit is not automatic You must use the functions s50 Invoice Commit ou s50 invoice Rollback s50 InvoiceDetail New sInvoiceNumber sPartCode IQuantity dPrice sTaxCode dAmountTaxA lAccountTaxA dAmountTaxB lAccountTaxB Description Adds a line to a transaction cus
31. t TransactionType TaNum e Description Formats the amount depending on the supplier invoice e Amount Parameter Amount e TransactionType Parameter Type of transaction e TaNum Parameter Sequential number of invoice detail Aco Utils DecimalSeparator Number e Description Converts the number format used by FileMaker for a format for SQL 100 50 gt 100 50 decimal numbers must be separated by a dot Somi t inc SODBC 22 e Number Parameter Amount Aco Utils ESQ Text e Description Converts single quotes for double apostrophe for the SQL e Text Parameter Text Aco Utils ISOCountryCodeEn Country e Description Returns the two character ISO code for the country name in English e Country Parameter Country name ex Germany gt DE Aco Utils ISOCountryCodeFr Country e Description Returns the two character ISO code for the country name in French e Country Parameter Country name ex Allemagne gt DE Aco Utils Phone Text e Description Returns a phone number of 7 10 or 11 characters e Text Parameter Phone number 23 Somi t inc SODBC CUSTOM FUNCTIONS SAGE 50 Simple Comptable List of the custom functions for the Sage 50 software s50 sASUserld s50 sASOrgld s50 Customer Exist sName 550 Customer New sName sCntcName sStreet1 sStreet2 sCity sProvState sCountry sPostalZip sPhonel sPhone2 sFax dCrLim
32. ter Account number where the payment will be recorded e sReference Parameter Cheque number or name of the credit card e sComment Parameter Notes s50 Purchase New sName IVendorld dtDate sReference sDescription sTaxCode dSubTotal IChargeAccount dTotalTaxA lAccountTaxA dTotalTaxB lAccountTaxB sNonTaxableDescription dNonTaxableAmount INonTaxableAccount dTotal SComment e Description Creates the header and the details of a transaction purchase supplier e sName Parameter Supplier company name e IVendorld Parameter Unique number of the supplier in Sage 50 e dtDate Parameter Transaction date e sReference Parameter External reference e sDescription Parameter Purchase description e sTaxCode Parameter Tax code to use e dSubTotal Parameter Transaction sub total e IChargeAccount Parameter Account number where the charge will be recorded e dTotalTaxA Parameter A tax amount e JAccountTaxA Parameter Tax A account number e dTotalTaxB Parameter B tax amount e JAccountTaxB Parameter Tax B account number e sNonTaxableDescription Parameter Description of the part without tax e dNonTaxableAmount Parameter Amount of the part without tax e INonTaxableAccount Parameter Account number where the part without tax will be recorded e dTotal Parameter Transaction total e sComment Parameter Notes Somi t inc SODBC s50 Vendor
33. that Microsoft NET 3 5 is installed with Windows Vista and 7 In the case of Windows XP install the resources NET 3 5 available from Microsoft Consult the Web page http www microsoft com net If your OS is Windows 7 in 64 bits mode or XP 64 bits use the following ODBC Manager for all 32 bits ODBC drivers C Windows SysWOW64 odbcad32 exe The sODBC plug in is compatible with versions 9 10 11 12 and 13 of FileMaker Pro FileMaker Pro Advanced and FileMaker Server The sODBC plug in can be used by the Acomba ODBC driver with versions 9 10 11 and 13 of FileMaker Pro and FileMaker Pro Advanced However it cannot be used with version 12 of FileMaker Pro and FileMaker Pro Advanced The sODBC plug in can be used by Sage 50 software with versions 9 10 11 12 and 13 of FileMaker Pro and FileMaker Pro Advanced The sODBC plug in is compatible with FileMaker Server side scripting SSS technology Codes of items must match between Sage 50 and your FileMaker solution The tax code for No Tax in Sage 50 must be empty There must be at least one client and one provider created in Sage 50 to allow export from FileMaker There are no management rebate discount for client in Sage 50 We recommend that you configure the default values to zero 0 in the menu Settings Customers amp Sales The Commit is not automatic for functions s50 Invoice New and s50 InvoiceDetail New Do not forget to use the functions 550 Invoice Commit o
34. tomer invoice sl nvoiceNumber Parameter Invoice number used by Sage 50 sPartCode Parameter The product or service code displayed in the section Stocks amp Services by Sage 50 IQuantity Parameter Quantity of the product or service dPrice Parameter Price of the product or service sTaxCode Parameter Tax code to use dAmountTaxA Parameter A tax amount IAccountTaxA Parameter Tax A account number AmountTaxB Parameter B tax amount IAccountTaxB Parameter Tax B account number Note the Commit is not automatic You must use the functions s50 Invoice Commit ou s50 invoice Rollback 27 Somi t inc SODBC s50 InvoiceDetailAccount New sInvoiceNumber IAccount sDescription IQuantity dPrice sTaxCode dAmountTaxA lAccountTaxA dAmountTaxB lAccountTaxB Description Uses a G L account instead of a catalog item sl nvoiceNumber Parameter Invoice number used by Sage 50 Account Parameter G L account number used sDescription Parameter Line invoice details description I Quantity Parameter Quantity of the product or service dPrice Parameter Price of the product or service sTaxCode Parameter Tax code to use dAmountTaxA Parameter A tax amount IAccountTaxA Parameter Tax A account number AmountTaxB Parameter B tax amount IAccountTaxB Parameter Tax B account number Note the Commit is not automatic You must use the functions 550 Invoice Commit ou
35. uEMail SuWebAddress SuPurchaseAccountNumber SuTaxGroupNumber SuPaymentTermNumber SuComments J A ee 19 Aco SupplierTrans Balance SuNumber InlnvoiceNumber eene 20 Aco TransAR Begli ici EE ee reisende OE vue docte pube dois klissete Hee N tues 20 AGO ran SAR NE 20 DO MEE LA End BE RE ER AE M 20 Aco TransARHeader New InInvoiceNumber InCustomerSupplierNumber InTransactionType InDate InAllPaid InTaxGroupNumber InReference InDescription TANUMP OSIS 55 20 Aco TransARDetail Update PACharterNumber PAAmount PaDescription PAlsTax REI IO C EE OE EE RE EE OE A N 21 Aco TransAP BEE 21 EE TAKST RE Ta et Pe ER AE EG ee ee EN EE Re ee ee 21 ACO TAMSAPSENG EE N N ER EE IE 21 Somi t inc SODBC Aco TransAPHeader New InInvoiceNumber InSupplierNumber InType InDate InAmount InSubTotal InReference InDescription TANumbPosts le 21 Aco TransAPDetail Update PACharterNumber PAAmount PaDescription PAlsTax TANUM Just bn n 22 Aco Utils CityProvince City Province 22 Aco Utils DebitCreditAR Amount TransactionType TaNum le 22 Aco Utils DebitCreditAP Amount TransactionType TaNum le 22 Aco Utils DecimalSeparator Number 22 AO UNS ESQ E 23 Aco Utils SOCountryCodeEn Country initi ROES ro eter ENKER a 23 Aco Utils lSOCountryCodeFr Country ee ee
36. ustomer in Sage 50 32 Somi t inc sODBC s50 Utils getPurchaseBalance sInvoiceNumber IVendorld e Description Returns the balance on a supplier invoice e sinvoiceNumber Parameter Invoice number used by Sage 50 e Vendorld Parameter Unique number of the supplier in Sage 50 s50 Utils getTaxCodeld sTaxCode e Description Returns the unique number of a tax group e sTaxCode Parameter Tax code used by Sage 50 s50 Utils getTaxInfo sTaxCode ITaxAccountNumber e Description Returns information about the tax code ref source code e sTaxCode Parameter Tax code used by Sage 50 e ITaxAccountNumber Parameter Account number of the sale or purchase s50 Utils setNextId Tableld e Description Assigns the next unique number to be used by the table Tableld see 550 Utils getNextld e Tableld Parameter Unique ID number of the table in Sage 50 s50 Utils setNextInvoiceNumber Number e Description Assigns the next number of customer invoice to use e Number Parameter Number s50 Utils setNextReceiptNumber Number e Description Assigns the next receipt number to use e Number Parameter Number 33 Somi t inc sODBC REFRENCES Technical notes The sODBC plug in requires the operating system Windows XP version or later The sODBC plug in requires the operating system Mac OS X version 10 8 Mountain Lion or later Under Windows OS sODBC standard requires

sODBC plug-in Manual - Somi-t

Contents

Download Pdf Manuals

Related Search

Related Contents