Using Taifun SQlite extension - Error at Date column?

[Pal Csanyi]

Hi,

I am using the Taifun SQlite Extension to store datas into SQLite database.
The database is very simple; it's schema is like this:

CREATE TABLE IF NOT EXISTS "MyHours"
(id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
Date TEXT,
DayOfTimetable TEXT,
BlockHours TEXT,
Grade INTEGER,
Class TEXT,
Absentees TEXT,
LessonPlan TEXT,
LessonDiary TEXT,
Reminder TEXT);

My app is very simple; I am developing it by using MIT App Inventor 2.
The relevant Block for the database usage is like on the attached two images.

So far the database contain only one row - for experimentation purpose:

select * from MyHours ;

1|2018.02.20.|Tu|43-44|8|c|John Wayne, Roscoe Lee Browne|This is the text of the Lesson Plan.|This is the text of the Lesson Diary.|This is the text of the Reminder.

Note! The Date is in Hungarian format because my phone is set to the Hungarian language.

So to see whether can the user Ask for this row in the App, the user first must set the Date, DayOfTimetable, BlockHours, Grade and Class to these:
2018.02.20., Tu, 43-44, 8, c in this order.

The user can now press the 'Asks for.' button.
When the user ( it is me for now ) press it , the following error occure in the Absentees, LessonPlan, LessonDiary and Reminder TextBoxes:
ERROR: near ".20": syntax error (code 1): , while compiling: SELECT MyHours.Absentees FROM MyHours WHERE
 ( Date is 2018.2.20. AND DayOfTimetable is Tu AND BlockHours is 43-44 AND Grade is 8 AND Class is c)

What could be the problem here? How can I solve it?

Best, from Pal

[Chris Ward]

Assigned to Taifun.

[Pal Csanyi]

I solved this problem myself like on the attached image.
I changed a little the database too:

CREATE TABLE IF NOT EXISTS "Hours"

[Pal Csanyi]

As I say, I solved the previous problem, but new problem occured.
For this Forum I am developing in parallel the app in Hungarian and in English.
But now I am in hurry so I post here the image of the Block which is in Hungarian. See my attached image!
When I do that UPDATE the change is not written into database. Why?

[Ghica]

Should we be in a hurry too?

How to debug SQLite:

Create a copy of your database using the Firefox plugin.

Try your SQL there, until it works.

Maybe there was an error message?

Cheers, Ghica.

[Pal Csanyi]

Should we be in a hurry too?

No, not at all.
I partially solved the problem like on the attached image.
The only drawback is that that when I press the Update button in the application, and after thet I Load that database row back into application, I get quotation marks repeatedly added - when a comma is in the Text of the TextBox, like "John Wayne, Robert Redford". So after every Update there appear on more quotation out there, like this: """"John Wayne, Robert Redford"""". Look my second attached image.
How can I avoid these quotation marks?
And one more thing. If the TextBox is empty, at the Update I get error message, because of this: ...'SET column = WHERE'...
Note that that after column = there is empty, but there should be some value, right?

[Abraham Getzler]

That's a lot of WHERE clauses in your UPDATE query.

If a single one of them goes wrong, no update for you.

You have a unique key field in your table definition.

That should be all the WHERE clause you need.

And it will never be empty.

ABG

[Abraham Getzler]

Your double quote and comma problem exposes a data modelling flaw.

SQL data bases work best with data with single value fields.

Your student absences would usually be kept in a separate table,

with two columns, your unique key from you class session table

plus one student name.  If no students are absent from a session,

keep no rows.  If two students are absent, keep two rows, etc.

Investigate use of the SQL JOIN to list absent students.

ABG

[Pal Csanyi]

There are five WHERE clauses out there, and those will never be empty. These are Date, DayOfTimeTable, BlockHours, Grade and Class. These five values will be never empty because Date is always set, and others are ListPickers with Default values.
But that is not true for the values of four columns in the four SET commands out there. The values ( TextBoxes )  could be empty. That is the problem. In the SET command there can't be any empty values out there. So, how can I solve this problem?

[Abraham Getzler]

The values ( TextBoxes )  could be empty. That is the problem. In the SET command there can't be any empty values out there. So, how can I solve this problem? 

If the textboxes are empty, what is your intention for the Update?

