3 Replies Latest reply on Oct 3, 2011 12:48 PM by philmodjunk

    Copy Fields in Column to Clipboard; Delete Multiple Rows



      Copy Fields in Column to Clipboard; Delete Multiple Rows


      I'm evaluating FileMaker Pro 11 as an alternative to Microsoft Access. I need to convert because I am approaching the database size limit on Access, and some limits to functionality. FileMaker Pro looks attractive, but I've found two limitations that are critical to my workflow needs. I have not yet found a satisfactory answer in the knowledge base or in the forums.


      I have multiple uses of relational databases, some using the relational model, some not. My most common use is to use the database as a flat file staging area to prepare text data for import into a fully relational system. In doing this, I import a lot of "junk" records that I need to delete in bulk with a minimum number of mouse movements and/or keystrokes. I repeat this action hundreds of times a day, involving thousands of records. So moving a mouse thousands of times to a delete record button, deleting them one at a time isn't going to work. I need to use a database for this rather than a spreadsheet for many reasons - I may import hundreds of thousands of records; I need the convenience of key integrity to automatically weed out duplicates; and I need the software to treat the imput as straight text wothout having to go through multiple steps with each batch of appends (e.g. Excel - one must use the "append as text" command each time, that uses multiple entries and a cursor trip to the menu bar each time; and on batch conversions will stubbornly forget that cell properties have been set to text and convert, for example, page ranges to a formula calculation).


      Once I have text files in the database, I need to select multiple sequential fields in one or more columns, on the fly, and copy them into the clipboard. I then paste these into a spreadsheet or word processor for further transformations before re-copying and pasting them back into the database as unique records.


      Both of these are simple and straightforward in Access. Access allows one to select blocks of fields from sequential records and from multiple columns into the clipboard as delimited data, which can be pasted easily into other programs. The key is ease - in Access this is done with shift+select (with mouse or arrow key). Access also allows one to select multiple rows by the same method, and delete them all at once. Of course, one can set this up to enforce or not enforce transaction integrity, so that if a parent record is deleted, child records are also deleted. But I'm not using it that way - again as a simple flat file. And again, for those who might reply, why not simply use a flat file, I refer you to the limitations above, and the need to have more powerful filter and view technologies to support the workflow.


      Are these functions something that can be done in FileMaker Pro with a script that can be integrated into the normal behavior of the program? Ideally the script would work through keystrokes and mouse movements/selections. Any solution that would force hundreds of trips across a large screen to a button would not work.








        • 1. Re: Copy Fields in Column to Clipboard; Delete Multiple Rows

          It's possible but no where near as simple to set up in FileMaker like it is in Access and will take lot of trial and error to work out all the details.

          Here's a general outline of an approach that I think will work for you, but you'll need to experiment and see if it works.

          Fields can be formatted as buttons so clicking a field can perform a script just like clicking a regular button. The script can use Get ( ModifierKeys ) to detect whether you are also holding down a modifier key such as shift. The script can load global variables with the name of the clicked field (name can be passed as a script parameter) It can also load a different variable with the record's serial number field (primary key). Thus, it would be possible to click one field to start the selection, then hold down shift and click a second field in the same record and then a field in a different record to select a block of fields spanning more than one record. On the third click, the script would loop through records and fields moving this data one field at a time into a global text field, separating fields with the tab character and separating records with return. It can then copy the contents of the global field to the clipboard for pasting into a different application such as a spreadsheet.

          To paste into a different Filemaker table, you'd have to run this process in reverse, pasting into a global text field and running a script to parse the pasted date into new records with the correct data inserted in each relevant field.

          • 2. Re: Copy Fields in Column to Clipboard; Delete Multiple Rows

             Thanks for the answer to the field copying issue. I'll give it a try. It may be too bulky to be useful for me (this is such a repetitive task that I can't afford many slowdowns on data management). I'll see how this solution works. Still looking for the multiple record delete solution.



            • 3. Re: Copy Fields in Column to Clipboard; Delete Multiple Rows

              Delete All Records is a script step that deletes all records in the found set as does Delete Found Set when manually selected from the Records menu. If a similar script isolates the selected group of records in a found set, this will delete them in one go.

              A looping script can also be set up to delete a set of contiguous records one record at a time, but fast enought that you see the whole set disappear on the second mouse click. (CLick one to select the starting record, click two to kick off the delete.