Are the names and time points constant or do they change with imports?
Why does this need to be entered this way? Why not use a relationship to show the data and for export?
If the tables are not related you need ExcuteSQL.
param = DwellTimes::Stop;
query = ExecuteSQL ( "SELECT StopNumber FROM StopNumbers_Names WHERE StopName = ?"; ""; ""; param);
stop = Case( query = "?"; ""; query);
timepoint = //*** no info on field names so no answer for this***
My apologies for not being very clear.
1. The stop names are consistent, but there are multiple stops.
2. They do not need to be entered in this way.
3. The tables are related.
Below is the data that is imported. It does not have the Stop Number or whether it is a timepoint or not. My goal is to have the Stop Number and whether it is a timepoint or not to be displayed also. In each record the stop may be different.
Below is how I have things related.
Are the stops a table that never change value? Is this the case with time points as well?
How are the FKs set? If the records are related you can simply put the related field data on the layout.
If this file is simple with only three tables you might try posting the file with some sample data. That would help see where your are at exactly.
In order for the value of stopnumbers_names::stopnumber to be accessible to a given record in DwellTimes so that you can either copy it over or just link to it, you have to have a relationship that works.
keep your relationships as they are and set up DwellTimes::_fk_StopNumbers with a value list that lists __pk_StopNumbers as field 1 and StopName as field 2. You can then use the StopName in this value list to select the StopNumber.
change the relationship to use the two StopName fields as the match fields.
With both options, you can simply add the StopNumber field to your DwellTimes layout to show this value though you can also set up an auto-enter calculation to copy over the value.
The records are not related because there are no key values. Your relationship is really based on the name so you can use that as in this example.
There are ways to set the keys with scripts or calc but this might be your easiest way to get where you need to go.
You could make the timepoint a stored calc.
Using Creation Date as a primary key for all tables is not a good idea especially when all the records are from the same day. Use something like Get(UUID) as a primary key.
The Creation Date was not intended to be the primary key, it should have been Serial number. Thanks for the catch, it has been corrected.
Thank you for your help. Now that I see how you did it, it seems so obvious.