Import/export data out of web database

[michele]

Hi all you filemaker guru's :-), I have a few questions,

1) How easy is it to retrieve/add data from an online database so that I can use the data for a spreadsheet, database...in the office. How do you do this?

2) Is it possible to get the data from an email that's sent to you (cgi-script?) but not as body text but as a .txt attachement.

I'm planning on making a filemaker website ( with lasso?) where questionaires have to be filled in daily.

These questionaires have a different number of questions and change very often. So they are part of the database.

I need to be able to get the data of the filled in questionaires daily to put it in my database or a spreadsheet and ofcourse I must be able to add the questions in my database (not sure if I want this to happen online). I'd rather prepare the questions locally and then insert them in the online database.

3) I'm a bit confused concerning php, can it be used with lasso/filemaker?

Maybe you've figured out I'm just starting by the questions I ask. But help would be very appreciated.

Thankyou for reading and eventually answering my questions.

[Garry Claridge]

Re: 1) How easy is it to retrieve/add data from an online database.....

On a Mac it would be relatively easy with Applescript.

Re: 2) .....

Check POPit. It is an email plug-in.

All the best.

Garry

[Anatoli]

RE: 1) How easy is it to retrieve/add data from an online database so that I can use the data for a spreadsheet, database...in the office. How do you do this?

You can export data with script in off-peak hours.

Or connect another FM to web serving Unlimited (host) and export in any time any data

Finally, if FMU will get data from FM server, another client can get that as well and exported.

[Steve T.]

Hi, Michele!

ONLINE DB > LOCAL USE

You can use the same db for both unless you need to separate them for some reason. In the former case, you can leave the online db serving.. just make sure you enable it for multi-user use and the Local Data Companion is on. You can then access the db from your local computer on the same network using Open Remote or Hosts (depending upon your version of FMP). If you DO need separate files, FMP exports to a variety of formats so you should be able to bring the data into whatever software package you use to analyze.

EMAIL CGI

I don't know about going straight to attachments from a web form, but you could probably write a script to export records to text files from FileMaker.

FMP/LASSO/PHP

I hear Lasso is pretty much the same as FileMaker CDML but with more, better, stuff. It should be fully functional for just about anything you want to do. Garry has mentioned PHP in the posts so you might be able to search and find what you need there. I haven't used it, but a colleague referred me to a product that might let you use CDML and PHP together... I just never got around to following up on it as I have not started PHP yet.

QUESTIONNAIRE/SURVEY

We used FMP for an online questionnaire and it worked fine. It had a slow load time (30sec) because it was 75 Q's and lots of text, but we probably could have avoided that if we broke it up into smaller chunks at a time (10 Q's at once or something). It only took about a day to do, though. You may want to consider using generic Answer fields (a1, a2, a3, etc.) for holding all your data and just have a field indicating what survey/questionnaire set was completed. Q's can be hardcoded or from a DB, but it sounds like you should go w/db since you are going to do many often.

Sounds like a fun project and one that will serve you well for a long time. Good luck!

-- ST

[michele]

Thankyou for your answers!

[Unable]

"ONLINE DB > LOCAL USE

You can use the same db for both unless you need to separate them for some reason."

In other words share as in a peer-to-peer solution? What happens then with record locking? What happens if the sharer goes into layout mode? I really don't know. Please, an answer.

[Steve T.]

Hi, Unable!

Actually, I meant with a server (though not necessarily using FMP Server), but PEER-TO-PEER will work, too. When a db is set to multi-user, the first machine to open the db becomes the HOST--no matter where the file is actually stored. Any other user accessing the db through OPEN > HOSTS or OPEN REMOTE will be a fully funcitonal user of the db who can do layouts, perform searches, edit records, etc. but they cannot change things like doing Define Fields or Value Lists. FileMaker prevents multiple users from messing each other up by granting EDIT power to the first user who starts to EDIT a record. Other users can only BROWSE that record until the user editing it has finished. It's great. Multiple users usually don't even notice the activities of other users unless editing a record in common.

