kraftyman

Which way is your data transfer? Access into FMP or FMP into Access? If Access is the source, then: 1.Open the control pane ODBC source applet 2.Create a new UserDSN from your Access database 3. Choose the MSAccess driver 4. Give your source a name 5. Choose the directory and database 6. Close the ODBC applet 7. Go to filemaker and open ODBC sources if you want to import the data. You might need to create a database structure first. NB it doesn't matter if MSAccess is running or not. you can still pull the data. If FMP is the source 1. Follow the same procedure as above, except you have to name the source with the name of your Filemaker database and the fp5 extension. 2.FMP HAS TO BE RUNNING to serve the data. 3. FMP is very slack with field names and types, not all of which are supported by ODBC. Make sure you don't use empty spaces or symbols or punctuation marks. Make sure the field names are not too long <10 characters. Otherwise it is a mess in other programs. No repeating fields ..boo hiss... The MSAccess client cannot handle more than 33 indexes, and the connection will be rejected. This is easily reached in a standard FMP database. Changing indovidual records on the fly in Access from an FMP source is very easy, you just navigate to the record, and change the field value By comparison, in FMP this type of operation is quite complicated because you have to create SQL statements and do a formal insert/update type statement, which involves calculated fields and scripts. This is done by MSAccess under the hood. Overall, the ODBC support in FMP is not flash, but workable. If this is your main application, migrate to FMP, and vice versa. Having said that, it is generally easier to get things into than out of FMP

