Virgin Ground

[onefish]

I am about to attempt moving into uncharted territory (for me anyway) and am looking for any advice that may assist. I am fairly proficient with filemaker but am entirely self taught. Needless to say there is always more to learn. I am currently close to finalising a solution for a friend. This is free of charge as developing databases is not my profession. This solution must import records in CSV format on a weekly bases. The CSV files are downloaded from a website. I can write a script that will import the records and match the correct fields easy enough but the problem is that the people that will use this solution are ex-builders and traditional Aboriginal people that generally struggle to find the computer's ON button. There is a huge margin for human error with them downloading the file at the scheduled time saving it to the correct file and choosing the correct file to import from. This will all be greek to many of these people. I can easily right a script that will initiate this series of tasks on a scheduled basis but what is the best way to handle the non-filemaker tasks? Please keep in mind that this is a freebee and would prefer not to spend much money on additional software.

[Lee Smith]

Hi onefish, and welcome to the Forum.

It probably can be done, but it would be easier for all concern it you Attached a Copy of the Text and a copy of your database. It is always better for us to see what you are talking about, as web downloads, or copy paste can be tricky.

HTH

Lee

[onefish]

Attached is a PDF of the interface protocal. They will be downloading the reports as a CSV file. The sort order begins on page 72. I would like to add that the person I am developing this for is a friend and the Housing Manager of a remote Aboriginal Community housing organisation that opperates on a not for profit basis. The reports represent the rent automatically deducted from their tenants unemployment benefits by the Au Government and paid to the housing organisation. I am about to begin developing the rental collection area of the database (it also manages different communities, assets, maintenance and tenants). That, and a few reports are all that is left but it probably won't be much help as the rental collection area is nothing more than a button that does nothing.

Chapt21-CPAYClientSystemInterface-Output.v7.2.pdf

[Lee Smith]

I don't know what the cvs file will look like, but I have a feeling you are going to have a lot of fun with this one. I asked that you give us a copy of the text file so we can see what it will look like.

I don't have Acrobat for OS X, and Priview doesn't have an Export Format choice for csv, so I'm unable to even see you file that you will be dealing with. As I said before. A copy of the Text file .csv is needed, otherwise we are shooting in the dark on advice.

I am about to begin developing the rental collection area of the database ....

I take it that this means that there isn't a database for this yet?

HTH

Lee

[onefish]

Lee,

I have developed tables, viewing & printing layouts and reports for- admin details, tenants, tenancy agreements, residence history, communities, assets, photo gallery, maintenance items, required maintenance, job cards and many others. I am about to begin the rental income monitoring tables, layouts and reports. They will be related to the tenants and assets tables for viewing and reporting purposes. So that's why I am seeking some advice at this point, to be sure I develop it to be as automated as possible. I'm fairly confident I will not have any problem scripting the import once the CSV file is in the correct location and named the correct name. The real problem is how to automate the task of going to the http address, pasting the correct password (which will be stored in the database), saving the CSV file to the correct location on the hard drive or LAN server, change the file name to the file name the import script uses, trigger the import script, then rename the file and move to the 'imported reports' file. This may all be a bit too much to ask but I know the way these community housing organisations opperate. They change staff almost bimonthly, many of the staff don't read english very well and have never used a computer before. They end up in a constant cycle of training new staff and in the mean time, the data in the database is not updated properly and eventually the system is not used at all because nobody can remember what it's even supposed to do. So bottom line is that I am trying to make the system smart enough to function under these circumstances.

[Fenton]

You might want to take a look at the Troi site for plug-ins; specifically the URL and File plug-ins. I have not used the URL one. It may or may not work entirely with 7, but it sounds like it can go help go between FileMaker and the web. The File plug-in is very useful for dealing with the file once it's been downloaded.

Unless the download site can give you a URL for XML download. That would be the best, no other software needed, just an XSL file to manipulate their xml to FileMaker xml arrangement.

[Lee Smith]

Oops, I left out the word part in my question before, sorry.

It should have read: I take it that this means that there isn't a database for this [color:"red"] part yet?

Most of what you need to do falls outside FileMaker's abilities. In other words, you will need to use something like AppleScript. I know that there is one for Windows, but I don't what it is called.

Check the archives of this list using a Search for your keywords (Web, import, csv, and add AppleScript to them), I seem to remember others wanting to do something like this before. If I recall, Fention Jones replied and posted AS code, and a file or two.

HTH

Lee

[onefish]

Thanks Fellas,

Unfortunately XML is not available for the reports. I have done a bit of searching around and there appears to be an absolute plethora of freeware scripting engines for windows. I have downloaded 4 and will have a go at trying them out soon. I think I can even control the process from file maker by using the send message script step and clip board. I can use a key stroke emulator (ctrl+1, ctrl+2, etc.) to run scripts in FM. Effectively I should be able to break the task up into several scripts and control the process through calculations in FM. (hopefully?) I'll have a dig through the archives as well.

[onefish]

I've been having a look and I think Phantom for windows is about the most powerfull and versatile of the freeware options. I'll be quite busy reading the 430 odd page manual and learning enough of the language to get the job done but at a glance it appears to be able to do almost anything you could think of doing on a computer.

[spb]

Is the original data on the website that downloads as CSV stored in SQL or MySQL tables?

Steve Brown

[onefish]

I've got my friend coming in this afternoon to go over this. He's bringing his user name and password so I'll have a look. I'm like a kid with a new toy here. As Phantom simply runs text from any text editor file with the correct extention, as a script, I can actually create the scripts for Phantom to run in FM based on calculations and existing conditions in the database and then send a comment to Phantom to run it. This opens up a whole new playing field with FM (for me at least!).