7 Replies Latest reply on Nov 21, 2016 1:19 PM by breeanne

    Serial number reset script based on previous fields

    breeanne

      Hi, I have one table that I'm trying to create a usable ID (and Get(UUID) would be cumbersome to reenter in another table) to link points.

       

      Table 1:

      Table 1 ID

      Lat

      Long

      etc.

      This table is a series of GPS points each having a unique ID, that I would like to be easily referenced. I've used two fields, one as serial number that is auto generated, and another that calculates the Table 1 ID using the Get(CurrentDate) and concatenates that with the serial number for that record to look like:

      21/11/2016-1

      21/11/2016-2

      etc

       

      Table 2

      Table 1 ID

      Observation ID

      Observation details...

      etc

      This table is for field observations. Each of these records I would like to connect to a record in Table 1 ID to be able to locate it.

       

       

      1. My first question is, how can I reset the serial number for each day so that tomorrow, the new ID's restart the serial number at one and produce 22/11/2016-1 instead of 22/11/2016-3

       

      I see this example of how to reset the serial number, but am at a loss how to apply it for what I'm hoping for.

      Set Next Serial Value [Invoices::Invoice ID; Max ( Invoices::Invoice ID ) + 1]

       

      Basically, I would like to create a script that when the user navigates to Table 1, it would check the current date against the last entered record's date to see if the serial number needs to be reset, and resets if its required. Or something along those lines.

       

      2. My second question is, how can I get Table 2's field for Table 1 ID to auto-guess based on the last record? I have it now where it will auto populate with the last record, but it still requires entering it once, but if I could get it to auto populate based on the latest record in Table 1, then that would be even better.

       

      Many thanks!!

      Bree

        • 1. Re: Serial number reset script based on previous fields
          philmodjunk

          It might help to explain what you are doing in more detail.

           

          and Get(UUID) would be cumbersome to reenter in another table) to link points.

          But why would you have a user enter any kind of such ID, serial number or UUID manually into a field in order to link records. That's a significant UI design flaw that should be avoided. The system should make that Linkage for the user without them having to do that.

           

          (I currently support a DB created long before I came to work here where users are constantly having to enter employee IDs, student numbers, etc and it's an ongoing migraine to fix all the data entry related problems this creates. I am working, gradually to reduce/eliminate this aspect of our system's design, but it's a long process...)

           

          But overall, if you are having to reset the serial number each day, it's pretty likely that there are better options.

          • 2. Re: Serial number reset script based on previous fields
            breeanne

            Yes, I understand and agree, but I'm struggling with how to best have the field tech reference the ID field since the database is going to be used for field collection. I feel like I'm missing an obvious data entry technique...

             

            I actually have three tables with the following relationships:

             

            screenshot.jpeg

             

            Each table has a data entry layout for a mobile device and I need to figure out how best to have the ID be referenced during data entry. The fields currently have dropdown menus that allow the user to see the available options for each field, i.e. on the Point of Interest entry layout, the Trail Name field has a drop down that shows the Trail Name records in Trail Info, which is ok for that relationship, because the trail name is recognizable and easy to select, but the POI ID is where I'm struggling. How would you create a naming convention for the ID that is unique and easy to reference? Or is it possible to make it auto populate the POI ID in the Observation table with the latest record in the Point of Interest POI ID? That would eliminate the entry all together. I hope my description is understandable.

             

            Thanks!!

            Bree

            • 3. Re: Serial number reset script based on previous fields
              keywords

              If the relationship between Table 1 and Table 2 is set to allow creation of records, the foreign key field will be automatically filled when table 2 records are created. So please excuse a couple of observations:

              1.     If you think using Get ( UUID ) is cumbersome, having users responsible for entering ANY key field is far, far worse—take note of Phil's warning in that regard.

              2.     Please don't name tables Table 1, Table 2, etc—use meaningful names that help to describe your setup. This is beneficial not just when asking for help but also to clarify things in your own mind.

              • 4. Re: Serial number reset script based on previous fields
                keywords

                Ahh! That came through while I was typing my last post. The "recognisable" part (the Lat/Long) can be set as the second value in a value list, so the user chooses that from the list but the list entry is actually the recordID. That is a very standard technique for handling this very situation. You should do the same on the TrailName relationship too, by the way, rather than use the trail name for the relationship.

                Think of recordID fields as behind the scenes unique identifiers that are meaningless to the user by=ut absolutely fundamental to drive your relationships.

                • 5. Re: Serial number reset script based on previous fields
                  breeanne

                  Great, thank you! I set that up, but I'm wondering if there's a way to mask the entry field or something so that when the user selects the trail name, that it is still visible, instead of the ID that is being recorded? It might be confusing to the user to not see the result they selected.

                   

                  Thanks!!

                  • 6. Re: Serial number reset script based on previous fields
                    philmodjunk

                    A popup menu can be used to show the second field value

                     

                    or:

                     

                    Put the trail name field from your related table occurrence on top of the value list formatted ID field. Make it's fill color opaque to hid the ID field. Use behavior settings on the inspector's data tab to deny access to the trail name field. When the user clicks on the trail name field, the mouse click will deploy the drop down list formatted field hidden behind it.

                    1 of 1 people found this helpful
                    • 7. Re: Serial number reset script based on previous fields
                      breeanne

                      Thank you so much!! I used the popup menu.