short answer = not in windows (don't own a MAC) This is one of the things I love about MSAccess... Filemaker is not able to modify screen object properties by a script. There is workaround, involving creating a formatted calculation field which might have a different font and font colour. Then, depending on the information in your data field, this would either be a blank or have a displayed value. Of course, this only caters for one type of condition. You would need several such calculation fields to cater for each possible value you wish to trap for. Alternatively, you can redirect the user to another layout with appropriate format/colour combination. These method work, but it's no really satisfactory. It is like having your steering wheel locked in one position so you can only turn right, and then having to buy another car so you can turn left, and another to go straight ahead... you get the drift.

I found a little useful trick whils experimenting. If use "User abort off" then the cance button disppears, and I go to the print subscript when continue is pressed. Then I can trap for the cancellation of the print script. I was dimly aware of the "Preview in Browse Mode with live buttons" trick but havent't tried it.

Thanks for the quick answer. I'm glad that there isn't something I'm missing - I'll stop banging my head against the monitor and move on to the next bug.... It's just a bit of a shame that preview mode is otherwise so restrictive

I want my users to view out a specific report in preview mode, which therefore enables sliding/shrinking etc, and gives them an indea of what the printed page will look like. I would also like them to have the option to print the report (or not). To get to this particular layout, the user can press a button from any one of 30 other layouts, which then flags the last viewed layout/record in two global fields. When the user finishes previewing/printing, I want them to go back to the last (non-report) layout. the script goes something tlike this Show all Omit Show omitted go to layout x enter preview mode (pause) ///////I had to get the pause here to give the the chance to see the pages/////// perform subscript ( print report) ///////the print report subscript uses error capturing so that if it is canceled, then the user is taken to the last (non-report) layout they originally came from When the user finishes previewing/printing, I want them to go back to the last (non-report) layout/////// My problem is as follows: 1.Once the layout is in preview mode, then there are no usable buttons at all on the screen, apart from the status bar. If I want to have the solution in Kiosk mode then what happens? I have trawled through the documentation,and there soesn't seem to be an answer! 2.If the cancel button is pushed on the status bar, it does not seem to generate any error codes so I can't error trap for it, and then the users get stuck in this layout, although it does go back to browse mode. I would like the user tobe taken back tothe original(non-report) layout if they cancel the script at this point Bright ideas anyone?

I'm using something rather more complicated. Suppose the original file is A1, the update is A2. Suppose also that the data structure is same, and you are not using a sequential primary key (bad move - too limiting) User downloads update into an update folder. A2Script copies all data from A1 into A2 A2Script then switches to A1 A1Script saves copy of A1 file into backup folder. A1Script closes A1 and switches back to A2 A2Script saves copy of file as A1's name in the original folder. A2Script exits and closes A2. It is a bit complicated to set up, because the scipt steps are named identically, and the backup/update folders need to be created before the solution is bound. The user could also get a bit confused,so that you might have to launch the right database from within the installer.

The problem seems to have been answered with the drag and drop of an excel spreadsheet into FMP. I have an alternative approach. If you cannot bring Mohammed to the mountain then bring the mountain to Mohammed! Set up the Excel file as the ODBC source and import the data into FMP rather than the other way around. Ditto for Access, and many other sources. I think it is significantly easier to get data INTO than OUT OF Filemaker....

Do you need to work with the data or just view it? If it is the latter, then you should be able to create some sort of relationship between the "first" and the "fourth" files. Then you could use a portal, or just drag a related field onto a layout. You may have to duplicate some data, but if this is done rignt, the performance hit is negligible. Alternatively, you could use some scripting to run some background finds on the "fourth" file and then flick across to either that file directly, or another layout. It may be necessary to bend the normalizatioin rules for the sake of convenience and logic. With the size of today's disks, and the speed of processors, I think we often strive for the purist "relational model" and shoot ourselves in the foot unnecessarily. Why not let the megahertz do the searching?

I have a slightly different suggestion You could use the feature of calculations which ignores text when generating an answer formatted as a number. For example " 1 worker" * "12 jobs" = 12 "10 bosses" * "1 million shares" = corporate disaster .... What you would then need to do is to generate a different value list for the job hours depending on which worker got picked. Then once the worker got picked, you could direct the user to another identical layout, but with a different value list for the work hour values. Then your unstored calculation field would give you the right number. If it is hours you want, then just use a label on the layout , and leave the filed value alone. This avoids any complex scripting. If the list is going to be very long, then I suggest using another file as the list source +/- some relationships to cut down your workload. Is this any use? [ February 08, 2002, 04:45 AM: Message edited by: kraftyman ]

I have this suggestion for you. It is a bit labour intensive, but should work. Have a look at the quickbooks documentation and decide wht you want to import. The documentation is reasonably good. The quickbooks american website has sample IIF files do download. You should be able to create a sript to populate a text field with the appropriate information. Then you should be able to copy and paste this into a blank text document and rename it with the extension IIF If you want to learn DDE, then you should be able to get Filemaker to use any DDE compliant text editor, or Excel to do this via a script. Altenatively, there are plugins which should be able to do this export for you. Try the Troi website. All these things might seem a bit vague ,but it is difficult to do this kind of thing with Filemaker. It is still probably much better integrated with a Mac than Windows. I did this sort of thing with MSAcess by using VBA, which allows very extensive control over files as well as being a real programming interface for the Windows platform. You can do just about anything via Scriptmaker, but not in Windows. I would be happy to correspond on this further.

There is no way to use ODBC with a runtime. (This is not spelt out clearly enough in the documentation, which is a shame. It could be that the inclusion of ODBC import support makes it too easy to write a competititve product to FMP, but surely the licence agreement could take care of that...) The runtime will import excel, dbf and text files.

What are you using as an ODBC client? Most applications and drivers will have a limit as to how much information can be read per record ( row). It may be that the client will only pull "..." as the upper limit for record size. For example, MsAccess has a maximum limit of 2000 characters in a single record, except for memo type oe OLE type fields. If you have 200 columns, chances are that you have exceeded this limit In addition to this, for Access, and I bet many other applications, the maximum number of colums in a table is 255. I suppose that there would not be many reasons to have such a huge flat file structure, as many fields could be kicked out into related tables. Maybe you have more than 255 fields? It is very easy to do in Filemaker...... Post some more details, maybe I can think of something...

Quickbooks PRO prbably uses the "IIF" file format. The details are available on their website, and in the quickbooks help. It's a plain text tab separated file, but it is very unwieldy because it is not a simple header column heading/separator/column heading record1 value/separator/value record2 value/separator/value structure. It contains multiple headers and records. The good news is that you can import sections of it one at a time. In ordeer to export/import, you will have to create a layout which contains the fields you want exported, and the appropriate colum titles such as !TRNS TRNSID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO TOPRINT TAXABLE ADDR1 ADDR2 ADDR3 ADDR4 ADDR5 DUEDATE SADDR1 SADDR2 SADDR3 FOB PONUM OTHER1 !SPL SPLID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO QNTY PRICE INVITEM TAXABLE OTHER2 OTHER3 !ENDTRNS etcetera etcetera The prolem with Filemaker is that you can't save that output easily to the appropriate file format . You can export a file with the TAB extension, but you will have to change it to ".IIF" in order to import to quickbooks. This is not something FMP can do by itself. Alternatively, the Troi file/text plugins mght help, butI'm just guesssing here. I did this kind of thing with MSAccess very easily because it has very powerful system integration with Windows. FMP? hmmm...

I had the same problem recently, only in windows.. I found the answer , sort of... You probably need to shut the server down, then open the file from another FMP machine, this way you'll have exclusive control of the file - then the save as option will be active. The other option that works in windows is to copy the file directly from within explorer ( on the server itself) to another location while it's still running. I don't think this will work in a mac because the record locking is tighter. (It's probably not that good an idea anyway..) (Only slightly?) off the topic: I only recently found out that FMP is a "disk-based" database, rather than a "RAM-based" database, that is the entire file is accessed and written to all the time (rather than like with a lot of other apps on windows, which load data into RAM and only save information at prespecified times ie shutdown) - tech support article number 102516 What are the possible advantages of this? To my untrained (untainted? ;-)) mind it would be asking for data corruption, at least if you are running windows 9x. What do the experts say?