1. don't update the columns for empty textboxes, leaving them at prior value, or
2. set the columns to empty string or zero? 

For case 1, you would build a SQL Update query clause by clause, JOINing

only those SET blocks for nonblank values

  a = JOIN(a,b)

For case 2, we need to see the effect of the blank value in the error message.

Did a numeric column require a 0 but got an empty string?

Did a text column require ' ' wrappers around its value?

ABG

[Pal Csanyi]

If the textboxes are empty, what is your intention for the Update?

1. don't update the columns for empty textboxes, leaving them at prior value, or
2. set the columns to empty string or zero?

If the textboxes are empty, my intention is the case
2. set the columns to empty string or zero.

For case 2, we need to see the effect of the blank value in the error message.
Did a numeric column require a 0 but got an empty string?

All textboxes corresponds to text columns, that is, there are only text columns out there in my SQLite database, except the id column and the Grade column which are INTEGER types. But, in my UPDATE command I don't want to update the id and Grade column at all.

Did a text column require ' ' wrappers around its value?

If a textbox contains text in which there is no comma in text out there, then the application stores this text data without "" quotes into database.
But, if a TextBox contains text in which there is a comma, then the application stores this text data with "" quotes into database.
My intention would be to store textual data without "" wrappers even if the text contains comma.

[Abraham Getzler]

As a work around, you can strip off those " characters using

a text replace block, each time you are about to update the 

student absences in the table.

The " characters probably come from a csv from row conversion block

where commas that are part of the data need to be distinguished from

commas that separate list items in the csv text format.

That's a good reason not to store lists in SQL table columns,

but instead to get the data model right and replace lists with

rows in a separate table.

ABG

[Pal Csanyi]

As a work around, you can strip off those " characters using

a text replace block, each time you are about to update the 

student absences in the table.

I just added these text replacement blocks, like on my attached TaifunSQLite_Block_07.png image.
This way I get only one " mark at the end of the text of textboxes, when repeatedly UPDATE the same text from textboxes.
The only drawback of this method is that, that after each UPDATE I get one more Space before the " mark, like on my attached UPDATE_when_comma_in_quotation_marks_repeatedly_added_02.png image.
I can show this behavior textually here too, like this:
"Name One, Name Two"
"Name One, Name Two "
"Name One, Name Two  "
"Name One, Name Two   "
"Name One, Name Two    "
and so on.

It is obvious that that " mark was replaced by Space at the end of text in the textbox. This is not good either. How can I replace the " mark with nothing?

That's a good reason not to store lists in SQL table columns,
but instead to get the data model right and replace lists with
rows in a separate table.

I agree with that, but what if I have no lists but sentences with commas in textboxes?

[Abraham Getzler]

It's not obvious to me why you would get an extra blank for the second "

but not for the first " of the pair.

Here's an extra Band-Aid you can apply, after the " replacement,

if you still want to go with patches...

Use the trim() text block.

http://appinventor.mit.edu/explore/ai2/support/blocks/text.html#trim

ABG

[Pal Csanyi]

It's not obvious to me why you would get an extra blank for the second "

but not for the first " of the pair.

This is not understandable to me either.

Use the trim() text block.

I just used it, and trim block solved my problem. Thank you very much for your help!

[Pal Csanyi]

The only problem remains when the text box is empty.

If the textboxes are empty, what is your intention for the Update?

    don't update the columns for empty textboxes, leaving them at prior value, or
    set the columns to empty string or zero?

For case 2, we need to see the effect of the blank value in the error message.

Did a text column require ' ' wrappers around its value?

I think in the case when a textbox is empty and do an UPDATE, I want to set the text columns to empty string.

How can I solve that problem, when run the UPDATE command?

[Abraham Getzler]

I think in the case when a textbox is empty and do an UPDATE, I want to set the text columns to empty string.

How can I solve that problem, when run the UPDATE command?

You are using the .SET block extension to the SQLLite extension.

What is the output of that block for an empty string?

I would expect the literal representation of an empty string

in a SQL statement to be '' (two apostrophes back to back).

ABG

 

[Pal Csanyi]

You are using the .SET block extension to the SQLLite extension.

What is the output of that block for an empty string?

I would expect the literal representation of an empty string

in a SQL statement to be '' (two apostrophes back to back).

