How to use Fusiontables Get Rows?

[Nadin Doughem]

Hello,

I have successfully created my fusiontable using the method in pizza party and it working perfectly well.

However, now I need to get rows from my fusion table back to my application to be shown on a List View.

What should I do and how to retrieve the data?

I would be looking to hear from you real soon.

Thanks in advance.

[Taifun]

see the FAQ chapter fusiontables https://groups.google.com/forum/#!category-topic/mitappinventortest/2cd6Uz__xA0 ...

did you already try a search in the forum?

just use the GetRows method do receive your data in the Got Result event

see also the documentation http://ai2.appinventor.mit.edu/reference/components/storage.html#FusionTablesControl

GetRows(text tableId, text columns)

Gets all the rows from a specified Fusion Table. The tableId field (required) is the id of the Fusion Table. The columns field is a comma-separated list of the columns to retrieve.

you might want to try something and if you get stuck, post a screenshot of your relevant blocks...

Taifun

[Nadin Doughem]

Dear Taifun,

Thanks for the links, however I have been through them all.

Here is my blocks screenshot. First, the blocks circled in red are duplicates to blocks in other screen where I created the fusiontable for creating rows. I am not sure if they are necessary. Second, the blocks circled in blue gives me empty spaces as a result in the listview and not the data in the table.

I appreciate your time and I am looking forward to hearing from you soon.

[Taifun]

as already said, receive your data in the Got Result event

to convert the result into a list of lists, use the list from csv table block and assign that list of lists to the listview elements in the GotResult event

• How to work with Lists by Saj
• How to work with Lists and Lists of lists (pdf) by appinventor.org

you might want to think about which global variables you really need and which not...

Taifun

[Abraham Getzler]

Here's a more advanced sample project, 

my Pizza Trader app ...

https://docs.google.com/document/d/1jEx4wJsvGLfsMAjvY5ft2gT8LpTuS2ceLICO3frJaU4/edit?usp=sharing

ABG

[Nadin Doughem]

Thanks I am working on it now. I will check and post the result asap!
Thanks for your concern and time, really appreciated.

[Nadin Doughem]

Dear Taifun and Abraham,

Thanks for your help so far. I have followed your directions and I am able now to show my data. However, now I took the value chosen from the list view (which is one cell of the column) and transfered it to another screen using "openscreen with startvalue" block. Now I need the fusiontable in the new screen to import this row only by comparing the starting value with the values in the column "Name" in the table and retrieve the row that have this value.

So far, I reached this but the result is error.

What am I missing in here? Not to mention that I would like to have every column value in separate label afterwards. Thanks in advance.

[Abraham Getzler]

So far, I reached this but the result is error.

Can you show us the error message or error output?

ABG

 

[Nadin Doughem]

Sure, Sir

[Taifun]

do not use the = block... the = block will always provide true or false as result as you can see in the error message

WHERE false in your case...

use only text blocks and variable blocks to put together your where condition...

and don't forget to quotify the values, see also the Pizza Party tutorial

Taifun

[Nadin Doughem]

I don't understand, could you please illustrate for me how to do this?

[Taifun]

you also might want to read tip 1

1. Use different screens wisely
   Before starting to create another screen, first you should think about is it really necessary? See also Building apps with many screens and SteveJG's post about advantages/disadvantages, because in only one screen you also can use vertical arrangements to simulate different screens, just set the arrangements to visible = true/false as needed... See also Martyn_HK's example about how to use Tabs in App Inventor and another example from Cyd. 
   
   If you decided to use different screens, then you should switch them correctly, else you will run out of memory after a while...      
   The recommended method of switching screens in App Inventor
   
   
App Inventor works best if you use images whose size matches the size you want them to appear on your screen. If you import larger images into your app, your app may run out of system memory. Using Images with App Inventor

by Italo: First, you need to understand that the file size of an image is not the amount of memory it uses when it's being displayed. The file size is the compressed size, much like a zip or rar file. When viewed, the image needs to be decompressed.

For example, if your image says its file size is 100 kb, and its dimensions are 1024 x 768, 32 bit color, then that image uses over 3 mb of RAM (not 100 kb!) when you show it on the screen. ((1024 * 768 ) * 32) / 8 = 3,145,728 kb (3 mb)

Now, this is a mistake most people make when using arrangements as "virtual screens": They set different image components with their images loaded but hidden, instead of having only one image component and changing the picture according to the user's selection or app events, not knowing that apparently the hidden image components are also using the ram, (yes, even though they are invisible!).



2. Avoid redundancy

   Probably it helps to read chapter 19 - 21 in Dave's book http://www.appinventor.org/book2 to get an idea how to do DRY programming with App Inventor - Don't repeat yourself 
   
   

3. See SteveJG's monster list of tips and tricks
   
   
4. How to overcome the App Inventor project limit of 10 MB
   
   
5. Backup your project frequently
   

Taifun

---

