Multiple columns in first table referencing different rows of a second table?

[Sarah_H]

I am making an app to manage a cat rescue. I have two tables involved: Cats and Locations. I want to have three different columns in the Cats table refer to different rows in the Locations table, kind of like this

[Cats].[Current Location] = Location ID 1
[Cats].[Transferred From] = Location ID 4
[Cats].[Transferred To] = Location ID 7

Note that "Transferred From" and "Transferred To" do not represent a location history within the rescue, but rather where we initially received the animal, and which organization we transferred it out to (if applicable). All animals will have a "Current Location", but only some animals will have transfers.

Trying to do regular REFs using [Location ID] obviously makes Appsheet think all three columns are the same thing.

I could do an ENUM, but I was hoping to have everything linked directly to the Locations entries that have addresses and phone numbers, as well as being able to quickly see which cats are associated with which location.

I sense that I'm missing something big about how I need to set this up, but I've done a lot of searching and haven't found an answer I can wrap my head around applying to my situation. Do I need separate "location assignment"/"transfer to assignment" etc tables? Something else?

Thanks so much!

Mod note: I tried to post this about 8 hours ago, but it seems to have vanished with no explanation, so I am trying again.

[TeeSee1 · Accepted solution]

All three cat columns, having a ref to the location table each, can have different values referencing different rows in the locations table.

[scott192]

I don't see any problems with you having 3 columns on your Cat table each being a Ref to your locations table?  What makes you think that it won't work?

[Sarah_H]

Thank you for your replies! You're correct, and I think I was hung up on two issues:

1. I thought the column name had to match the referenced column name in order for the full reference functionality to work. When I had them with different names, the link to child records wasn't showing. That led me to think I had to name them all "Location ID", which of course meant the three columns were conflating, so it couldn't work.

2. I finally figured out the reason my second two location refs were not working - I had a pie chart reference view active. I had created that for fun, and moved it to the Reference Views section to get it out of the menu. I didn't fully understand the functionality of the Reference Views or it was going to mess with the typical ref and ref rows links (I thought it was for views your other views were going to reference, such as dashboard pieces). Removing that pie chart view returned everything to normal.

--Side note, the first location column did not have the link/pie chart issue, and I'm not sure why it escaped.

Thank you for your help!