4 Replies Latest reply on Sep 18, 2014 1:03 PM by CharlotteLasek

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

    CharlotteLasek

      Title

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

      Post

      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 http://www.onepartharmony.com/custom_functions_one_function.php?function=32 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!

        • 1. Re: Append user-added text to related field's value list
          philmodjunk

          The field formatted with your CVL of cities would be a field from the Events table not Geography_EVENTState::Cities

          Commit Records/Requests []
          If (IsEmpty ( FilterValues ( ValueListItems ( Get (FileName) ; "EVENT_City" ) ; EVENTS::City ) )
              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

          But the rest of your script is bit more cumbersome than it needs to be. Each City value should be a separate record and thus the rest of your script, once the user confirms a new value, would be just to create a new record in the Geography table rather than inserting a new value into a list of values in a single field. (Yes, that works, but it's more complicated and less flexible.)

          You might check out the Adventures in FileMaking series. Adventures 1 and 2 for more ideas on working with value lists. Adventure #1 shows an approach to conditional hierarchical value lists (like you have here) that do not require the user to select values in a rigid "left to right order". They are free to download.

          Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a basic field based value list)
          Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Append user-added text to related field's value list
            CharlotteLasek

            Again, thanks for your lightning-fast reply!

            I updated the table as you pointed out, but it added the new city to the wrong state (Detroit, Arizona instead of Michigan). As for having a new record for each city, how would it still relate to the selected state? Or would I set up a script for the new record to copy the values from the State and Country fields? 

            Thanks for the extra links… I'm always looking for more resources :)

            __________________________________________________________________

            I have always admired the ability to bite off more than one can chew and then chew it.
            ~ William C. deMille

            • 3. Re: Append user-added text to related field's value list
              philmodjunk

              Each new record for each new city would also have a state field populated with the correct state.

              Your existing set up would need to perform a find to to find the record for the correct state before appending the value to the list in the field.

              • 4. Re: Append user-added text to related field's value list
                CharlotteLasek

                Excellent! I changed them to separate records and now the script works! I have it setting variables from the Event record's Country,  State and custom City, then creating a new record in Geography with variables' data in those fields. Easy! Thanks! :)