Trying to push the limits of App Inventor! Snippets, Tutorials and Extensions from Pura Vida Apps by  Taifun. 

[Abraham Getzler]

An interesting mistake!

It looked so plausible, I shot right past it.

ABG

[Abraham Getzler]

(added to long overdue Mistakes section of FAQ Part 1)

https://groups.google.com/forum/#!msg/mitappinventortest/wqJin0qO8M0/8xUafdROAgAJ

ABG

[Nadin Doughem]

Thank you so much, it is now fixed and I could go beyond.

One more question please, in the pizza trader tutorial, how did you define the row_id on page 10? I need it to be associated with the same row I am working on. Can't I say WHERE Name='sth' ?

Thanks to you both again and again!

[Abraham Getzler]

The rowid is obtained as a result of selecting an Element from the List Picker,

which has been populated from a SELECT operation.

https://docs.google.com/document/d/1jEx4wJsvGLfsMAjvY5ft2gT8LpTuS2ceLICO3frJaU4/edit#heading=h.i9950m8cpr1b

(edit)

Each row in a Fusion Table has a unique rowid value, supplied by

Fusion Tables when it is INSERTed.

See https://developers.google.com/fusiontables/docs/v2/sql-reference#updateRow

for UPDATE syntax.  Notice it requires the rowid.

You can SELECT by Name, if you like, and get the rowid from its results,

assuming it is unique by name.

ABG

[Nadin Doughem]

It worked pretty well when I adapted this

However, I want it to get to change the row every time. So I want to take it from column Name= get start value so I did this

 but it showed me this error

What am I missing here :(

[Taifun]

For UPDATE statements the first step to be done is to get the ROWID of the row to be updated with a SELECT statement. The second step is to do the UPDATE.

what about doing a search in the forum? there are loads of threads talking about this...

for example these threads ...

Taifun

[Abraham Getzler]

Fusion Tables are not as powerful as other SQL implementations.

Don't expect everything that works in other SQL environments to work in Fusion Tables.

See the Fusion Tables section of this FAQ, especially the Fusion Tables SQL Syntax link ...

https://groups.google.com/d/msg/mitappinventortest/wqJin0qO8M0/8xUafdROAgAJ

ABG

[Nadin Doughem]

How to illustrate the SELECT statement and how to define the ROWID? I can see in the pizza trader tutorial that you used "get global rowid" without previously defining it. Please illustrate how to define it as I can't find it in any of the forum topics, neither do I able to understand this line to apply it: 

 

The ID of the row you want to update. A quoted string. To get the row ID, perform a SELECT before the UPDATE.

[Taifun]

1. get the rowid:

SELECT ROWID FROM <tableid> WHERE Name = <get start value>

2. do the update

UPDATE tableid SET Status = quotify(response) WHERE ROWID = <rowid received in step 1>

Taifun

[Abraham Getzler]

I added some explanation to https://docs.google.com/document/d/1jEx4wJsvGLfsMAjvY5ft2gT8LpTuS2ceLICO3frJaU4/edit#heading=h.trt1kyb1ewfk

explaining where the rowid came from.

I hope that makes my example clearer.

ABG

[Nadin Doughem]

Okay things are improving around.To start with, I eased things up by following your tips regarding getting them all in one screen. Afterwards, I have done the two steps Taifun mentioned successfully which is illustrated below in the green box.

However, when I adapted the rowid from ABG added explanation from the pizza trader I reached this illustration in the red box, and as I didn't realize their initial values, I did what is in the small red square.

This is how my fusiontable looks like

And yet the error persists :(

Please help and illustrate your answer in blocks if possible. I really appreciate both your help so far.

[Taifun]

It would really help if you provided a screenshot of your relevant blocks, so we can see what you are trying to do, and where the problem may be.

[Abraham Getzler]

The rowid value arrives via snail mail,

in the ftbName.ResultsAvailable(?) event,

not instantaneously like you assumed.

I need a cute name for this error type, for the FAQ Mistakes section.

ABG

[Nadin Doughem]

Dear Taifun,
These are the blocks I am mentioned in my last reply it was hidden in the quoted text, I am sorry.

Dear ABG,

I would be happy to suggest some cute names for this error as soon as I reach a solution :D

Can you please tell me how can I solve my issue by using blocks as I don't understand this: "ftbName.ResultsAvailable(?) event" please illustrate the solution using blocks.

[Taifun]

as ABG already said...

you have to move these blocks into the FusiontableControl.GotResult event...

the fusiontable control component works asynchronously!

Taifun

[Abraham Getzler]

See http://ai2.appinventor.mit.edu/reference/components/storage.html#FusionTablesControl

especially

Events

GotResult(text result)

Indicates that the Fusion Tables query has finished processing and returned with a result. The result of the query will generally be returned in CSV format, and can be converted to list format using the "list from csv table" or "list from csv row" blocks.

You can't complete this project without this.

ABG

 

[Abraham Getzler]

In case you need more ideas on getting the rowid ...

ABG