Indeed. I get the output of that block by 'Do it' and get the output like on the attached image.
There is the '' (two apostrophes back to back) out there.
I just tried out in my application UPDATE when a textbox is empty. It works!
Then there is no problem anymore.
Again thank you very much for your help.

[Abraham Getzler]

What is the output of the .UPDATE block?

Does it match proper SQLLite UPDATE syntax?

Exactly what is the error message if it isn't accepted?

ABG

[Abraham Getzler]

I missed your note that everything works now.

I'll mark this thread closed, then.

Glad you got it working

(until you try to calculate student absence statistics,

when you will be back and sorry you did not

keep them in a separate table.)

ABG

[Pal Csanyi]

I'll mark this thread closed, then.

I will implement INSERT and DELETE commands as well into my application.
Maybe it would be nice if you were still waiting for closure.

Glad you got it working
(until you try to calculate student absence statistics,
when you will be back and sorry you did not
keep them in a separate table.)

I will consider this option, that is, to keep student absence in a separate table.
Thank you for reminding me of this.

[Ghica]

Pal,

What happened to the designs and diagrams you made about a year ago? It should have the proper 3rd normal form if I remember well.

Cheers, Ghica

[Pal Csanyi]

What happened to the designs and diagrams you made about a year ago? It should have the proper 3rd normal form if I remember well.

I'm a teacher in an elementary school. I do not have much time for programming. That is why I delayed its development.

[Ghica]

I meant you could have used his database model for what you are doing now.

Anyway, maybe you can still get some inspiration for improvements as Abraham suggests, by looking at your earlier work.

Cheers, Ghica.

[Pal Csanyi]

I agree to the Abraham proposal and have changed the database:

CREATE TABLE Orak (az INTEGER PRIMARY KEY ASC AUTOINCREMENT, Keltezés TEXT DEFAULT NULL, ÓrarendNapja TEXT DEFAULT NULL, BlokkóraSzáma TEXT DEFAULT NULL, Osztály INTEGER DEFAULT NULL, Tagozat TEXT DEFAULT NULL, ÓraVázlat TEXT DEFAULT NULL, ÓraNapló TEXT DEFAULT NULL, Emlékeztető TEXT DEFAULT NULL);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE Hianyzok (az INTEGER PRIMARY KEY ASC AUTOINCREMENT, HiányzóTanulók TEXT DEFAULT NULL, FOREIGN KEY(HiányzóTanulók) REFERENCES Orak(az));

However, I waive the INSERT and DELETE SQL commands.
My strategy is as follows: I create from a Bash script this database ( with the SQL commands as above). The database's 'Orak' Table will contain 180 rows with DEFAULT NULL values. Then I upload it to my Android application. Within the application I can SELECT and UPDATE records. The database's 'Orak' table will never have more than 180 rows. Then I keep student absences in the separate Table 'Hianyzok'.
This is a good strategy, is not it?

[Taifun]

you might want to read the answer to Q6 here https://puravidaapps.com/sqlite.php#q

Taifun

[Pal Csanyi]

Hello Taifun,

I was not clear enough, sorry.
After I created the Database with Bash script on my computer, I will upload it to MIT AppInventor 2 into my project out there.
Then I will use the database like  on my attached image.
I can then Build in AppInventor2 my App and install it on my phone.

[Abraham Getzler]

That data model doesn't look quite right.

The foreign key from the Absences table should be the 

ID of the class session from the Orak table, with a matching numeric type

matching the numeric type of the primary key of the Orak table.

You had assigned a text field named Missing Student as the foreign key (wrong.)

Unless you keep a master list of student names,

the absent student name should not be wired as a foreign key.

ABG

[Pal Csanyi]

That data model doesn't look quite right.

The foreign key from the Absences table should be the
ID of the class session from the Orak table, with a matching numeric type
matching the numeric type of the primary key of the Orak table.

If I understand your advice right then this is my result database now:

CREATE TABLE Hours (id_Hours INTEGER PRIMARY KEY ASC AUTOINCREMENT, Date TEXT DEFAULT NULL, DayOfTimetable TEXT DEFAULT NULL, BlockHours TEXT DEFAULT NULL, Grade INTEGER DEFAULT NULL, Class TEXT DEFAULT NULL, LessonPlan TEXT DEFAULT NULL, LessonDiary TEXT DEFAULT NULL, Reminder TEXT DEFAULT NULL);

