ANY(SELECT() formula not working for new values in the referred table

[Pejme]

hi,

In the table, I have this formula for the Project 0 column. I want it to give me the value that is in the Project value column in the Sites table when the data in the Address column in the Sites table matches with the data in the Project 0 column in the table.

When I originally was working on this, I just created the data in the Sites table in Google sheets (and assigned a ID number to each row). See below screenshot. 

The ANY(SELECT(Sites[Project value],[_THISROW].[Project 0]=[Address])) formula was working well for the data that was provided manually in the Sites table.

However, when I create new data in the Sites table, Project value 0 won't show as the formula is not working.

Same thing happens if I update the ID data of the rows that were working before (and refreshing both Appsheet and Google sheet).

Here is a screenshot of the Sites table.

Can someone please help me get this sorted?

[Joseph_Seddik]

Please show us column configuration of your CFMEU table. Thanks.

[Pejme]

@Joseph_Seddik Thanks for your reply. Not sure what the column configuration is. Is this what you are asking for?

[Joseph_Seddik]

Hello, I need to see the columns in your CFMEU table, some screenshots similar to the ones you provided in the post for the Sites table. Thanks.

[Pejme]

@Joseph_Seddik  I have 262 columns currently. The project value column is a virtual column. The Project 0 column is a database column. I hope this is what you are asking for? Thanks again for trying to help.

ANY(SELECT(Sites[Project value],[_THISROW].[Project 0]=[Address]))

[Joseph_Seddik]

You are repeating screenshots that you had already posted in the main post. 

@Joseph_Seddik  wrote:

Please show us column configuration of your CFMEU table

I need to see the columns in your CFMEU table

some screenshots similar to the ones you provided in the post for the Sites table

I'd like to see the columns in your CFMEU table. I'm sorry I don't know how to explain this better. 

[Pejme]

@Joseph_Seddik I really hope this is what you are asking for 🙂 If not, maybe you can share a screenshot of an example of what you are looking for to help me understand better?

[Joseph_Seddik · Accepted solution]

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. 

1. What is a Key? | AppSheet Help Center 
2. References Between Tables | AppSheet Help Center 
3. Dereference Expressions | AppSheet Help Center 
4. Data: The Essentials | AppSheet Help Center 

Optional but valuable:

1. Relational Database Design 
2. Introduction to Database Design | Tutorial | Datanamic 
3. 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: 

1. 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.
   
   
2. 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:
   
   
   1. Table Weekdays - columns: "UniqueID", "staffID" ref to your CFMEU table, and any other column related only to the Weekday. 
      
      
   2. 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. 
   3. Table Leaves - the same, and here you also add a column "Leave Type" to differentiate between the different leave types: Annual, Personal, etc. 
      
      
   4. Table RDOs
      
      
   5. Table Holidays, this would contain both Holiday Days and Holiday Hours. 
      
      
   6. Table Site Shutdowns, would contain two references, one to the CFMEU table and another to the Sites table. 
   7. 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. 

[Pejme]

Thanks for your great post! I can't wait to get home and try your suggestion. I will also make sure to read your posts over and over together with the articles you have shared to get a better understanding.

I kind of assumed that my app isn't the most efficient the way it's structured. But I just wanted to get started, and I think I learn better by doing and learning as I encounter problems. But again, thanks for all the feedback! I really appreciate the time it must have taken you to write all that. Can't wait to deep drive into your post and improve 😊

[Joseph_Seddik]

« I think I learn better by doing and learning as I encounter problems »

I agree! That’s a great way to learn 🙂

All the best!