9 Replies Latest reply on Jul 19, 2011 1:43 PM by DgWater

    Autofill linked/portal  field, when data entered in other field

    DgWater

      Title

      Autofill linked/portal  field, when data entered in other field

      Post

      Hi

      I need to autofill data into a linked file (data). The field "parameter" ,needs to be auto-entered when a data is entered into another field "text" or "numeric"  through a portal .   I currently have multiple files - one is called "data" another "events" another "locations"  and on..  So for a particular event, the user can view the data collected via the event file. I use a portal filter to view the specific data for the specific event. This is currently working.

      Unfortunately I can't seem to figure out when new data is entered - I am not able to have the "data type" auto enter. There are many different data types.  I have attached an IA diagram that might help explain what I am trying to do. 

      Look forward to any helpful suggestions. 

      thanks!

      Watershed_Database_IA_diagram.jpg

        • 1. Re: Autofill linked/portal  field, when data entered in other field
          rjlevesque

          Why different files? This can all be done in one FMP solution using different tables, which would make your data manipulation much easier and more reliable.

          • 2. Re: Autofill linked/portal  field, when data entered in other field
            philmodjunk

            I agree that I don't see a pressing need here for separate files for each table unless you are using FileMaker 6 or older.

            By parameter, do you mean the "parameter ID" field? (Not trying to be picky, but details are important here.)

            You indicate that you want a value entered in this field when "data is entered into another field "text" or "numeric"". What field and how does the value entered determine what value is entered in Parameter (Parameter ID?). And is this all taking place in fields defined in the Data table? Or is this "other field" defined in a different table, and if so, which one.

            If all the fields are defined in the same table, it should be possible to set up an auto-enter calculation to enter the needed value. If the "other field" is in a related table, you will likely need to use a script and script trigger (If you have FileMaker 10 or newer) to produce the updated value that you want.

            • 3. Re: Autofill linked/portal  field, when data entered in other field
              DgWater

              Thank you to both of you for your quick feedback. I am using Filemaker 11.

              - > "Why different files? This can all be done in one FMP solution using different tables, which would make your data manipulation much easier and more reliable."

              There are couple of reasons. Originally the database was just one file with 60 different fields of data.  The main reason for the current set up is a few years ago the database was converted to a mysql database to display data online. The programmer at the time - "normalized" the data and created the different tables as shown on the diagram. Unfortunately it was slow and cumbersome to enter data online.   Today I want to be able to create a filemaker database with the data (for easier updates/reporting, data-entry)  and communicate data back to the mysql database for online retrieval.  

              The database needs to track 75 different data types  (stream/water quality database) at different streams (50 and growing). The database contains data going back 10 years.   How do you know when you are using too many fields in one filemaker database file?

              So those are my considerations.  I was tempted to go back to a single file with different tables if you think that makes sense and that would still be able to communicate with the MYSQL database. (exchange data)

              If I go with separate files approach  - the questions was asked  "You indicate that you want a value entered in this field when "data is entered into another field "text" or "numeric"". What field and how does the value entered determine what value is entered in Parameter (Parameter ID?). And is this all taking place in fields defined in the Data table? Or is this "other field" defined in a different table, and if so, which one."

              _> depending on the type of data being entered it will be either numeric or text (different fields provided for each in data table). The parameter table provides a list of all of the different types of data with an id and name (example "water temperature"). The user does not create new parameters.  I am not sure why this table "parameters" was created by the programmer. I am trying to use the "event file” to design a data entry form that is similar to what is used in the field, to place the data collected into the data file. I can view the data using portal filtering, I am just stuck when entering data - putting the correct parameter id with the data. - SO maybe based on your comment  PhilModJunk I can use script and script triggers. 

              Again - thanks for the quick and helpful feedback. i look forward to reading your responses. 

              • 4. Re: Autofill linked/portal  field, when data entered in other field
                philmodjunk

                As to separate files, you certainly can merge all of this data into a single file and you can still export this data to different tables in MySQL. That may or may not have been possible when your original programmer set this up. If you choose to make this change, however, you'll need to both import the data with the new table option and then redirect data source references in Manage | Database | Relationships to use the new table instead of the original file's table. If any of the separate files contain scripts or layouts that you need, you'll have additional work ahead to move all of those elements into your combined file.

                I think you want to add fields to your events layout for collecting data about each event. The simplest way to do that is just to add the fields in your events table and then use them on your layout. Given your current setup, you can place a portal to Data on your events layout and include the Data::parameter ID field formatted as a drop down list or pop up menu in this portal. Set it up with a value lists so that you can select a parameter by name, but the value list really enters the Parameter ID instead to identify the type of value. You can set up this kind of value lists if you define it to list ID numbers from Parameters::ID as field one and Parameters::Name as field 2. The user sees and selects by name, but the value list enters the ID to link the Data record to a specific Parameter record. You then add the numeric value and text value fields to this portal. If you only want to put one of these fields in your portal, you could just add the text value field and add an auto-enter calculation to copy the value entered in the text value field into the numeric value field.

                • 5. Re: Autofill linked/portal  field, when data entered in other field
                  DgWater

                  Okay - based on the advice here i am going develop the solution using one file and possibly multiple tables within the file. But I have a question, I have over 60 different fields of data for the data file - how do I decide on to place them in separate tables? Or is this a judgement call -and is there a good resource about the logic of using tables and when to divide fields from one  table into multiple tables and when to use separate files instead?

                  Again thanks for all of the great feedback. 

                  • 6. Re: Autofill linked/portal  field, when data entered in other field
                    philmodjunk

                    You've definitely asked the right question and it's one new developers often over look. It's important to map out the basic structure of your database before you dive in and start creating tables, layouts, scripts etc. That gives you a road map to follow and makes it easier to set something up that doesn't have a lot of potential problems inherent to its basic table structure.

                    Think of each record as a 3x5 note card and a table represents a stack of such note cards. How you divide up that ntoe card into sections where you store different pieces of data represents the fields you define within that table. The key is to map out a structure where you don't need to enter the same exact information twice. If you have a list of customers, you'll want a table of customer information where each record (note card) records the information for one customer. Then on a sales invoice, you can set up a link to that record instead of re-entering all the same information for the same customer every time they buy something from you. Thus, each table should be structured so that each record records information about one specific "object" with relationships linking that table to other tables such that you can combine data from the different tables in ways that allow you to use your database effectively.

                    Think about what data you need to record and what logical categories this data naturally falls into. Ask yourself what tasks need to be supported by your database and how that recorded data will need to be used to do so. Map out at least the main outline of any reports you will need and any key questions you know your users will need to use your database to answer.

                    Best approach is to map it out on paper first and then be careful to revisit this design concept regularly as you develop the database as you may find you need to modify your original ideas as you develop the system.

                    There are two common mistakes that new developers make:

                    Putting data in one table that should be divided into two or more related tables.

                    There are two common symptoms of this mistake. If you find you'll need to enter the same information over and over again in different records, you likely can use a single record in a related (parent)  table and avoid the redundant data entry. If you find yourself adding a repeating field or a series of individual fields that store the same information, then you likely need a related (child) table for that informaiton and in many cases you will set up a portal to that set of related reocords so that you can see and edit this data in a group.

                    Putting data into two or more tables that should be combined into a single table.

                    This is a more subtle mistake that is more crucial in FileMaker than some other database systems since we can't construct "union" queries to combine data from separate tables for a report. If you have two or more tables where all or most of the fields are for storing the same exact data, you may be able to use a single table, but rely on sorts, finds, portal filters etc. to work with different groups of records from the same table. A classic example of this is when a database develops a contact info table for customers and then develops a second contact info table for vendors. Both tables record name, address, email etc data and thus the same info can be stored in one table with an extra field added to identify each contact as either a customer or a vendor. The two tables becomes a significant issue for the database user when they discover that one of their vendors is also their customer and they have to enter and update their contact info in two different records in two different tables...

                    • 7. Re: Autofill linked/portal  field, when data entered in other field
                      DgWater

                      thanks for helpful responses.

                      • 8. Re: Autofill linked/portal  field, when data entered in other field
                        rjlevesque

                        You know, you could also make your database a client to the mySQL backend. have it communicate directly to your mySQL server live instead of syncing up each time.

                        ----------------------------------------------------------------------------------

                        Also, going to throw in a nice time saver if you build a lot of stuff with mySQL backends. I got really tired of typing out the same stuff over and over with each new project for my SQL backends. Then I found App Gini! It creates your entire mySQL database structure from your existing tables you export from FileMaker. So instead of taking days or months it creates the base mySQL framework in minutes.

                        http://www.bigprof.com/appgini/

                        Had to throw that in, it's an excellent time saver!  =)

                        I always like to share handy developer tools I find...

                        RJ

                        • 9. Re: Autofill linked/portal  field, when data entered in other field
                          DgWater

                          Thanks for the additional follow up. That is - a great idea, and the the App Gini - sounds extremely useful !

                          Dg