CREATE TABLE Absences (id_Absences INTEGER PRIMARY KEY ASC AUTOINCREMENT, MissingStudent TEXT DEFAULT NULL, FOREIGN KEY(id_Absences) REFERENCES Hours(id_Hours));

Is this what you advised?

If I am right, then this is a one to one relationship? If yes, then it is not good for me, because I want one to many relationship; on one class session there could be zero, or more missing students. Right?

[Abraham Getzler]

Is this what you advised?

Yes

 

If I am right, then this is a one to one relationship?

No, it is a one to many relationship, like you want.

  

I want one to many relationship; on one class session there could be zero, or more missing students. Right?

Right.

ABG

 

[Pal Csanyi]

Meanwhile, I made another database with Ponyorm:

CREATE TABLE Hours (id INTEGER CONSTRAINT pk_Hours PRIMARY KEY AUTOINCREMENT, Date TEXT, DayOfTimetable TEXT, BlockHours TEXT, Grade TEXT, Class TEXT, LessonPlan TEXT, LessonDiary TEXT, Reminder TEXT);
CREATE TABLE Absences (id INTEGER CONSTRAINT pk_Absences PRIMARY KEY AUTOINCREMENT, MissingStudent TEXT, Hours INTEGER NOT NULL REFERENCES Hours (id));
CREATE INDEX idx_Absences__Hours ON Absences (Hours);

Is this right for my purpose too?

[Abraham Getzler]

I'm having trouble with the primary key definition for the Hours table:

(id INTEGER CONSTRAINT pk_Hours PRIMARY KEY AUTOINCREMENT, ...)

I can't understand why it needs two names : id and pk_Hours.

I assume they are names because they are in  lower case and by position.

Is one of them a keyword?

Is one of them generated ?

I don't have Ponyorm, nor Python, and I am concerned that Ponyorm is

inappropriate for this application because of additional two-way expression

of relations needed just for Python's sake.

I downloaded https://sqlitestudio.pl portable, to try to set up

the DDL you posted, but I can't find a DDL import section,

so modelling your DDL requires hand setup.

Maybe some one with a better SQLLite setup can give you a faster answer?

(I will need a few days to get back to this.)

ABG

[Abraham Getzler]

Also, I don't advise using TEXT data type for a field named Date,

when you have an SQLLite DATE type available that can give you the

benefit of chronological sorting and date formatting.

It would make more work for you in your app that could be handled

easier by the SQLLite engine.

ABG

[Abraham Getzler]

My SQLiteStudio export:

(I suspect I got trailing blanks into some of my field names,

and I did not bother with the index.)

ABG

--

-- File generated with SQLiteStudio v3.1.1 on Fri Mar 2 17:00:11 2018

--

-- Text encoding used: System

--

PRAGMA foreign_keys = off;

BEGIN TRANSACTION;

-- Table: Absences 

CREATE TABLE [Absences ] (

    id                INTEGER PRIMARY KEY AUTOINCREMENT,

    [MissingStudent ] TEXT    NOT NULL,

    [Hours ]          INTEGER REFERENCES Hours (id) 

                              NOT NULL

);

-- Table: Hours

CREATE TABLE Hours (

    id             INTEGER PRIMARY KEY AUTOINCREMENT

                           UNIQUE,

    Date           DATE,

    DayOfTimetable TEXT,

    BlockHours     TEXT,

    Grade          TEXT,

    [Class ]       TEXT,

    [LessonPlan ]  TEXT,

    [LessonDiary ] TEXT,

    [Reminder ]    TEXT

);

COMMIT TRANSACTION;

PRAGMA foreign_keys = on;

[Pal Csanyi]

Thank you very much Abraham, for help.

My Oraim.db is now like this:
CREATE TABLE Hianyzok (
az INTEGER PRIMARY KEY AUTOINCREMENT,
HianyzoTanulo TEXT NOT NULL,
Orak INTEGER REFERENCES Orak (az) NOT NULL );

CREATE TABLE Orak (
az INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
Keltezes DATE,
OrarendNapja TEXT,
BlokkoraSzama TEXT,
Osztaly TEXT,
Tagozat TEXT,
OraVazlat TEXT,
OraNaplo TEXT,
Emlekezteto TEXT );

