13 Replies Latest reply on Dec 3, 2012 5:38 PM by RonJennings

    Transfer/move or copy fields between tables

    RonJennings

      Title

      Transfer/move or copy fields between tables

      Post

           Let me start by saying (much to my annoyance) I don't have Filemaker Advanced although I'm not sure if it would matter in this case.

           It's been decided (midstream) that instead of having 4 tables each containing the questions to a specific type of audit  that we will instead have 1 table with all of the questions.  There will be a key/code field in each record that indicates what type of audit each question belongs to and those flags will be used to generate the actual audit.  

           What would be the most painless way (with the standard version of Filemaker) to integrate the 300 or so fields into a single table?  We're still in development so there are really no records to be concerned about.  

           As a possible selling point to encourage the purchase of Advanced, is this someting it supports doing very simply? Along the lines of Copy/Paste (I'm sure there would be some clean-up involved), or Import/Export Field.

           BTW, Phil, I can see you doing the "I told you so." dance and you're fully entitled.  But the frustraiting part is that we started with one plan then The Powers That Be changed it.

           Thanks,

           Ron

            

        • 1. Re: Transfer/move or copy fields between tables
          philmodjunk

               Advanced enables you to copy and paste field definitions from one table to another. This is one of several major advantages to Advanced that jsutify the increased cost, but keep in mind that you only need one such copy to use for developing your database. Your users can stick with the less expensive version as advanced confers no advantages to them.

               Other reasons for Getting FileMaker Advanced:

               Copy and Paste Table definitions (Schema).
               Script Debugger---be able to walk through a script one step at a time while watching layouts change and script triggers trip and watching the data in fields and variables change in the....
               Data Viewer--not only can you watch specific values, but you can build "watch" expressions to check how key calculations such as that inside an If step evaluate at key points in your script while walking through it in the script debugger.
               Database Design Report: Produce a report where you can search it for all instances of a given table occurrence, layout, field name, etc. Search for keywords like "missing" or "unknown" to spot broken references to objects that have been removed from the database.
               Custom Menus--enhance the user interface by adding/removing/changing the menu options and keyboard shortcuts that are available to the user for each given user group and/or layout.
               Custom Functions: build your own functions that calculate results not possible with FileMaker's built in functions.

               But 300 fields????!!!!

               That sounds like way, way too many fields for one table. I suspect that you have multiple fields where you need multiple related records.

          • 2. Re: Transfer/move or copy fields between tables
            RonJennings

                 I've been trying to sell my boss that the advantages of Advanced are more than worth the additional cost but so far I'm not getting any traction :(

                 OK, 244 fields total spread among 4 audit types (83, 91, 45, and 25) with very few of the audit items repeated among the types.

                 The original thought was that each audit type would be in it's own table with 1 field per question and that's how it's currently set up.  User would select the audit type they needed to perform, be taken to a layout for that type, and go about their merry way. 

                  

                 Now the desire is to unify all the questions into a single table (with a key/code field) and generate the desired audit type based on a relationship with the Audit Type table (which has a key/code field for each audit type).  In the Questions Pool table, each record is comprised of Question # (the key/code field for this table), the Audit Type keyfield (linked to the Audit Type table), and the Question itself.   The actual audit is (now) to be done with the Audit Design table which has a keyfield and relationships to Audit Type and Questions Pool to generate the desired type of audit and writes the question responses to the Audit Answers table.  Audit Answers consists of it's keyfield, a relationship to the keyfield from Audit Design,  and the Answer.  It seems like I may be leaving another field off of that but I can't recall (I think relation to the table we're calling Common that contains essentally what you would consider "header" information that is common to all audits  (Auditor, location, Department (being audited), date, etc).

                 When a particular audit needs to be reviewed, it will be looked up against/generated from the information in Audit Answers and I'm guessing Audit Questions & Common

                  

                  

                  

            • 3. Re: Transfer/move or copy fields between tables
              philmodjunk

                   Yes, but why does this result in 244 fields in one table? and WHICH of the many tables you just outlined has all of those fields?

                   When you described the different tables and fields in your last post, you only identifed a very small number of fields for each such table.

                   Something that you might point out to your boss is that Advanced saves him a very valuable commodity--your time. By spending less time manually building/maintaining/debugging this database, you are then freed up to invest your time in other more valuable pursuits.

              • 4. Re: Transfer/move or copy fields between tables
                RonJennings

                     Take the 4 tables (Chemical, Biological, Radiation, and Hazardous Waste)  each of which contains 1 field for each question (83, 91, 45, and 25 questions respectively) and combine them ALL into a single table (Questions).  

                     As I said, the original idea was that each audit type and it's respective questions would be contained in their own table and have their own layout with the Common or "Header" information being at the top of each layout.  That idea has since been scrapped in favor putting ALL questions in a single table from which the audits will be created.   I think things have been made unnecessarily complicated but that's what I have to deal with now.

                     I've told my boss that.  So far he either just isn't interested or he's putting me through the "painful methods/repetition teach lessons" thing.

                • 5. Re: Transfer/move or copy fields between tables
                  philmodjunk

                       Each question should be a separate record in the same table, thus no need for so many different fields for managing the questions.

                       Having a different field for each question will be extremely cumbersome to work with.

                  • 6. Re: Transfer/move or copy fields between tables
                    RonJennings

                         DOH!

                         OK, reading your reponse several times and looking at some notes (pics on my phone actually) just helped me understand something I wasn't getting when my boss was walking me through the design which is exactly what you're describing.  I now see a much easier way to skin this particular cat.

                         The joys and frustrations of being a newb developer.

                         Back to the original question though, is Advanced the only easy/sophisticated way of copying/moving fields between tables?

                         Thanks for (again) reducing my annoyance/stress level.

                          

                          

                    • 7. Re: Transfer/move or copy fields between tables
                      philmodjunk

                           Without Advanced, you can only copy the field's name as text to the clipboard for pasting as a field name into another table. I suppose you could copy a calculation field's name to the clipboard, paste it into a field defined as text in the new table, then return to the original table, copy it's calculation expression, return to the new table, change the field to calcualtion and then paste the copied expression...

                           Ick.

                           Two other tacks that you might take with any ongoing compaign to convince your boss to spend the $$ on advanced:

                           Research custom menus and devise a possible layout design that exploits a custom menu in a significant way, then point out that you can't create the custom menu without advanced. (But once created, fileMaker Pro users can use the custom menu just fine.

                           Quote me on this observation:

                           I strongly recommend FileMaker Advanced for developers that are new to scripting because the script debugger/data viewer combination is a very effective teaching tool for learning how scripts work and don't work in FileMaker. For one thing it is a very good way to see how a script can trip script triggers added to the layouts in a database. For another, they illustrate how "table context" as determined by the current layout's reference to a table occurrence in layout setup | Show Records from affects what values in different fields of different tables are accessible at any given moment during the script's execution.

                      • 8. Re: Transfer/move or copy fields between tables
                        RonJennings

                             Oh trust me, I considered the text-based copy/paste method and it made me so ill I started this thread.  The added aggrivation of bringing over all the other field properties just compound things.

                             RE: the campaign for Advanced--I don't understand why Filemaker doesn't have some snazzy videos that clearly show the features in Advanced.  The  Product Comparison feature list w/out a demonstration just doesn't sell Advanced effectively.

                              

                        • 9. Re: Transfer/move or copy fields between tables
                          philmodjunk

                               Even better yet would be a 30 day trial of FMP Advanced so users can see for themselves. I'd be OK with that even if they felt that they had to cripple the ability to produce runtimes via the trial.

                          • 10. Re: Transfer/move or copy fields between tables
                            RonJennings

                                 Yeah, we talked about a trial version in a different thread & I agree that it would help a lot.

                                 Maybe find a way to code the  Trial version so that you had to enter a current license code for the Standard version to make the Advanced trial work, the Advanced version has to check in with Filemaker when it starts up (and periodicly while it is running) to see if it's within the 30 days otherwise it won't start or shuts down.  Maybe disables the Advanced features..? 

                                 Quark and Adobe have done simiar things to control licensing in the past.  I don't even wanna talk about the "unique" ideas SAS has regarding software licensing and deployment.  I'll say that the developers behind it should be sitting beside people that talk in the theater.

                            • 11. Re: Transfer/move or copy fields between tables
                              vickyn

                                   I feel like I need to do what Ron needs to do: upgrade from regular FileMaker Pro to Advanced. Does anyone know if there is a way to move laterally without plunking down the $500 that a full copy of Advanced costs? Especially since I already paid $300 for the full version of regular FMP.....

                                   I can't think Ron and I are the only people who'd want to do this.....

                              • 12. Re: Transfer/move or copy fields between tables
                                RonJennings

                                     Vicky: I'm pretty sure the answer is Yes.  Contact someone in Sales for the specifics on your license:

                                     http://www.filemaker.com/company/contact/

                                • 13. Re: Transfer/move or copy fields between tables
                                  RonJennings

                                       https://store.filemaker.com/US/ENG/RTL/product/view/group/PTA/

                                       Select Upgrade.  Looks like an additional $300.  

                                       It looks like if you already had Filemaker 9-11 (even Standard version) that you could upgrade to Advanced version of FMP 12 for $300.