[Solved] Spreadsheet query

[jasepielli]

Do you have plans to implement spreedsheet SQL queries like GetRowsWithConditions?
I am looking for a fusiontables like storage for my app that I have implemented in MIT AppInventor.

[kartik14]

Hi, @jasepielli
Welcome to our Community

If you are looking for some spreadsheet-like database, Airtable is available for Thunkable X.

Personally, I prefer Airtable
It’s a simple to use, secure DB.

Thanks

[jasepielli]

I am using Airtable and that article is how I set it up.
Do you have an example screenshot of the Thunkable X block with a row select with condition?
Thanks for your response.

[actech]

Instead of queries used to filter the data.

[domhnallohanlon]

Alternatively what you can do is create a new table in your base and do the queries/filtering Airtable-side.

If you’re just doing simple functions then it might be enough just to put them in their own column?

[Mark]

@domhnallohanlon

Alternatively what you can do is create a new table in your base and do the queries/filtering Airtable-side

You can also create a new, filtered, view of the same table and use that.

All that said, I would also suggest also making a feature request to add filtering to the Spreadsheet component. You can do that here.

-Mark

[Michael_Rogulla]

Hi @Mark and other interested parties,

I believe that this question has not yet been resolved. Views are a great way to filter data, but as far as I know, the views do not work dynamically, they only deliver static filtering / sorting.

Have a look at my issue:

Airtable: query functions with search conditions

[domhnallohanlon]

@Michael_Rogulla Jared had more info on filtering Airtable data here:

Airtable tutorial

Working with airtable can be confusing. This sample app features: Filtering and viewing data Dynamically changing the filter Find a row number Pull specific info from a cell in a row Updating 1 cell inside a particular row Adding Rows Deleting Rows Remix Link: https://x.thunkable.com/copy/15949b2e4aec44fe2eb7fd1a72c47414 [UPDATED LINK] Live Web App: https://thunk.app/w/bbHuW-EAv An explanation of how to build this app will be posted on my website sometime this week along with the w…

Perhaps that’s what you’re looking for?

[Michael_Rogulla]

Hi @domhnallohanlon, thanks for the reply. The tricks that @jared implemented in his app are known to me. I suggest another approach, I want to get the result from a filtering from airtable directly. Let me try to explain the difference:

• let’s say we have in airtable a list of all restaurants in the US
• you want to show in your app restaurants located in for example San Francisco.
• you say, I have to download the whole list and apply a filter (with a loop)
• I would like to have a function that says “get all rows from that list of restaurants only in San Francisco” instead of getting all rows and filtering away what you do not want to know.

The first function “get the row where [column “abc”] contains [“value”]” is helpful with linked fields, because one has to look for the _id very often.
The second function “get all rows where […]” is also very helpful, just have a look at all those applications with a SQL-Database in the background.

See my issue #439: https://github.com/thunkable/thunkable-issues/issues/439

[jared]

Hi @Michael_Rogulla,

I accomplished this task today using the Airtable API.

using the URL located in teh CURL,

i add those into the property designer

i use the URL up the question mark
I add a parameter of filterByFormula and use the quickchart formula to filter by an email

then for the header put Authorization as the property and the value is “Bearer YOURKEYHERE”

i checked the response with this

i will be addin gthis feature into my app. let airtable do the fitltering. save performance on the app/phone

the next step is to add that parameter in dynamically using the text join block and in my case the userID blcok. I also want to add a second filteryBy parameter like name = ‘email’ & cell2 = ‘SomethingElse’

[Michael_Rogulla]

Hi @jared,

this is übercool. I am speechless.

[jared]

The only thing to be concerned with is pagination. If the filter returns more than 100 records you need to set up a way for the app to retrieve al records. When getting filtered results, airtable only returns 100 results at a time. Then you have to request the next 100 and again and again if there are hundreds of filtered results.

[Michael_Rogulla]

Hi @jared . For my next project I could use it for user management:

• do the basic authentication with the component “Sign In” in Thunkable, validate the LogonID.
• after authentication, get the credentials from airtable with “filterByFormula={Name}=LoginID”.

This is much simpler than getting all users from an airtable and search for one LogonID.

Thanks again!

[Michael_Rogulla]

Hi @jared,

would you please be so kind and test your app again?

• two weeks ago, the approach with “filterByFormula” worked perfectly. I defined a web_api, added a Query-string in a block and executed the “web_api get…”. The results were filtered.

Today I am not sure if I make a mistake:
– when I change the query string in a block, I get unfiltered data from airtable (all records). This is not what I want and it worked before.
– when I put the query string into the “query parameters” section in design modus, the results are filtered, as expected.

Maybe thunkable changed anything? Could you check before I open an github issue?

[Apps_a_medida]

Hello everyone, I have a question … how to delete rows in data source? doesn’t have that block … thank you.
This option is also not in db_local

[dhm.thacker1]

Could you please share the blocks…i am not able to call the data from the json…