or the English version MyHours.db:

CREATE TABLE Absences (
id INTEGER PRIMARY KEY AUTOINCREMENT,
MissingStudent TEXT NOT NULL,
Hours INTEGER REFERENCES Hours (id) NOT NULL );

CREATE TABLE Hours (
id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
Date DATE,
DayOfTimetable TEXT,
BlockHours TEXT,
Grade TEXT,
Class TEXT,
LessonPlan TEXT,
LessonDiary TEXT,
Reminder TEXT );

I created it with sqlite3 cli, and tried it with SQLiteStudio 3.1.1.
Now my UPDATE blocks changes and I do not know how to UPDATE the Oraim.Hianyzok table's HianyzoTanulo column with the Block that I attached here as an image.
My questions follows.
1. When I want to UPDATE the Absences table ( Hianyzok table ) I want to split names of missing students ( which are there in the TextBox field ) so I can store those names separately into Absences table's MissingStudent column. How can I do that with Blocks?
2. How can I set the WHERE clause? This is the Foreign key and I want to refer to the Hours ( Orak ) table's id. Moreover, in my app I SELECT a record from the Hours ( Orak ) table which has it's id. How can I use this id in these Blocks for this purpose, that is, for set the WHERE clause?

[Pal Csanyi]

2. How can I set the WHERE clause? This is the Foreign key and I want to refer to the Hours ( Orak ) table's id. Moreover, in my app I SELECT a record from the Hours ( Orak ) table which has it's id. How can I use this id in these Blocks for this purpose, that is, for set the WHERE clause?

I did it!
See my attached image for SELECT!
This way I get the names of missing students into textbox, but like this:
Name1
Name2

However, I would like to get those names into textbox like this:
Name1, Name2

How can I achive this goal?

[Taifun]

This way I get the names of missing students into textbox, but like this:
Name1
Name2

the result in this case is a 1 column table in csv format

convert the result into a list using the split block, just split at \n (new line), then use select list item blocks to get the values

set textbox1.text to select list item listResult

                                                       1

set textbox2.text to select list item listResult

                                                       2

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

Taifun

[Pal Csanyi]

This way I get the names of missing students into textbox, but like this:
Name1
Name2

However, I would like to get those names into textbox like this:
Name1, Name2

How can I achive this goal?

I did it!
I used the 'replace all text' Text block like this:
segment: '\n'  for the new line ( without '' )
replacement: ', ' note the SPACE after comma.

[Pal Csanyi]

Taifun,

I read your answer after I sent my own answer:

I did it!
I used the 'replace all text' Text block like this:
segment: '\n'  for the new line ( without '' )
replacement: ', ' note the SPACE after comma.

 So, my solution is not good enough? I am uncertain.
Anyway I do not understand you advice:

convert the result into a list using the split block, just split at \n (new line), then use select list item blocks to get the values

set textbox1.text to select list item listResult
                                                       1

set textbox2.text to select list item listResult
                                                       2

The split block is the Text 'split text at' block?
The select list item block is the 'select list item list' block?
The numbers 1 and 2 above are the indexes?

[SteveJG]

Your solution Pal is fine.   Simple and it works. :)   There are many ways to achieve a goal using App Inventor's programming tools.

Regards,

Steve

[Pal Csanyi]

Your solution Pal is fine.   Simple and it works. :)

OK
Now I'm struggling with the UPDATE solution.
How to UPDATE the Absences table ( Hianyzok ) with MissingStudents ( HianyzoTanulo ) names from textbox?
See my attached image!
The Orak table is the Hours table in English. That table contains id (az), Date (Keltezes), DayOfTimetable (OrarendNapja), BlockHours (BlokkoraSzama), Grade (Osztaly) and Class (Tagozat) columns.

I get in the Do it text exactly this:
Do It Result: UPDATE Hianyzok SET HianyzoTanulo = '(Gubi Dávid
  Bozsonyi Kristóf
  Robert Redford)' WHERE 1
;
---

But after I click on the Update button, and after that on the Select button of my app, I get back the names but without the last 'Robert Redfort' name.

This does not work. Why?

[SteveJG]

A missing single quote?

[Ghica]

