Combine Lists with Dereference and ability to Add or use text

[ZOCO_Unlimited]

Hello, I may be asking too much but shooting for it anyhow. I have obtained each of these goals separately but I cannot find a  way to do them all. When I change the configuration to accommodate another requirement it looses a function elsewhere.

I need to be able to:

1. Have a single drop down with combined lists: (I have this setup)
   a. Suggested Values: Custom.Locations[Location Name]+Locations[Location Name] (produces list and allows "Add to custom location" but not "Use", allow other values is selected.
   b. Problem: Can't Dereference items from the added locations table . (could use lookup instead of dereference for all?)
2. Dereference the selected location to obtain Lat/Long, etc :
   a: Works w/ the references list, Doesn't with the added list, shows broken links (yellow triangle)
3. Add a new location to the custom locations table: (this works, listing only as a need)
4. Use entered Text instead of adding or selecting from the list.
   b. (works when the column type is text, not when it is enum even w/ the option selected)

 Table 1 (Custom Locations) + Table 2 (Locations)

Dropdown: Shows search only, no "add" or "use", breaks referenced links w/ the added list

Please let me know what additional info you may need. Thank you in advance for all of your help

[Karimmc2]

I understand that the references come from 2 different tables, is that correct?

if so, that is the reason for the exclamation marks, those are the values from the second table, not being found in the actual table that is being used as reference. 

[ZOCO_Unlimited]

That is correct

[Karimmc2]

In order to repair this as the way you want this to work, both lists have to come from the same table. You can create a combined table with all the locations and the users can add new locations to that table, then (in case you need to) you can differentiate the inputs depending on the user or the context by adding a column thar records this whenever a new record is entered, and finally you’ll need to replace the 2 different tables you use with slices (in case you need different records displaying in each one)

[ZOCO_Unlimited]

So I did this as well, This was actually my first setup. I am now trying to obtain my needs through appsheet but everywhere I go I end up with a function missing..  The other problem with this is that table 2's location list is about 15k records deep. If I do a direct ref on it, it seems to crash my app constantly. An enum base type ref did fix this for the most part

I currently have 3 tables, the current configuration is Table 1 (Custom Locations) + Table 2 (Locations) = Table 3 (Locations) Google sheets queries Table 1 & Table 2 to produce a list of all locations.  Appsheet is connected to Table 3 for the user selection. Table 1 has 10 columns and Table 2 has 50 columns so I used the Query Function to combine both into one. While this works for selecting from a single source  and allowing the dereference for the pindrops, I do not have the functionality to Add or to Use typed (unless set as text w/ suggested values). I need the add and the use abilities.