AnsweredAssumed Answered

Append user-added text to related field's value list

Question asked by CharlotteLasek on Sep 18, 2014
Latest reply on Sep 18, 2014 by CharlotteLasek


Append user-added text to related field's value list


Hi all you experts,

I'm working on a DB for our events company. I’m just learning how to use relational value lists and it's working well, but I also need the ability to take a user's custom value and add it “backwards” to the related field’s value list. 

[Warning: This is long! I want to make sure you have as much info as possible to understand what I’m working with.]

I found this thread add to value list but it didn’t work, I think because I don’t need a new record. I also found this but I’m not exactly sure what to do with it. I tried adding it as a calculation to my text field, but I got an error on the Clean_Trailing function. Maybe it’s custom… Oh well.

So I’ve pretty much exhausted my searches and I’m hoping someone here can help. It could be that I just don’t know the right terminology to find the answer I need, in which case I apologize for taking up so much space.

Anyway, here’s my setup:

Table1: Geography 
Fields: Country, StateAbbrev, Cities

Table2: Events
Fields: EventCountry, EventState, EventCity
Relationship 1: Geography_EVENTCountry::Country = Events::EventCountry
Relationship 2: Geography_EVENTState::StateAbbrev = Events::EventState

Table3: Clients
Fields: ClientCountry, ClientState, ClientCity
Relationship 3: Geography_CLIENTCountry::Country = Clients::ClientCountry
Relationship 4: Geography_CLIENTState::State = Clients::ClientState

Table4: Vendors
Fields: VendorCountry, VendorState, VendorCity
Relationship 5: Geography_VENDORCountry::Country = Vendors::VendorCountry
Relationship 6: Geography_VENDORState::State = Vendors::VendorState

/ * I have six instances of the Geography table in my relationships so the relational value lists work. If there’s a better way to do this, I’m all ears!

Value Lists (just working with EVENTS for now):
1: EVENT_Country
Use values from field: Geography_EVENTCountry::Country
Include all values
/ * Select from all available countries

2: EVENT_State
Use values from field: Geography_EVENTCountry::StateAbbrev
Include only related values starting from: Events
/ * List only states related to chosen country

3: EVENT_City
Use values from field: Geography_EVENTState::Cities
Include only related values starting from: Events
/ * List only cities from chosen state

The user selects a country, gets a list of states in that country, then gets a list of cities in that state. If we have an event in a new city, the user can add it. When the user adds a custom value (a new city), I want it to ALSO be added to the Geography table in the appropriate related field. That’s where I’m having trouble.

I tried this but it didn’t work; I didn’t even get the dialog window, but at least you'll see where my mind is:

Commit Records/Requests []
If (IsEmpty ( FilterValues ( ValueListItems ( Get (FileName) ; "EVENT_City" ) ; Geography_EVENTState::Cities ) )
Show Custom Dialog [“New City?”; Events::City & “ is a new city. Add it?”] /* Yes/No button, no commit
If ( Get ( LastMessageChoice ) = 1 )
Set Variable [$NewCity ; Value: Events::City]
Freeze Window
Go to Layout [“StatesGlobal_STATES” ()] /* a list of states by country with carriage delimited list of cities
Set Variable [$CityList; Value:Geography_EVENTState::Cities]
Set Field [Geography_EVENTState::Cities; $CityList & ¶ & $NewCity]
Go to Layout [original layout]
End If
End If

It seems tricky because of all the occurrences and relationships of the Geography table. I don't know which one(s) to use. I'm going to the Event-State version because that's the one "feeding" the Cities list, but I could be wrong.

Thanks for any and all advice! It's not a MAJOR thing, but something that would make it work smoother and it's good training!