Subtitles of the Movie

There are three aspects to ODBC that we have to deal with. The first is the data that lives in external file or applications such as Oracle or SQL Server or text file. Then we need to configure a data source and it's the data source to which FileMaker connects and through the data source it goes to whatever the application is. So the issue here is to look at the date of the record that we are going to deal with to construct a data source and then to use FileMaker to import data or update the data source. Now before you get started there is a couple of things you need to do because as a oppose windows where the ODBC software is a part of the operating system for FileMaker you are going to be using separate ODBC software. There is some ODBC software installed with Mac OS10 which you want to use the FileMaker software. So if you install FileMaker you got a applications FileMaker ODBC import drivers and then you will find it installer in that program double click and install the installer , you do that only once . Then you can take a look at some of the sample data that has been provided inside the FileMaker 7 folder in the English extras example and ODBC example and there are several text files here that can be used as data sources. And I am going to double click sales people and you are going to see that it's a comma eliminated file containing seven records, first one of which is the name of the fields. So this is the data that we are going to be dealing with this is part one of ODBC. Step two in using ODBC with FileMaker is to create the data source. Now if you have done the configuration and the installation of ODBC configure which is in the FileMaker7 folder, then you will be able to go into your applications folder ,they will be entitled to direct ODBC folder and double click ODBC configure. I have no data sources here. I am going to add a single data source. And the drivers that are installed now are Oracle, SQL Server or text driver and the text driver is what I am going to use with the examples that are provided with FileMaker7. So I will select the driver and then I configure it and the configuration dialogue that you will see depends on the type of driver that you have selected. The data source name I am going to call this is the fmsales people example and I could give it any name that I want I can describe it however I want. But the most important thing now is to actually locate the data. And the data is going to be living inside applications, FileMaker, English extras, examples, ODBC example and I am selecting the directory not the individual file . So I select the directory which is sort of complicated to a database containing many tables with the text driver you have the directory that contains individual text files which are basically tables. And I can choose how it's the limit, and I am going to choose coma and I know that the column names are there in the first line. I now have created my data source, hats the end of step two. Step three of ODBC is to use make FileMaker to connect to the data source that you have defined. ODBC its self will take care of going from the data source to the database or file or whatever. All the FileMaker does is communicate with data source. And I am going to import the record to a blind database that I have created from the contact management template. Import records ODBC data source, I have one data source which is the one that I have just created. If I have a username and password now it's the time that I would enter them. Here are the tables in that directory those are the two text files in that directory. If it were database there would be the tables within a database, and these are the columns and I can add any of the columns into the SQL query I also can just double click. This is the same as what I do in windows. I am just taking sales person out of the sales people table. I will click execute and now I have my standard import field mapping dialogue. Now you will notice this one of those cases in which the source fields being imported don't exactly match the database that I have. The sales person people contains both first and last name. I am going to put it into last name and if you want to you can then come back and write yourself the script that will take the last part of the last name field leaving there and take the beginning of it and put it into first name. So it is up to you how you map these things, the point is that you can easily import from a table or I could since I have no records in the contact management database at this point I could use to find database to change the database structure. But I will just put sales person into last name, I will click import. I usually do want to use auto enter options. Seven records have been imported and there is the date that I have imported from an external data source using ODBC it's as simple as that. There is one additional feature for ODBC that is very powerful and I access it from scripts menu. I come up here to script maker and I have already prepared this demo script for ODBC. It has one script step in it, its the execute SQL script step which is down here under miscellaneous. And if I select here I can say that I want to perform it without dialogue which I usually do. And I can specify SQL text to be used, I first of specify the data source. Then I come down here and I can type in insert into sales people, I want the sales person account, values, test data. And that is a valid SQL statement that will update the data source provided that you are allowed to update it. Now this is all very well and good, it means that I have a way of going from FileMaker into the SQL data source the ODBC data source. But I can do something that is even more powerful. Instead of typing text in here, I can create a calculation and the calculation is the text string insert into sales people, sales person values and then I and I had open parenthesis and the single quote and I close the quote for this string. And I canconcatinate this string with the value of a field in the current database record. And then I am closing the SQL command with a quoted string that consists of within the quotes single quotes and the close parenthesis this closes this parenthesis up here. I have constructed a dynamic SQL statement that will let me update the data source again provided that I have the access to do that and insert data from FileMaker into the data sources. So you can see that data source is actually a data container from which you can extract data and into which using executed SQL you can insert data.