5 Replies Latest reply on Mar 21, 2012 10:09 AM by philmodjunk

    auto populate field based on another table



      auto populate field based on another table


      I am creating a database that is going to be used for data collection. He is a short summary of the tables




      Active (a boolean field)

      Order (numeric starting at 1)





      What I want to be able to do is have the layout used to collect the data auto populate the CustomerName field in numeric order based on the Order field for Active customers. Each subsequent record will have the next CustomerName autopopulated until the last customer and then it will start back at the first one. Only customers indicated as Active will be included.

      I am having difficulty figuring out how to do this and any help/suggestions would be appreciated.



        • 1. Re: auto populate field based on another table

          What will you do when you get two customers of the same name? What will you do when a customer changes their name? Relating tables by name has potential issues that you can avoid if you use an auto-entered serial number instead of their name in order to define a relationship between the two tables.

          A typical relationship between two such tables would be:

          CustomerInofrmation::__pk_CustomerID = Data::_fk_CustomerID

          And there would be no customerName field in Data. Instead, you'd add the existing CustomerName field from the CustomerInformation table where needed to display the customer name.

          I'm curious as to why you want the approach you describe in your post.

          Why do you need such a specified order?

          How will you update the value in order when a customer changes from active to inactive status?

          Will you renumber when this happens or leave gaps in the series?

          How will this affect your data entry process?

          I'm asking these questions as there may be simpler ways to set up your layouts for data entry.

          • 2. Re: auto populate field based on another table

            I really don't want to divulge exactly what I am doing because it may be a marketable product in the industry that I am doing it for but here is an example..


            Collecting statistics for a baseball team. I want to be able to cycle through the lineup automatically. The RosterTable may include more than 9 players so I will be indicating which ones are currently playing and what their order in the lineup is.

            There will never be gaps in the series and changes will be made manually on another layout.

            • 3. Re: auto populate field based on another table

              Do you need to create the new records in Data one at a time or would it work to press a button that pops up the complete list all in one "batch" operation.

              If you want the list to appear all at once, you can use this script and the relationship I already specified:

              Go To layout [CustomerInformation]
              enter FInd mode[] //clear the pause check box
              Set field [CustomerInformation::Active ; 1 ]
              Set Error capture [on]
              Perform Find[]
              Sort [restore, no dialog ] //sort them by the order field
              If [Get ( FoundCount ) ]
                 Go to layout [Data]
                 Import Records   //specify an import from CustomerINformation, only import the __pk_CustomerID into _fk_CustomerID
                 Show Custom dialog ["No active customers were found"]
              End If 

              To show the customer name, put the name field from CustomerInformation on your Data layout.

              Let me know if you want the records to be created one at a time as that can also be done.

              • 4. Re: auto populate field based on another table

                My idea was to create the next record after the current one is finalized, so one at a time.


                This would allow for possible changes to the active list on the fly.

                • 5. Re: auto populate field based on another table

                  I'd still keep the relationship I specified for linking data recors to customer records as it's safer, but this relationship can be added for enabling your "next customer" capability:

                  CustomerInformationByOrder::Order = Data::gNextCustomer

                  In Manage | Database | relationships, make a new table occurrence of CustomerInformation by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be: CustomerInformationByOrder.

                  We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                  Define gNextCustomer to be a global number field.

                  Put a "next Customer" button on your Data layout that performs this script:

                  Set Field [Data::gNextCustomer ; Data::gNextCustomer + 1]
                  New Record/Request
                  Set field [ Data::_fk_CustomerID ; CustomerInformationByOrder::__pk_CustomerID]

                  At the start of each session of entering data, clear the gnextCustomer field or set it to zero. You can do that manually or with a button.