FileMaker to Fill In The Gaps

[Ted S]

I'm working for a manufacturing company that has an Oracle based business information system (ERP). The ERP system does many things but falls short in certain areas. What I would like to do is use FileMaker to fill in the gaps.

We would like to track a lot of information about our "installed base" (machinery that we have sold). The ERP system has a table that contains the SYSTEM_ID (primary key) and a bunch of other information including the customer's name, address, phone, etc. The ERP system however does not track any unique information such as parameter settings that we had established before the machine left our factory. I would like to use FileMaker to house information like this.

The company is on FileMaker version 3.0. We have been on it for many years but have been reluctant to upgrade in the hope that the ERP system would fulfil most of our needs. It hasn't - there are many gaps. I downloaded the trial version (6.0) to see if I can get it to work in conjunction with the Oracle based ERP system. Since 3.0 was not ODBC complient I have no experience in this area but I was able to quickly get FileMaker to import records from Oracle via ODBC. The problem with this is I don't envision importing records.

Ideally what I would like to do is keep the ERP info in the ERP system and the FileMaker info in the FileMaker. I would like to be able to go to a record in FileMaker, display the parameter settings for a given SYSTEM_ID (the settings would be contained in FileMaker) and also show the customer's name, address, phone, etc. from the Oracle DB without actually importing it into FileMaker. I would make the link obviously on SYSTEM_ID. This would be like a window into the ERP system from FileMaker.

I've poked around a bit in 6.0 but can't figure out how to do this.

Sound feasible?

Thanks,

-Ted

[Vaughan]

I believe the ODBC functionality in FMP allows you to import into FMP, and export out of FMP. I don't think there is any way to magically have the information in the remote database appear in the FMP records: it will have to be imported down.

Not having done any of it myself (all our corporate systems refuse any sort of direct ODBC interaction on principle) I dunno how much time a single record import is going to take... if it's in the order of milliseconds then perhaps with suitable control over the interface you could be importing the data down as the user is browsing without them knowing.

[Ted S]

Thanks for the feedback Vaughan.

I guess I was expecting to see an option for a calculated field where the calculation was a SQL statement. i.e. FmpPhoneField = SELECT phone FROM inst_base WHERE system_id = FmpSystemId;

Also, after poking around in the trial version for a couple hours I'm struck by how little has changed since 3.0. There's more colors available, the format painter is very nice and I'm sure there are other changes deeper in the system but I'm pretty underwhelmed so far. What am I missing?

-Ted

[Lee Smith]

If you go to FileMaker's site, you can put in a version such as 3, and it will give you a list of the major changes between the two.

There are quite a few.

HTH

Lee

:ciik:

[BruceJ]

It sounds like you want to use SQL and I'm 95% sure that FMPro version on will do this. I've recently gone through the XML process. However, I believe that in any of these cases, you will be populating fields in the FMPro database with the ERP data. There's no true linking/relating of the databases. So in effect, you are "importing" the records. It's just a matter of how fluid and transparent the quasi-import is.

[Ted S]

Thanks for the info Bruce & Lee... and thanks for the karate movies Bruce Lee!

I guess since nobody has written in with a quick answer to my question it must not be possible. It really goes against my grain to have the same information in two different spots but I may have to live with that. I just know there will be trouble when the users encounter records with conflicting information. What's that old saying; "Give a man a watch and he'll tell you what time it is. Give a man two watches and he won't know what time it is."

-Ted

[BruceJ]

TedS said:

It really goes against my grain to have the same information in two different spots but I may have to live with that. -Ted

You can actually program this som that's it appears seamless. You can even have it check both sources to update and incongruent data. This type of question is more related to SQL and XML, perhaps one of those gurus could get you going on this one.

[JerrySalem]

I did something similar to this. We had an SQL database of customers. I built a FM database for recording calls from customers to our service center. Same situation. Main data in the SQL(in your case Oracle) database and ancilary data in the FMP database.

To solve this delema, I had FMP do an OBDC based import of all the info I needed from the SQL database (based on the key field that lived in both systems). If you want to be fancy use a plug in to perform this every morning, or you can program it in at startup. I just trained someone to click a button every morning.

The important thing is, don't allow the user to modify any of the SQL specific info. I did and you can build a script to update info on the fly but it is not trivial(depending on the Update statment you need to write, no temp tables from FMP), I only did this for the most comon couple of fields that need to get updated.

Worked fine for me.

Doing the import every day assured me that the data was up to date and also doubled as a way to import new records from the mother load.

Hope this helps.

Jerry

[Ted S]

Jerry & Others,

Thanks for the good advice!

In an earlier post I stated that I was surprised at how little had changed since 3.0. Well, I'm nearing the limit on my 30 day trial version and I'm backing off my statement somewhat. A lot of the changes are pretty deep under the hood. I especially like the record locking capability and the ability to sort portal results. The Excel like layout is pretty cool too.

I have one more question; is the developer version worth the extra bucks? I won't be designing any stand alone stuff.

-Ted

[bruceR]

Depends on whether you ever need to debug any script and whether you ever need to rename one or more files in a system of files.

[_ian]

You do need to do the data import from Oracle into FileMaker. One approach to avoiding the problem of different data in the two systems is to update the FileMaker records as the user looks at them. So if your user views the detail for a particular item you would query the Oracle system as part of the script that navigates to the record detail. That should be pretty quick to do using the ability to update an existing database record when importing. You just need to ensure that your FileMaker system references the same database keys as used by Oracle and use that when importing.

We've done this kind of thing in several environments and it should work quite well.

regards

Ian