Cursussen/Courses Codesnippets     Top 
B4A-budget - lists and db_dialog


1. clv item layouts
Now that we know that the database can be used we focus our attention on the layout of the list items.
Click on the B4XMainPage tab in the IDE.
Go to the designer window and create a new layout file. Give it the name clv1_item.
lbl1 label has the following dimensions: width 120 height 30
lbl2 label has the following dimensions: width 145 height 30
lbl3 label has the following dimensions: width 316 height 30
lbl4 label has the following dimensions: width 316 height 20
lblcurr label has the following dimensions: width 50 height 30
The panel1 has a width of 320 and a height of 80.
Save the file and save the file also as clv2_item.
In this tutorial the layouts from the expenses and the earnings are the same. If you want to keep different information about the expenses than about the earnings then by creating 2 separate layouts you will be able to do so.


2. db_dialog layout
In the designer window you can also create a dialog layout for the database operations on the expenses or earnings.
This is how the layout looks like:
The dimensions for the lbldate are width 120 height 50.
The btndate has a width and height of 50.
The ftf1 field has the following dimensions: width 145 height 50.
The cbx1 combobox has a width of 310 and a height of 60.
The ftf2 field has a widht of 310 and a height of 50.
The label1 is placed on top of the cbx1 combobox and has a width of 160 and a height of 20.
The label2 is placed on top of the lbldate and has the following dimensions: width 120 height 20.
Leave some room (height 30) on the top of the layout for a title bar in the dialog.
Save the db_dialog layout file.
For the category operations we use a different dialog. Let's create that file (cat_dialog) as well.
Here is the simple layout:
The ftf1 field is positioned 30 from the top and has a width of 300 and a height of 50. The ftf2 field has the same dimensions.
Save the cat_dialog layout file.


3. calendar db_dialog
In the db_dialog layout we placed a calendar button to select a date.
For this to work we need to repeat the steps we took in the SettingsPage.
Click on the SettingsPage tab in the IDE and copy the code starting from the subroutine datepicker_dialog until the end of that page.
Click on the B4XMainPage tab and paste the code below the last line of code from that page.
Copy and paste the declarations and initializations as described in the section 'settings layout' - 'Access the dialog' from the SettingsPage to the B4XMainPage.
In the datepicker_dialog remove the lines with the setdate tests.
And put these 2 lines in their place:
		DateTime.DateFormat = "yyyy-MM-dd"
		lblseldate.Text = DateTime.Date(cal.lblticks)


4. show dialogs
Now let's show the dialogs.
The db_dialog will be used when the user taps on the Add button or on a customlistview item.
The cat_dialog is used in the Category page to add, change or delete records from the category table.
Click on the B4XMainPage tab and open the designer window. Open the db_dialog file en generate the members (Tools / Generate Members).
Click on the CategoryPage tab in the IDE.
Open the file cat_dialog and generate the members for that layout.
Back in the B4XMainPage we need to initialize the variables.
Add a declaration (in Class_Globals subroutine) for the db_dialog:
	Public dbdialog As B4XDialog
In the B4XPage_Created add these lines:
	dbdialog.Initialize(Root)
	dbdialog.PutAtTop = True
	dbdialog.BackgroundColor = Colors.LightGray
	dbdialog.BodyTextColor = Colors.Blue
Now write the code to show the dialog.
Do you remember how? You can find examples in the tutorial B4A-lists
Here is the code sofar (it will change later):
Private Sub show_add_dialog(title As String,title_color As Int)
	pnl1 = setup_dialog("db_dialog"," Add " & title,title_color)
	lbldate.Text = ""
	ftf1.HintText = "Amount (" & strcurrency & ")"
	ftf1.update
	ftf2.HintText = "Description (optional)"
	ftf2.update
	ftf1.text = ""
	ftf2.Text = ""
	Dim rsub1 As ResumableSub =  dbdialog.ShowCustom(pnl1, "OK", "", "CANCEL")
	Wait For (rsub1) Complete (Result As Int)
	If Result = xui.dialogResponse_Positive Then
		Log(lbldate.Text)
		Log(ftf1.Text)
		If ftf1.Text <> "" And lbldate.Text <> "" Then
			add_record(strtablename, Array As String(lbldate.Text,"1",ftf2.Text,ftf1.Text))
		Else
			xui.MsgboxAsync("Date and amount cannot be empty!","Add " & strtablename)
		End If
	End If
