What would be a practical limit on the amount of columns a table should have (from google sheet) before navigation and sync become impacted? Assuming the columns only have simple app expressions (e.g arithmetic and logical operations) and no aggregation expressions (e.g Select, Count, etc…). Would 500 columns be possible?
I’d expect the capabilities of the device running the app would factor in.
I’d question a table design that includes so many columns. Care to elaborate on your thinking?
@Steven_Coile I currently have an app that captures workday data based on the seven days of a week. Each row being a day.
I need to do many logical operations and aggregation across each day of the week, and up until now Appsheet is quite limited in that regard. Performance is also becoming an issue as the table grows, since cross rows operations are the most costly. So I am contemplating collapsing all 7 days of the week into a single row per week. No doubts this is a lot of columns, but I am thinking that once on the same rows the logical and aggregation operations will be much more efficient.
I also have backend scripts updating the data after a form commit, which forces me to have the user sync twice in order to proceed. This is error prone and breaks the user’s workflow. I have made the request on another thread for the ability to trigger background syncs transparent to the user, but this is not possible currently. So again, it seems that the collapsing a full week into a single row would help in that regard.
Have you used any relationship between your tables? I’m thinking a structure where one table contains weeks or what ever the main record can contains and days would be in a separate related table? Is your structure already like that?
@Aleksi_Alkio I have though about it, but I am not sure that it would help my main challenge of having to do multiple logical and aggregation operations across multiple days, nor would it help with having to sync due to the backend scripts running after a form commit. Unless there is something I am not understanding about th relationship feature.
I was thinking about the related table structure because then your queries could be quicker because you don’t need to read the whole table. When you create a ref field on the days table, the app will generate the virtual list field as you probably know. If you need read values from the Days table, formula like SUM(SELECT([VirtualColumnName][AnotherField],TRUE))) will read only those few rows.
You can read more about it from this post… plus.google.com - Filtering of ref lists – expressed (and run) more efficiently Quite often, … Filtering of ref lists – expressed (and run) more efficiently Quite often, … plus.google.com