I have an app which uses google spreadsheet as database.
In the app I have a table for one of the tabs and on top I have multiple slices that show data based on certain criteria.
I then have multiple views that show data from the different slices. One of them, in particular, is able to add new data to this table. When I insert a new row the app synchronizes, saves the data to the spreadsheet, and it appears in the app (a simple table).
The funny thing is, the new line only appears in the app when the synchronization is over. In case I’m working in offline mode, the data doesn’t appear at all in the screen. I have the expected error saying the synchronization failed and later, when I have network and the synchronization is done, the data is then showed properly.
Of course I expect the synchronization to not happen if there is no network, but I also expect the data to be shown in case there is no synchronization.
In a previous iteration of the app this was not a problem. The data was shown properly and when the synchronization happened the data would then be stored in the spreadsheet.
Some time ago I did a major rework on the app and this behavior started to happen. I have no idea why.
First, verify that your row key column is still set properly AND that there is no functionality that could DUPLICATE the row key.
Create a test view on the BASE table and view it. Does your row show there? If so, it means your Slices are for some reason filtering it out and you’ll need to take a closer look at which data attribute is causing it not to appear.
If the row is not showing in your test view immediately and your row key seems good, then do you have Security Filters implemented? Check that logic carefully to make sure they are not causing the new row to be filtered out immediately.
If still nothing then I would resort to some deep analysis.
Reduce the row add to its basic form without any filtering or post-save activity. TEMPORARILY remove (but save the logic somewhere) any Security Filters, remove any Form Save actions and disable any Bots that trigger on row add. Add a row and verify it shows in your test view. If it still doesn’t then there is some data issue with the table configuration and you need to dig deeper into the table.
If the row shows as expected, then start replacing the removed functionality bit-by bit - testing that a new row shows as expected. When it doesn’t then you’ll know where to dig into to find the issue
I’m not sure what you mean by key column being set properly. I’m using the row number as key. I know it’s not ideal but it works for my needs. But that also means duplication is not possible.
I tested with the base table and you’re right, it works as expected. Even offline the data appears immediately in the app.
Now I’m not sure what is happening with the slice. I have no security filters in the base table.
The slice has a filter:
The intention is to show only the current month expenses.
The filter works, as I can see the data when the app is online. I just don’t understand why it doesn’t work offline.
Is this expected? Are the slices supposed to work only when online? If so I’ll have to get rid of all my slices and move the logic to the views, which I feel is quite bad…
Why would the key be the reason for the app not to update the slices when offline?
The base table updates the data immediately. There are no problems with keys. No overwriting, no duplicate keys, no nothing.
I’m failing to understand what is behind it. By the way, I have no relations between the tables or anything. The table exists by itself and the only reason for the key to exist is to keep the synchronization between the spreadsheet and the app. (which is doing quite well)
You are using the TODAY() function to determine which month to show. Note that we are in a NEW month of Feb. If the date you are entering is Jan, that row will get filtered OUT of the Slice!!
OHHH, it just occurred to me that the MONTH() function returns a numeric value of 1 - 12. If you are entering textual value of Jan, Feb, etc, there will never be a match!!
I got it!
The column Mes (Month) does not have anything special in the app. It’s simply a column coming from the sheet.
But on the spreadsheet side, it does have a calculation:
It basically extracts the month from the date.
The thing is, this calculation only happens on the server side. While offline the calculation cannot happen, therefore that column remains empty. While empty the filter does not show the entry. It works on the base table because that shows everything.
I could not see it because this (together with a few other support columns) is hidden from the visualization.
Thanks a lot for the help. I just tested and it works.
Glad you were able to identify the issue!! I would have never thought of that (explained below).
Two things I would strongly recommend:
Do not use Row Number as the key. Instead, in each table have a dedicated column as the key column and assign the UNIQUEID() function as the Initial Value. It’s quick and easy to do and you will NEVER need to worry about row keys again. While what you have today is not an issue, getting into the habit of this practice now could save you some headaches later as the app expands.
Refrain from using formulas in the sheets used as a datasource. You have discovered one reason why. There are others, such as when using sheets as a source, updates can be slowed down if you have a lot of cascading formulas in the sheets. When you migrate to a database, you’ll need to implement all the formulas somewhere. Again, being in the habit of not using formulas in the sheets will save time and issues later. I never use formulas which is why I would never thought of it as being an issue.
I just tested and it works.