End Sub

private Sub setup_dialog(strlayout As String,lbltext As String,lblcolor As Int) As Panel
	Dim pnl As B4XView = xui.CreatePanel("")
	pnl.SetLayoutAnimated(0dip, 0dip, 0dip, 340dip, 200dip)
	pnl.LoadLayout(strlayout)
	Dim lbl As Label
	lbl.Initialize("")
	lbl.Text = lbltext
	lbl.TextSize = 18
	lbl.TextColor = Colors.Black
	lbl.Color = lblcolor
	pnl.AddView(lbl,0dip,0dip,340dip,30dip)
	Return pnl
End Sub
To call the subroutine add these lines to the sbtn1_Click subroutine:

	strtablename = "expenses"
	show_add_dialog("Expense",Colors.ARGB(100,249,177,241))
You can change the existing lines by putting them in comments (select the lines, Edit / Block Comment).
If you run the app now the db_dialog will be displayed.
When you tap on the date icon in the db_dialog the datepicker_dialog will appear on top of the db_dialog.
That is O.K. and after you selected a date (displayed in the label between the 2 buttons) the datepicker_dialog is closed.
But now the code continues and returns to the main thread and not to the db_dialog.
We change the code from the datepicker_dialog. Add after the line that sets the lbldate.text the following code:
		lbldate.Text = DateTime.Date(cal.lblticks)
		' resume the db_dialog
		Dim rsub1 As ResumableSub =  dbdialog.ShowCustom(pnl1, "OK", "", "CANCEL")
		Wait For (rsub1) Complete (Result As Int)
		If Result = xui.dialogResponse_Positive Then
			Log(lbldate.Text)
			Log(ftf1.Text)
			add_record(strtablename, Array As String(lbldate.Text,"1",ftf2.Text,ftf1.Text))			
		End If
This code shows the datepicker_dialog again because we use the pnl1 variable from the datepicker to show the db_dialog.
After we change the pnl1 in the datepicker_dialog to pnlcal the db_dialog will be displayed.
Declaration in Class_Globals:
	Private pnlcal As Panel
Initialization in B4XPage_Created:
	pnlcal.Initialize("")
Changes in the datepicker_dialog subroutine:
Sub datepicker_dialog
	lblseldate.Text = ""
	pnlcal.Color = Colors.White
	pnlcal.SetLayoutAnimated(0dip, 0dip, 0dip, 278dip, 368dip)
	pnlcal = cal.set_date_picker(displaymonth,displayyear)
	Dim rsub1 As ResumableSub =  caldialog.ShowCustom(pnlcal, "OK", "", "CANCEL")
...
This should fix that issue.
The cbx1 combobox will be filled in the next section.
Now insert the show_change_dialog subroutine into the B4XMainPage code before the datepicker_dialog subroutine.

Private Sub show_change_dialog(rowid As Int, title As String, title_color As Int)
	Dim pnl1 As Panel = setup_dialog("db_dialog"," Edit/Delete " & title, title_color)
	ftf1.HintText = "Amount (" & strcurrency & ")"
	ftf1.update
	ftf2.HintText = "Description (optional)"
	ftf2.update
	Dim rsub1 As ResumableSub =  dbdialog.ShowCustom(pnl1, "SAVE", "DELETE", "CANCEL")
	Wait For (rsub1) Complete (Result As Int)
	If Result = xui.dialogResponse_Positive Then
		If ftf1.Text <> "" And lbldate.Text <> "" Then
			change_record(strtablename, Array As String(lbldate.Text,"1",ftf2.Text,ftf1.Text),rowid)
			B4XPage_Appear
		Else
			xui.MsgboxAsync("Date and amount cannot be empty!","Change " & strtablename)
		End If
	else if Result = xui.dialogResponse_Negative Then
		remove_record(strtablename,rowid)
		B4XPage_Appear
	End If
End Sub
To show the dialog add code to the clv1_ItemClick event subroutine:
	strtablename = "expenses"
	show_change_dialog(1,"Expense",Colors.ARGB(100,249,177,241))
