9 Replies Latest reply on Oct 1, 2013 11:59 AM by dmayembe

    Railrod Databse



      Railrod Databse


           Hi. In my Railroad database. I have a field for the station name, code and distance. The user can populate these fields manually  however they make mistakes. I created a value list for "station". my goal is once a station is selected the "code and distance" fields are populated with the right information. Example. I have 3 stations and their codes and distance;

           Station      code     distance 

           Jaguar     JGR        200

           Panda      PND       100

           Oxford      OXD        230

           now once the user selects Panda as station, the Code field is populated with PND and the Distance with 100.

           Any Idea?

           Thank you

        • 1. Re: Railrod Databse

               There are two basic methods that you can use. Both use a related table of information and the same relationship to access the correct record in that table, but one method copies the data from that table--preserving the values that were current at the moment the data was copied over and the other dynamically links to the data such that any future changes to the data will automatically update the info "looked up" on your layout.

               Here's a link to a thread that describes both methods: Auto Fill

          • 2. Re: Railrod Databse

                 Thank you. I succeeded in one and failed on the second attempt. I created a two different tables (for Departure Station and Arrival Station). In each table I populated it with station name, code and distance. I was able to select a departure station and all the departure fields for code and distance were populated. however when i do the same with the Arrival station, the value of the station name appear when i click on the drop down arrow for the drop down menu but would not populate the station name, therefore all the arrival fields (station name, code and distance) are not populated.

                 Here is how i have it set up

                 Departure station

                 Display Data from                                          Value From

                 Invoices::_StationsIDfk                                    "Stations::__StationsIDpk" and "Stations::Stations Name"

                 Arrival station

                 Invoices::_ArrivalStationsIDfk                                    "Stations::__ArrivalStationsIDpk" and "ArrivalStations::Stations Name"

                 So the first one works but not the second. Note the stations list is the same for both departure and arrival but saved in two different tables and table names

                  I also noticed that when i go to my manage database, the arrival station or departure station fields are changed automatically to indexed

            • 3. Re: Railrod Databse

                   Which of the two methods are you using? Using two look up tables, on for arrivals and one for departures will cause problems for either your layout design or the settings on your "look up" fields in Invoices.

              • 4. Re: Railrod Databse

                     Yes I used two look up tables for both arrivals and departures

                • 5. Re: Railrod Databse

                       I can see that you did, but this does not answer my question. wink

                       Are you placing fields from the look up tables on your layout or did you set up corresponding fields in the Invoice table that copy information from a look up table? If so, which auto-enter option did you use? Calculation or Looked up Value?

                  • 6. Re: Railrod Databse

                         Here is the video to give you an idea of my situation. I couldn't do a screenshot just to give you the details



                    • 7. Re: Railrod Databse

                           Hey Diedonne.

                           In your video things are looking pretty good. I did however see a little mistake. In your arrival station you don't seem to have a serial number being entered.

                           The IdPk field should be a number field set to auto enter a serial number.

                           But that doesn't appear to be the case.

                           So you have 11 records there without a primary key.

                           I suggest (if you don't have it yet) that you create a new table view layout based on your arrivals station table and you show all the fields. That way you can have a look at the actual data that's in there.

                           You should see that the IdPk field in empty.

                           Now you could go into the IdPk field and use the "Replace Field Contents command that you can find in the "Records" menu.

                           Then you can choose to "Replace with Serial Numbers"

                           And check "Update Serial Numbers in Entry Options".

                           It's very important that you check this.

                           After you did this and you have serial numbers in all of your records, memorize the highest number. I think you have 11 records in that table now, so your ID's should go from 1 to 11.

                           Then you go to "File" - "Manage" - "Database" and you go to the Arrival Station table and set the IdPk field to auto enter a serial number, but set the "Next Value" to the next number that needs to be created.

                           In my example that would be "12"

                           I hope this helps.

                           P.s. always keep a "table view" of every single table you make so you can go in there to verify your actual data. So you can see what data you actually have in your fields.




                      • 8. Re: Railrod Databse

                             What I didn't see in the video was what value list you used for the second station field. Given that you have two different tables of stations, you can't use the same value list as you use for the first station field as the ID number in the second table might not be the same. So you'd need two value lists, one based on each of the two station tables.

                        • 9. Re: Railrod Databse

                               Thank you both for your help. I found out I had many records with no idpk. I deleted the tables and started over following your inputs and now it works fine. I can now move to the next step.

                               Thank you