1 2 3 Previous Next 39 Replies Latest reply on May 19, 2011 3:14 PM by mgores

    portal to data table



      portal to data table


      I am trying to create some testing forms, for each job there will be a filemaker file that the report is written in that contains layouts to show the test data.  There are several tests for each job, which ones vary from job to job.  There are varying number of samples for each job.  The number of data points per sample/test can vary from 1 to several hundred.

      I was figuring it would work to have a data table and a report table, with the layouts for the test forms having portals to the data table.

      A typical test result form has 6 columns of 20 blocks to enter the data.  When filling them out by hand it is easy to accomodate variations by labeling each column with a sample number for tests that have less than 20 entries/sample or multiple columns with the same sample number if there are more than 20 entries/sample.  I have figured out a way to make 6 portals for the six columns, the first one starts at record 1, showing 1; the second starts at record 2 showing 1; etc.

      Is there a way to have the portals see if there are >20 records to spill those over into the next portal, then if > 40 into the next and so on?


        • 1. Re: portal to data table

          have you tried using filters in the portal?

          You might try:

          For the first 20= get(recordnumber)≥1 and get(recordnumber)≤40

          next 20=get(recordnumber)>40 and get(recordnumber)<60


          • 2. Re: portal to data table

            There could be cases where the number of tests per sample are less that 20 (say 5), so we would want each column to have 5 rows.  But then there could be cases where the number of tests could be 140, so we would need 2 copies of the layout (one with 6 columns of 20 and the other with 1 more column of 20).

            Maybe a separate table and portal is not the way to do this?  Maybe it would work better to just have 120 data fields, with Sample# fields at the top of each column, then create new records if multiple sheets are needed.

            • 3. Re: portal to data table

              Sounds like you want columns of data with 20 portal rows in each column. THere's a simple way to configure your portals to do this.

              Open portal setup for each portal and change the row settings at the bottom.

              For the first portal, select "Initial row: 1, Number of rows 20"
              For the 2nd portal, select "Initial row 21, Number of rows 20"

              and so forth...

              • 4. Re: portal to data table

                The problem is that if there is a sample that only has 8 measurements, I want that column of 20 to only show the 8 rows of data with the remaining 12 below it being blank.  The next column would then show the 8 rows of the next sample.

                If the sample has 21 measurements it would take all 20 of the first column plus the first row of the next column with the next 19 rows blank.  The second sample would then take the third column plus the first row of the fourth, etc

                I am starting to think it would be easier to have a separate table for each data sheet type, with a separate field for each data blank on the form.  That way there could be separate records for each type of test.  i.e  all of the samples would probably fit on one visual inspection form, but would need several mechanical test forms for each sample.

                • 5. Re: portal to data table

                  So Instead of writing a fixed number in the filter calc:

                  get(recordnumber)≥1 and get(recordnumber)≤40

                  Use a Calculation within the calculation to determine the intervals in which they should split.

                  With your example of 8. You either have a field that has how many measurements are in the sample (either by calculation or manual entry) or you have a global field you set when displaying the layout.

                  Then setup each portal (column) as follows:

                  get(recordnumber)≥1 and get(recordnumber)≤MEAUREMENT_NUMBER_FIELD

                  get(recordnumber)>MEAUREMENT_NUMBER_FIELD and get(recordnumber)≤MEAUREMENT_NUMBER_FIELD*2

                  get(recordnumber)>MEAUREMENT_NUMBER_FIELD*2 and get(recordnumber)≤MEAUREMENT_NUMBER_FIELD*3


                  • 6. Re: portal to data table

                    Or give each set of records a common value and filter for it in each portal such as some kind of Sample ID.

                    • 7. Re: portal to data table

                      I added a screenshot of a typical form up above.  I just placed fields in for the first column of data readings, each reading has 3 fields for wire#, force, code.

                      This would work well for any job that had =< 6 parts that had =<17 wires/part.  It is not uncommon however, to have parts that have more than 17 wires or more than 6 parts per job or a combination.  I have used the "horizontal" portal before in a different type of application, but am having trouble getting it to work here and not sure it is the best way after all.

                      The main issue is that the form needs to be flexible as to the number of samples and data points per sample.  When using paper forms it is easy for the technicians to just fill in the details by hand and make as many copies of the blank form as needed to record the data.  We are starting to try to make them electronic so that the techs can fill them out at local terminals or ipads. 

                      • 8. Re: portal to data table

                        What issues do you see with setting each column to filter by Sample#?

                        You can define a field for each column where you enter or select a sample number. You can then enter data into the portal to record measurements for each sample in different columns. You can even work with more samples than you have columns as you can select different samples for different portals as needed.

                        There are ways to auto-enter the correct Sample# into the portal records when you create a new related record in the portal by entering data into the next blank row in the portal.

                        Example with just two filtered portals:

                        One relationship to be used for both portals:

                        PullTest::TestID = WireSamples::TestID  //Enable "Allow Creation of records... for WireSamples in this relationship

                        Two Sample Fields, Sample1, Sample2

                        Set a filter expression of PullTest::Sample1 = WireSamples::SampleNo for the first portal and PullTest::Sample2 = WireSamples::SampleNo for the second portal.

                        Define this auto-enter calculation for SampleNo:  $$SampleNo

                        Write this script:

                        Set Variable [$$SampleNo ; Value ; Get ( ScriptParameter ) ]

                        Set an OnObjectEnter trigger on both portals, pass PullTest::Sample1 as the parameter on the first portal and PullTest::Sample2 as the paramter on the second. This script loads the global variable $$SampleNo with the correct value so that it is in turn auto-entered when you add records in the portal.

                        • 9. Re: portal to data table

                          One of the issues is that if there are more measurements in a job than spaces on the sheet, either due to # of samples or # of wires/sample, how would I get it to go into a new record of that layout showing the continuation of the data.  If I set it up to be 1 portal row per column, 1-6, the create a new record in the pulltest table, won't the new record just show the same portal rows, 1-6, not 7-12?

                          Or are you saying to have a pull test table with a data child table?  That way there would only be up to 6 child table records per pull test record, and only enough pull test records created to accomadate the number of measurements.

                          • 10. Re: portal to data table

                            All the portals would be set to show rows 1 to 20 or whatever maxiumum number of rows you choose for your layout. The portal filter would control which sample records appear from the portal table in each column not the row number.

                            In fact, you can enable the scroll bars and have as many data points for a given sample as you need.

                            • 11. Re: portal to data table

                              Actually the scroll bars won't work, as we want the form to look as it will print out.  With the users used to having blank forms printed and filling in the data by hand it would be easier for all involved to make the layout look the same as the paper form.

                              Doing some work over the weekend, it looks like it will work to use a one to many to one table set up.

                                             Test A form

                              report      Test B form           Data

                                             Test C form


                              So that the technician doing the test goes into the report file, find the layout for the test he is doing as he enters data a new record is created in that form table with a reportID key field and in the Data table using a TestID key field.  When the form is filled up and a new one is needed a new record is made in that form's table and as the data is entered it is related to that TestID.  At the end we can go to each layout and print all records and the proper # of each form will be printed.

                              • 12. Re: portal to data table

                                I think you need to take a step back, the solution is probably a lot easier than what it seems.

                                Is your primary concern data entry for all this portal stuff?

                                Data Entry is not the same thing on a computer than it is on a sheet of paper. On a sheet of paper, the way you input it is normally the way you ultimately visualize it (it is data entry and report at the same time). In a database, you can input record by record. Then when you are done with all the data entry, you can create a script to take you to a layout that displays it differently. For example, there is one report I was asked to produce that had so many different variables from so many different places in the database, I opted to write a script that basically mimics a person searching for info and writing it in specific fields on a layout. I even created a table to put the data in and display the report.

                                So you can create a layout that first asks for how many sets, or tests, or whatever they are going to input. Then take them to the approriate form layout where they will do the data entry. Then when they are done you can have them clic a button that displays the info all together as a report.

                                • 13. Re: portal to data table

                                  Actually the scroll bars won't work

                                  Please note that this was an option if you want the greater flexibility this offers. You do not need to use them if you don't want to, the portal filters on each column will still work. And the layout you use for data entry need not be the layout you use for report purposes.

                                  • 14. Re: portal to data table

                                    Phil,  I was trying to think ahead to the printing of the reports and avoid having to go through and insert blank fields or records to get the data to look right.

                                    Using the screen shot above, imagine fields filled in for all 6 columns instead of just the first one.  Now if a job requires 3 measurements for 5 parts, we would want the the data in the first 3 lines of the first 5 columns.  If a job required 30 measurements for 5 parts we would want the first part to take up the entire first column and 13 of the second, the second part to take all of the third column and 13 of the first, etc. and it would take 2 form records to show it all.

                                    I cannot see an easy way to write a portal filter to break the data up that way.  What I am trying is to set up each column as a portal showing only 1 record with column 1 showing the first record, column 2 showing the second.......   Then creating a new form record for cases where I need more columns.

                                    Does it make sense (will it work) doing it that way?

                                    @ Charlie  The primary concern is that the data gets entered correctly and displayed in the final report correctly.  The issue with the data displaying in the final printed form is what I am struggling the most with (unless the solution I am trying proves out).  The main issue is that there are many possiblities of the # of parts tested and the # of tests per part.  There are also many different forms that will end up in the report, the example of the pull test is the one with the most potential data per part.

                                    1 2 3 Previous Next