1 2 Previous Next 16 Replies Latest reply on Jan 6, 2012 8:01 AM by NanetteHartley

    only one portal row allows entry

    NanetteHartley

      Title

      only one portal row allows entry

      Post

      We are running an analytical lab.  We get samples in to test for various things.  We never know what the samples will be, or what type they are, but need to record that information.  So, I have a  table called "Requests", and one called "Data Entry."  I have a portal on the Requests layout that shows data from the "Data Entry" table.  The link is Requests::Sample ID --> Data Entry::Sample ID.  It is set to allow the creation of records in the Data Entry table.  

      Ideally, we would enter the information on the Requests layout, specifying the Requestor, the information on the job or project, and information about the samples.  We then need a Data Entry record for each sample.  I want to have most of the information that was entered on the request show on that Data Entry record, but don't want to have to enter it again. 

       

      I several problems with my portal, all of which probably have the same source:

      1.  the sample ID is supposed to be an auto-entered, serial number (it is defined this way on the Data Entry table, and the portal has that field on it).  It is listed as the first column in the portal.  It does not populate when the record is filled in for the rest of the data.

      2.  No record is created in teh Data Entry table after entering data in the portal. 

      3. After entering in information for the first portal row, I cannot enter anything into the other portal rows.

       

      I know I have missed something really simple here, but can't for the life of me figure out what.  Thanks!

      screen_shot_of_portal.JPG

        • 1. Re: only one portal row allows entry
          philmodjunk

          It would appear that your portal is based on Requests instead of data entry. Either that or the fields in the portal--especially sample ID are from Requests instead of data entry.

          Enter layout mode and look for text displayed in the bottom left corner of the portal. I suspect it reads "Requests" instead of "data entry". If so, then double click the portal to open Portal Setup and change the "show related records from" drop down to select Data Entry instead of Requests. Then exit the portal setup by clicking OK. Click each field in turn and check the "display data from" box in the data tab of the inspector to confirm that the field is from data entry rather than requests. If it is from Requests, double click it and re-specify it to be from Data Entry.

          • 2. Re: only one portal row allows entry
            Markus Schneider

            check the fields in the portal... wrong table?

            • 3. Re: only one portal row allows entry
              NanetteHartley

              OK, I moved all the fields I had in the portal that referred to Requests and put them on the Data Entry Page.  It seems to be working in this configuration.  

               

              Now, one more related question:  We would like to have each sample ID be unique.  We also want it to relate to the request number.  The request number is in the format of 12-0001, where the first two digits represent the year and the number goes up sequentially.  That works great.  However, we would like samples 1-4 of request 12-0001 to be samples 12-0001-001, 12-0001-002, 12-0001-003, and 12-00001-004.  Then if we have request 12-0002, we want samples 1 and 2 to be 12-0002-001 and 12-0002-002.  Is there a way that can be done?  I am perfectly fine with it being a combined index key, but don't really know how to set that up, or to have the full index key show up on the Data Entry page.  

               

              Thanks again for the help!  

              • 4. Re: only one portal row allows entry
                philmodjunk

                Don't use such fields as a keys in relationships. Keep to a simple serial number for that purpose. Even 12-0001 is not a format I recommend for fields used as keys in your main relationships. You can put such fields in place in your database as "label" fields so that they can appear on reports and be used for finding/sorting records, just don't use them for relationships.

                Is it possible that you will (either now or in the future) have two or more users entering such data at the same time? What you are requesting is much simpler to set up with single user databases. With multi-user systems, care must be taken to ensure that two users don't produce different records with the same number when you use such a system as this.

                • 5. Re: only one portal row allows entry
                  NanetteHartley

                  PhilModJunk,

                  Thanks for the input.  You are correct; we do have two people that do entry on the requests, and it is very possible that they would both be doing entry at the same time.  I will use the relationships as single digits and have labels for our sorting purposes.  

                   

                  Thanks again. 

                  • 6. Re: only one portal row allows entry
                    NanetteHartley

                    How can I pass the portal row number to the Data Entry page to create such a label?

                    • 7. Re: only one portal row allows entry
                      philmodjunk

                      I've actuall mentioned two different issues--the duplication and "meaning added" Id numbers. Using these numbers as labels--not relationship keys solves the second issue, but you still will have an issue with avoiding duplication if two users log data for the same sample (Request) at the same time.

                      I'd use separate number fields for the sequences and a calculation field that uses these numbers to produce the final label value with appropriate leading zeroes.

                      Requests::SampleNumber and Data Entry::DataNumber

                      Then this calculation adds the additional data:

                      Right (Year ( Get ( Currentdate ) ) ; 2 ) & "-" & Right ( "000" & Requests::SampleNumber ) & "-" & Right ( "000" & DataNumber ; 2 )

                      Make this an auto-entered calculation into a field of type text. Specify a Unique values validation rule to catch cases where two users accidentally generate the same DataNumber for the same sample.

                      Requests::SampeNumber can be an auto-entered serial number. At the beginning of each year you can manually reset the next serial value back to 1 or you can use a script to reset it.

                      For getting the next dataNumber in the sequence for a given sample, add a self join relationship linking Data Entry records to a second occurrence of data entry records by SampleID:

                      Data Entry::SampleID = SameSampleData Entry::SampleID

                      To create SameSampleData Entry, click Data Entry in manage | Database | relationships to select it, then click the duplicate button (two green plus signs). Double click the new 'box' that appears to rename it like I have here.

                      Now you can set up an auto-entered calculation for DataNumber as: Max ( SameSampleData Entry::DataNumber ) + 1. Be sure to clear the "do not replace existing value" check box or it will not update correctly.

                      • 8. Re: only one portal row allows entry
                        NanetteHartley

                        OK, so I have tried this and it's not working for me.  

                         

                        I have the relationship Requests::SampleID --> DataEntry::SampleID, which allows creation of records for DataEntry.  I have defined the values as above, and that did nothing.  This is probably because we have no data in the system yet (we are not moving over our old data to the new database).  So, I changed the DataNumber calculation to be: If ( IsEmpty ( Same Sample Data Entry::_DataNumber ); "1";  (Max ( Same Sample Data Entry::_DataNumber ) + 1)).  This gives me a value for the first sample.  If I go to add a second sample to the same request, then I get a message saying the value is not unique and therefore cannot be used.  It works fine if there is only one sample per request, but that is not normally the situation we have.  Can this be modified to make it work with multiple samples being added at the same time?

                        The requirement for the requests is that the number there also be unique (no two requests can have the same ID in the system).  Therefore, I created a SameRequestRequests, and linked that to the Requests table through the _SampleNumber field.  I then tried to do the same  definition as before: If ( IsEmpty (Same Request Requests::_SampleNumber); "1" ; (Max (Same Request Requests::_SampleNumber) + 1)). Currently, we only have one user on the system (since it's still in development), so I don't know if we will be having the same problem with that or not.  

                        While we're on the subject, by the way, is it possible to get the information from the first row of a portal automatically copied into the ones below it, and then modified as required?

                         

                        Thanks!

                         

                         

                        • 9. Re: only one portal row allows entry
                          philmodjunk

                          I don't see in your last post where you created this relationship:

                          Data Entry::SampleID = SameSampleDataEntry::SampleID

                          This is not the relationship you report:

                          Requests::SampleID --> DataEntry::SampleID

                          What I have suggested here is a self join relationship between two occurrences of Data Entry, not between Requests and Data Entry. On your relationship graph, you's see this:

                          Requests----<DataEntry---<SameSampleDataEntry

                          If you hover the mouse over the upper left hand corner of DataEntry and SameSampleDataEntry, a tool tip should pop up identifying DataEntry as the data source table for both of these occurrences.

                          If "table occurrence" is a new concept to you, you may find this tutorial to be helpful:  Tutorial: What are Table Occurrences?

                          • 10. Re: only one portal row allows entry
                            NanetteHartley

                            I did define the Data Entry::SampleID = SameSampleDataEntry::SampleID between the DataEntry Table and the SameSampleDataEntry table.  I also have one for the Requests::_SampleNumber =  SameRequestRequests::_SampleNumber.  However, I also have the Requests::Sample ID --> DataEntry::Sample ID relationship between those two tables.  Sorry for the confusion.  I do see that SameSampleDataEntry is sourced from DataEntry, and that SameRequestRequests is sourced from Requests.  That has been there the whole time.  Like I said, it works fine for a single sample enterd in the portal, but not if I try to add more than one sample.  

                            • 11. Re: only one portal row allows entry
                              philmodjunk

                              I also have one for the Requests::_SampleNumber =  SameRequestRequests::_SampleNumber. 

                              You do not need that and it's inclusion in the relationships may be keeping this from working.

                              SampleNumber can be just a simple auto-entered serial number field that you reset to 1 with each new year. You don't need the extra stuff for it because you should have only one record in Sample Number with that combination of year and SampleNumber.

                              • 12. Re: only one portal row allows entry
                                NanetteHartley

                                I removed the Requests::_SampleNumber =  SameRequestRequests::_SampleNumber, and the SameRequestRequests::_SampleNumber table.  I am still getting the same error in the portal, however, where only one sample per request can be eneterd.  

                                • 13. Re: only one portal row allows entry
                                  philmodjunk

                                  Which indicates either an error in the unique values validation setting or in the auto-enter settings for auto-entering the next value in the series.

                                  On which field did you specify Unique Values? Is it a field of type text?

                                  On the auto-enter calculation, did you clear the "do not replace existing values (if any)" check box?

                                  • 14. Re: only one portal row allows entry
                                    NanetteHartley

                                    The _SampleNumber is required to be unique, and is of type number.  It is listed as auto-enter serial number on creation incremented by one.  The next value is listed as one (there are currently no records in the database).  The Prohibit modification of value during data entry is checked.  It is only validated during data entry.  

                                    The _DataNumber is required to be unique, and is of type text.  The "do not replace existing values (if any)" cleared.  It is listed as a calculated value with If ( IsEmpty ( Same Sample Data Entry::_DataNumber ); "1";  (Max ( Same Sample Data Entry::_DataNumber ) + 1)) being the calculation.  The result must be text, and prohibit modification of value during data entry is checked.  Validation is only during data entry, with allow user to override cleared, and unique value selected.  

                                     

                                    I am still having the same issue.  The field is DataEntry::_DataNumber, but it only works for the first one.  

                                    1 2 Previous Next