[Solved] Spreadsheet query

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.
image

2 Likes

Hi, @jasepielli :wave:
Welcome to our Community :tada: :tada:

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

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

Thanks :blush:

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.

Instead of queries used to filter the data.

%D0%B8%D0%B7%D0%BE%D0%B1%D1%80%D0%B0%D0%B6%D0%B5%D0%BD%D0%B8%D0%B5

3 Likes

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?

1 Like

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

2 Likes

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:

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

Perhaps that’s what you’re looking for?

1 Like

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

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
Screen Shot 2020-04-10 at 3.43.07 PM

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’

3 Likes

Hi @jared,

this is übercool. I am speechless.

2 Likes

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.

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!

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?

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

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