Thanks for the screenshots.
OK. This goes further than I thought it would 
but don’t worry, just bear with me a bit please. I knew where your problem was but I expected that you have more issues in your data and for this reason I asked you to show the columns of your table. It turned out you have 260+ columns in your table!
Short answer to your question: you are trying to establish a reference to the Sites table targeting a column that is not the key column. You have based your expressions on a completely false assumption. That’s why they don’t work. You cannot have a “Project 0” reference column, expecting its value to be the “address”, while the “address” is not your key column in Sites table.
The reason it worked before with your dummy data, is because you filled in the same dummy value for the key column and the address column for each row. Once you started to put actual data with actual keys, you kept looking for the address value in your “Project 0” column, while Project 0 column was actually holding and will always hold the values of the key column in the referenced Site table.
Hence, the expression to use for the “Project Value 0” is: [Project 0].[Project Value]
That should solve your problem.
Now before reading the rest of my post, I strongly encourage you to stop now, study the guides below carefully, then come back and read the rest of my reply.
- What is a Key? | AppSheet Help Center
- References Between Tables | AppSheet Help Center
- Dereference Expressions | AppSheet Help Center
- Data: The Essentials | AppSheet Help Center
Optional but valuable:
- Relational Database Design
- Introduction to Database Design | Tutorial | Datanamic
- Solved: Rows vs Columns for table data - work orders - Google Cloud Community
Welcome back!
I hope now you understand why you should drastically change your data structure. You should NOT have data in columns and have table with 260+ columns, this is far too many and will make your app too complex to be maintainable and as the dataset grows it will be too slow to be usable. I could count some more than 200 formulas in your table and I could see some 40 with SELECT statement in virtual columns. Imagine having these calculated for every row in your table.
Instead you should do the following:
-
The CFMEU table is essentially a staff table. Here you should only maintain the columns that are inherent to each person, such as: employer, pay, photo, date of hire, etc.
-
The rest of your 260+ columns should be removed from the table. Instead, you should have dedicated tables for each category that you are enumerating as columns in your staff table:
-
Table Weekdays - columns: “UniqueID”, “staffID” ref to your CFMEU table, and any other column related only to the Weekday.
-
Table Days - same as above. I don’t know what is the difference between this table and the one above, but if you end up having the exact column structure for the two tables, then you should merge them into one table and add a column “Day Type” to differentiate between the two.
-
Table Leaves - the same, and here you also add a column “Leave Type” to differentiate between the different leave types: Annual, Personal, etc.
-
Table RDOs
-
Table Holidays, this would contain both Holiday Days and Holiday Hours.
-
Table Site Shutdowns, would contain two references, one to the CFMEU table and another to the Sites table.
-
Table Projects, with all project related info in columns, and references to CFMEU, Sites, and other tables as needed.
When you have setup the tables above, you’ll hardly need any of the 40+ virtual columns with SELECT that you currently have in your table.
Take your time to read and understand this and don’t hesitate to ask any questions you might need.