Pal,
What are you thinking?
You want to recorde 3 missing students, most probably by adding 3 NEW rows to your table. If not, your model is wrong as Abraham pointed out.
However, with UPDATE you can only update existing rows, row that are already there, and that fulfill the condition in the WHERE clause. What does 1 mean there?

Actually, you want to do 3 INSERT statements. A very good idea would be to try this out first on an SQLite database using the Firefox plugin. It is much easier to debug.
If you have the SQL right, then you can try to implement this using the SQLite extension.
Cheers, Ghica

[Pal Csanyi]

A missing single quote?

No, because the Grade ( Osztaly ) is an INTEGER type.

[Pal Csanyi]

What are you thinking?

 The night I realized I had to use INSERT commands if rows not exists, and if exists, then maybe the INSERT OR REPLACE command.

Actually, you want to do 3 INSERT statements. A very good idea would be to try this out first on an SQLite database using the Firefox plugin. It is much easier to debug.
If you have the SQL right, then you can try to implement this using the SQLite extension.

The Firefox SQLite Manager extension no longer functions. See more here.
Indeed, it is much easier to try out these SQL commands on my SQLite database on my Gentoo linux system.
I am using sqlite3 CLI and SQLite Studio for this.
And yes, if I have the SQL right, then I will try to implement this using the SQLite extension

[Pal Csanyi]

I am trying to INSERT into Absences (Hianyzok) table the names of Missing Students (HianyzoTanulo) and the foreign key Hours (Orak).
I determined the Hours already, and it works. Now I want to get the names of Missing Students separately for use in the INSERT command.
In this I have achieved only partial results. See below.

I made a list from the Missing Students names from the textbox.
By using Do it, it's look good.

I made a 'for each item in list do' block that uses the list I made above.
This loop block should INSERT every name of Missing Student from the above list, and the Hours which is same for all items in the list.

I only do not know how to determine index in the 'select list item list / index' block for the actual list item in the loop?

[Ghica]

Yes, indeed, the Firefox addon does not work anymore. What a pity! There seems to be SQLite Studio (free or donation), but I did not try it.

I am totally mystified with your when.Frissit.Click block.

I see two extension, TaifunSQLite and SQLiteAddons. I know Taifun's SQLite extension, but what is the SQLiteAddons extension?

First of all, what is the result of the outermost call SQLiteAddons.SELECT etc. block? It should be a valid SQL statement, because you are going to use it in the call.TaifunSQLite.Execute sql block, but is it? And if yes, what exactly? Did you try to execute this statement? Did it have the expected result?

What exactly should the INSERT statement look like that you need?

By the way, if Hianyzok.Text contains something like "student name1, student name2, student name3", then you could make a list out of it like this:

Cheers, Ghica.

[Abraham Getzler]

You should get your az primary key value from the Azok (Hours) table in

a separate step before you start the loop though the absent student names.

That will simplify what you have to code considerably for the INSERT (az, studentName) steps.

ABG

[Pal Csanyi]

    I see two extension, TaifunSQLite and SQLiteAddons. I know Taifun's SQLite extension, but what is the SQLiteAddons extension?

One can read about SQlite Addons Extension here: https://puravidaapps.com/sqlite.php#q

    First of all, what is the result of the outermost call SQLiteAddons.SELECT etc. block?

It's result is:
Do It Result: SELECT az FROM Orak WHERE (Keltezes = '2018-3-2' AND OrarendNapja = 'P' AND BlokkoraSzama = '45-46' AND Osztaly = 5 AND Tagozat = 'b');
---

    Did you try to execute this statement? Did it have the expected result?

Yes, I did execute this statement in the sqlite3 cli. Yes, I get the expected result.
The result is the number 1.
Exactly this number 1 get the TaifunSQLite1.Execute sql block:

Do It Result: 1

---

What exactly should the INSERT statement look like that you need?

I think it should look like this:
INSERT INTO Hianyzok (HianyzoTanulo, Orak) VALUES ('Robert Redford',1);
or in English:
INSERT INTO Absences (MissingStudent, Hours) VALUES ('Káplár Áron', 1);

By the way, if Hianyzok.Text contains something like "student name1, student name2, student name3", then you could make a list out of it like this:

