Prepopulating form data for reference columns

[dbdabell]

Hello,

I have two tables (Cities and Counties used for simplicity): 

Table 1: Cities.  Key Column: [CityID].  Text Columns: [Name], [State], [Country].  Ref Column: [County]

Table 2: Counties. Key Column: [CountyID].  Text Columns: [Name], [State], [Country]

Cities was created first and is populated nicely.  Counties was created later and now it needs to be populated.

When editing a record for Cities in a form, I would like to be able to hit the Add button for the [County] field and bring up a form pre-populated with the [State] and [Country] inherited from the City record I was editing.  All I would need to do is input the County Name and I would be set.

Can I accomplish this by setting some reference in the Initial Value fields for the Counties table?  How would I reference the existing data contained in the Cities rows?

Hopefully this makes sense! Thanks.

[Landan_QREW · Accepted solution]

You could use the following in the initial value for columns in the Counties table:

Country: ANY([Related Cities][Country])
State: ANY([Related Cities][State])

[dbdabell]

That worked beautifully!  Thanks.