When you run the app now it appears that everything is working. But, after a date selection in the datepicker_dialog the db_dialog is set to the add version (OK and CANCEL buttons) and not to the change version (SAVE, DELETE and CANCEL buttons).
Let's insert a test in the datepicker_dialog subroutine.
We use a public variable called old_amount and set it to empty in the show_add_dialog. In the show_change_dialog this variable will be set to the value from the database field of the expenses or earnings table.
In Class_Globals:
	Public old_amount As String
In show_add_dialog below the setup_dialog(...) statement:
	old_amount = ""
In show_change_dialog below the setup_dialog(...) statement:
	old_amount = "100"
In the datepicker_dialog above and below the Wait For statement:
	If Result = xui.dialogResponse_Positive Then
		DateTime.DateFormat = "yyyy-MM-dd"
		lbldate.Text = DateTime.Date(cal.lblticks)
		' resume the db_dialog
		If old_amount = "" Then
			Dim rsub1 As ResumableSub =  dbdialog.ShowCustom(pnl1, "OK", "", "CANCEL")
		Else
			Dim rsub1 As ResumableSub =  dbdialog.ShowCustom(pnl1, "SAVE", "DELETE", "CANCEL")
		End If
		Wait For (rsub1) Complete (Result As Int)
		If Result = xui.dialogResponse_Positive Then
			Log(lbldate.Text)
			Log(ftf1.Text)
			If old_amount = "" Then
				add_record(strtablename, Array As String(lbldate.Text,"1",ftf2.Text,ftf1.Text))
			Else
				change_record(strtablename, Array As String(lbldate.Text,"1",ftf2.Text,ftf1.Text),1)
			End If
		End If
	End If
Now the show_change_dialog will be displayed if the old_amount is not empty.


5. fill the combobox
To fill the cbx1 combobox we need to get the list of categories using the database table category.

private Sub get_cbx1_items(upd_id As Int) As List
	Dim lst As List
	lst.Initialize
	Private sql As String = "SELECT rowid, name, reference FROM category"
	Private rs1 As ResultSet = db.select_query(sql)
	Do While rs1.NextRow
		lst.Add(rs1.GetString("name") & " " & rs1.GetString("reference"))
To fill the list with items we use a select SQL statement and loop through the records in the resultset.
The value in the cbx1 combobox will be the rowid of the category table.
For this we use a cbx1rowids list. Don't forget to declare the public variable cbx1rowids as a list and initialize it in B4XPage_Appear below fill_clv2.
If we provide a rowid in the argument from the get_cbx1_items subroutine then we can select that item in the cbx1 combobox.

		cbx1rowids.Add(rs1.GetString("rowid"))
		If upd_id = rs1.GetString("rowid") Then
			SelectedIndex = cbx1rowids.IndexOf(rs1.GetString("rowid"))
		End If
	Loop
	Return lst
End Sub
Declare a public variable SelectedIndex as int and a public variable SelectedItem as String.
When the user selects a different item in the combobox then the values of these 2 variables are changed.

Sub cbx1_SelectedIndexChanged (Index As Int)
	SelectedIndex = Index
	SelectedItem = cbx1.SelectedItem
End Sub
In the dialogs the combobox needs to be filled.
This is the code you can add to do that (below the line ftf2.update in the show_add_dialog subroutine):
	cbx1rowids.Clear
	Private cbx1lst As List = get_cbx1_items(-1)
	If cbx1lst.Size > 0 Then
		cbx1.SetItems(cbx1lst)
		cbx1.SelectedIndex = 0
		SelectedIndex = 0
		SelectedItem = cbx1.GetItem(SelectedIndex)
	Else
		Return
	End If
And this is the code for the show_change_dialog subroutine (below ftf2.update).
	cbx1rowids.Clear
	Private cbx1lst As List = get_cbx1_items(1)
	cbx1.SetItems(cbx1lst)
	cbx1.SelectedIndex = SelectedIndex
	SelectedItem = cbx1.GetItem(SelectedIndex)


6. Compile and run
Time to run the app.
Tap on the Add button of the expenses tab.
Nothing happens!
Tap on the first item in the list.
The app throws an index out of bounds exception!
The reason for this is that the category table is empty and therefore the cbx1 combobox list is also empty.
Let's go fix that in the next section.