8 Replies Latest reply on May 27, 2010 8:49 AM by Jon1009

    Best way to layout my database

    Jon1009

      Title

      Best way to layout my database

      Post

      Hi there pretty new to file maker and just wondering what the best way to set out this datebase would be

       

      The first thing we do is login the sample which has a unique ID but the sample can be tested for about 7 different things could be (1 or could be all 7) what would be the best field setup for this would it be once field with checkboxes or 7 seprate fields for each different test.

       

      The next thing I need to do is enter the test result for each test. Would it be wise to have a different table for each test? 

       

      hope that makes sense.

       

      jon

       

       

        • 1. Re: Best way to layout my database
          philmodjunk

          Jon1009 wrote:

           

          The first thing we do is login the sample which has a unique ID but the sample can be tested for about 7 different things could be (1 or could be all 7) what would be the best field setup for this would it be once field with checkboxes or 7 seprate fields for each different test.

           

          A better approach may be just to set up a portal of related records for recording your tests and the results. Since this is in a portal, you could have 1 test, 7 tests or, if your needs change in the future, more than 7 tests and their results

           

          The next thing I need to do is enter the test result for each test. Would it be wise to have a different table for each test? 

           

          I'd keep all test results in one table if at all possible. It makes for easier reporting if you need to report all the results for a given sample or group of samples. You very likely will need a test table that describes the needs and parameters of each type of test, but the test ID's and results should be recorded in a combined table. 


           


          • 2. Re: Best way to layout my database
            Jon1009

            Thanks for the input managed to get abit further.

             

            Got another problem,

             

            I've sorted a report to list all the samples that need test results entered, each record has a "Add Test Result" button next to it

             

            what i am trying to achieve with a script is once the buttons click

             

            it Goes to a layout, added a new recorded and then Sets the Sample ID in the Tests tables to the same as the one listed in the Sample login table.

             

            The script I have is

            Go to Layout ["Tests" (Tests)]

            New Record/Request

            Set Field [Tests::Sample ID[Sample Login::Sample ID]

             

            Any help would be great

             

            Jon :)

             

             

            • 3. Re: Best way to layout my database
              philmodjunk

              In your script, I see two problems with this step.

               

              Set Field [Tests::Sample ID[Sample Login::Sample ID]

               

              First, the extra square bracket [, suggests you've entered Sample Login::Sample ID in the repetition box instead of clicking the second specify button and entering the expression there.

              Second, this won't work as written anyway, because the change in layouts breaks the connection to your Sample Login table and this expression thus won't return a value.

               

              Instead, do this:

              Set Variable [$ID; Sample Login::Sample ID]

              Go to Layout

              new record/request

              Go to Layout ["Tests" (Tests)]

              New Record/Request

              Set Field [Tests::Sample ID; $ID]

               

              You liikely do not need a script for this at all.

               

              If your portal uses the sample ID field in its relationship, you can simple enable "allow creation of records via this relationship" and then entering a result in the portal's bottom blank row will automatically create a new record and enter the sample ID value for you.

              • 4. Re: Best way to layout my database
                Jon1009

                Couldnt get it to work using the portal but got it working with the script. must be the way ive set it up

                 

                Ive got my main table which holds the Sample ID and the result for each test (Fields Test 1 Result, Test 2 Result etc for all 7 tests) then ive got a table for each test with the other information in like test date, test do by. Using the sample ID as the relation. (not sure if thats the best setup) Seems to work ok but iam having trouble with the report

                 

                The problem ive got now is that 3 of the test get reported on the same report but the sample could just be for 1 or 2 of the test. Is they away around this or do I need to create a report for each combination?

                 

                Hope that makes sense abit hard to explain

                 

                (Thanks very much for you help)

                 

                 

                • 5. Re: Best way to layout my database
                  philmodjunk

                  To allow creation of records in your portal, you open Manage | Database | Relationships and find the relationship line linking Sample Log In and Tests.

                  Double-Click this line to pop up the Edit Relationship dialog. There's a set of check boxes at the bottom. On the Tests side of the dialog click the "Allow creation of records via this relationship" check box.

                   

                  Now return to your layout and try typing text in the bottom blank row of your portal.

                   

                  "The problem ive got now is that 3 of the test get reported on the same report but the sample could just be for 1 or 2 of the test."

                  I don't quite see the problem there. Your portal should only list the tests you actually ran on that sample not all possible tests.

                   

                  For a more flexible and sophisticated report, you can make one on a layout based on your tests and place fields from the Sample Log In table in either a header or a sub-summary part.

                   

                  Here's a link to a simple tutorial on setting up summary reports that you may find useful: (Think of sample log in as an "invoice" and your test records as "line items".)

                  Creating Filemaker Pro summary reports--Tutorial

                  • 6. Re: Best way to layout my database
                    Jon1009

                    Hello got it working that way had to changed the table layout has it had it in abit of a mess

                     

                    Another question is iam trying to do a If statment but cant get it to work

                     

                    I have a list of all the tests and want to create a new record IF the Tests has ASP

                     

                    If[Logbook::Test = "ASP"] works but not if other test are slected ive tried using * but couldnt get it to work

                     

                    Cheers

                     

                    Jon

                     

                    • 7. Re: Best way to layout my database
                      philmodjunk

                      Is Logbook::Test a check box formatted field where "ASP" is one value in the check box list?

                       

                      If so, try this expression:

                       

                      If [Not IsEmpty ( FilterValues ( Logbook::Test ; "ASP ) ) ]

                      • 8. Re: Best way to layout my database
                        Jon1009

                        Thanks that worked great

                         

                        cheers

                         

                        Jon