It sounds like VB has nothing to connect to... This usually happens to me for two reasons. -) 1. I have forgotten to turn local data access companion on. 2. I have forgotten to open the Filemaker database. Unlike MS data sources, FilemakerPro has to be running to be able to serve as a data source.You could of course open the database from within your code. Is this at all helpful?

Something isn't quite right. This error generally comes up if the ODBC source is not available, either because the file is not there, or the odbc server is turned off. Like when you try to use FMP files as sources and FMp isn't running. Have you set up the Excel file as an ODBC source? Have you set up the appropriate worksheet? In ny case, you don't need ODBC to import files from excel (unless you want to filter the records) Is this of any help ? -)

You may have to go over your FMP file and turn off all of the "turn indexing on if necessary" options. Are there any related files as well? It may be that these get included in the count. I have the same problem with my stuff. If you have Access, then you have Excel and MSquery. MSquery doesn't have this limitation.

Connecting to an Access Database is very easy. I'm assuming you are using windows. 1. Open the control panel 2. Select ODB sources , it should not matter for the single user if you select the "user" or "system" data source. 3. click "add" 4. select "microsoft access driver" 5. make a name for the data source so you can find it again 6. select the database you wish to connect to from the file selector. 7. type in the appropriate user/password if that is required 9. voila! 8. use the normal filemaker processes to import/export/move data [ November 01, 2001: Message edited by: kraftyman ]

Have you tried setting up the filemaker file as an ODBC source? This is the easiest way if you can do it. Access will then automatically link or import a table. ( I do it all the time) Your field names might be too long or otherwise incompatible with ODBC. It is worth making a copy of your file and renaming some fields. Then again, you might have repeating fields( bummer dude....) in which case you're stuck. Could you give some more detail about your file structure?

No disagreements here , but I'm not the boss fella.