Yes it does contain almost that: "student name1 , student name2 , student name3 ". Note the extra space represented here as '[]' at the and of a 'student name1[]'.
So when I do 'Do it' I get result like on my attached image 'ListFromCSV_row_and_table.png' ( I tried 'list from csv row text' and 'list from csv table text' too).
So 'list from csv row text' can't do it right, except when I manually remove - when using AI Companion - these trailing extra spaces before commas.
In that case I get result like on my attached image 'ListFromCSV_row_and_table_02.png'.
What makes me disturb is that the list is not separated by commas or anything else.
So what is the right way to get the proper list?

[Pal Csanyi]

You should get your az primary key value from the Azok (Hours) table in

a separate step before you start the loop though the absent student names.

Should I get the az Primary key value from Orak (Hours) table within the 'for each item in list + get "HianyzoTanulok" / do' block?

[Abraham Getzler]

What makes me disturb is that the list is not separated by commas or anything else.
So what is the right way to get the proper list?

According to your blocks and the Do It results,

you had two names, name1 \n\n name2.

So if that is how you want your users to enter names, 

do a split at \n on the .Text value,

then ignore empty text items in the resulting list.

ABG

 

[Abraham Getzler]

Should I get the az Primary key value from Orak (Hours) table within the 'for each item in list + get "HianyzoTanulok" / do' block? 

No

ABG

 

[Pal Csanyi]

According to your blocks and the Do It results,

you had two names, name1 \n\n name2.

Do it results like this:
Do It Result: Gubi Dávid
Bozsonyi Kristóf
---

 I see here only one new line at the end of first name, and after second name, like this:

Do It Result: Gubi Dávid\n
Bozsonyi Kristóf\n
---

No?

So if that is how you want your users to enter names,
do a split at \n on the .Text value,
then ignore empty text items in the resulting list.

I want my users to enter names like this:

Gubi Dávid, Bozsonyi Kristóf

into textbox:
name1, name2, name3
So in this case what is advisable to do?

[Abraham Getzler]

I want my users to enter names like this:

Gubi Dávid, Bozsonyi Kristóf

into textbox:
name1, name2, name3
So in this case what is advisable to do?

This would be a good place to give thought to populating

a List Picker from the DISTINCT student names in a (not yet existing) 

table of student enrollments 

that would cover this class.

Wouldn't it be friendlier to your users to let them select names one at a time

from their class roster, instead of having to type them?

If you are not ready for that yet, ask the users to enter the names one at a time,

and have your app INSERT them into Absences one at a time, then refresh a ListView

from a SELECT from that Hour's Absence records.

ABG

[Abraham Getzler]

P.S.  Here is a sample doc showing how to 

pick multiple items from a list into another list ...

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

ABG

[Pal Csanyi]

This would be a good place to give thought to populating

a List Picker from the DISTINCT student names in a (not yet existing) 

table of student enrollments 

that would cover this class.

Wouldn't it be friendlier to your users to let them select names one at a time

from their class roster, instead of having to type them?

I will follow this advice of you. Thank you very much!
I modified the database so it is now like this:

CREATE TABLE Absences(
  id INTEGER PRIMARY KEY,
  MissingStudent TEXT NOT NULL,
  Hours INTEGER REFERENCES Hours(id) NOT NULL
);
CREATE TABLE Hours(
  id INTEGER PRIMARY KEY UNIQUE,

  Date DATE,
  DayOfTimetable TEXT,
  BlockHours TEXT,

  Grade INTEGER,

  Class TEXT,
  LessonPlan TEXT,
  LessonDiary TEXT,
  Reminder TEXT
);

CREATE TABLE Enrollments(
  id INTEGER PRIMARY KEY UNIQUE,
  StudentName TEXT NOT NULL,
  Grade INTEGER NOT NULL,
  Class TEXT NOT NULL
);

I think this is what you adviced, right?
In Hungarian also:

CREATE TABLE Hianyzok(
  az INTEGER PRIMARY KEY,
  HianyzoTanulo TEXT NOT NULL,
  Orak INTEGER REFERENCES Orak(az) NOT NULL
);
CREATE TABLE Orak(
  az INTEGER PRIMARY KEY UNIQUE,

  Keltezes DATE,
  OrarendNapja TEXT,
  BlokkoraSzama TEXT,

  Osztaly INTEGER,

  Tagozat TEXT,
  OraVazlat TEXT,
  OraNaplo TEXT,
  Emlekezteto TEXT
);

