Re: Loading not very huge data

Kristian_ASK · 10-01-2021 04:59 AM

Pretty new at this so bear with me. We have a source in bigquery that we can’t make smaller than 300k rows and with a few additional smaller tables. Is there a way to filter the first fetch using security filter or the 300k data is always loaded into the sheet first and then filtered?

If not, what are the options?

EIG

Yes, absolutely. There are so many ways to do this but if you create a user setting (Shows up in the app menu under settings) where the user will select the data that they want to see. The security filter will then use that user setting to filter. Then when they sync, only the data needed will show up. You could also filter by a date range. You also could split the table up into multiple identical tables in bigquery and then use AppSheet’s table partition scaling feature to select a different table based on what the user selects as a user setting. There are other options but that is just the first couple that came to mind that should help you get started.

Ref:

Kristian_ASK

Thanks. We have partitions in use already. What we don’t have is security filters but that would be great to have but we use automation so there’s no app per se. We have a sheet with parameters.

If Run_Flag is set to TRUE we trigger the job. I want to get the project of that table (which we have defined in tables)

I want to “bind” project of the row in the parameters table to a User Setting and then use that to filter.

Is that possible?

EIG

Yes, using the security filter on Project will reduce the amount of data that is downloaded by that users app. I am a little confused about your statement that “there’s no app per se”. Maybe I don’t understand the flow of the app completely. I think that you are wanting to filter the user setting options to only show the projects that contain Run_Flag = TRUE. Then once a user selects that project then the security filter will filter and display only that project’s data. Does that sound correct? Then the automation process can occur?

Kristian_ASK

The flow is this:

The sheet in the image I posted is updated with a row where Run_Flag = TRUE
There is an event in AppSheet picking that change up
The project 21737-001 specified on the sheet will be the parameter that should be used for the security filter

Did that explain it better?

EIG

Yes, thank you. I reread your original post and I think I understand the problem now. My question back to you is why do you need to filter this? This computation is being run on the AppSheet servers not the user’s devices so this should not effect their performance. However, I do think that my comments about the security filter still apply. Your Parameters table can have a filter which reduces the data based off if Run_Flag = TRUE. That way it is pre-filter (at the database level) before going into this automation routine. Does that answer your question or am I still missing it?

Kristian_ASK

No, I got it. Figured the security filtering out in the end. We could pass the project on the api call and it’sa all good. Thanks