quote: Originally posted by Anatoli: On the other hand, show me another database, which works in heavy duty usage, and I can still program their scripts and layouts "live" through Internet on 64-128k line! I am unqualified to comment on web usage of FMP. All power to them if it is that easy. quote: Originally posted by Anatoli: No one is really forcing you to do this. You can have just database with layouts and database with data. Just link them. As a matter of fact I am attempting to do just that. Great minds think alike? My primary database has about 180 fields, including the jolly globals needed for variables. It is linked to 6 other files which carry related data. I managed to get most of the UI into the main database, so the scripts would work properly. I plan to send off the whole package as a standalone product. The only thing is the searching. The user needs to be able to do complex serches with AND and OR statements, as well as produce lists. I found that I almost have to create a layout for every type of search ( and include a few more global fields and scripts for each) because FMP will not produce data/reports "on the fly" without adding specific summary fields etc.(unlike MSAccess) My first attempt then ended up with moe than 255 fields. This was a bummer, because now I was unable to pull data out of FMP via Excel. (Incidentally, for complex searches, it is so much easier to connect to FMP from Excel via MS query and pull data out than to create such searches in FMP itself.) I cannot write any of this user interface without access to the data structure. Now pulling that out of the server is not so quick.(You know the drill. Get everybody to log off, shut down the server, go to ANOTHER machine with a proper FMP installation, connect to the server, make a clone,restart the server and repeat the process in reverse when you come back after adding some global fields etc etc.) One of the connected files is 1.2Gb in size. (Don't ask, it wasn't me. THAT baby was designed with no less that 40 repeating fields, and has embedded graphics) Plan B is to create a database just for searching, then I can give FMP all the jolly globals it needs. This one will import/export record Id's on startup, and link the rest. And I should be able to get a bit more freedom with the UI. As I struggle with this, I realise that in the end I will be able to do what I need to do. FMP has never really failed me before, and it will succeed again. But it could be much better.

I love these forums too.. I've learned heaps and haven't lost as much hair as I could have. I'd rather post a reply in a different forum, but I'm not sure which - one of the administrators could redirect this post and/or correct me here. In general, many design changes can be done in your original file. It should not harm your data. I'm not sure why FM should crash, maybe you are producing a calculation it cannot complete, or an endless loop ( ie waiting for a condition that will never happen). As a safety measure, it is always better to fiddle with interfaces away from the data. It is a major issue if you have a multi user database with thousands of records on a server. The recommended approach is to make a copy, compressed if you want to shrink it, or a clone if your data file is very large. You can then try things out in a non critical setting. When all is well import your records into the clone, rename it and make it the active file. (Great system for a single file in one folder with no external relationships or plugins...Appaling for 15 files with 25 relationships and 10 passwords and plugins etc etc.) Hmmm, avoiding crashes... I'm not sure. This probably belongs in a different forum and more detail would be needed

I don't know what hapened to the rest of my message. I tried to edit it and the changes didn't get through. Take 2 I wasn't complaining about the price as much as the lack of really substantial improvements. The hype on the website is way out of keeping with the product. It should have been v 5.04.. ( in my application, I spent a long time working around all the limitations, and 5.5 does nothing much to improve the existing problems.) If you look at the differences between 5.03 and 5.5 it really has a bugfix flavour. 5.5 is still a three legged dog in the same places as 5.03. There are a couple of things you could do easier in 5.5, but if FM Inc. really did want to make a big difference, they would take a look at what plugins are popular. This is what people need and are willing to pay for. This is what needs to be included in the new version. They should tak to these clever programmers and incorporate the code. So bring on proper SQL communication, proper conditional lists, proper event driven scripting, proper variables, proper dialogs, proper file handling , proper OLE support etc etc etc. I long for the day when data can be divorced from the interface, so I don't have to worry about importing/exporting thousands of records, each time I want to change a layout. I salivate over the thought of saving my scripts as a separate entity from the data, and e-mailing them to my friends, so they can import it directly into FMP. I dream of being able to run a query, without having to design 15 layouts in the process. I want to preview the record I just browsed without having to "find all, omit and then show omitted". I want to store variables without having to create global fields which choke ODBC clients... Ooops, apologies.. Did I just spit the dummy? I really didn't mean this to turn into a diatribe It's fun using my "necktop" to create workarounds, and creating solutions. I just wish FM Inc. didn't blow their trumpet so loud, and pat themselves on the back so much - because it aint that good... ( example FMP5.5 >>FM mobile ready - what a joke! FM mobile is a waste of space on my Palm. For half the price you can actually get a proper database in HanDBase or ThinkDB or JFile) I really appreciate FM Inc's efforts, but I'll wait for their next baby, maybe that one will have all the legs.