Error with keys on referencing tables

[Cabelo]

Hello there,

I’m a beginner with appsheets and I am living a love x hate relationship with it at times as I guess most people who don’t know what they are doing probably live

I am creating an app to collect, manage and share data for long commercial cycling expeditions. There is a main table with the route info and other tables ex: hotels, campgrounds, restaurants, etc.

Originally I chose the Stage# column on the main table as the key, and referenced the accommodations and restaurants to it.
Then I realized that it was better to have the destination/town name as the key on the main table because sometimes we split and merge days and therefore the stage# changes. The name of the town where a hotel or a restaurant will very likely not change.
The problem is even after undoing and redoing the process many times, even thou the key on the main table is now the town name, the referenced accommodation still gets associated with the stage# and worse, when trying to add new accommodations or restaurants the app will not allow me to add the town name and only opens the drop down for the stage#’s at the town name column.
I’ve read a lot of things and watched the webinars and other videos on this and still haven’t figured out where my mistake is.

Since I’m right now in Central America scouting a new route this has become a problem.

Thank you very much for everyone’s time…

[Mike_A]

It might be easier to get some help if you included some screen shots of your column setup (keys, labels, refs, etc).

[Cabelo]

Thanks Mike!

This is the main table with the town name as a key:

This is the Restaurants table with the town as the reference:

This is the backend of the town field on the Restaurants table:

And this is what it looks like on the app (those numbers are from the stage# column and noth the town column so something is very wrong):

Thank you!!!

[LeventK]

@Cabelo
If you can share the app with levent@able3ventures.com as co-author and the gSheet with edit access, I can have a quick look.

[Mike_A]

@Cabelo - It looks like you have Stage# as the Label for the key, so that may be why you see stage#. Maybe try changing the label to Town?

[LeventK]

Good catch @Mike

[Mike_A]

Just a guess because I make this mistake a lot (especially when changing table structures - I forget to recheck after re-generate).

[Steve]

Have you made use of the Valid If column constraint to control the dropdown menus for any of the Ref columns that refer to the main table? If so, make sure those have been updated with the new key column name.

[Cabelo]

No I haven’t Steve. I actually just read the links on your questions thinking it would help me to do a “reference if” instead of a “show if” but I see that “valid if” is something else. Pretty cool!

Thank you !

[Cabelo]

Thanks @Mike, @LeventK and @Steve!
You guys rule!
It worked once I changed the label to “town” on the main table.

I will create another post for another question I have, but there is one which I think that still fits here:

I have a couple of other tables which I wish to reference to this same main table, but I would like to associate it to a different column. These tables I would like to reference to "stage# instead of “town”. Is that possible?

Thank you very much team, you have no idea how much you helped me!

[LeventK]

@Cabelo
You can create references to a table from multi tables but you cannot create multi ref tables from one table.

[Cabelo]

@LeventK Thanks! So I can create many references as long as all of them are “linked” to the “town” column on the main table, right?

[LeventK]

@Cabelo that’s correct and they will appear as [Reated …s] under your main table