I am trying to fill in two fields from related records but cannot figure the correct working way to do it.
In my main table (DwellTimes) I have a lot of imported data from an AVL system. One of the fields with data is Stop, which has all of the stop names. I created two fields, StopNumber and Timepoint (the imported data does not have these two fields). I created a table (StopNumbers_Names) that has all of the stop names and the associated stop numbers. I also created another table (Timepoints) that has a list of the stops that are timepoints. Not every stop is a timepoint, that is why I created two tables.
1. I would like the StopNumber field to be filled in from the StopNumbers_Names table. If a stop name equals a name in the StopNumbers_Name table, the Stop Number should be filled in. Example, in the DwellTimes table a stop named Lorene7th matches in the StopNumbers_Names to Lorene7th and has a stop number of 10000, I would like the 10000 to be placed in the StopNumber field of the DwellTimes table. I made the StopNumber field a calculation field with the following calculation with a result field of number (all stop numbers are 5 digits), but it does not enter the number in any of the records.
If (Stop = StopNumbers_Names::StopName ; StopNumbers_Names::StopNumber ; "" )
2. If the DwellTimes field Stop is in the Timepoints table, I would like "Yes" to be entered in the DwellTimes Timepoint field. I made the Timepoint field a calculation with the following calculation with a result field of text, but it returns "No" in all records.
If ( Stop = Timepoints::TimepointName ; "Yes" ; "No" )
Any help would be greatly appreciated.