CREATE TABLE Beiratkozottak(
  az INTEGER PRIMARY KEY UNIQUE,
  TanuloNeve TEXT NOT NULL,
  Osztaly INTEGER NOT NULL,
  Tagozat TEXT NOT NULL
);

--
Pal

[Pal Csanyi]

I am following Abrahams (ABG) advice.
I did create two ListPickers (OsztalyTanuloi and Hianyzok -- Eng. StudentEnrollments and MissingStudents).
Then I've put together the following blocks as can be seen on attached image
ToSetListPickerElementsFromSQLiteDB_01.png

which failed with the error message shown on the image too:
Do It Result: ERROR: no such table: Beiratkozottak (code 1): , while compiling: SELECT TanuloNeve FROM Beiratkozottak WHERE (Osztaly = 5 AND Tagozat = 'a');
I can run successfully the SQL command in my sqlite3 cli wiht this database:
select TanuloNeve from Beiratkozottak where (Osztaly = 5 and Tagozat = 'a');
It gives me the names of Students from the Grade 5, Class 'a'.
Why does not work this block here?

Update:
sorry, I do not know why this happen, but after I upload again(!) my database to AppInventor2 out there, it works.
Sorry for the noise.

[Abraham Getzler]

I can only guess ...

You are working with 2 database environments, the cli on your desktop and the AI2 emulator,

and they got out of sync?

You tried to remove a row from the AI2 data base and removed the entire table?

ABG

[Pal Csanyi]

Abraham,

see my Update in my previous post.
I solve my last problem, which was to set ListPicker Elements.

You are working with 2 database environments, the cli on your desktop and the AI2 emulator,

and they got out of sync?

I have the Oraim.db database for this App on my desktop which I often upload into AI2.
In AI2 I do not use the emulator, but I connect my phone with AI Companion.
I am working with the Oraim.db database on my desktop with sqlite3 cli or with SQLiteStudio.

[Pal Csanyi]

P.S.  Here is a sample doc showing how to
pick multiple items from a list into another list

Thank you for this document. I am trying to use it in my app now.

But I just can't find the Math block: smaller than or equal.
Where is it?

[Abraham Getzler]

Pull in the < block,

then use its internal pulldown to change it.

ABG

[Pal Csanyi]

I found it, but it is look like different then in your document, right?

[Taifun]

see the documentation http://appinventor.mit.edu/explore/sites/all/files/UserGuide/blocks/math/equals.gif

Can't find the math block you're looking for in the built-in blocks?
Some math blocks are dropdowns which means that they can be converted into different blocks. Here's a list of what is included in each dropdown:
=, ≠, >, ≥, <, ≤ 

Taifun

[Abraham Getzler]

I found it, but it is look like different then in your document, right?

Right, I was working from (failed) memory. 

ABG

[Pal Csanyi]

I've worked a bit on this, and I've got this far:
it does not move Student Names to 'Missing' ListPicker from the 'Enrollment' ListPicker.
See my uploaded images.
What am I missing here?

[Pal Csanyi]

I found my error, now it is working.
Now the Missing Students names appeares in the included listpicker - sorted.

[Pal Csanyi]

Now I'm thinking of giving for the listpickers the names of missing students from the database.
If I fill the 'Missing' listpicker with names of Missing Students from database, then how to fill the 'Enrollment' listpicker with those names which are in the Enrollment - except with names which are already in the 'Missing' listpicker?

[Abraham Getzler]

Now I'm thinking of giving for the listpickers the names of missing students from the database.
If I fill the 'Missing' listpicker with names of Missing Students from database, then how to fill the 'Enrollment' listpicker with those names which are in the Enrollment - except with names which are already in the 'Missing' listpicker?

You will need to keep a third global list, REMAINING_Students,

consisting of all enrolled students minus the students that have been selected as missing.

So you will have a total of 3 global lists:

1. enrolled
2. missing
3. enrolled but not (yet) missing = remaining

[Abraham Getzler]

See this project for the logic ...

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

By the way, for a new question, a new thread is better.

This one is getting cumbersome.

ABG

[Pal Csanyi]

This thread can be closed now.
Thank you all for help!