Layouts can be modified by any user, including the host (new layouts, changing layouts, chainging field formats, etc.) but Define Fields/Value Lists, etc. can only be changed from the host when no other user is logged on. FMP even gives you the option to prompt/send messages to connected users to ask them to disconnect from the db.

Now, hosting with web companion is not the same thing. Vaughan and some others do not recommend working from the host if you are serving the host on the internet because web queries are immediately reflected in the host database. If you try to work on the host while it's serving on the web, you risk messing some things up. It's kind of cool to watch, though.... sometimes I leave the db up on screen so I can see people accessing it. The screen jumps and you see FIND results from people using the db through the web. It's nice to see my solution in action and tells me how busy it is. For quick things, I risk the jumps and change things live on the server... I just try to be careful. Of course, if it's a long layout or other process, I can (and do) do that from my desktop system without taking the server offline. That way I don't have to worry about racing the web users.

It works great!

--ST

[Unable]

Ok, in a peer-to-peer hosting, "FileMaker prevents multiple users from messing each other up by granting EDIT power to the first user who starts to EDIT a record." Isn't that record locking?

Now someone on the peer-to-peer has locked a record in this solution which you suggest can be shared on the web, they are data entry and have not realized it is locked and gone on their lunch break. What happens when a web user tries to approach the same record through the browser during this time-frame?

If I understand you correctly (...because web queries are immediately reflected in the host database...) the web user will be able to access that locked record but the peer-to-peer folks won't.

[Steve T.]

RECORD LOCKING

It sounds like an appropriate term but I cannot claim expertise in data management enough to say it is, but it sounds good! As to the lunch break scenario, I THINK this is what happens (please chime in, folks, if I'm wrong)...

You have a shared db in multi-user mode and web hosting.

Several users are connected and web users are accessing the db, too.

Local user Wimpy creates a new record and starts to enter data.

He gets a craving for a hamburger and goes to lunch before finishing data entry for that record. (his cursor is still in one of the fields, either the one he just typed or the next field to be entered).

In this case, I do not think the record is accessible to anyone (local user or web user) yet even though some fields have been entered. Had Wimpy clicked the cursor outside any of the fields so no field was selected, the record would be accessible even though it was not completed.

I was the first one in this morning so our users aren't here for me to test this hypothesis yet but I THINK that's what happens. When I was talking about watching web users access records, I didn't mean while the record was locked, but it might be interesting to see when edits are reflected (field by field as edits are made or all at once as soon as the user is editing them... I think the latter).

--ST

[Unable]

Please do check. This goes to your original advice:

"ONLINE DB > LOCAL USE

You can use the same db for both unless you need to separate them for some reason."

It may be that Record Locking is THE reason to never host a web solution from a single shared dbfile in a multi-user (peer-to-peer) mode unless hosted through FM Server and in conjunction with FM Unlimited for the www clients.

[Steve T.]

Hi, Unable!

OK, folks... here's what I did...

I accessed the db thru FMP and edited a field (CA > California) but left cursor in field.

I accessed same record from another terminal thru FM and it showed CA.

I tried to edit it but FM warned that the record warned that the record could not be modified until User X was finished with it.

I accessed the record thru web and it showed CA.

I moved cursor directly to another field and made a minor text change.

I accessed same record from 2nd terminal thru FM and it still showed CA and other info and gave same warning.

I accessed record thru web and it showed CA and other info.

I removed cursor from fields by clicking on non-field area.

Record on 2nd terminal immediately showed changes; I was able to insert cursor in field to edit on 2nd terminal.

I accessed record thru web and it showed California and other minor text change.

That's it! It seems a record being edited does not show any changes to other users until the modifier stops being in edit mode, at which point changes become reflected to all users and someone else can then edit it and lock out the others. I had assumed so, but I guess it's nice to be sure. It wouldn't have really mattered to us, though... we would have been OK with any timing results as long as there was no data corruption.

Hope this helps, folks!

--ST

[Unable]

Thanks for posting the results of your efforts Steve.